Last try... On Sat, Jun 6, 2009 at 1:30 AM, Nikolaus Rath<nikol...@rath.org> wrote: > John Machin <sjmac...@lexicon.net> writes: >> On 6/06/2009 8:19 AM, Nikolaus Rath wrote: >>> John Machin <sjmac...@lexicon.net> writes: >>>>> Now I'm confused. I want to know if it will be sufficient to wrap my >>>>> last_insert_rowid() call between BEGIN .. and END in order to make it >>>>> return the rowid that was last inserted by the same thread even if >>>>> multiple threads are using the same connection (but different cursors). >>>>> >>>>> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is >>>>> saying that it's not sufficient, I need to use additional mechanism.
Forget all I and Igor said... You have 2 choices to have consistent last_insert_rowid() after an INSERT using threads sharing the same connection handle: 1) Wrap the "INSERT..." ==> "last_insert_rowid()" inside a BEGIN/END transaction. Advantages: - No need for mutexes or other synchronization primitive - Other threads that don't use transactions can still read from the database if they don't require the last_insert_rowid() value (this can be of no great importance as they will still find the database busy while doing the insert, which is probably the big majority of the time of the operation) Disadvantages: - You need to check for the nested transaction error and retry - Implies 2 more automatic mutex locks done by the sqlite library for the BEGIN and END (probably negligible time on most cases but maybe important when dealing with a lot of threads) 2) Wrap a mutex or other synchronization primitive around the "INSERT" ==> "last_insert_rowid()" calls. Advantages: - 1 less mutex held (although this one has a longer duration) - No need to check for a specific error condition. Other threads just wait until they can proceed. Disadvantages: - Other threads are locked for the full duration of the event (as in the point 1, probably also not that important, as the insert will make most of the time) - You need to share the mutex to all threads involved and removes some flexibility for the algorithm design. The pros/cons relating to the mutexes held can very well be inverted depending on the specific code being run. If you don't use the same connection handle, then all this is moot, as all synchronization is done at the file level. I didn't try to be exhaustive. That is your job. Now, if you still have doubts after this I don't think I can be of any help, as that makes it obvious my communication skills are lacking. Regards, ~Nuno Lucas _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
