On 7/1/2014 4:40 PM, Srikanth Bemineni wrote:
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.
It can't - Thread 2 is holding a read lock on that 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.
It can't - Thread 1 is holding a read lock on that table.
In this
case none of the threads are getting lock on the table and are waiting for
ever.
To be precise, both threads have acquired a read lock on the same table;
both are now trying and failing to promote it to a write lock.
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.
Won't help: once acquired, all locks are held until the end of
transaction. You may choose to re-read the same table later in the
transaction, and SQLite must guarantee that you see the same data. The
only way to resolve a deadlock is for one thread to roll back its
transaction.
In any of this case one thread should be the winner in getting the lock on
this table.
Which part of the documentation makes you believe that?
why is this happening for delete when concurrent update
and inserts are happening properly ?
With all due respect, I find this difficult to believe. All other things
equal, the same problem should manifest if DELETE statement is replaced
with INSERT or UPDATE. There must be something else different about the
scenarios where those operations work.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
