On Jul 22, 2010, at 6:07 PM, Alan Chandler wrote: > I have been reading about WAL, and there are a few questions I would > like to ask. > > 1) I am slightly confused about readers building the WAL index. It > says > way down the page > > quote: > > Using an ordinary disk file to provide shared memory has the > disadvantage that it might actually do unnecessary disk I/O by writing > the shared memory to disk. However, the developers do not think this > is > a major concern since the wal-index rarely exceeds 32 KiB in size > and is > never synced. Furthermore, the wal-index backing file is deleted when > the last database connection disconnects, which often prevents any > real > disk I/O from ever happening. > > /quote > > In a situation where I have a web application (php based) in essence > each request makes a database connection, does a couple of queries, > and > exits (thereby closing the connection). Unless my site gets really > loaded (which in the application I am thinking of porting over to > SQLite > doesn't happen) it is quite frequent that there are no open database > connections. Does this mean that every web access has to rebuild the > index just to perform a query? is this a long process?
When in WAL mode, clients use file-locks to implement a kind of robust (crash-proof) reference counting for each database file. When a client disconnects, if it is the only client connected to that database file, it automatically runs a checkpoint and then deletes the *-wal file. When the next client connects (incrementing the database ref-count from 0 to 1), it does have to rebuild the wal-index. But since the *-wal file is empty, this is pretty quick. On the other hand, if the only client connected to a database does not disconnect cleanly (i.e. it crashes, the system crashes, or the client exits without calling sqlite3_close()), then it leaves the *-wal file in place. In this case, when the next client connects to the database file it has to read the entire *-wal file to reconstruct the wal-index. If the *-wal file is large, this might take a while. So one thing to bear in mind when using WAL mode is always to call sqlite3_close() on all database handles before process exit. Otherwise you may leave a big *-wal file in the file-system which will need to be traversed by the next client that connects to the database. > 2) If the WAL is not synced back to the database (because the 1000 > pages > have yet to be created), is it still in non volatile storage, synced > at > the end of the last commit. In other words, if the server happens to > get shut down and rebooted, will the WAL still be there and continue > to > function from the last committed transaction. (And, given question 1 > the WAL index will just be rebuilt). Yes and yes. Assuming you are using synchronous=FULL. If using synchronous=NORMAL then the contents of the WAL may or may not have made it to persistent media when the crash occured. In this case the checksums in the WAL are used to recover as many transactions as possible. In other words, in synchronous=NORMAL mode your transactions are not necessarily durable if a system crash occurs. You might lose any transactions written since the most recent checkpoint. > 3) When the 1000 page (or perhaps a smaller number that might be more > appropriate in my case) watermark is passed, does the writer (if not > interrupted) completely clear the WAL. It tries to. If there are long-running read transactions still using old database snapshots it might not be able to checkpoint the entire WAL. In this case it does as much as it can without overwriting any database pages that existing readers may still be using. > 4) Is the escalation of locks process process still in place. In > particular, are the semantics of BEGIN, BEGIN IMMEDIATE and BEGIN > EXCLUSIVE the same? (My current approach is that for web access that > does only reads, I do a BEGIN, and then the SELECTS and then COMMIT, > for > one that has some INSERT and UPDATES, I do a BEGIN IMMEDIATE, do an > application level check (via SELECT) on a version field on the > database > to ensure its the same as when I originally read it, ROLLBACK if it > isn't, but if it is proceed with the INSERTS and UPDATES and finally > COMMIT). In WAL mode, "BEGIN IMMEDIATE" and "BEGIN EXCLUSIVE" do the same thing - open a write transaction. In both cases readers are not affected (different from rollback mode - in rollback mode a "BEGIN EXCLUSIVE" would lock out all readers). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
