Small. Fast. Reliable.
Choose any three.

使用sqlite3_unlock_notify() API
Using the sqlite3_unlock_notify() API

/* 这个示例使用 pthreads API */
/* This example uses the pthreads API */
#include <pthread.h>

/*
** 当注册解锁通知回调的时候会把这个结构体实例的指针当做用户上下文传入。
** A pointer to an instance of this structure is passed as the user-context
** pointer when registering for an unlock-notify callback.
*/
typedef struct UnlockNotification UnlockNotification;
struct UnlockNotification {
  int fired;                         /* True after unlock event has occurred */
  pthread_cond_t cond;               /* Condition variable to wait on */
  pthread_mutex_t mutex;             /* Mutex to protect structure */
};

/*
** 这个函数是注册的解锁通知回调函数。
** This function is an unlock-notify callback registered with SQLite.
*/
static void unlock_notify_cb(void **apArg, int nArg){
  int i;
  for(i=0; i<nArg; i++){
    UnlockNotification *p = (UnlockNotification *)apArg[i];
    pthread_mutex_lock(&p->mutex);
    p->fired = 1;
    pthread_cond_signal(&p->cond);
    pthread_mutex_unlock(&p->mutex);
  }
}

/*
** 这个函数假设SQLite API调用（包括sqlite3_prepare_v2() 和sqlite3_step()）
  ** 只会返回SQLITE_LOCKED。参数是相应的数据库连接。
** This function assumes that an SQLite API call (either sqlite3_prepare_v2() 
** or sqlite3_step()) has just returned SQLITE_LOCKED. The argument is the
** associated database connection.
**
** 这个函数调用sqlite3_unlock_notify()来注册一个解锁通知回调，然后一直阻塞
  ** 到注册完回调函数并返回SQLITE_OK。这时候调用者重试失败的操作。
** This function calls sqlite3_unlock_notify() to register for an 
** unlock-notify callback, then blocks until that callback is delivered 
** and returns SQLITE_OK. The caller should then retry the failed operation.
**
** 否则，如果sqlite3_unlock_notify()表明阻塞会导致系统死锁，那么这个函数会立即返回
  ** SQLITE_LOCKED。这种情况下，调用者不能再次尝试，而应该回滚当前事务。
** Or, if sqlite3_unlock_notify() indicates that to block would deadlock 
** the system, then this function returns SQLITE_LOCKED immediately. In 
** this case the caller should not retry the operation and should roll 
** back the current transaction (if any).
*/
static int wait_for_unlock_notify(sqlite3 *db){
  int rc;
  UnlockNotification un;

  /* 初始化UnlockNotification结构体*/
  /* Initialize the UnlockNotification structure. */
  un.fired = 0;
  pthread_mutex_init(&un.mutex, 0);
  pthread_cond_init(&un.cond, 0);

  /* 注册解锁通知回调 */
  /* Register for an unlock-notify callback. */
  rc = sqlite3_unlock_notify(db, unlock_notify_cb, (void *)&un);
  assert( rc==SQLITE_LOCKED || rc==SQLITE_OK );

  /* 调用sqlite3_unlock_notify() 返回SQLITE_LOCKED或SQLITE_OK */
  /* The call to sqlite3_unlock_notify() always returns either SQLITE_LOCKED 
  ** or SQLITE_OK. 
  **
  ** 如果返回SQLITE_LOCKED， 那么系统会死锁，这时候这个方法要返回SQLITE_LOCKED给调用者，
  ** 以回滚当前事务。否则，一直阻塞到解锁通知回调执行，然后返回SQLITE_OK。
  ** If SQLITE_LOCKED was returned, then the system is deadlocked. In this
  ** case this function needs to return SQLITE_LOCKED to the caller so 
  ** that the current transaction can be rolled back. Otherwise, block
  ** until the unlock-notify callback is invoked, then return SQLITE_OK.
  */
  if( rc==SQLITE_OK ){
    pthread_mutex_lock(&un.mutex);
    if( !un.fired ){
      pthread_cond_wait(&un.cond, &un.mutex);
    }
    pthread_mutex_unlock(&un.mutex);
  }

  /* 销毁互斥体和条件变量。 */
  /* Destroy the mutex and condition variables. */
  pthread_cond_destroy(&un.cond);
  pthread_mutex_destroy(&un.mutex);

  return rc;
}

/*
** 这个函数是sqlite3_step()函数的一个封装。这个函数与step()类似，
** 但是如果无法获取所需的共享缓存锁时，这个函数会阻塞，直到锁可用为止。这种情况下，
  ** 标准API 的step()函数总是返回SQLITE_LOCKED。
** This function is a wrapper around the SQLite function sqlite3_step().
** It functions in the same way as step(), except that if a required
** shared-cache lock cannot be obtained, this function may block waiting for
** the lock to become available. In this scenario the normal API step()
** function always returns SQLITE_LOCKED.
**
** 如果这个函数返回SQLITE_LOCKED，调用者就需要回滚当前事务，并稍后重试。
  ** 否则的话可能会导致系统死锁。
** If this function returns SQLITE_LOCKED, the caller should rollback
** the current transaction (if any) and try again later. Otherwise, the
** system may become deadlocked.
*/
int sqlite3_blocking_step(sqlite3_stmt *pStmt){
  int rc;
  while( SQLITE_LOCKED==(rc = sqlite3_step(pStmt)) ){
    rc = wait_for_unlock_notify(sqlite3_db_handle(pStmt));
    if( rc!=SQLITE_OK ) break;
    sqlite3_reset(pStmt);
  }
  return rc;
}

/*
** 这个函数是对sqlite3_prepare_v2()函数的封装。这个函数与prepare_v2()类似，
** 但是如果无法获取所需的共享缓存锁时，这个函数会阻塞，直到锁可用为止。这种情况下，
  ** 标准API 的prepare_v2()函数总是返回SQLITE_LOCKED。
** This function is a wrapper around the SQLite function sqlite3_prepare_v2().
** It functions in the same way as prepare_v2(), except that if a required
** shared-cache lock cannot be obtained, this function may block waiting for
** the lock to become available. In this scenario the normal API prepare_v2()
** function always returns SQLITE_LOCKED.
**
** 如果这个函数返回SQLITE_LOCKED，调用者就需要回滚当前事务，并稍后重试。
  ** 否则的话可能会导致系统死锁。
** If this function returns SQLITE_LOCKED, the caller should rollback
** the current transaction (if any) and try again later. Otherwise, the
** system may become deadlocked.
*/
int sqlite3_blocking_prepare_v2(
  sqlite3 *db,              /* Database handle. */
  const char *zSql,         /* UTF-8 encoded SQL statement. */
  int nSql,                 /* Length of zSql in bytes. */
  sqlite3_stmt **ppStmt,    /* OUT: A pointer to the prepared statement */
  const char **pz           /* OUT: End of parsed string */
){
  int rc;
  while( SQLITE_LOCKED==(rc = sqlite3_prepare_v2(db, zSql, nSql, ppStmt, pz)) ){
    rc = wait_for_unlock_notify(db);
    if( rc!=SQLITE_OK ) break;
  }
  return rc;
}

在共享缓存模式中，当两个以上链接访问同一个数据库的时候，会对每个表单独使用读锁和写锁（共享锁和互斥锁）来确保并发执行事务时候的隔离性。在写入一个表之前，必须在这个表上获取一个写（互斥）锁。在读取之前，必须获取一个读（共享）锁。在事务结束时，链接会释放持有的所有锁。如果一个链接无法获取所需的锁，那么调用sqlite3_step()会返回SQLITE_LOCKED。
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.

偶尔还会遇到调用sqlite3_prepare()或sqlite3_prepare_v2()的时候，如果无法获取每个附加库的sqlite_master表的读锁，那么也会返回SQLITE_LOCKED。这些API需要读取存储在sqlite_master表中的数据库模式，以此将SQL语句编译为sqlite3_stmt*对象。
Although it is less common, a call to sqlite3_prepare() or sqlite3_prepare_v2() may also return SQLITE_LOCKED if it cannot obtain a read-lock on the sqlite_master table of each attached database. These APIs need to read the schema data contained in the sqlite_master table in order to compile SQL statements to sqlite3_stmt* objects.

本文介绍的技术是使用SQLite的sqlite3_unlock_notify()接口来实现当调用sqlite3_step()和sqlite3_prepare_v2()时如果遇到无法获取所需锁的时候能够阻塞一直等到所需的锁可用，而不是立即返回一个SQLITE_LOCKED。如果函数sqlite3_blocking_step()或sqlite3_blocking_prepare_v2()返回了一个SQLITE_LOCKED，这表示如果阻塞会导致系统死锁。
This article presents a technique using the SQLite sqlite3_unlock_notify() interface such that calls to sqlite3_step() and sqlite3_prepare_v2() block until the required locks are available instead of returning SQLITE_LOCKED immediately. If the sqlite3_blocking_step() or sqlite3_blocking_prepare_v2() functions presented to the left return SQLITE_LOCKED, this indicates that to block would deadlock the system.

只有在编译库时预处理器定义了符号SQLITE_ENABLE_UNLOCK_NOTIFY时才能使用sqlite3_unlock_notify() API。具体文档在这里。本文并不是一篇可替代的完整API文档！
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 documented here. This article is not a substitute for reading the full API documentation!

sqlite3_unlock_notify()接口是为每个数据库连接都分配一个单独的线程的系统设计的。在实现中，没有阻止一个线程运行多个数据库连接。但是sqlite3_unlock_notify()接口每次只能在一个连接上使用，所以，这里提供的锁逻辑只适用于一个线程一个数据库连接的情况。
The sqlite3_unlock_notify() interface is designed for use in systems that have a separate thread assigned to each database connection. There is nothing in the implementation that prevents a single thread from running multiple database connections. However, the sqlite3_unlock_notify() interface only works on a single connection at a time, so the lock resolution logic presented here will only work for a single database connection per thread.

sqlite3_unlock_notify() API
The sqlite3_unlock_notify() API

在调用sqlite3_step()或sqlite3_prepare_v2()返回SQLITE_LOCKED之后，sqlite3_unlock_notify() API会注册一个解锁通知的回调函数。当阻塞sqlite3_step()或sqlite3_prepare_v2()调用的持有表所的数据库连接随后完成了事务并释放了锁之后，SQLite会执行解锁通知回调。例如，如果调用sqlite3_step()会读取表X，而另一个连接Y正持有表X的写锁，那么sqlite3_step()会返回SQLITE_LOCKED。如果这时候调用sqlite3_unlock_notify()，那么在连接Y的事务结束后会执行解锁通知回调。其中解锁通知回调等待的连接，也就是本例中的连接Y，被称为“阻塞连接”。
After a call to sqlite3_step() or sqlite3_prepare_v2() 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() or sqlite3_prepare_v2() from succeeding has finished its transaction and released all locks. For example, if a 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".

如果调用sqlite3_step()写入一个数据库表，但是返回了SQLITE_LOCK，那么至少有一个连接持有这个数据库表的读锁。这种情况下，SQLite简单的选择这些连接中的一个，然后在这个连接的事务完成后执行解锁通知回调。不管sqlite3_step()调用是被多少个连接阻塞的，当注册的解锁通知回调执行时并不能保证所需的锁是可用的，只能说时可能可以。
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 arbitrarily 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.

当执行解锁通知回调时，这个调用是在阻塞连接相应的sqlite3_step()或sqlite3_close()调用中执行的。在一个解锁通知回调中时不允许执行sqlite3_XXX() API函数的。解锁通知回调中应该是通知其他等待的线程或者调度一些稍后发生的行为。
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 signal some other waiting thread or schedule some action to take place later.

sqlite3_blocking_step()函数中使用的算法如下所示：
The algorithm used by the sqlite3_blocking_step() function is as follows:

调用sqlite3_step()来处理提供的语句。如果调用返回的不是SQLITE_LOCKED，那么将这个值返回给用户，否则继续。
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.
在处理语句相应的数据库连接上执行sqlite3_unlock_notify()，注册一个解锁通知回调。如果unlock_notify()调用返回SQLITE_LOCKED，那么将这个值返回给调用者。
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.
在另一个线程调用解锁通知回调之前一直保持阻塞。
Block until the unlock-notify callback is invoked by another thread.
调用sqlite3_reset()，因为之前第一次调用sqlite3_step()的时候返回了一个SQLITE_LOCKED错误（不会出现第一次调用sqlite3_step()返回SQLITE_ROW而后续的调用返回SQLITE_LOCKED），在这个时候重置语句处理器，以免影响查询结果。如果这个时候不调用sqlite3_reset()，那么下一次调用sqlite3_step()会返回SQLITE_MISUSE。
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.
返回第一步。
Return to step 1.

sqlite3_blocking_prepare_v2()函数中的算法与此类似，只是会忽略第四步（重置语句处理器）。
The algorithm used by the sqlite3_blocking_prepare_v2() function is similar, except that step 4 (resetting the statement handle) is omitted.

写饥饿
Writer Starvation

多个连接可能会同时持有读锁。如果多个线程都叠加着请求读锁，就可能会出现总是有至少一个线程持有读锁的情况。这时，这个表上的写锁等待可能就会永远等待下去。这种情况就叫做“写饥饿”。
Multiple connections may hold a read-lock simultaneously. If many threads are acquiring overlapping read-locks, it might be the case that at least one thread is always holding a read lock. Then a table waiting for a write-lock will wait forever. This scenario is called "writer starvation."

SQLite帮助应用程序避免了写饥饿。在多次尝试获取写锁失败后（由于一个或多个连接持有读锁），所有试图在共享缓存上打开一个新事务的请求都会失败，直到下面两个条件其中之一为真：
SQLite helps applications avoid writer starvation. 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:

当前的写入者结束了它的事务，或者
The current writer concludes its transaction, OR
共享缓存上的读事务数量降为零。
The number of open read-transactions on the shared-cache drops to zero.

打开新读事务都会失败并返回一个SQLITE_LOCKED给调用者。如果调用者这是用调用sqlite3_unlock_notify()注册一个解锁通知回调，那么阻塞线程就是当前共享内存上正打开着写入事务的连接。这就阻止饿了写饥饿，因为如果不再有新的读事务打开，并且假设所有已有的读事务都最终会结束，那么写入者最总就会有机会获取所需的写锁。
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 since 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 write-lock.

pthread API
The pthreads API

当wait_for_unlock_notify()调用sqlite3_unlock_notify()的时候，有可能阻塞sqlite3_step()或sqlite3_prepare_v2()的阻塞连接已经完成了其事务。这种情况下，会在sqlite3_unlock_notify()调用返回之前，立即执行解锁通知回调。也有可能会在sqlite3_unlock_notify()调用完成但是线程开始等待之前由其它线程执行解锁通知回调来异步通知。
By the time sqlite3_unlock_notify() is invoked by wait_for_unlock_notify(), it is possible that the blocking connection that prevented the sqlite3_step() or sqlite3_prepare_v2() 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 signaled.

具体如何处理这种可能的竞争条件，取决于应用程序使用的线程和同步原语。这个示例使用的pthread，现代的类UNIX系统包括Linux都提供了这个接口。
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.

pthread接口提供了pthread_cond_wait()函数，这个函数允许调用者同时释放一个互斥锁并等待异步信号。使用这个函数、一个“fired”标志和一个互斥锁，上面说的竞争条件就可以按照下面的方法消除：
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:

如果调用解锁通知回调发生在调用sqlite3_unlock_notify()的线程开始等待异步信号之前，会执行如下事情：
When the unlock-notify callback is invoked, which may be before the thread that called sqlite3_unlock_notify() begins waiting for the asynchronous signal, it does the following:

获取互斥锁。
Obtains the mutex.
“fired”标志设置为true。
Sets the "fired" flag to true.
尝试通知等待线程。
Attempts to signal a waiting thread.
释放互斥锁。
Releases the mutex.

当wait_for_unlock_notify()线程准备开始等待解锁通知回调到达时，会：
When the wait_for_unlock_notify() thread is ready to begin waiting for the unlock-notify callback to arrive, it:

获取互斥锁。
Obtains the mutex.
检查是否设置了“fired”标志。如果设置了，那么解锁通知回调已经执行了。释放互斥体，然后继续。
Checks if the "fired" flag has been set. If so, the unlock-notify callback has already been invoked. Release the mutex and continue.
原子的释放互斥锁并开始等待异步信号。当信号到达时，继续执行。
aAtomically releases the mutex and begins waiting for the asynchronous signal. When the signal arrives, continue.

这种方法，就不用担心在wait_for_unlock_notify()现场开始的时候，解锁通知回调已经执行过了。
This way, it doesn't matter if the unlock-notify callback has already been invoked, or is being invoked, when the wait_for_unlock_notify() thread begins blocking.

可能的增强
Possible Enhancements

本文中的代码至少有两个改进的方向：
The code in this article could be improved in at least two ways:

能够调整线程优先级。
It could manage thread priorities.
能处理卸载表或索引引起的特殊SQLITE_LOCKED情况。
It could handle a special case of SQLITE_LOCKED that can occur when dropping a table or index.

虽然sqlite3_unlock_notify()函数只允许调用者指定一个用户上下文的指针，但是解锁通知回调会传入一个这种上下文指针的数组。这是因为，当一个阻塞线程执行到事务末尾时，如果调用同样的C函数注册超过一个解锁通知回调，那么上下文指针会被整理到一个数组中，然后通过一个回调完成。如果每个线程都指定了一个优先级，那么会根据实现替换简单的随机顺序通知线程，而是改为先通知高优先级的线程，后通知低优先级的线程。
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 marshaled into an array and a single callback issued. If each thread were assigned a priority, then instead of just signaling the threads in arbitrary order as this implementation does, higher priority threads could be signaled before lower priority threads.

如果执行“DROP TABLE”或“DROP INDEX”SQL命令，并且当前数据库连接上还有一个或多个正在执行的SELECT语句，那么会返回SQLITE_LOCKED。如果在这种情况下调用sqlite3_unlock_notify()，那么会立即执行指定的回调函数。重试“DROP TABLE”或“DROP INDEX”语句会返回另一个SQLITE_LOCKED错误。左边展示了sqlite3_blocking_step()内部的实现，这可能会导致无限循环。
If a "DROP TABLE" or "DROP INDEX" SQL command is executed, and the same 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. 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.

调用者能够区分“DROP TABLE|INDEX”情况和其它使用扩展错误代码的情况。当适合调用sqlite3_unlock_notify()时，扩展错误码是SQLITE_LOCKED_SHAREDCACHE。否则在“DROP TABLE|INDEX”情况下，只有一个简单的SQLITE_LOCKED。另一个解决方案是可以限制一个查询重复尝试的次数（例如100）。虽然这个相比期望的效率会低一些，但是问题中的这个场景并不是经常会发生的。
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. Another solution might be to limit the number of times that any single query could be reattempted (to say 100). Although this might be less efficient than one might wish, the situation in question is not likely to occur often.

使用sqlite3_unlock_notify() API Using the sqlite3_unlock_notify() API

使用sqlite3_unlock_notify() API
Using the sqlite3_unlock_notify() API