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
