Locking bug?

[Nico Coesel]

Hello,
I have the following situation:

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.

I suspect there may be a path in the code which skips releasing the
locks.

Nico Coesel


_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin-3]

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.

Simon.


_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Nico Coesel]

> -----Original Message-----
> From: sqlite-users-bounces@... [mailto:sqlite-users-
> bounces@...] 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



_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Jay A. Kreibich-2]

On Wed, Nov 04, 2009 at 05:19:11PM +0100, Nico Coesel scratched on the wall:
>
> > > 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.
>
> 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.

  There are situations when SQLite can dead-lock, where two processes
  will both sit and wait for the other to release its locks.  It isn't
  just a matter of checking the return values, but also of doing the
  right thing.  In specific, if you get any SQLITE_BUSY codes, you need
  to rollback the transaction and try again from the start.

  However, if you using sqlite3_exec() for all your SQL and you're NOT
  using explicit transactions, then that should take care of things for
  you.  If you are using explicit transactions (sending a "BEING" and
  "END" or "COMMIT") you need to handle this situation yourself.



  Is process A cleanly exiting when the lock is left behind?

  What OS and filesystem are you using?

   -j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[O'Neill, Owen]

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.



-----Original Message-----
From: sqlite-users-bounces@...
[mailto:sqlite-users-bounces@...] 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-bounces@... [mailto:sqlite-users-
> bounces@...] 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



_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Nico Coesel]

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-bounces@...
> [mailto:sqlite-users-bounces@...] 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-bounces@... [mailto:sqlite-users-
> > bounces@...] 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-bounces@... [mailto:sqlite-users-
> bounces@...] 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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users