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] -----------------------------------------------------------------------------
