In sqlite3, can a selection succeed in an insert transaction?

Question

I am starting a transaction, which is to insert multiple records into a table. Is it possible to select the last inserted record from the database before the transaction?

Answer 1

Yes.

Inside the transaction, your application sees everything.

No other transaction, however, sees any part of the change.

The point of the transaction is to make the sequence of instructions seem like one atomic change in the database.

If you commit, all transaction statements will be completed, and everyone else will be able to see the effects.

If you rollback, no instruction in the transaction is complete, and no changes to the database occur.

Not all statements may be part of a transaction, BTW. DDL (Create and Drop, for example) will complete any previous transaction.

Answer 2

Yes, during or after a transaction, you can use the last_insert_rowid () function.

The last_insert_rowid () function returns the ROWID of the last row inserted from the database connection that calls the function.

In other words:

SQLite version 3.6.23 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table T (C); sqlite> insert into T values ('hello'); sqlite> select last_insert_rowid(); 1 sqlite> BEGIN; sqlite> insert into T values ('test 2'); sqlite> select last_insert_rowid(); 2 sqlite> select rowid,* from T; 1|hello 2|test 2 sqlite> ROLLBACK; sqlite> select last_insert_rowid(); 2 sqlite> select rowid,* from T; 1|hello sqlite>