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 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. 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. Just to be clear, these are local filesystems, correct? Network mounts like NFS are not expected to work. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
