Hi, I'm using SQLite 3.0.8 on Linux in an Apache module, in a multithreaded multi-process configuration. Occasionally, the database becomes deadlocked - all requests fail with a busy error. Even when I try to access it using sqlite command line I get "Error: database is locked" for every operation. My application is completely idle at that point.
My scenario consists of concurrent inserts, updates and deletes from multiple threads from multiple processes to a single table at a high rate. I'm NOT using transactions. I'm using a POOL of sqlite3 sessions for all threads, however at any moment a session is in use by at most a single thread. I couldn't reproduce the deadlock when I used a dedicated session per thread, but I don't think it's a good idea to keep the database open dozens of times. One point maybe worth mentioning is that I saw identical thread IDs on two separate processes. The deadlock occurs both when SQLite is compiled with THREADSAFE and without it (BTW, why is the default Linux setting not thread safe?). The update command is rather complex: "UPDATE Q_DATA SET ID = ?, FLAG = ? WHERE ROWID = (SELECT ROWID FROM Q_DATA WHERE ID ISNULL AND DEQ_TIME < ? ORDER BY DEQ_TIME LIMIT 1)" This update sets a unique id and flag to a ZERO or ONE row with the smallest DEQ_TIME that is smaller than input and that was not chosen already. Later this entry is deleted based on this ID. The insert command is trivial. I also noticed that after the deadlock a database journal file is present. Is it OK to use the same sqlite3 session in multiple threads but making sure that at anytime it is used by only one thread? Is there any way to get more information on the deadlock or how to avoid it? Any help will be greatly appreciated. Ron
