On Tue, Oct 16, 2012 at 4:33 AM, Daniel Polski <dan...@agelektronik.se>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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users