Does kind of seem like conflicting documentation. Begin immediate is basically immediately calling dibbs on being the next process with write permissions. But it doesn't stop anyone else from reading at that point, and doesn't stop any current readers. You have dibbs on writing, but you can't actually do that writing until all those readers are done with their queries in their own time.
https://www.sqlite.org/rescode.html#busy is where your quote came from. "To avoid encountering SQLITE_BUSY errors in the middle of a transaction, the application can use BEGIN IMMEDIATE instead of just BEGIN to start a transaction. The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent operations on the same database through the next COMMIT will return SQLITE_BUSY." https://www.sqlite.org/lang_transaction.html "After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however." "The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with an error code SQLITE_BUSY." "An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear." "In very old versions of SQLite (before version 3.7.11 - 2012-03-20) the ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. In more recent versions of SQLite, the ROLLBACK will proceed and pending statements will often be aborted, causing them to return an SQLITE_ABORT or SQLITE_ABORT_ROLLBACK error. In SQLite version 3.8.8 (2015-01-16) and later, a pending read will continue functioning after the ROLLBACK as long as the ROLLBACK does not modify the database schema." -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Andy Dickson Sent: Tuesday, July 24, 2018 1:22 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit Hello. Long time reader, first time poster here. I have read the pertinent documentation on this issue but may have missed some important point. I am puzzled by some results I am seeing. Apologies in advance for not providing a small code sample that reproduces the issue, but I think the question should be answerable without such. Using sqlite version 3.7.17 (which I know is old but I in my little cube have no power to change that). Not using WAL mode. I have a database with one process (in one thread) writing to it, and another process (also in a single thread) reading from it only. All writes are done under BEGIN TRANSACTION IMMEDIATE. Sometimes, an END TRANSACTION fails with error 5, SQLITE_BUSY. The documentation says this should not happen: "The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent operations on the same database through the next COMMIT will return SQLITE_BUSY." The read-only process has busy_timeout set to 10000, if that matters. Am I right that this should not be happening, if indeed my assertions are correct? thanks, Andy _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
