It seems like sqlite 3.28.0 can return stale results (missing writes
that committed before the read transaction started) when two read
transactions from a shared cache execute concurrently with a write
from a private cache or another process on a WAL database. A test
case for this issue is here (also pasted below):
http://es.csail.mit.edu/sqlite3-bug.c
Here's an example execution that demonstrates the issue (along the
lines of the above C test case). Lines are prefixed by connection ID.
R1 and R2 are connections from the same process with
SQLITE_OPEN_SHAREDCACHE, and W is a separate connection
with a private cache (same or other process).
R1: BEGIN
R1: SELECT from table
W: INSERT into table (implicit transaction)
(X)
R2: SELECT from table (implicit transaction)
As written above, R2's select does not observe W's insert. If R1
issues a COMMIT at the line marked by (X), R2's select does observe
W's insert. If R1 and R2 don't have a shared cache, R2's select does
observe W's insert.
I believe R2 not seeing W's insert violates sqlite's isolation spec
because https://www.sqlite.org/isolation.html says:
In WAL mode, SQLite exhibits "snapshot isolation". When
a read transaction starts, that reader continues to see an
unchanging "snapshot" of the database file as it existed at
the moment in time when the read transaction started.
By this specification, R2 should observe a snapshot as it existed at
the time R2's implicit transaction started, which was after W's
implicit transaction committed (and thus should have included W's
insert).
Formally speaking, serializability allows the apparent order of
transactions to differ from the order in which they were issued
(unlike strict serializability). But the above text in sqlite's
isolation page seems to suggest strict serializability, and I couldn't
find anything more precise about what sqlite's spec really is (whether
it provides serializability, strict serializability, or something
else).
Is this a bug in sqlite's implementation, or a bug in sqlite's
documentation, or am I missing something altogether here?
Thanks in advance,
Nickolai.
---
#include <stdio.h>
#include <unistd.h>
#include <stdlib.h>
#include <assert.h>
#include "sqlite3.h"
int total_rows;
static int
counter(void *arg, int cols, char **data, char **names)
{
total_rows++;
return 0;
}
int
main(int ac, char **av)
{
char dbpath[64];
snprintf(dbpath, sizeof(dbpath), "/tmp/bug.%d.sqlite3", getpid());
/*
* Step 1: create a WAL database with a single table and a single row.
* The cache setting (private or shared) doesn't matter here.
*/
sqlite3 *db_w;
assert(sqlite3_open_v2(dbpath, &db_w, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, NULL) == SQLITE_OK);
assert(sqlite3_exec(db_w, "PRAGMA journal_mode = WAL",
NULL, NULL, NULL) == SQLITE_OK);
assert(sqlite3_exec(db_w, "CREATE TABLE t (a INTEGER PRIMARY KEY)",
NULL, NULL, NULL) == SQLITE_OK);
assert(sqlite3_exec(db_w, "INSERT INTO t (a) VALUES (1)",
NULL, NULL, NULL) == SQLITE_OK);
sqlite3_close(db_w);
/*
* Step 2: read the single row from a shared-cache connection, inside
* a transaction that doesn't commit yet.
*/
sqlite3 *db_r;
assert(sqlite3_open_v2(dbpath, &db_r, SQLITE_OPEN_READONLY |
SQLITE_OPEN_SHAREDCACHE, NULL) == SQLITE_OK);
assert(sqlite3_exec(db_r, "BEGIN",
NULL, NULL, NULL) == SQLITE_OK);
assert(sqlite3_exec(db_r, "SELECT a FROM t WHERE a=1",
counter, NULL, NULL) == SQLITE_OK);
assert(total_rows == 1);
/*
* Step 3: insert a second row from a private-cache connection.
* The bug also manifests if this insert happens from a different
* process, such as using the system() invocation below.
*/
if (1) {
sqlite3 *db_w2;
assert(sqlite3_open_v2(dbpath, &db_w2, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_PRIVATECACHE, NULL) == SQLITE_OK);
assert(sqlite3_exec(db_w2, "INSERT INTO t (a) VALUES (2)",
NULL, NULL, NULL) == SQLITE_OK);
sqlite3_close(db_w2);
} else {
char cmd[256];
snprintf(cmd, sizeof(cmd),
"sqlite3 %s \"INSERT INTO t (a) VALUES (2)\"", dbpath);
system(cmd);
}
/*
* Step 4: read the second row from a new shared-cache connection.
* This fails to find the newly inserted row, if the first read
* transaction hasn't committed yet. The bug does not show up if
* db_r commits first, with the if-(0)-ed-out statement below.
*/
sqlite3 *db_r2;
assert(sqlite3_open_v2(dbpath, &db_r2, SQLITE_OPEN_READONLY |
SQLITE_OPEN_SHAREDCACHE, NULL) == SQLITE_OK);
if (0) {
assert(sqlite3_exec(db_r, "COMMIT",
counter, NULL, NULL) == SQLITE_OK);
}
assert(sqlite3_exec(db_r2, "SELECT a FROM t WHERE a=2",
counter, NULL, NULL) == SQLITE_OK);
assert(total_rows == 2);
}
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
