<a name="stale_master_journals"></a><h3>4.2 Deleting stale master journals</h3><p>A stale master journal is a master journal that is no longer beingused for anything.  There is no requirement that stale master journalsbe deleted.  The only reason for doing so is to free up disk space.</p><p>A master journal is stale if no individual file journals are pointingto it.  To figure out if a master journal is stale, we first read themaster journal to obtain the names of all of its file journals.  Thenwe check each of those file journals.  If any of the file journals namedin the master journal exists and points back to the master journal, thenthe master journal is not stale.  If all file journals are either missingor refer to other master journals or no master journal at all, then themaster journal we are testing is stale and can be safely deleted.</p><a name="writing"></a><h2>5.0 Writing to a database file</h2><p>To write to a database, a process must first acquire a SHARED lockas described above (possibly rolling back incomplete changes if thereis a hot journal). After a SHARED lock is obtained, a RESERVED lock must be acquired.The RESERVED lock signals that the process intends to write to thedatabase at some point in the future.  Only one process at a timecan hold a RESERVED lock.  But other processes can continue to readthe database while the RESERVED lock is held.</p><p>If the process that wants to write is unable to obtain a RESERVEDlock, it must mean that another process already has a RESERVED lock.In that case, the write attempt fails and returns SQLITE_BUSY.</p><p>After obtaining a RESERVED lock, the process that wants to writecreates a rollback journal.  The header of the journal is initializedwith the original size of the database file.  Space in the journal headeris also reserved for a master journal name, though the master journalname is initially empty.</p><p>Before making changes to any page of the database, the process writesthe original content of that page into the rollback journal.  Changesto pages are held in memory at first and are not written to the disk.The original database file remains unaltered, which means that otherprocesses can continue to read the database.</p><p>Eventually, the writing process will want to update the databasefile, either because its memory cache has filled up or because it isready to commit its changes.  Before this happens, the writer mustmake sure no other process is reading the database and that the rollbackjournal data is safely on the disk surface so that it can be used torollback incomplete changes in the event of a power failure.The steps are as follows:</p><ol><li>Make sure all rollback journal data has actually been written to    the surface of the disk (and is not just being held in the operating    system's  or disk controllers cache) so that if a power failure occurs    the data will still be there after power is restored.</li><li>Obtain a PENDING lock and then an EXCLUSIVE lock on the database file.    If other processes are still have SHARED locks, the writer might have    to wait until those SHARED locks clear before it is able to obtain    an EXCLUSIVE lock.</li><li>Write all page modifications currently held in memory out to the    original database disk file.</li></ol><p>If the reason for writing to the database file is because the memorycache was full, then the writer will not commit right away.  Instead,the writer might continue to make changes to other pages.  Before subsequent changes are written to the database file, the rollbackjournal must be flushed to disk again.  Note also that the EXCLUSIVElock that the writer obtained in order to write to the database initiallymust be held until all changes are committed.  That means that no otherprocesses are able to access the database from thetime the memory cache first spills to disk until the transactioncommits.</p><p>When a writer is ready to commit its changes, it executes the followingsteps:</p><ol><li value="4">   Obtain an EXCLUSIVE lock on the database file and   make sure all memory changes have been written to the database file   using the algorithm of steps 1-3 above.</li><li>Flush all database file changes to the disk.  Wait for those changes    to actually be written onto the disk surface.</li><li>Delete the journal file.  This is the instant when the changes are    committed.  Prior to deleting the journal file, if a power failure    or crash occurs, the next process to open the database will see that    it has a hot journal and will roll the changes back.    After the journal is deleted, there will no longer be a hot journal    and the changes will persist.    </li><li>Drop the EXCLUSIVE and PENDING locks from the database file.    </li></ol><p>As soon as PENDING lock is released from the database file, otherprocesses can begin reading the database again.  In the current implementation,the RESERVED lock is also released, but that is not essential.  Futureversions of SQLite might provide a "CHECKPOINT" SQL command that willcommit all changes made so far within a transaction but retain theRESERVED lock so that additional changes can be made without givenany other process an opportunity to write.</p><p>If a transaction involves multiple databases, then a more complexcommit sequence is used, as follows:</p><ol><li value="4">   Make sure all individual database files have an EXCLUSIVE lock and a   valid journal.<li>Create a master-journal.  The name of the master-journal is arbitrary.    (The current implementation appends random suffixes to the name of the    main database file until it finds a name that does not previously exist.)    Fill the master journal with the names of all the individual journals    and flush its contents to disk.<li>Write the name of the master journal into    all individual journals (in space set aside for that purpose in the    headers of the individual journals) and flush the contents of the    individual journals to disk and wait for those changes to reach the    disk surface.<li>Flush all database file changes to the disk.  Wait for those changes    to actually be written onto the disk surface.</li><li>Delete the master journal file.  This is the instant when the changes are    committed.  Prior to deleting the master journal file, if a power failure    or crash occurs, the individual file journals will be considered hot    and will be rolled back by the next process that    attempts to read them.  After the master journal has been deleted,    the file journals will no longer be considered hot and the changes    will persist.    </li><li>Delete all individual journal files.<li>Drop the EXCLUSIVE and PENDING locks from all database files.    </li></ol><a name="writer_starvation"></a><h3>5.1 Writer starvation</h3><p>In SQLite version 2, if many processes are reading from the database,it might be the case that there is never a time when there areno active readers.  And if there is always at least one read lock on thedatabase, no process would ever be able to make changes to the databasebecause it would be impossible to acquire a write lock.  This situationis called <em>writer starvation</em>.</p><p>SQLite version 3 seeks to avoid writer starvation through the use ofthe PENDING lock.  The PENDING lock allows existing readers to continuebut prevents new readers from connecting to the database.  So when aprocess wants to write a busy database, it can set a PENDING lock whichwill prevent new readers from coming in.  Assuming existing readers doeventually complete, all SHARED locks will eventually clear and thewriter will be given a chance to make its changes.</p><a name="how_to_corrupt"></a><h2>6.0 How To Corrupt Your Database Files</h2><p>The pager module is robust but it is not completely failsafe.It can be subverted.  This section attempts to identify and explainthe risks.</p><p>Clearly, a hardware or operating system fault that introduces incorrect datainto the middle of the database file or journal will cause problems.Likewise, if a rogue process opens a database file or journal and writes malformeddata into the middle of it, then the database will become corrupt.There is not much that can be done about these kinds of problemsso they are given no further attention.</p><p>SQLite uses POSIX advisory locks to implement locking on Unix.  Onwindows it uses the LockFile(), LockFileEx(), and UnlockFile() systemcalls.  SQLite assumes that these system calls all work as advertised.  Ifthat is not the case, then database corruption can result.  One shouldnote that POSIX advisory locking is known to be buggy or even unimplementedon many NFS implementations (including recent versions of Mac OS X)and that there are reports of locking problemsfor network filesystems under windows.  Your best defense is to notuse SQLite for files on a network filesystem.</p><p>SQLite uses the fsync() system call to flush data to the disk under Unix andit uses the FlushFileBuffers() to do the same under windows.  Once again,SQLite assumes that these operating system services function as advertised.But it has been reported that fsync() and FlushFileBuffers() do not alwayswork correctly, especially with inexpensive IDE disks.  Apparently somemanufactures of IDE disks have defective controller chips that reportthat data has reached the disk surface when in fact the data is stillin volatile cache memory in the disk drive electronics.  There are alsoreports that windows sometimes chooses to ignore FlushFileBuffers() forunspecified reasons.  The author cannot verify any of these reports.But if they are true, it means that database corruption is a possibilityfollowing an unexpected power loss.  These are hardware and/or operatingsystem bugs that SQLite is unable to defend against.</p><p>If a crash or power failure occurs and results in a hot journal but thatjournal is deleted, the next process to open the database will notknow that it contains changes that need to be rolled back.  The rollbackwill not occur and the database will be left in an inconsistent state.Rollback journals might be deleted for any number of reasons:</p><ul><li>An administrator might be cleaning up after an OS crash or power failure,    see the journal file, think it is junk, and delete it.</li><li>Someone (or some process) might rename the database file but fail to    also rename its associated journal.</li><li>If the database file has aliases (hard or soft links) and the file    is opened by a different alias than the one used to create the journal,    then the journal will not be found.  To avoid this problem, you should    not create links to SQLite database files.</li><li>Filesystem corruption following a power failure might cause the    journal to be renamed or deleted.</li></ul><p>The last (fourth) bullet above merits additional comment.  When SQLite createsa journal file on Unix, it opens the directory that contains that file andcalls fsync() on the directory, in an effort to push the directory informationto disk.  But suppose some other process is adding or removing unrelatedfiles to the directory that contains the database and journal at the themoment of a power failure.  The supposedly unrelated actions of this otherprocess might result in the journal file being dropped from the directory andmoved into "lost+found".  This is an unlikely scenario, but it could happen.The best defenses are to use a journaling filesystem or to keep thedatabase and journal in a directory by themselves.</p><p>For a commit involving multiple databases and a master journal, if thevarious databases were on different disk volumes and a power failure occursduring the commit, then when the machine comes back up the disks mightbe remounted with different names.  Or some disks might not be mountedat all.   When this happens the individual file journals and the masterjournal might not be able to find each other. The worst outcome fromthis scenario is that the commit ceases to be atomic.  Some databases might be rolled back and others might not. All databases will continue to be self-consistent.To defend against this problem, keep all databaseson the same disk volume and/or remount disks using exactly the same namesafter a power failure.</p><a name="transaction_control"></a><h2>7.0 Transaction Control At The SQL Level</h2><p>The changes to locking and concurrency control in SQLite version 3 alsointroduce some subtle changes in the way transactions work at the SQLlanguage level.By default, SQLite version 3 operates in <em>autocommit</em> mode.In autocommit mode,all changes to the database are committed as soon as all operations associatedwith the current database connection complete.</p><p>The SQL command "BEGIN TRANSACTION" (the TRANSACTION keywordis optional) is used to take SQLite out of autocommit mode.Note that the BEGIN command does not acquire any locks on the database.After a BEGIN command, a SHARED lock will be acquired when the firstSELECT statement is executed.  A RESERVED lock will be acquired whenthe first INSERT, UPDATE, or DELETE statement is executed.  No EXCLUSIVElock is acquired until either the memory cache fills up and mustbe spilled to disk or until the transaction commits.  In this way,the system delays blocking read access to the file file until thelast possible moment.</p><p>The SQL command "COMMIT"  does not actually commit the changes todisk.  It just turns autocommit back on.  Then, at the conclusion ofthe command, the regular autocommit logic takes over and causes theactual commit to disk to occur.The SQL command "ROLLBACK" also operates by turning autocommit back on,but it also sets a flag that tells the autocommit logic to rollback ratherthan commit.</p><p>If the SQL COMMIT command turns autocommit on and the autocommit logicthen tries to commit change but fails because some other process is holdinga SHARED lock, then autocommit is turned back off automatically.  Thisallows the user to retry the COMMIT at a later time after the SHARED lockhas had an opportunity to clear.</p><p>If multiple commands are being executed against the same SQLite databaseconnection at the same time, the autocommit is deferred until the verylast command completes.  For example, if a SELECT statement is beingexecuted, the execution of the command will pause as each row of theresult is returned.  During this pause other INSERT, UPDATE, or DELETEcommands can be executed against other tables in the database.  But noneof these changes will commit until the original SELECT statement finishes.</p><hr><small><i>This page last modified 2008/04/27 15:38:53 UTC</i></small></div></body></html>