Re: [sqlite] Commit fails due to "database is locked" in active transaction
Richard Klein wrote: 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? You don't need to close the connection, but you do need to ROLLBACK the transaction, unless you have some sort of a priori knowledge that the second transaction will not try to write to the database. In such a case, the second transaction will not try to acquire the RESERVED lock already held by the first transaction, and so the second transaction will eventually run to completion. In such a scenario, the first transaction can sit in a busy wait loop (sleep for a bit, then retry the COMMIT) until the COMMIT succeeds. However, if the second transaction will (or might) try to write to the database, you must ROLLBACK the first transaction, sleep for a bit, and restart the first transaction. I've written the attached test program. In this program, I run 2 threads, each opening an explicit transaction (begin immediate), inserting a row into a table, and committing the transaction. I run the program 4 times, in loopback mounted directories, using JFS, Ext3, TMPFS and ReiserFS3. I expected that it would be possible for the program to get SQLITE_BUSY only for the begin statement, however, based on the underlying filesystem, I get: Ext3: takes 6 seconds to run on my system, with SQLITE_BUSY happening for commit even though each thread has a RESERVED lock on the database. JFS: takes 4 seconds to run on my system, with SQLITE_BUSY happening for commit. ReiserFS version 3: takes 90 (!!!) seconds on my system, with SQLITE_BUSY happening for commit, and the threads nearly completely serialized. Tmpfs: Takes 2 seconds, again with SQLITE_BUSY happening for the commit statements. My questions are: 1. Why do I get SQLITE_BUSY for commit statements? Each thread has a reserved lock - there should be no busy situation for anything other than a "begin immediate" operation. 2. Why does running on reiserfs cause such a huge performance penalty? 3. Another weird thing - if I change the program to open a deferred transaction, instead of an immediate transaction, the program doesn't deadlock, even when I get a SQLITE_BUSY on an insert statement. Thanks Lior - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - test.c.gz Description: GNU Zip compressed data - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
In a threaded environment the simple and effective solution is to synchronize your transactions with a mutex. You lose a little possible concurrency but if you do not need it you simplify the logic no end and have a more robust application. Using pthreads you can improve a little by using read and write locks, a sophistication on a simple mutex. Richard Klein wrote: 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? You don't need to close the connection, but you do need to ROLLBACK the transaction, unless you have some sort of a priori knowledge that the second transaction will not try to write to the database. In such a case, the second transaction will not try to acquire the RESERVED lock already held by the first transaction, and so the second transaction will eventually run to completion. In such a scenario, the first transaction can sit in a busy wait loop (sleep for a bit, then retry the COMMIT) until the COMMIT succeeds. However, if the second transaction will (or might) try to write to the database, you must ROLLBACK the first transaction, sleep for a bit, and restart the first transaction. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
On 1/2/08, Lior Okman <[EMAIL PROTECTED]> wrote: > Trevor Talbot wrote: > > 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? It's safe to retry a commit. It may not be safe to retry a writing statement, depending on the presence of other writers. These two messages should help explain what you need to consider to avoid deadlocks: http://www.mail-archive.com/sqlite-users@sqlite.org/msg27284.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg28638.html > 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". You could get that behavior now by simply not using an explicit transaction in the reader. Actual "read committed" isolation support comes in when there are concurrent writers, so one transaction can see its own changes as well as the changes of others that have committed in parallel. Keep in mind, SQLite has no central transaction arbiter managing the file; its concurrency is implemented in terms of OS-level file locks. In order to implement parallel writers at any isolation level, a writer would need to somehow distinguish its changes from those of other writers in progress. That makes the act of committing itself, as well as crash recovery, much more complex. It also has to deal with potential conflicts on pending changes, and with "read committed" as an option, it's complicated even more by transactions using a mix of isolation levels. A very difficult kind of change. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
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? You don't need to close the connection, but you do need to ROLLBACK the transaction, unless you have some sort of a priori knowledge that the second transaction will not try to write to the database. In such a case, the second transaction will not try to acquire the RESERVED lock already held by the first transaction, and so the second transaction will eventually run to completion. In such a scenario, the first transaction can sit in a busy wait loop (sleep for a bit, then retry the COMMIT) until the COMMIT succeeds. However, if the second transaction will (or might) try to write to the database, you must ROLLBACK the first transaction, sleep for a bit, and restart the first transaction. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
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.
Re: [sqlite] Commit fails due to "database is locked" in active transaction
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] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
Try your test using a "begin Immediate" instead of begin. A write lock is not taken out until the very last moment (ie a spill to disk or commit). Lior Okman <[EMAIL PROTECTED]> wrote: Hi, 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. I open an SQLite3 database from two terminals. In the first terminal I run the following SQLs: > $ sqlite3 test.db > SQLite version 3.4.2 > Enter ".help" for instructions > sqlite> create table a (id integer not null primary key); > sqlite> begin; > sqlite> insert into a values (null); > sqlite> insert into a values (null); In the second terminal, I run the following SQLS: > $ sqlite3 test.db > SQLite version 3.4.2 > Enter ".help" for instructions > sqlite> begin; > sqlite> insert into a values (null); > SQL error: database is locked 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 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. 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? Thanks, Lior - To unsubscribe, send email to [EMAIL PROTECTED] -
