On Wed, Jun 3, 2009 at 11:22 PM, Nikolaus Rath <nikol...@rath.org> wrote: > Nuno Lucas <ntlu...@gmail.com> writes: >> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath <nikol...@rath.org> wrote: >>> Nuno Lucas <ntlu...@gmail.com> writes: >>>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath <nikol...@rath.org> wrote: >>>>> Hello, >>>>> >>>>> How can I determine the rowid of the last insert if I am accessing the >>>>> db from different threads? If I understand correctly, >>>>> last_insert_rowid() won't work reliably in this case. >>>> >>>> It should work if you do: >>>> >>>> BEGIN >>>> INSERT ... >>>> last_insert_rowid() >>>> END >>> >>> That would be very nice. But does "it should work" mean that you know >>> that it works (and it is documented and guaranteed)? The above sounds a >>> bit uncertain to me... >> >> It just means I'm too old to assume anything is certain. The Universe >> is always conspiring against you ;-) >> >> What I mean is that if it doesn't work, then you found a bug, most >> probably in your own code. > > Well, now you are in direct contradiction to Igor who says that it does > not work:
Let's try end this confusion... If you open a sqlite shell and do: SQLite version 3.6.14.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> begin; sqlite> begin; SQL error: cannot start a transaction within a transaction sqlite> So you see that you can't start a second transaction using the same connections handle. The last_insert_rowid variable is a property of the connection, so they can only be different in different connections as they relate to the last INSERT statement using that single connection. You question involves the same connection handle used by different threads, so now you know they share the same last_insert_rowid variable and that you can't nest transactions using the same handle. If you don't use transactions then you have to think of this variable as a shared resource, and as such protect it's access using whatever means you have at your disposal (being mutexes, semaphores, critical sections, etc). But, if you have a thread doing: BEGIN INSERT ... last_insert_rowid() ... END You now know that a second thread executing the exact same code should (barring bugs in sqlite code, bugs in your code, bugs in the compiler, bugs in all the code your application and sqlite call, random bits toggled by action of cosmic rays or similar occurrences) automatically fail on the first BEGIN statement if executed in the middle of the first thread transaction, so you simply can't even get the chance of running the last_insert_rowid() function. Hope this clears your doubts. Regards, ~Nuno Lucas > > ,---- > | >> If all threads share the same connection, it is your responsibility > | >> to make "insert then retrieve last rowid" an atomic operation, using > | >> thread synchronization mechanism of your choice. Just as with any > | >> access to shared data. > | > > | > Is BEGIN ... COMMIT sufficient for that? > | > | No. Transaction is also maintained per connection. Starting a > | transaction would prevent other connections from making concurrent > | changes, but wouldn't block other threads using the same connection. > `---- > > > Any third opinions or references to documentation? > > > Best, > > -Nikolaus > > -- > »Time flies like an arrow, fruit flies like a Banana.« > > PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
