Date: Fri, 19 Jun 2009 14:53:05 -0700
From: Robert Lehr <rl...@cadence.com>
Subject: Re: [sqlite] async io and locks
To: sqlite-users@sqlite.org
In-Reply-To: <43c62cbb-57db-4d1b-af36-2facf239c...@gmail.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes

I have never been able to reply to messages w/in the mailing-list's
thread.  Outlook must be munging the headers to prevent proper
threading.  I'm trying to force the threading by manipulating the
headers manually.  I apologize if this reply winds up in the top-level
as all of my others have.

On Jun 20, 2009, at 12:30 AM, Dan wrote:
> On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote:
> > I finally got around to reviewing SQLite's asynchronous I/O 
> > functionality.
> >
> >    http://sqlite.org/asyncvfs.html <http://sqlite.org/asyncvfs.html>
> >
> > We actually have an C++ wrapper to uses the same concept, a background 
> > thread for I/O.
> 
> You mean you have implemented the callback methods in struct sqlite3_vfs to 
> read
> and write asynchronously just as we have? Or some other technique?
> 

Another technique - the C++ __wrapper__, akin to the numerous other C++ wrappers
that are being hacked together around the world.  We required asynchrony.  We
therefore wrote our own wrapper that implements it.

> >
> > W/rt to the locking policy w/ multiple updates, are there design 
> > reasons for not releasing and re-acquiring a lock between 
> > transactions?  That would facilitate higher concurrency albeit it a 
> > slightly higher cost than the current implementation.
> > That cost should match the current cost of multiple transactions, 
> > though.
> 
> It's to handle this:
> 
>    BEGIN;
>      UPDATE t1 SET <stuff> WHERE <condition>;
>    COMMIT;
>    BEGIN
>      UPDATE t2 SET <morestuff> WHERE <anothercondition>;
>    COMMIT;
> 
> If the SQLite user starts the second transaction before the asynchronous 
> thread
> has had time to commit the first, it will read the database to figure out the
> set of rows to apply the <morestuff> modifications to.  Once that has 
> happened,
> the database file cannot be unlocked before the second transaction is
> committed. Otherwise, some other client might sneak in while the database was
> unlocked and modify table t2, changing the set of rows <anothercondition>
> selects.
> 
> Of course, if the asynchronous thread manages to commit the first transaction 
> to
> disk before the user has time to execute the second, the database file will be
> unlocked between transactions.


Yeeeees, except that I verified that this interpretation is incorrect according
to the docs as I interpret them.  The relevant part of the docs follows.

        "If an application using asynchronous IO executes transactions
        in quick succession, other database users may be effectively
        locked out of the database. This is because when a BEGIN is
        executed, a database lock is established immediately."

Particularly note that the following denotes that the lock on the database file
persists beyond the duration of all transactions.

        "But when the corresponding COMMIT or ROLLBACK occurs, the
        lock is not released until the relevant part of the
        write-queue has been flushed through. As a result, if a COMMIT
        is followed by a BEGIN before the write-queue is flushed
        through, the database is never unlocked,preventing other
        processes from accessing the database."

Thus the async functionality does NOT simply wrap each update query (UPDATE,
DELETE) in a transaction.

If it did then I would be sending a completely different query, one pertaining
to broken semantics (for our purposes) of automatically wrapping my queries in
nested transactions which would, again, prevent me from adopting SQLite's
baseline async functionality.

BTW, it occurred to me as I wrote this reply that the fact the SQLite
distinguishes FETCHES from CREATES, UPDATES and DELETES indicates that it is
inspecting the queries themselves before deciding to append them to a
write-queue or not.  It therefore could detect beginnings and ends of
transactions.  Thus SQLite could enqueue transactions as it currently enqueues
write queries.

-robert
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to