Trevor Talbot wrote:
On 1/2/08, Lior Okman <[EMAIL PROTECTED]> wrote:
I'm using SQLite3 version 3.4.2 (latest in Debian testing and unstable),
and I have a scenario using transactions in SQLite3 that is a bit
counter-intuitive.
You'll want to look at http://sqlite.org/lockingv3.html for this.
I'll go over this document.
I open an SQLite3 database from two terminals. In the first terminal I
run the following SQLs:
> sqlite> begin;
> sqlite> insert into a values (null);
> sqlite> insert into a values (null);
This transaction has acquired a RESERVED (intent to write) lock, at
the first INSERT statement. Others may read, but no others may
announce an intent to write.
In the second terminal, I run the following SQLS:
> sqlite> begin;
> sqlite> insert into a values (null);
> SQL error: database is locked
This transaction has acquired a SHARED (reading) lock at the first
access to the database. At the INSERT statement, it tries to acquire
RESERVED, but fails because another has already announced its intent
to write. This transaction remains SHARED.
I go back to the first terminal at this stage and I try to end the
transaction using commit:
> sqlite> commit;
> SQL error: database is locked
The second connection still has a SHARED (reading) lock, so this
transaction cannot make any physical changes to the file yet. The
INSERT statements you already executed are buffered internally, so it
did not need to make physical changes before.
At this point, I can't commit the transaction in the first terminal,
until I run a commit in the second terminal, even though the first
terminal is the one with the active transaction, and the second terminal
shouldn't have any effect on the active transaction.
The second connection has an active transaction too, just in read-only
state. It must end before the first can proceed with physical changes
to the file.
This behaviour varies, depending on the filesystem type on which the
sqlite database file is created in. If I use reiserfs, it sometimes
takes a long while until I can commit from any of the terminals. In
ext3, this is usually resolved after retrying the commit a few times in
both terminals. In tmpfs, there is never any issue, the first terminal
can always commit.
What am I missing here? Is this behaviour the expected one?
Requiring the second transaction to complete first is expected in
terms of SQLIte's concurrency system.
So in terms of using SQLite, I need to close the entire transaction and
restart it when I get a "database locked" return code in a writer
thread? It's not enough to just retry the commit in a little while?
Wouldn't it be more intuitive to allow the single handle holding the
RESERVED lock to finish? Right now, the SQLite behaviour allows only the
serialized isolation level. Making this change would make the isolation
level be more like "read committed".
The fact that you are seeing
changes in behavior depending on the filesystem is disturbing though.
They should all behave the same if they are implementing locking
correctly. I will let others speak to this point; if you can post more
detail on the steps (e.g. if I do "commit" here and "commit" here
nothing happens for N minutes), it will probably help them.
If I'm working in a tmpfs partition, the second transaction doesn't hold
a SHARED lock at all, even though I got the "database is locked"
message. I am able to commit the first transaction (holding the RESERVED
lock) without ending the second transaction.
If I'm working in an ext3 or reiserfs partition, the second transaction
does hold the SHARED lock, and I am not able to commit the first
transaction without ending the second one first.
Currently my program runs two threads, both attempting to open a
transaction, insert a single row into a table and commit the
transaction. This is done using separate connection handles to the
database - one for each thread, and when I get the SQLITE_BUSY return
code, I sleep for around 10ms and retry the failed operation. When I'm
running on a reiserfs based database, the SQLITE_BUSY return code never
changes, and essentially my program is deadlocked once I get the
SQLITE_BUSY return code in one of the threads. When I'm running on an
ext3 based database, the issue resolves itself within a few retries.
I'll try (maybe later today) to create a tester program that I can post
to this mailing list that recreates this issue - I can't post my current
program.
I'll also try to change the "begin" statement to "begin immediate" -
like Ken suggested in a separate message.
Just to be clear, these are local filesystems, correct? Network mounts
like NFS are not expected to work.
These are not network mounts.
Tmpfs is a memory based filesystem. The ext3 and the reiserfs
filesystems are both local to where I'm running the test.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
