SQLite

#
# Run this script to generated a lockingv3.html output file
#
set rcsid {$Id: }
source common.tcl
header {File Locking And Concurrency In SQLite Version 3}

proc HEADING {level title {label {}}} {
  global pnum
  incr pnum($level)
  foreach i [array names pnum] {
    if {$i>$level} {set pnum($i) 0}
  }
  set h [expr {$level+1}]
  if {$h>6} {set h 6}
  set n $pnum(1).$pnum(2)
  for {set i 3} {$i<=$level} {incr i} {
    append n .$pnum($i)
  }
  if {$label!=""} {
    puts "<a name=\"$label\">"
  }
  puts "<h$h>$n $title</h$h>"
}
set pnum(1) 0
set pnum(2) 0
set pnum(3) 0
set pnum(4) 0
set pnum(5) 0

This document describes the new locking mechanism.
The intended audience is programmers who want to understand and/or modify
the pager code and reviewers working to verify the design
of SQLite version 3.
</p>
}

HEADING 1 {Overview} overview

puts {
<p>
Locking and concurrency control are handled by the the 
<a href="http://www.sqlite.org/cvstrac/getfile/sqlite/src/pager.c">
pager module</a>.
The pager module is responsible for making SQLite "ACID" (Atomic,

<p>The pager module effectively controls access for separate threads, or
separate processes, or both.  Throughout this document whenever the
word "process" is written you may substitute the word "thread" without
changing the truth of the statement.</p>
}

HEADING 1 {Locking} locking

puts {
<p>
From the point of view of a single process, a database file
can be in one of five locking states:
</p>

The pager module only tracks four of the five locking states.
A PENDING lock is always just a temporary
stepping stone on the path to an EXCLUSIVE lock and so the pager module
does not track PENDING locks.
</p>
}

HEADING 1 {The Rollback Journal} rollback

puts {
<p>Any time a process wants to make a changes to a database file, it
first records enough information in the <em>rollback journal</em> to
restore the database file back to its initial condition.  Thus, before
altering any page of the database, the original contents of that page
must be written into the journal.  The journal also records the initial

        empty string, and</li>
    <li>There is no RESERVED lock on the corresponding database file.</li>
    </ul>
</li>
</ul>
}

HEADING 2 {Dealing with hot journals} hot_journals

puts {
<p>
Before reading from a a database file, SQLite always checks to see if that
database file has a hot journal.  If the file does have a hot journal, then
the journal is rolled back before the file is read.  In this way, we ensure
that the database file is in a consistent state before it is read.

</ol>

<p>After the algorithm above completes successfully, it is safe to 
read from the database file.  Once all reading has completed, the
SHARED lock is dropped.</p>
}

HEADING 2 {Deleting stale master journals} stale_master_journals

puts {
<p>A stale master journal is a master journal that is no longer being
used for anything.  There is no requirement that stale master journals
be 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 pointing
to it.  To figure out if a master journal is stale, we first read the
master journal to obtain the names of all of its file journals.  Then
we check each of those file journals.  If any of the file journals named
in the master journal exists and points back to the master journal, then
the master journal is not stale.  If all file journals are either missing
or refer to other master journals or no master journal at all, then the
master journal we are testing is stale and can be safely deleted.</p>
}

HEADING 1 {Writing to a database file} writing

puts {
<p>To write to a database, a process must first acquire a SHARED lock
as described above (possibly rolling back incomplete changes if there
is 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 the

    </li>
<li>Delete all individual journal files.
<li>Drop the EXCLUSIVE and PENDING locks from all database files.
    </li>
</ol>
}

HEADING 2 {Writer starvation} writer_starvation

puts {
<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 are
no active readers.  And if there is always at least one read lock on the
database, no process would ever be able to make changes to the database
because it would be impossible to acquire a write lock.  This situation
is called <em>writer starvation</em>.</p>

<p>SQLite version 3 seeks to avoid writer starvation through the use of
the PENDING lock.  The PENDING lock allows existing readers to continue
but prevents new readers from connecting to the database.  So when a
process wants to write a busy database, it can set a PENDING lock which
will prevent new readers from coming in.  Assuming existing readers do
eventually complete, all SHARED locks will eventually clear and the
writer will be given a chance to make its changes.</p>
}

HEADING 1 {How To Corrupt Your Database Files} how_to_corrupt

puts {
<p>The pager module is robust but it is not completely failsafe.
It can be subverted.  This section attempts to identify and explain
the risks.</p>

<p>

All databases will continue to be self-consistent.
To defend against this problem, keep all databases
on the same disk volume and/or remount disks using exactly the same names
after a power failure.
</p>
}

HEADING 1 {Transaction Control At The SQL Level} transaction_control

puts {
<p>
The changes to locking and concurrency control in SQLite version 3 also
introduce some subtle changes in the way transactions work at the SQL
language level.
By default, SQLite version 3 operates in <em>autocommit</em> mode.