D. Richard Hipp wrote:
Paolo Vernazza wrote:
D. Richard Hipp wrote:
Dave Hayden wrote:
I'm running into a deadlock,
db1: BEGIN TRANSACTION; db2: BEGIN TRANSACTION; db1: INSERT INTO test VALUES ( 1 );
At this point, both of these return SQLITE_BUSY:
db2: UPDATE test SET num = 2 WHERE num = 1; db1: END TRANSACTION;
Is this a bug? Or do I have to do something with sqlite 3 I didn't with 2?
After the db1 transaction ends, the db2 UPDATE should be able to complete. In version 2, db2 would have blocked when it tried to begin the transaction. Version 3 allows db2 to continue future, but you still cannot have two threads changing the same database at the same time, so it also eventually blocks.
Works as designed.
But db1 transaction never ends.... it will ever return SQLITE_BUSY!
Paolo
Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can
do an END TRANSACTION (since it never made any changes) and allow
db1 to complete instead. The point is that when two threads or
processes are trying to write at the same time, one of the two
must back off, abandon their transaction (using ROLLBACK) and let
the other proceed.
But doing in that way, you can have this behaviour (and this is what happends to me):
db1: BEGIN TRANSACTION; -> SQLITE_OK db2: BEGIN TRANSACTION; -> SQLITE_OK
db1: INSERT INTO test VALUES ( 1 ); -> SQLITE_BUSY db2: INSERT INTO test VALUES ( 1 ); -> SQLITE_BUSY
db1: -> ROLLBACK -> SQLITE_OK db2: -> ROLLBACK -> SQLITE_OK
and then again....
db1: BEGIN TRANSACTION; -> SQLITE_OK db2: BEGIN TRANSACTION; -> SQLITE_OK
db1: INSERT INTO test VALUES ( 1 ); -> SQLITE_BUSY db2: INSERT INTO test VALUES ( 1 ); -> SQLITE_BUSY
db1: -> ROLLBACK -> SQLITE_OK db2: -> ROLLBACK -> SQLITE_OK
Paolo
