On Tue, Nov 16, 2010 at 04:08:16PM +0300, Ruslan Mullakhmetov scratched on the wall: > i got following contradictory replies to my question > > > if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select > > last_insert_rowid() as li;" would be it atomic? or it anyway would suffer > > from threads? > > from borgan: > > Hi, i think this will probably be "atomic". > > What i mean is the transaction will aquire EXCLUSIVE lock (because of > > the insert command) and it will keep this lock until commit, which > > takes place after the last select and last semicolon. > > > > So i believe no other threads should be able to do anything with your > > db until the whole query finishes. > and from Kees Nuyt: > > The last_insert_rowid() function returns the ROWID of the last row > > insert from the database connection which invoked the function. > > So, yes, it suffers from threads if the threads use the same > > database connection. > > whom to trust?
The docs, which make this fairly clear: http://sqlite.org/lang_corefunc.html#last_insert_rowid The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function. http://sqlite.org/c3ref/last_insert_rowid.html [...] This routine returns the rowid of the most recent successful INSERT into the database from the database connection in the first argument. If no successful INSERTs have ever occurred on that database connection, zero is returned. [...] So Kees Nuyt is correct... the value returned by last_insert_rowid() is "per database connection". It is not a global. It is not a per-database-file value. It is not a pre-thread value. If each thread is using its own, private database connection, then you're safe. If multiple threads are sharing a database connection and using the "serialized" thread mode, then you have a race condition as it is possible for the database connection to be handed off between statements. The value returned by last_insert_rowid() may be invalid by the time it is called. In either case, these two operations are never "atomic", in the fullest sense of that word. last_insert_rowid() is not bounded by transactions in the way statement processing is. > as for Kees Nuyt reply, did you toke int account that "select > last_insert_rowid() " > and insert query combined in single query and executed via single call > of sqlite3_exec()? Using sqlite3_exec() changes nothing. It just runs the normal prepare/step interfaces. It does not provide any kind of atomic processing above and beyond autocommit. It doesn't even wrap multiple statements in a transaction. It is just a syntax short-cut and provides no significant functionality beyond convenience. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
