On Tue, Oct 16, 2012 at 4:33 AM, Daniel Polski <[email protected]>wrote:
>
> Hello,
> I have a hard time finding the cause of a bug in my application. I believe
> that it's me doing something wrong and not sqlite since I can't reproduce
> the error in a simple example. I have 4 threads mostly reading data from a
> database, and all threads open "own" database connections. I've compiled
> with the 3.7.13 amalgamation C source file and I'm using journal_mode = wal
> and sqlite_threadsafe returns 1.
>
> Pseudo code showing my applications logic:
>
> while(keep_running){
> sqlite3_exec ( "SELECT a, b, c FROM table1, table2, table3, table4;");
> sqlite3_exec ( "SELECT d, e, f FROM table1, table2;");
> if( sqlite3_exec ( "BEGIN IMMEDIATE TRANSACTION;") == SQLITE_OK){
> sqlite3_exec ( "INSERT INTO table3 VALUES('1'));");
> sqlite3_exec ( "INSERT INTO table4 VALUES('1'));");
> sqlite3_exec ( "COMMIT;");
> }else{
> //failed to start transaction
> }
> }
>
> What confuses me is that the "BEGIN IMMEDIATE TRANSACTION" sometimes fails
> with a SQLITE_BUSY[5] (database is locked) after some iterations in the
> loop even though all previous actions so far has returned SQLITE_OK. All
> subsequent calls I do to try to begin the transaction again also fails. Any
> ideas how that can happen?
>
One of the other threads is writing at the same time. SQLite only allows a
single writer at a time to a single database file. Others have to wait in
line.
Your solution is that when you get an SQLITE_BUSY, delay for a short while
and then try again. Keep trying until you break through.
>
> I've tried to print debug info from my threads to figure out what's
> locking the database, but to my eyes they seem to do what and I didn't
> expect the database file to get locked from this usage:
>
> thread action
> 0x10edf8 select
> 0x10edf8 select
> 0x10edf8 begin immediate transaction
> 0x10edf8 insert
> 0x10edf8 insert
> 0x10edf8 commit
> (switched to another thread)
> 0x10d4f8 select
> 0x10d4f8 select
> 0x10d4f8 begin immediate transaction
> 0x10d4f8 insert
> 0x10d4f8 insert
> 0x10d4f8 commit
> 0x10d4f8 select
> 0x10d4f8 select
> 0x10d4f8 begin immediate transaction
> SQL Error: SQLITE_BUSY[5]: database is locked
> 0x10d4f8 waiting 1 sec
> 0x10d4f8 begin immediate transaction
> SQL Error: SQLITE_BUSY[5]: database is locked
> ...
>
> Do you have any idea what can cause my problem? Do you have any
> suggestions about how I can dig deeper and debug better to find the real
> cause?
>
> Thank you in advance,
> Daniel
>
> ______________________________**_________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
