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
