blocking - busy_timeout vs database is locked(5)

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

blocking - busy_timeout vs database is locked(5)

Jonathan H N Chin-5
With debian packages:
        sqlite3                 3.2.1-1                                      
        libsqlite3-0            3.2.1-1
        libdbd-sqlite3-perl     1.08-1 (with looks_like_number test elided)

I have an sqlite3 database that is accessed by a perl cgi script.
I want accesses to block if the database is in use by another process,
and not to fail with an error.
The cgi code looks like:

        my $attr = { RaiseError => 1, AutoCommit => 1 };
        my $cdbh = DBI->connect( "dbi:SQLite:dbname=$CREDB", "", "", $attr);
        $cdbh->func( 3600000, 'busy_timeout' );
        $cdbh->do( q[DELETE FROM m2u WHERE user = ?;], undef, $user);

but I still occasionally get failures:

        DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 403

Am I doing something wrong?

What is the correct way to make accesses block/retry when the
database is busy?


-jonathan

--
Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK
<[hidden email]> | systems mangler | tel/fax: +44 1223 767091/330508

                "respondeo etsi mutabor" --Rosenstock-Huessy
Reply | Threaded
Open this post in threaded view
|

Re: blocking - busy_timeout vs database is locked(5)

Jonathan H N Chin-5
I should perhaps note that there are only around fifty accesses in
any given five minute interval, so it is not as if anything is being
overloaded.


-jonathan

--
Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK
<[hidden email]> | systems mangler | tel/fax: +44 1223 767091/330508

                "respondeo etsi mutabor" --Rosenstock-Huessy
Reply | Threaded
Open this post in threaded view
|

Re: blocking - busy_timeout vs database is locked(5)

Robert Simpson
In reply to this post by Jonathan H N Chin-5
----- Original Message -----
From: "Jonathan H N Chin" <[hidden email]>
To: <[hidden email]>
Sent: Thursday, August 18, 2005 8:00 AM
Subject: [sqlite] blocking - busy_timeout vs database is locked(5)


[snip]

>
> but I still occasionally get failures:
>
>        DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line
> 403
>
> Am I doing something wrong?
>
> What is the correct way to make accesses block/retry when the
> database is busy?

I'm afraid you'll have to write your own internal retry mechanism.  The
busy_timeout only works in certain areas when the database is busy.  When an
update is in progress however, all attempts to read will return
*immediately* with a failure message.  You'll then have to call
sqlite3_reset() to find out what that error message is.  If its a
SQLITE_SCHEMA you need to call sqlite3_prepare() again (don't forget to
rebind your parameters if any), and if its a SQLITE_LOCKED then you need to
sleep for some random amount of time and retry -- with hopefully an eventual
timeout mechanism in place.

Robert


Reply | Threaded
Open this post in threaded view
|

Re: blocking - busy_timeout vs database is locked(5)

Ray Mosley
I have somewhat-related questions.
1) Using the Tcl binding for SQLite 2.8, how do I even obtain the value to
know the DB is busy?
2) If I use
 db timeout 2000
 won't the application wait until the db is free to access the db? Do I even
need to know it was once busy if SQLite waits until it can proceed?
 Thanks from a DB rookie.

 On 8/18/05, Robert Simpson <[hidden email]> wrote:

>
> ----- Original Message -----
> From: "Jonathan H N Chin" <[hidden email]>
> To: <[hidden email]>
> Sent: Thursday, August 18, 2005 8:00 AM
> Subject: [sqlite] blocking - busy_timeout vs database is locked(5)
>
>
> [snip]
> >
> > but I still occasionally get failures:
> >
> > DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line
> > 403
> >
> > Am I doing something wrong?
> >
> > What is the correct way to make accesses block/retry when the
> > database is busy?
>
> I'm afraid you'll have to write your own internal retry mechanism. The
> busy_timeout only works in certain areas when the database is busy. When
> an
> update is in progress however, all attempts to read will return
> *immediately* with a failure message. You'll then have to call
> sqlite3_reset() to find out what that error message is. If its a
> SQLITE_SCHEMA you need to call sqlite3_prepare() again (don't forget to
> rebind your parameters if any), and if its a SQLITE_LOCKED then you need
> to
> sleep for some random amount of time and retry -- with hopefully an
> eventual
> timeout mechanism in place.
>
> Robert
>
>
>


--
Ray Mosley
Reply | Threaded
Open this post in threaded view
|

Re: blocking - busy_timeout vs database is locked(5)

Kurt Welgehausen
> Using ... Tcl ... how do I even obtain the value ...

man n catch

> If I use db timeout 2000 ...

SQLite will retry for 2 seconds, then if the db is still
locked, it should return an error code.

Any SQLite command that can fail should be run within a
catch command.


Regards