Hi,
We are using the sqlite 3.7.14.1 code in our application. Lately we are
seeing a dead lock kind of state while deleting records from a table. The
deletion is done two different threads and acting upon the same table.
Sqlite is configured in WAL mode. All threads open their own shared
connection to the database. The application is complied with
SQLITE_THREADSAFE=1 and SQLITE_ENABLE_MEMORY_MANAGEMENT.
m_init = sqlite3_open_v2(
m_dbfilename.toUtf8().data(),
/* Database filename (UTF-8) */
&m_dbHandler,
/* OUT: SQLite db handle */
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_SHAREDCACHE, /* Flags */
NULL
/* Name of VFS module to use */
);
All update,insert and deletion of records in multiple threads happen in
transactions. The update/insert statement works absolutely fine with out
any dead lock. But when two or more threads try to remove an entry in the
same table, then they go into a dead lock state where sqlite3_step keeps on
returning SQLITE_LOCKED. When we did debug the code
10:00.234 Thread 1 BEGIN
10:00.235 Thread 1 select * from <table1>
10:00.234 Thread 1 select * from <table x>
10:00.456 Thread 1 delete from <table1>
10:00.456 Thread 2 BEGIN
10:00.456 Thread 2 select * from <table1>
10:00.906 Thread 2 select * from <table x>
10:01.156 Thread 2 delete from <table1>
Thread 1 SQLITE_LOCKED(6) Error <Table1> is locked
Thread 2 SQLITE_LOCKED(6) Error database table is locked
Thread 1 which is the first one to enter BEGIN and do modifications to the
table, and it should have gained the WRITE lock on the table. Even If we
consider Thread 2 was performing the select on the same table at the same
time, then Thread 2 should have locked table in its delete call. In this
case none of the threads are getting lock on the table and are waiting for
ever. In each of the thread we are waiting for a random amount of time
re-executing(sqlite3_step) the same prepared statement after calling reset
on the prepared statement.
In any of this case one thread should be the winner in getting the lock on
this table.
My question is how can I find who is locking the table. Is there any way I
can get this info ? why is this happening for delete when concurrent update
and inserts are happening properly ?
Srikanth Bemineni
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
