Apparently the following happens: 13875 Thread2 SAVEPOINT Thread2 success (shared lock acquired) 13875 Thread1 SAVEPOINT Thread1 success (shared lock acquired) 13880 Thread2 INSERT INTO TableB success (reserved lock) 13890 Thread2 RELEASE SAVEPOINT Thread2 fail (pending lock is acquired, exclusive lock cannot be acquired because of existing shared lock, the busyhandler callback is called here) 13890 Thread1 SELECT blah FROM TableB success (shared lock is still active) 13906 Thread1 SELECT blah FROM TableC success (shared lock is still active) 13906 Thread1 INSERT INTO TableD fail (reserved lock cannot be acquired because another thread already acquired one, busyhandler can NOT be called because another thread already have pending lock and waiting is senseless - error database is locked)
I'm not sure if SAVEPOINT actually acquires shared lock but probably something happened which you didn't tell and it acquired shared lock. General rule: if you're trying to promote read-only transaction into writing transaction and get SQLITE_BUSY you MUST rollback and try again. Another option - start transaction with BEGIN IMMEDIATE in the first place. Pavel On Fri, Feb 19, 2010 at 6:47 PM, Trainor, Chris <chris.trai...@ironmountain.com> wrote: > I am running into a "database is locked" error and I don't quite > understand what is going on. If someone could explain what is happening, > I'd appreciate it. (I am using sqlite version 3.6.17 on Windows XP.) > > A busy handler callback has been set up with sqlite3_busy_handler(). In > addition to some logging, the callback simply checks the number of > retries and either gives up (by returning 0) if it reaches the max retry > count or sleeps for 50 msecs then returns 1. > > I have two threads (each with their own connection) that are trying to > access the database. The 1st thread is doing some reads and writes and > the other one is just writing. For a while, things work as expected. > Thread1 does some reads and writes within explicit transactions (using > Savepoint and Release Savepoint syntax). Thread2 attempts to write to > the database and can't, so the busyhandler callback is called. > Eventually, Thread2 is able to get the exclusive lock and write to the > database (again within an explicit transaction). Thread1 does some more > reads and writes, etc. > > Here's where it stops making sense. I'm paraphrasing what is logged out > by our app. The first column is the number of msecs since the process > started. The second obviously is the active thread. 3rd is the query > (simplified for easy reading). The 4th indicates success or failure. > The 5th contains notes as to what appears to be happening. > > > 13875 Thread2 SAVEPOINT Thread2 success > (No locks acquired) > 13875 Thread1 SAVEPOINT Thread1 success > (No locks acquired) > 13880 Thread2 INSERT INTO TableB success > (exclusive lock) > 13890 Thread2 RELEASE SAVEPOINT Thread2 fail > (the busyhandler callback is called here) > 13890 Thread1 SELECT blah FROM TableB success > (shared lock???) > 13906 Thread1 SELECT blah FROM TableC success > (shared lock???) > 13906 Thread1 INSERT INTO TableD fail > (busyhandler is NOT called - database is locked) > > The call to sqlite3_step succeeds for the query "INSERT INTO TableB" at > 13880 msecs. I believe this means that an exclusive lock must have been > obtained for the connection on thread2. Thread2 attempts to release the > savepoint right around the same time as thread1 attempts to read from > TableB (at 13890 msecs). Thread1 is able to read from TableB, then is > able to read from TableC. This seems to indicate that the connection on > thread1 acquired a shared lock. I don't see how this is possible, since > thread2 should have had an exclusive lock at that point. I am assuming > that sqlite thinks that a deadlock will occur when thread1 tries to > write to the database at 13906 msecs and that is why the busyhandler > callback is not invoked. > > Also I'm not sure if it matters, but I am using sqlite3_exec to execute > the SAVEPOINT and RELEASE SAVEPOINT statements. All other queries are > executed using prepared statements and calls to sqlite3_step. Note that > there are NO nested transactions created. > > Can anyone shed some light on this? > > 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
