Yes exactly my point for some time now!!!
I think it would be nice if sqlite could optionally maintain a pre-write
state version of the cached page in memory. This really means maintaining some
form of page versioning, which is already done via the pager writing to the
journal. The pager must write the original page that undergoes modification to
the journal file before it can manipulate the cached version.
To expedite performance the journaled pages could be maintained in memory as
well as written to disk. That way a writer and reader could co-exist.
Writer modifies a page, (acquiring a Write page lock).
Make a copy in memory, saving a reference to this via the cache as well
as its offset in the journal.
The origainal Page is then written to the disk journal.
If the Journal cache exceeds memory capacity, Just release pages and
retain a file offset pointer in memory.
The reader when encountering a locked page, could then check the cached
journal pages.
If not found in the cache use the file offset reference and read this in
from the journal file.
This would take care of the simple case of writer blocking! As there is only
ever 1 writer. The original page is sufficient to take care of (isolation) in
ACID.
As it stands today, sqlite imho, is ACD, it is not have isolated
transactional capabilities.
Dan Kennedy <[EMAIL PROTECTED]> wrote:
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]
-----------------------------------------------------------------------------
