I did some further testing. It turns out there are two solutions: 1) Replace 'BEGIN IMMEDIATE' from the transaction with 'BEGIN EXCLUSIVE' 2) Set sqlite3_busytimeout() instead of re-trying the query during a given period (with sleep intervals). I'm not sure this really solves the issue. It just might take longer for the problem to surface.
Though I'm still not convinced the locking problem isn't a bug. IMHO a database should never allow itself to be left in a situation from which it cannot recover after a query. Nico Coesel > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nico Coesel > Sent: Wednesday, November 04, 2009 4:19 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Locking bug? > > > -----Original Message----- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Simon Slavin > > Sent: woensdag 4 november 2009 16:09 > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Locking bug? > > > > > > On 4 Nov 2009, at 1:17pm, Nico Coesel wrote: > > > > > Two seperate processes (process A and process B) on a Linux system > > > read > > > and write to the same database. Process A performs a transaction > > > every 5 > > > seconds. Every now and then the locks from process A are not > released > > > (judging from /proc/locks). It seems this situation occurs when > > > process > > > A and B both try to access the database at the same time. I have not > > > found a way to release the lock besides closing the database handle. > > > I'm > > > using the sqlite3_exec function to execute a query; this function > > > calls > > > slite3_finalize at the end so this should release the locks. > > > > Are you checking how every sqlite3_ function call returns, in both > > processes, to see whether it is reporting an error ? Even a function > > like 'COMMIT' can correctly make the changes you want but return an > > error message anyway. > > > > Yes, I check every return value. I also see errors from process B saying > the database is locked. In /proc/locks I can see process A still locked > the database. I also tried to use the command line tool to access the > database using the vacuum command but it also says the database is > locked. > > Nico Coesel > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of O'Neill, Owen > Sent: woensdag 4 november 2009 17:33 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Locking bug? > > > I could be wrong, but even if the statement failed, and you have called > finalize, I vaguely recollect reading somewhere that (if you called > BEGIN) then you need to rollback. > Can't find the manual / wiki link to back that up at the moment. > > (oh and installing a busy handler should help things - just setting a > timeout will do to start with, just watch out that if your system only > has sleep and not usleep then the timeout needs to be >=2000ms to do > anything.) > > Owen. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
