Works as intended. "our code base does not use transactions at all" does NOT mean that there are no transactions, just that SQLite uses *implicit* transactions, i.e. every statement is in it's own transaction.
"we can share a connection between threads as long as we don't read/write into the same table at the same time" is NOT a correct assumption. The assertion you need to prove for SQLite running in multi-threaded mode is "we can share a connection between threads as long as the connection is not used simultaneously in two or more threads". What you program is doing is guaranteed to provoke SQLITE_BUSY_SNAPSHOT errors. Your first thread is creating an implicit transaction on connection FIRST by reading from table FIRST. Your second thread is creating an implicit transaction on connection SECOND by writing into table SECOND. The implied COMMIT "invalidates" the snapshot on connection FIRST. Your third thread now attempts to insert into table THIRD on connection FIRST. But connection FIRST is within an READ transaction started by your first thread, so it has to escalate it's transaction to a WRITE transaction. But because it is in an "invalid" snapshot, it cannot do so. This is well documented behaviour. https://sqlite.org/isolation.html "Another example: X starts a read transaction using BEGIN and SELECT, then Y makes a changes to the database using UPDATE. Then X tries to make a change to the database using UPDATE. The attempt by X to escalate its transaction from a read transaction to a write transaction fails with an SQLITE_BUSY_SNAPSHOT error because the snapshot of the database being viewed by X is no longer the latest version of the database. If X were allowed to write, it would fork the history of the database file, which is something SQLite does not support. In order for X to write to the database, it must first release its snapshot (using ROLLBACK for example) then start a new transaction with a subsequent BEGIN. " -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Kira Backes Gesendet: Montag, 12. August 2019 09:33 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed! Dear sqlite mailing list, I had to spend 2 days debugging a SQLITE_BUSY_SNAPSHOT, and reading the documentation did not help me. Our code base does not use transactions at all (we have a segmented code base protected by mutexes for a whole section, so reads/writes do not conflict ever). We neither had a crashing sqlite connection nor a corrupted database file. Even reading everything in isolation did not help me, since we're supposed to be able to share a connection between threads as long as we do not read/write into the same table at the same time, which we were able to ensure due to the section mutexes. After thinking a very long time about this I found the reason: You absolutely can not share a WAL connection between threads or risk SQLITE_BUSY events. Yes, you heard right. If connection A runs a SELECT query in table A, then connection B inserts something into table B, and then you try in a concurrent thread to INSERT into table C using connection A you will get SQLITE_BUSY_SNAPSHOT errors until all SELECT queries are finished on connection A (in my case this took about a minute because I was reading some cache tables with several threads, so there was never a second where all queries were finished...). So I had SQLITE_BUSY_SNAPSHOT errors for a full minutes even though I never used transactions anywhere... Just because I had some long running SELECT statements in unrelated tables it made the connection completely unusable. This is not a theoretical case, this can happen *VERY* easily and as far as I can tell this is not documented anywhere (and believe me, I've ready nearly every single page of the sqlite3 documentation). So you should really really document this very easy to trigger case. And if you don't believe me: since I'm a nice girl I've written a unit test (C++, catch2) for this which reliably reproduces this behavior: TEST_CASE("Test SQLITE_BUSY_SNAPSHOT", "[sqlite3]") { std::string db_name{"test_sqlite3_busy_snapshot.sqlite3"}; if (std::filesystem::exists(db_name)) { REQUIRE(std::filesystem::remove(db_name)); } std::mt19937_64 engine{std::random_device{}()}; std::uniform_int_distribution<int64_t> u(INT64_MIN, INT64_MAX); auto open_db_fn = [&]{ sqlite3* handle; REQUIRE(sqlite3_open_v2(db_name.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr) == SQLITE_OK); REQUIRE(sqlite3_exec(handle, "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA secure_delete=FAST;", nullptr, nullptr, nullptr) == SQLITE_OK); REQUIRE(sqlite3_busy_timeout(handle, 600'000) == SQLITE_OK); return handle; }; std::string insert_into_FIRST = "INSERT INTO `FIRST_table` VALUES (?)"s; std::string insert_into_SECOND = "INSERT INTO `SECOND_table` VALUES (?)"s; std::string insert_into_THIRD = "INSERT INTO `THIRD_table` VALUES (?)"s; // initialize db, insert 4 rows into FIRST table { sqlite3* init_handle{open_db_fn()}; REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `FIRST_table` (`a` LONG)", nullptr, nullptr, nullptr) == SQLITE_OK); REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `SECOND_table` (`a` LONG)", nullptr, nullptr, nullptr) == SQLITE_OK); REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `THIRD_table` (`a` LONG)", nullptr, nullptr, nullptr) == SQLITE_OK); sqlite3_stmt* stmt{nullptr}; REQUIRE(sqlite3_prepare_v3(init_handle, insert_into_FIRST.c_str(), -1, 0, &stmt, nullptr) == SQLITE_OK); for (int i{0}; i != 4; ++i) { REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK); REQUIRE(sqlite3_step(stmt) == SQLITE_DONE); REQUIRE(sqlite3_reset(stmt) == SQLITE_OK); } REQUIRE(sqlite3_finalize(stmt) == SQLITE_OK); REQUIRE(sqlite3_close(init_handle) == SQLITE_OK); } // init handle is closed, from here on we have a clean state sqlite3* FIRST_handle{open_db_fn()}; sqlite3* SECOND_handle{open_db_fn()}; std::atomic_bool shall_continue_read_from_FIRST = true; // Continuously read from FIRST table using FIRST handle std::thread thread_read_from_FIRST{[&] { sqlite3_stmt* stmt; REQUIRE(sqlite3_prepare_v3(FIRST_handle, "SELECT * FROM `FIRST_table`", -1, SQLITE_PREPARE_PERSISTENT, &stmt, nullptr) == SQLITE_OK); while (shall_continue_read_from_FIRST) { while (sqlite3_step(stmt) == SQLITE_ROW) { sqlite3_column_int(stmt, 0); std::this_thread::sleep_for(std::chrono::milliseconds{5}); } sqlite3_reset(stmt); } sqlite3_finalize(stmt); }}; // Insert once into SECOND table (untouched before) using SECOND handle (unused before) { sqlite3_stmt* stmt; REQUIRE(sqlite3_prepare_v3(SECOND_handle, insert_into_SECOND.c_str(), -1, SQLITE_PREPARE_PERSISTENT, &stmt, nullptr) == SQLITE_OK); REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK); REQUIRE(sqlite3_step(stmt) == SQLITE_DONE); REQUIRE(sqlite3_reset(stmt) == SQLITE_OK); sqlite3_finalize(stmt); } // Insert once into THIRD table (untouched before) using FIRST handle (which is still doing a select in another thread somewhere) { sqlite3_stmt* stmt; REQUIRE(sqlite3_prepare_v3(FIRST_handle, insert_into_THIRD.c_str(), -1, 0, &stmt, nullptr) == SQLITE_OK); REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK); REQUIRE(sqlite3_step(stmt) == SQLITE_BUSY); REQUIRE(sqlite3_extended_errcode(FIRST_handle) == SQLITE_BUSY_SNAPSHOT); REQUIRE(sqlite3_reset(stmt) == SQLITE_BUSY); REQUIRE(sqlite3_extended_errcode(FIRST_handle) == SQLITE_BUSY_SNAPSHOT); sqlite3_finalize(stmt); } // Stop select on FIRST handle and FIRST table in other thread shall_continue_read_from_FIRST = false; thread_read_from_FIRST.join(); // Retry insert into THIRD table using FIRST handle { sqlite3_stmt* stmt; REQUIRE(sqlite3_prepare_v3(FIRST_handle, insert_into_THIRD.c_str(), -1, 0, &stmt, nullptr) == SQLITE_OK); REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK); REQUIRE(sqlite3_step(stmt) == SQLITE_DONE); REQUIRE(sqlite3_reset(stmt) == SQLITE_OK); sqlite3_finalize(stmt); } std::filesystem::remove(db_name); } kind regards, Kira Backes _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
