> Does that seem correct? Yes.
> If so, my options are: > > 1) rollback/commit one of the transactions > > 2) use begin exclusive That's correct, but it's better to be 'begin immediate' than 'exclusive'. > I don't think the second one will work, since I need nested transactions > and the savepoint syntax doesn't seem to support the exclusive option. You can easily check if transaction is already started (see http://www.sqlite.org/c3ref/get_autocommit.html). If it is then you'll use savepoint syntax otherwise you'll use 'begin immediate' syntax. When you need to commit (nested) transaction you'll need to use 'release savepoint' syntax if you used savepoint at the beginning and 'commit' if you used 'begin'. Pavel On Mon, Feb 22, 2010 at 4:59 PM, Trainor, Chris <chris.trai...@ironmountain.com> wrote: > Thanks for the response Pavel. The order that the events were getting > logged wasn't accurate enough so I increased the timing precision for my > logging. I didn't leave anything out, but some commands were logged > slightly out of order. > > > > Thread1 is doing reads and writes for a while, with Thread2 attempting > to insert. Thread2 ends up in the BusyHandler for a while and then the > following occurs in this order: > > > > Thread Command Lock Step > > 1 RELEASE SAVEPOINT Thread1 None 1 > > 2 INSERT INTO TableA > Exclusive 2 > > 2 RELEASE SAVEPOINT Thread2 None 3 > > 2 SAVEPOINT Thread2 None > 4 > > 2 INSERT INTO TableA > Exclusive 5 > > 1 SAVEPOINT Thread1 None > 6 > > 2 RELEASE SAVEPOINT Thread2 None 7 > > 1 SELECT FROM TableA > Shared 8 > > 2 SAVEPOINT Thread2 None > 9 > > 1 SELECT FROM TableB > Shared 10 > > 2 INSERT INTO TableA > Reserved? 11 > > 1 INSERT INTO TableB > ***** 12 > > > > > > Step 1 - The transaction is closed on thread1, so it no longer has a > lock. > > Step 2 - This is the insert that was failing, with Thread2 ending up in > the busyhandler. When thread2 first tried to insert, it obtained a > reserved lock. Now that thread1 released its lock, thread2 gets an > exclusive lock and the insert finally succeeds at this point. > > > > I think what is going wrong is this: > > Step 11 - Thread2 tries to do an insert. Since Thread1 has a shared > lock, thread2 acquires a reserved lock but it cannot be promoted to > Exclusive. > > Step 12 - BusyHandler is not called. Database is locked error is > returned. Thread1's shared lock cannot be promoted to a reserved lock, > since Thread2 already has one. > > > > Does that seem correct? > > > > If so, my options are: > > 1) rollback/commit one of the transactions > > 2) use begin exclusive > > > > I don't think the second one will work, since I need nested transactions > and the savepoint syntax doesn't seem to support the exclusive option. > > > > Thanks, > > Chris > > > > > > The information contained in this email message and its attachments > is intended > only for the private and confidential use of the recipient(s) named > above, unless the sender expressly agrees otherwise. Transmission > of email over the Internet > is not a secure communications medium. If you are requesting or > have requested > the transmittal of personal data, as defined in applicable privacy > laws by means > of email or in an attachment to email you must select a more > secure alternate means of transmittal that supports your > obligations to protect such personal data. If the reader of this > message is not the intended recipient and/or you have received this > email in error, you must take no action based on the information in > this email and you are hereby notified that any dissemination, > misuse, copying, or disclosure of this communication is strictly > prohibited. If you have received > this communication in error, please notify us immediately by email > and delete the original message. > _______________________________________________ > 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
