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

Reply via email to