Yes, I second this opinion. However I believe sqlite is ACID, just not when shared cache mode is enabled...
Mike -----Ursprüngliche Nachricht----- Von: Ken [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 14. Januar 2007 17:00 An: sqlite-users@sqlite.org; [EMAIL PROTECTED] Betreff: Re: [sqlite] Of shared cache, table locks and transactions 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] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
