Andrew, Nice modification.. Did you buy any chance post this into the sqlite ticketing system? It would be nice if sqlite would let you acquire a shared lock via the BEGIN statement.
Andrew Teirney <[EMAIL PROTECTED]> wrote: > I have multiple thread reading the database and a thread inserting in or > updating the database. > when i try to simulate this case by executing sqlite3 from shell by > beginning a transaction from a shell and retrieving data from other shell > eveything works fine. > But when it's the cas from my C program it raises the error message > database > is locked. > How can i avoid this issue? > thanks a lot I myself was running into this problem a lot whilst trying to use a lua binding to sqlite where each thread used its own connection (obtained via a connection pool). The reason for getting the "database is locked" is that i was creating situations where there would be a deadlock because of the type of locks held by the various connections, one of them would have to yeild to allow the other to continue. This baffled me for a bit as i noticed my busy handler was not being called. The solution i found was to wrap the complete operation i was doing in a transaction via "BEGIN" ... "END". I did however find the default style of "BEGIN" (which is a deffered style lock) i could end up with database is locked problem, this was because there would have been a deadlock in accessing the database due to the deffered style of locking. So alas i did some more investigation. In the end i started to use "BEGIN IMMEDIATE" which acquires a pending lock on execution of the BEGIN, thus i could be sure that the lock required for the operation i was going to perform was granted, this also meant that any busy handler would be run whilst acquiring the lock. However because of the usage pattern where there were multiple readers and a single writer this was obviously not the best idea, as it meant there could only be one thread/process accessing the database at one time. To get around this i wanted to be able to acquire a "SHARED" lock via a "BEGIN" statement, have any busy handling operating around there. Unfortunately sqlite by default doesn't support acquiring a shared lock when the "BEGIN" is executed, even with a type specifier, for this i extended sqlite to enable a "BEGIN SHARED" style of transaction where this shared lock has been acquired. In doing this i was able to do all my busy handling around the "BEGIN" statements, and have multiple readers read from the db via "BEGIN SHARED", and then have writers call "BEGIN IMMEDIATE". More info in the "BEGIN [TRANSACTION] ..." can be found at http://www.sqlite.org/lang_transaction.html I would strongly suggest reading http://www.sqlite.org/lockingv3.html to get an overview of the different state of locks that can be acquired on the database. If you are interested in the "BEGIN SHARED" transaction i posted a simple patch to this mailing list within the last month if i recall correctly. Hope this helps, Andrew ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
