Documentation Source Text

<title>SQLite Unlock-Notify API</title>
<tcl>hd_keywords {Using the SQLite Unlock Notification Feature}</tcl>

<style>
  pre a:visited, pre a:link { text-decoration: none ; color: #40534b }
  pre {
    background: #F3F3F3;
    float: right;
    padding: 1ex 2ex;
    margin-left: 1em;
    border: solid black 1px;
  }
  h1,h2 { clear: both ; text-align: center }
</style>

<h1>Using the sqlite3_unlock_notify() API</h1>

<tcl>
proc C_Code {text} {
  hd_puts "<pre>\n"
  set iLine 0
  foreach zLine [split [string trim $text "\n"] "\n"] {
    set zLine [string map {< &lt; > &gt;} $zLine]
    set zSubspec {<span style="color:blue;font-style:italic">&</span>}
    regsub {(/\*|^ *\*\*|\*/).*} $zLine $zSubspec zLine 
    if {[regexp {(.*)(sqlite3[a-z0-9_]*)(.*)} $zLine -> one two three]} {
      hd_puts $one
      hd_resolve "\[$two\]"
      hd_puts $three
    } else {
      hd_puts $zLine
    }
    hd_puts "\n"
  }
  hd_puts "</pre>\n"
}

set fd [open $::SRC/src/test_thread.c r]
set code ""
set copy 0
while {![eof $fd]} {
  set line [gets $fd]
  if { [string match *BEGIN_SQLITE_BLOCKING_STEP* $line] } {
    set copy 1
  } elseif { [string match *END_SQLITE_BLOCKING_STEP* $line] } {
    set copy 0
  } elseif {$copy} {
    append code "$line\n"
  }
}
C_Code $code
</tcl>

<p>
  When two or more connections access the same database in shared-cache 
  mode, read and write (shared and exclusive) locks on individual tables
  are used to ensure that concurrently executing transactions are kept 
  isolated. Before writing to a table, a write (exclusive) lock must be 
  obtained on that table. Before reading, a read (shared) lock must be
  obtained. A connection releases all held table locks when it concludes
  its transaction. If a connection cannot obtain a required lock, then
  the call to [sqlite3_step()] returns SQLITE_LOCKED.

<p>
  This page presents a technique using the SQLite [sqlite3_unlock_notify()]
  API to create a version of sqlite3_step() that blocks until the required
  lock is available instead of returning SQLITE_LOCKED immediately, for
  use in multi-threaded applications. If the sqlite3_blocking_step() function
  presented to the left returns SQLITE_LOCKED, this indicates that to block 
  would deadlock the system.

<p>
  The [sqlite3_unlock_notify()] API, which is only available if the library is
  compiled with the pre-processor symbol SQLITE_ENABLE_UNLOCK_NOTIFY defined,
  is [sqlite3_unlock_notify | documented here]. This page is no substitute for
  reading the full API documentation!

<p><b>The sqlite3_unlock_notify() API</b>

<p>
  After a call to [sqlite3_step()] returns SQLITE_LOCKED, the
  [sqlite3_unlock_notify()] API may be invoked to register for an 
  unlock-notify callback. The unlock-notify callback is invoked by SQLite
  after the database connection holding the table-lock that prevented
  the call to sqlite3_step() from succeeding has finished its transaction
  and released all locks. For example, if the call to sqlite3_step() is an
  attempt to read from table X, and some other connection Y is holding a
  write-lock on table X, then sqlite3_step() will return SQLITE_LOCKED. If
  sqlite3_unlock_notify() is then called, the unlock-notify callback will
  be invoked after connection Y's transaction is concluded. The connection 
  that the unlock-notify callback is waiting on, in this case connection 
  Y, is known as the "blocking connection".

<p>
  If a call to sqlite3_step() that attempts to write to a database table
  returns SQLITE_LOCKED, then more than one other connection may be holding 
  a read-lock on the database table in question. In this case SQLite simply
  selects one of those other connections at random and issues the 
  unlock-notify callback when that connection's transaction is finished.
  Whether the call to sqlite3_step() was blocked by one or many connections, 
  when the corresponding unlock-notify callback is issued it is not 
  guaranteed that the required lock is available, only that it may be.

<p>
  When the unlock-notify callback is issued, it is issued from within a
  call to sqlite3_step() (or sqlite3_close()) associated with the blocking 
  connection. It is illegal to invoke any sqlite3_XXX() API functions from
  within an unlock-notify callback. The expected use is that the unlock-notify
  callback will single some other waiting thread or schedule some action
  to take place later.

<p>
  The algorithm used by the sqlite3_blocking_step() function is as follows:

<ol>
  <li><p> Call sqlite3_step() on the supplied statement handle. If the call
       returns anything other than SQLITE_LOCKED, then return this value
       to the caller. Otherwise, continue.

  <li><p> Invoke sqlite3_unlock_notify() on the database connection handle
       associated with the supplied statement handle to register for an
       unlock-notify callback. If the call to unlock_notify() returns
       SQLITE_LOCKED, then return this value to the caller.

  <li><p> Call sqlite3_reset() on the statement handle. Since an 
       SQLITE_LOCKED error may only occur on the first call to sqlite3_step()
       (it is not possible for one call to sqlite3_step() to return 
       SQLITE_ROW and then the next SQLITE_LOCKED), the statement handle may 
       be reset at this point without affecting the results of the query
       from the point of view of the caller. If sqlite3_reset() were not
       called at this point, the next call to sqlite3_step() would return
       SQLITE_MISUSE.

  <li><p> Block until the unlock-notify callback is invoked by another thread.
       Then go back to step 1.
</ol>

<p><b>Writer Starvation</b>

<p>
  Based on the description above, it could be concluded that if there are
  sufficient database readers reading the same table often enough, it is
  possible that the table will never become unlocked and that a connection
  waiting for a write-lock on the table will wait indefinitely. This 
  phenomena is known as writer-starvation.

<p>
  SQLite helps applications avoid this scenario. After any attempt to
  obtain a write-lock on a table fails (because one or more other 
  connections are holding read-locks), all attempts to open new transactions
  on the shared-cache fail until one of the following is true:

<ul>
  <li> The current writer concludes its transaction, OR
  <li> The number of open read-transactions on the shared-cache drops to zero.
</ul>

<p>
  Failed attempts to open new read-transactions return SQLITE_LOCKED to the
  caller. If the caller then calls sqlite3_unlock_notify() to register for
  an unlock-notify callback, the blocking connection is the connection that
  currently has an open write-transaction on the shared-cache. This prevents
  writer-starvation as, if no new read-transactions may be opened and 
  assuming all existing read-transactions are eventually concluded, the 
  writer will eventually have an opportunity to obtain the required o
  write-lock.

<p><b>The pthreads API</b>

  <p> By the time sqlite3_unlock_notify() is invoked by
      sqlite3_blocking_step(), it is possible that the blocking connection
      that prevented the sqlite3_step() call from succeeding has already
      finished its transaction. In this case, the unlock-notify callback
      is invoked immediately, before sqlite3_unlock_notify() returns. Or,
      it is possible that the unlock-notify callback is invoked by a
      second thread after sqlite3_unlock_notify() is called but before
      the thread starts waiting to be asynchronously signalled. 

  <p> Exactly how such a potential race-condition is handled depends on the
      threads and synchronization primitives interface used by the application.
      This example uses pthreads, the interface provided by modern UNIX-like 
      systems, including Linux.

  <p> The pthreads interface provides the pthread_cond_wait() function. 
      This function allows the caller to simultaneously release a mutex
      and start waiting for an asynchronous signal. Using this function,
      a "fired" flag and a mutex, the race-condition described above may
      be eliminated as follows:

  <p> When the unlock-notify callback is invoked, which may be before the
      sqlite3_blocking_step() thread begins waiting for the asynchronous 
      signal, it does the following:

    <ol>
      <li> Obtains the mutex.
      <li> Sets the "fired" flag to true.
      <li> Attempts to signal a waiting thread.
      <li> Releases the mutex.
    </ol>

  <p> When the sqlite3_blocking_step() thread is ready to begin waiting for
      the unlock-notify callback to arrive, it:

    <ol>
      <li> Obtains the mutex.
      <li> Checks if the "fired" flag has been set. If so, the unlock-notify
           callback has already been invoked. Release the mutex and continue.
      <li> Atomicly releases the mutex and begins waiting for the
           asynchronous signal. When the signal arrives, continue.
    </ol>

  <p> This way, it doesn't matter if the unlock-notify callback has already
      been invoked, or is being invoked, when the blocking_step() thread
      begins blocking.

<p><b>Possible Enhancements</b>

  <p> The code on this page could be improved in at least two ways:

  <ul>
    <li> It could manage thread priorities.
    <li> It could handle a special case of SQLITE_LOCKED that can occur
         when dropping a table or index.
  </ul>

  <p>
    Even though the sqlite3_unlock_notify() function only allows the caller
    to specify a single user-context pointer, an unlock-notify callback 
    is passed an array of such context pointers. This is because if when
    a blocking connection concludes its transaction, if there is more
    than one unlock-notify registered to call the same C function, the
    context-pointers are marshalled into an array and a single callback
    issued. If each thread were assigned a priority, then instead of just
    signalling the threads in arbitrary order as this implementation does,
    higher priority threads could be signalled before lower priority threads.

  <p>
    If a "DROP TABLE" or "DROP INDEX" SQL command is executed, and the
    associated database connection currently has one or more actively
    executing SELECT statements, then SQLITE_LOCKED is returned. If
    sqlite3_unlock_notify() is called in this case, then the specified
    callback will be invoked immediately. Unless the other running SELECT 
    statements complete execution in the meantime, re-attempting the "DROP
    TABLE" or "DROP INDEX" statement will return another SQLITE_LOCKED 
    error. In the implementation of sqlite3_blocking_step() shown to the
    left, this could cause an infinite loop.

  <p>
    The caller could distinguish between this special "DROP TABLE|INDEX" 
    case and other cases by using extended error codes. When it is appropriate
    to call sqlite3_unlock_notify(), the extended error code is
    SQLITE_LOCKED_SHAREDCACHE. Otherwise, in the "DROP TABLE|INDEX" case,
    it is just plain SQLITE_LOCKED.


<div style="clear:both"></div>