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
