On Jun 20, 2009, at 6:22 AM, Robert Lehr wrote: > Once again, I am forced to reply to the top-level, outside the thread > b/c I am not receiving individual replies. I apologize for the > inconvenience. > > 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) before I sent my original query. > 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 is acquired independent of any transaction. > * 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.
I think we have quite different approaches. The SQLite asynchronous IO backend is queueing IO, not queries. It intercepts the equivalent of the write() system call. It does not overload query processing in any way. The things on the write-queue are blobs of data to be written to specific offsets within the database file, not SQL queries. It's more complex than that of course. But the point is that the write- queue is a layer between SQLite and the OS, not between the user and SQLite. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
