The problem is you are starting read-only transaction by executing SELECT and then try to convert this transaction into writing one by executing BEGIN IMMEDIATE. If in such situation SQLITE_BUSY is returned you have to finish the transaction and start it again. In your code solution is easy: finalize SELECT statement before executing BEGIN IMMEDIATE.
Pavel On Wed, Oct 17, 2012 at 6:12 AM, Daniel Polski <dan...@agelektronik.se> wrote: > Hello again, > Attached is a test application which replicates the problem. > > I expected the transactions to block each other exactly like they do in the > beginning (one connection successfully begins and the other receives > SQLITE_BUSY), but I didn't expect the blocked connection to never get > unlocked in the end. What's holding the lock on the database so that the > "begin" can't proceed? > > Sample console output: > > sqlite3_libversion: 3.7.13 > sqlite3_sourceid: 2012-06-11 02:05:22 > f5b5a13f7394dc143aa136f1d4faba6839eaa6dc > sqlite3_libversion_number: 3007013 > sqlite3_threadsafe: 1 > Creating thread 0 > Creating thread 1 > > conn addr status query > --------- ------ ----- > 0x6a6278 Success PRAGMA journal_mode=wal; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6bd678 Success PRAGMA journal_mode=wal; > 0x6bd678 Failed BEGIN IMMEDIATE TRANSACTION; (return code: 5) > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6a6278 Success BEGIN IMMEDIATE TRANSACTION; > 0x6a6278 Success INSERT INTO test_table_1 (test1, test2) VALUES ( > 1, 2); > 0x6a6278 Success COMMIT; > 0x6bd678 Failed BEGIN IMMEDIATE TRANSACTION; (return code: 5) > 0x6bd678 Failed BEGIN IMMEDIATE TRANSACTION; (return code: 5) > (... And so on) > > > Thank you in advance, > Daniel > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
