On 22/07/10 17:14, Dan Kennedy wrote: > > 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. >
Aah - this is an important point which I didn't get from the docs. I thought the wal was only cleared after the 1000 page watermark had been reached. > > 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. Thanks - that is an important point. I don't do that at the moment. I wrote >> 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. I am not so worries about a system crash killing a transaction in the middle as an otherwise quiescent system being shut down normally. However, as you say, the last closed database connection clears the WAL anyway, so I don't think this matters >> 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). I have a worry about consistency of view rather than whether or not a reader is locked out. If I do a BEGIN, SELECT1 and at that point a writer does BEGIN IMMEDIATE, SELECT3, UPDATE, COMMIT, and then I continue with SELECT2 COMMIT, will SELECT1 and SELECT2 have a consistent view of the database unaffected by the UPDATE in the middle. In other words, is the Readers view of how far up the WAL it is allowed to look get controlled by the BEGIN ... COMMIT bracket rather than just the individual SELECTS it is performing. -- Alan Chandler http://www.chandlerfamily.org.uk _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
