On Sat, 2007-01-13 at 23:55 -0800, Peter James wrote:
> 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.
Here's how I figure it:
When the shared-cache was DISabled, the first connection loaded it's own
cache and then modified it (the INSERT statement). No writing to the
disk has occured at this point, only the cache owned by the first
connection.
The second connection then loaded up it's own cache (from the file on
disk) and queried it. No problem here.
However when the shared-cache was ENabled the second connection
piggy-backed onto (i.e shares) the cache opened by the first connection.
Since the pages corresponding to table "foo" in this cache contain
uncommitted modifications, SQLite prevents the second connection from
reading them - returning SQLITE_LOCKED. Otherwise, the second connection
would be reading uncommitted data.
So you're right, when you use shared-cache mode there is less
concurrency in some circumstances.
Dan.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
