On 6/19/2013 4:58 PM, J Decker wrote:
I have a sqlite database that ends up with 3 connections from the same
program in a single thread. The first, only SELECT statements are
used. The second, is opened, a BEGIN TRANSACTION issued and then some
inserts. I end up opening a 3rd connection to the database and issue
another begin transaction and start more inserts.
The insert on the 3rd connection is blocked 'SQLITE_BUSY'.
After 1 second, a COMMIT on the 1st connection is issued, and stays
blocked with 'SQLITE_BUSY'.
The first connection never started an explicit transaction - why does it
issue a COMMIT? Shouldn't the second connection do that?
Is there an active SELECT statement (on any connection)? A statement is
active after a call to sqlite3_step, and until sqlite3_reset or
sqlite3_finalize. An active statement (that is not part of an explicit
transaction) would keep an implicit transaction open. A reader
transaction would then block writer transactions from other connections,
unless the database is configured for WAL mode.
Does the third connection perform any SELECTs before the insert, by any
chance? There is a possibility of a deadlock in SQLite when a
transaction starts as a reader and later tries to promote to a writer.
The documentation at http://www.sqlite.org/c3ref/busy_handler.html
describes the scenario. When two transactions deadlock this way, the
only way to make progress is for one of them to roll back. To avoid this
scenario, start writer transactions with BEGIN IMMEDIATE.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
