Hi,
I think I have found a regression/bug in Sqlite with respect to
the sqlite3_get_autocommit() function.
Sometimes, when multiple connections are used in multiple threads,
sqlite3_get_autocommit() reports that the connection has a transaction
open (i.e. not in auto commit mode), even though sqlite3_step() returns
SQLITE_BUSY. I have verified that sqlite3_get_autocommit() reports that
there is no transaction open before sqlite3_step() is called. The problem
seems to be specific to the WAL journal mode, as I haven't been able to
reproduce it for
Initially, the problem only showed up under Linux, but I have now been able
to reproduce the problem on Windows too. For me it shows up quicker under
linux, but that may just be because of the difference in environments. I
have attached a single-file C++11 application that consistently reproduces
the problem for me.
I was also able to bisect the introduction of the problem to sqlite
v3.17.0. v3.16.2 does not have the problem. The latest 3.24.0 release
does. I am not an expert on the sqlite source code, but during my
debugging, I was able to determine that the auto commit flag was being
cleared in "case OP_AutoCommit", which returns without error. Obviously,
something else must be causing the SQLITE_BUSY afterwards, but is not
resetting the auto commit flag.
In my code, I have worked around the problem by issuing a "rollback"
whenever I detect the problem described.
I hope this is enough information to be able to solve the bug. If,
however, this behaviour turns out not to be a bug, but something that is
expected to happen occasionally, then the "rollback" provides a reasonable
way to mitigate the issue.
Regards
Nic
#include <assert.h>
#include <stdio.h>
#include <chrono>
#include <thread>
#include "sqlite3.h"
static const char DATABASE_NAME[] = "test.db";
using namespace std::chrono;
static volatile bool finished = false;
static int Update (sqlite3 *db)
{
printf ("Entering Update() for db = %p\n", db);
int rc = SQLITE_OK;
unsigned loops = 0;
const char *msg = "UNKNOWN";
while (finished == false && rc == SQLITE_OK)
{
++loops;
// We should not be in a transaction.
assert (sqlite3_get_autocommit (db) != 0);
// The failure always occurs on "begin immediate", so
// prepare and step it manually.
sqlite3_stmt *st = nullptr;
rc = sqlite3_prepare_v2 (db, "begin immediate", 15, &st,
nullptr);
if (rc != SQLITE_OK)
{
msg = "sqlite3_prepare_v2()";
break;
}
// Repeat while SQLITE_BUSY
do
{
rc = sqlite3_step (st);
if (rc == SQLITE_DONE)
{
break;
}
msg = "sqlite3_step()";
if (rc == SQLITE_BUSY)
{
// Since sqlite3_step() failed, we do not expect
// to be in a transaction.
if (sqlite3_get_autocommit (db) == 0)
{
// Oops, this is unexpected!
printf ("Failed!\n");
msg = "sqlite3_get_autocommit()";
break;
}
// Back off for a while.
std::this_thread::sleep_for (milliseconds
(rand() % 15));
}
}
while (rc == SQLITE_BUSY);
sqlite3_finalize (st);
if (rc != SQLITE_DONE)
{
break;
}
// UPDATE should not fail, as we have our lock,
// so just use sqlite3_exec() for simplicity.
rc = sqlite3_exec (db, "UPDATE t SET i = i + 1", nullptr,
nullptr, nullptr);
if (rc != SQLITE_OK)
{
msg = "'UPDATE'";
break;
}
// COMMIT can cause a SQLITE_BUSY
do
{
msg = "'COMMIT'";
rc = sqlite3_exec (db, "commit", nullptr, nullptr,
nullptr);
if (rc == SQLITE_BUSY)
{
// Back off for a while.
std::this_thread::sleep_for (milliseconds
(rand() % 15));
}
}
while (rc == SQLITE_BUSY);
}
if (rc != SQLITE_OK && rc != SQLITE_INTERRUPT)
{
printf ("%s failed on db %p, with %d\n", msg, db, rc);
}
printf ("Exiting Update() for db = %p, loops = %u\n", db, loops);
// Tell the other threads to terminate their loops too.
finished = true;
return rc;
}
static sqlite3 *OpenDb()
{
sqlite3 *db;
int rc = sqlite3_open_v2 (DATABASE_NAME, &db, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, nullptr);
assert (db);
assert (rc == SQLITE_OK);
return db;
}
// Create the initial table that we need for the test.
bool InitialiseDb (sqlite3 *db)
{
// I have been able to reproduce the problem with the WAL journal mode,
// but it may affect other modes too.
int rc = sqlite3_exec (db, "PRAGMA journal_mode=WAL", nullptr, nullptr,
nullptr);
if (rc != SQLITE_OK)
{
printf ("'PRAGMA journal_mode=WAL' failed with %d\n", rc);
return false;
}
sqlite3_exec (db, "DROP TABLE t", nullptr, nullptr, nullptr);
sqlite3_exec (db, "CREATE TABLE t (i int)", nullptr, nullptr, nullptr);
if (rc != SQLITE_OK)
{
printf ("'CREATE TABLE t (i int)' failed with %d\n", rc);
return false;
}
sqlite3_exec (db, "INSERT INTO t values (0)", nullptr, nullptr,
nullptr);
if (rc != SQLITE_OK)
{
printf ("'INSERT INTO t values (0)' failed with %d\n", rc);
return false;
}
return true;
}
int main()
{
// Setup
finished = false;
sqlite3 *db1 = OpenDb();
if (InitialiseDb (db1) == false)
{
sqlite3_close (db1);
return 1;
}
sqlite3 *db2 = OpenDb();
sqlite3 *db3 = OpenDb();
sqlite3 *db4 = OpenDb();
assert (db1 && db2 && db3 && db4);
// Run the Update() function in multple threads
std::thread thread1 (&Update, db1);
std::thread thread2 (&Update, db2);
std::thread thread3 (&Update, db3);
std::thread thread4 (&Update, db4);
// Run the test for 30 seconds.
// Comment this out to loop indefinitely
for (int i = 0; finished == false && i < 30; ++i)
{
std::this_thread::sleep_for (seconds (1));
}
// Force the threads to terminate
finished = true;
sqlite3_interrupt (db1);
sqlite3_interrupt (db2);
sqlite3_interrupt (db3);
sqlite3_interrupt (db4);
// Wait for the threads and close the connections
thread1.join();
thread2.join();
thread3.join();
thread4.join();
sqlite3_close (db4);
sqlite3_close (db3);
sqlite3_close (db2);
sqlite3_close (db1);
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
