Re: [sqlite] thread concurrency, inserts using transactions, bug?
Brad, its my understanding that Locking occurs at the Database level, not the table level. http://www.sqlite.org/lockingv3.html Brad House <[EMAIL PROTECTED]> wrote: > Here are my results after modifying the "begin transaction" to a "begin exclusive" > Begin transaction is a bit Lazy in that the lock escalation doesnt occur > until the pager escalates the lock due to a write. > > You'll see that the begin exclusive acquires a lock immediately and avoids > the behavoir. Hi Ken, thanks for the reply. Begin exclusive is not a sufficient solution though as that would have an effect on _every_ transaction, not just a transaction modifying the same table. If the solution is indeed to use 'BEGIN EXCLUSIVE' why then are there different transactional modes at all, considering that would mean SQLite cannot handle anything else? BTW- I had actually switched my production code to use BEGIN EXCLUSIVE after finding this bug, that was a few weeks ago, until recently when I had the time to write the test case, so I was actually aware of that 'workaround'. Thanks. -Brad - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] thread concurrency, inserts using transactions, bug?
[EMAIL PROTECTED] wrote: >> It appears that if 2 threads start transactions at the same time, >> both inserting into the same table, neither thread can finish until >> one has rolled back. > The behavior is deliberate because it gives you, the programmer, > more control and better concurrency in some situations. But it > can also result in the deadlock behavior that you observe. > > The plain BEGIN will succeed even if another process is > already expressed and interest in writing to the database. > This allows you to read from the database concurrently > with the writer, if that is what you want to do. But > because another process is already writing, you will not be > able to write. And if you try to write, you will get into a > deadlock. > > The BEGIN IMMEDIATE, on the other hand, will fail with SQLITE_BUSY > if another process has already started writing. It will not > succeed until the other process commits. This is the perferred > thing to do if you intend to write within your transaction. Hmm, I just modified my test to make each thread write to a separate table, and the same symptom occurs. I guess I assumed that this wouldn't occur with multiple tables, but apparently the lock happens on a database-wide level. Now I'm just confused at the reason why transactions have any other mode besides IMMEDIATE (or EXCLUSIVE) in SQLite. I don't think any implementations would use transactions for read-only work, as I don't believe there is any benefit to using transactions in that scenario. I don't understand how _not_ using IMMEDIATE would give you better concurrency in any situation... If you were performing read-only queries, and not using a transaction at all, you should get the same behavior [as a standard BEGIN transaction, then performing the read-only query], if there was an outstanding RESERVED or EXCLUSIVE lock. Please enlighten me if I am wrong here, I'm still pretty new to SQLite. Thanks! -Brad - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] thread concurrency, inserts using transactions, bug?
> Here are my results after modifying the "begin transaction" to a "begin > exclusive" > Begin transaction is a bit Lazy in that the lock escalation doesnt occur > until the pager escalates the lock due to a write. > > You'll see that the begin exclusive acquires a lock immediately and avoids > the behavoir. Hi Ken, thanks for the reply. Begin exclusive is not a sufficient solution though as that would have an effect on _every_ transaction, not just a transaction modifying the same table. If the solution is indeed to use 'BEGIN EXCLUSIVE' why then are there different transactional modes at all, considering that would mean SQLite cannot handle anything else? BTW- I had actually switched my production code to use BEGIN EXCLUSIVE after finding this bug, that was a few weeks ago, until recently when I had the time to write the test case, so I was actually aware of that 'workaround'. Thanks. -Brad - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] thread concurrency, inserts using transactions, bug?
Brad House <[EMAIL PROTECTED]> wrote: > I've read http://www.sqlite.org/cvstrac/wiki?p=MultiThreading under the > "Case in point: a benchmark application I've written for this purpose" > and found that current releases of SQLite do not appear to behave in > this manner. I cannot find any documentation which clearly states > the intended behavior. > > It appears that if 2 threads start transactions at the same time, > both inserting into the same table, neither thread can finish until > one has rolled back. > > The first thread succeeds until the COMMIT is issued, then returns BUSY > on the COMMIT. The second thread keeps returning BUSY on the INSERT > statement. I've created a test case which will retry on BUSY (up to > 25x to prevent infinite loops). You'll notice the test ultimately fails. > Also in this test case, I've created a test which does a ROLLBACK when > a BUSY is hit just to show that it does succeed. > > Is this intended functionality? It appears at least a release at some > point in time did not behave this way (Jan 10, 2003 from the Wiki). > Considering > the second thread never gets a successful response to an INSERT statement, > it would seem that it should not have tried to obtain a lock on that table, > preventing the COMMIT from succeeding... but it is... It seems to be a bug > to me. > The behavior is deliberate because it gives you, the programmer, more control and better concurrency in some situations. But it can also result in the deadlock behavior that you observe. You can easily work around this problem by always doing BEGIN IMMEDIATE Instead of just BEGIN When you start a transaction in which you intend to write. The plain BEGIN will succeed even if another process is already expressed and interest in writing to the database. This allows you to read from the database concurrently with the writer, if that is what you want to do. But because another process is already writing, you will not be able to write. And if you try to write, you will get into a deadlock. The BEGIN IMMEDIATE, on the other hand, will fail with SQLITE_BUSY if another process has already started writing. It will not succeed until the other process commits. This is the perferred thing to do if you intend to write within your transaction. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] thread concurrency, inserts using transactions, bug?
Brad, Here are my results after modifying the "begin transaction" to a "begin exclusive" Begin transaction is a bit Lazy in that the lock escalation doesnt occur until the pager escalates the lock due to a write. You'll see that the begin exclusive acquires a lock immediately and avoids the behavoir. Creating a table 0 => Executing: CREATE TABLE test_table(threadnum INT, cnt INT, testcol TEXT) 0 => started 1 => started all threads started 0 => Executing: BEGIN EXCLUSIVE 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 0, 'test0_0') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 1, 'test0_1') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 2, 'test0_2') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 3, 'test0_3') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 4, 'test0_4') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 5, 'test0_5') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 6, 'test0_6') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 7, 'test0_7') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 8, 'test0_8') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 9, 'test0_9') 1 => Executing: BEGIN EXCLUSIVE 1 => BUSY 0 => Executing: COMMIT 0 => finished. 1 => Executing: BEGIN EXCLUSIVE 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => Executing: INSERT INTO test_table VALUES(1, 1, 'test1_1') 1 => Executing: INSERT INTO test_table VALUES(1, 2, 'test1_2') 1 => Executing: INSERT INTO test_table VALUES(1, 3, 'test1_3') 1 => Executing: INSERT INTO test_table VALUES(1, 4, 'test1_4') 1 => Executing: INSERT INTO test_table VALUES(1, 5, 'test1_5') 1 => Executing: INSERT INTO test_table VALUES(1, 6, 'test1_6') 1 => Executing: INSERT INTO test_table VALUES(1, 7, 'test1_7') 1 => Executing: INSERT INTO test_table VALUES(1, 8, 'test1_8') 1 => Executing: INSERT INTO test_table VALUES(1, 9, 'test1_9') 1 => Executing: COMMIT 1 => finished. exiting...(test succeeded)
[sqlite] thread concurrency, inserts using transactions, bug?
I've read http://www.sqlite.org/cvstrac/wiki?p=MultiThreading under the "Case in point: a benchmark application I've written for this purpose" and found that current releases of SQLite do not appear to behave in this manner. I cannot find any documentation which clearly states the intended behavior. It appears that if 2 threads start transactions at the same time, both inserting into the same table, neither thread can finish until one has rolled back. The first thread succeeds until the COMMIT is issued, then returns BUSY on the COMMIT. The second thread keeps returning BUSY on the INSERT statement. I've created a test case which will retry on BUSY (up to 25x to prevent infinite loops). You'll notice the test ultimately fails. Also in this test case, I've created a test which does a ROLLBACK when a BUSY is hit just to show that it does succeed. Is this intended functionality? It appears at least a release at some point in time did not behave this way (Jan 10, 2003 from the Wiki). Considering the second thread never gets a successful response to an INSERT statement, it would seem that it should not have tried to obtain a lock on that table, preventing the COMMIT from succeeding... but it is... It seems to be a bug to me. I have attached the test case. Any insight would be appreciated. Here are the results (for both RETRY_BUSY scenarios and ROLLBACK): $ gcc -Wall -D RETRY_BUSY=1 -W -o sqlitetest sqlitetest.c -l sqlite3 [EMAIL PROTECTED] ~ $ ./sqlitetest Creating a table 0 => Executing: CREATE TABLE test_table(threadnum INT, cnt INT, testcol TEXT) 0 => started 1 => started all threads started 0 => Executing: BEGIN TRANSACTION 1 => Executing: BEGIN TRANSACTION 0 => Executing: INSERT INTO test_table VALUES(0, 0, 'test0_0') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 1, 'test0_1') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 2, 'test0_2') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 3, 'test0_3') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 4, 'test0_4') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 5, 'test0_5') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 6, 'test0_6') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 7, 'test0_7') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 8, 'test0_8') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 9, 'test0_9') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => MAX BUSY CNT 1 => thread failed ... 0 => Executing: COMMIT 0 => finished. exiting...(test failed) [EMAIL PROTECTED] ~ $ gcc -Wall -D RETRY_BUSY=0 -W -o sqlitetest sqlitetest.c -l sqlite3 [EMAIL PROTECTED] ~ $ ./sqlitetest Creating a tabl
