[pysqlite] Question about locked database

[Gerhard Haering]

On Mon, May 02, 2005 at 05:35:00PM +0200, Leo Leo wrote:
> Hello!
> 
> I have a very small web-app, but there have to be many parallel
> users (about up to 50). 

Using pysqlite 2.0alpha3 or alpha4 I suppose? Earlier versions had 0.0 as
default for the `timeout` parameter, alpha3 and later have 5.0 seconds as
default.

> What I am doing is a kind of a sequence (but a bit more complicated,
> otherwise I would use an autoincrement-field). So what I do, is
> a) Step 1: SELECT the current value
> b) Step 2: Calculate the new value
> c) Step 3: UPDATE new value.
> 
> No I get timing-problems. Occasionally this error-msg occurs:
> 
> "pysqlite2.dbapi2.OperationalError: database is locked".

The timeout value is set to 5.0 by default. So SQLite will wait up to
5 seconds for a lock to get away. You'll get that error when your
connection does not aquire the lock within five seconds.

So I think you should do this:

- Ensure that your transactions are short-lived: commit/rollback ASAP.
- Try setting the timeout value in connect() a little higher.

> The response-time is not very important here, but what I would need is an
> (good) approach to "wait" until I can do the update. That means I wait a
> second and try the statement over and over again, until I can do the
> statement (until no execption is thrown). 

pysqlite does exactly this for you already. If it gets an SQLITE_BUSY
(database is locked) error the first time, it will sleep and try
again, sleep and try again, etc. until from the first sleep to now
more than `timeout` seconds passed. Only then will it give up and
throw an exception.

The exact algorithm in C is this one:

#v+
    Py_BEGIN_ALLOW_THREADS
    rc = sqlite3_step(statement);
    Py_END_ALLOW_THREADS

    if (rc != SQLITE_BUSY) {
        return rc;
    }

    connection->timeout_started = pysqlite_time();
    while (1) {
        Py_BEGIN_ALLOW_THREADS
        rc = sqlite3_step(statement);
        Py_END_ALLOW_THREADS
        if (rc != SQLITE_BUSY) {
            break;
        }

        if (pysqlite_time() - connection->timeout_started > connection->timeout) {
            break;
        }

        how_long = 0.01* (1 << counter);
        pysqlite_sleep(how_long);

        counter++;
    }
#v-

The interesting part is the calculation of `how_long`: It will start for 1/100
seconds at the first run, then double the time to sleep for each iteration:
0.01, 0.02, 0.04, 0.08, 0.16, 0.32, 0.64, 1.28 seconds, and so on. I figured
this would be a good generally applicable behaviour for applications using
pysqlite.

At the moment, there is no way to customize this, and if people would really
really need this (but after 2.0 final), I could then make it possible.

> The problem is, what can I do in order to prevent interferance from
> other web-users?
> 
> Is there a better way, something like this
> a) Lock the database for update
> b) select the current value
> c) calculate new value
> d) update table
> e) unlock database
> 
> If this is possible, how can I do it?

If you're not doing anything fancy or in unusual ways, this should already
happen automatically when you use pysqlite, because of the transactions  ...

Can you show the actual code you're using so I can maybe see what could be
improved?

-- Gerhard
-- 
Gerhard Häring - gh at ghaering.de - Python, web & database development

pysqlite - Powerful and fast embedded database engine "SQLite" for Python.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://lamu.initd.org/pipermail/pysqlite/attachments/20050504/53924655/attachment.pgp