Hey folks...
I have a situation that caused me a little head-scratching and I'm wondering
if it's intended behavior or not.
I'm running a server thread (roughly based on test_server.c in the distro)
on top of the 3.3.6 library. The effectve call sequence in question (all
from one thread) looks something like this:
sqlite3_open("/path/to/db", &db_one);
sqlite3_prepare(db_one, "CREATE TABLE foo (id);", -1, &stmt_one, NULL);
sqlite3_step(stmt_one);
sqlite3_finalize(stmt_one);
sqlite3_prepare(db_one, "BEGIN DEFERRED;", -1, &stmt_one, NULL);
sqlite3_step(stmt_one);
sqlite3_finalize(stmt_one);
sqlite3_prepare(db_one, "INSERT INTO foo VALUES (123);", -1, &stmt_one,
NULL);
sqlite3_step(stmt_one);
sqlite3_finalize(stmt_one);
sqlite3_prepare(db_one, "SELECT count(*) FROM foo;", -1, &stmt_one, NULL);
sqlite3_step(stmt_one);
// point of interest #1
sqlite3_column_int(stmt_one, 0);
sqlite3_finalize(stmt_one);
// new connection here, previous transaction still pending...
sqlite3_open("/path/to/db", &db_two);
sqlite3_prepare(db, "SELECT count(*) FROM foo;", -1, &stmt_two, NULL);
// point of interest #2
sqlite3_step(stmt_two);
// point of interest #3
sqlite3_column_int(stmt_two, 0);
sqlite3_finalize(stmt_two);
If shared cache is DISabled, then I get "1" on the first point of interest
and "0" on the third point of interest, which is what I'd expect. The
database file is at a RESERVED lock state in both locations, and the first
point of interest gets uncommitted data since it's in the same connection,
while the second point of interest can't yet see that data since it's a
different connection and the transaction is not yet committed.
On the other hand, if shared cache is ENabled, then I get "1" on the first
point of interest and SQLITE_LOCKED at the second point of interest. This
would seem to indicate an actual degradation of concurrency by using shared
caching. Without shared caching, readers in the same thread as a pending
writer are allowed. With shared caching, they are not. The EXPLAIN output
seems to confirm that this is a result of the binary nature of table locks
vs. the staged nature of sqlite file locks.
This came up when I was running the JUnit tests for the SQLiteJDBC driver (
http://zentus.com/sqlitejdbc). TransactionTest.testInsert() failed out in
the manner described above when using a server-based shared-cache backend.
I scanned the CVS logs and didn't see anything obvious between 3.3.6 and
3.3.10 that would change this behavior.
Thoughts?
Thanks,
Pete.
