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
