I can empathize with this problem, having just worked through this recently. The bottom line is if you need concurrency, you're going to have to structure your code appropriately. Here are some things I found helpful:
1. ENCAPSULATE! You'll want to encapsulate your handling of queries so that you only have to wrap things for LOCKED and BUSY handling in one place. 2. Shared cache mode (sqlite3_enable_shared_cache()) has a better locking style for concurrency within a single process (it uses table level locking). This is almost a necessity if you need concurrent access between threads. 3. If you can possibly get away with it, use "PRAGMA read_uncommitted = true". THIS WILL MAKE YOUR READS NON ACID, but it greatly reduces contention. Generally speaking, a little care in your code should keep the non-acid reads from being a problem. 4. An open VDBE (sqlite3_stmt*) in the middle of returning rows will hold a read lock on its table. While that read lock is open, other threads will be unable to write to that table. Beware long time consuming loops that hold a read lock on a table another thread might want to write to. 5. Keep as much writing as possible in one thread (all of it, if you can manage.) 6. If you have to break rule 5, try to make sure that the different threads use different tables. 7. If any thread has a long running operation, make sure that it won't block any important tables for the whole time 8. If you have to break rule 7, make it possible to detect when another thread has been blocked, and yield to that thread by committing the transaction and releasing any open cursors. 9. Every query needs to happen in a loop. This loop needs to check for LOCKED (and perhaps BUSY) conditions. Handle LOCKED using sqlite3_unlock_notify(). This loop is half the reason for encapsulating. This isn't everything, but the list is long already, and that should get you past most of the hard stuff. Best luck. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Thursday, October 15, 2009 10:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Exception writing to database from multiple processes You're definitely talking about some bug in your application or some misunderstanding about how SQLite should work. SQLite by itself never causes any deadlocks. So I guess in order to be able to help you we need to know more about what you're doing. Maybe for example you're forgetting to commit/rollback transaction somewhere, maybe you're dead-locking on your own mutexes. Are you able to look at the stack traces where your workers hang? Pavel On Thu, Oct 15, 2009 at 10:40 PM, David Carter <david.car...@erdas.com> wrote: > Yes, that's correct. I also tried using BEGIN EXCLUSIVE instead of BEGIN > IMMEDIATE. This results in only one worker process being able to write to > the database, while the other worker processes continually get SQLITE_BUSY > when trying to write. > > David > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Thursday, 15 October 2009 9:53 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Exception writing to database from multiple processes > >> However, when it is run >> inside an Application Pool with multiple worker processes, the database >> soon becomes locked and cannot be written to by any of the worker >> processes. > > You mean your application hangs? None of workers can write to database > and nothing else happens in application? Nobody's reading database at > the same time, nobody connecting to database via command line tool, > nothing happens at all? > > Pavel > > On Thu, Oct 15, 2009 at 2:40 AM, David Carter <david.car...@erdas.com> wrote: >> Hello, >> >> >> >> I am currently using the SQLite Amalgamation v3.6.19 from >> http://www.sqlite.org/download.html in an ISAPI Extension to write out >> usage statistics to an SQLite database. >> >> >> >> When the ISAPI extension is running inside an Application Pool with a >> single worker process, everything works fine. However, when it is run >> inside an Application Pool with multiple worker processes, the database >> soon becomes locked and cannot be written to by any of the worker >> processes. >> >> >> >> Each worker process has a separate background thread which writes to the >> database every 5 seconds. Each write is performed as a single >> transaction starting with "BEGIN IMMEDIATE". >> >> >> >> Any help you can provide would be greatly appreciated. >> >> >> >> Thanks, >> >> >> >> David >> >> _______________________________________________ >> 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 > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
