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]
-----------------------------------------------------------------------------
