返回:SQLite—系列文章目录
上一篇:SQLite的PRAGMA 声明(二十三)
下一篇:SQLite、MySQL 和 PostgreSQL 数据库速度比较(本文阐述时间很早比较,不具有最新参考性)(二十五)
注意:将 PRAGMA 同步设置为 NORMAL 的 WAL 模式可避免对 fsync() 并且仅在 检查点操作。WAL 模式的使用在很大程度上避免了 需要这个异步 I/O 模块。因此,此模块不再 支持。源代码继续存在于 SQLite 源代码树中, 但它不是任何标准构建的一部分,也不再维护。 保留此文档以供历史参考。
通常,当SQLite写入数据库文件时,它会等到写入 在将控制权返回给调用应用程序之前,操作已完成。 由于与 CPU 相比,写入文件系统通常非常慢 绑定操作,这可能是性能瓶颈。异步 I/O backend 是一个扩展,它使 SQLite 执行所有写入请求 使用在后台运行的单独线程。虽然这没有 减少整体系统资源(CPU、磁盘带宽等),确实如此 允许 SQLite 快速将控制权返回给调用方,即使在写入 数据库。
1.0 功能
使用异步 I/O 时,写入请求由单独的线程处理 在后台运行。这意味着启动的线程 数据库写入不必等待(有时很慢)磁盘 I/O 发生。写入似乎发生得非常快,尽管实际上 它在后台以通常的缓慢速度发生。
异步 I/O 似乎提供了更好的响应能力,但要付出代价。 您将失去 Durable 属性。使用 SQLite 的默认 I/O 后端, 写入完成后,您就知道您写入的信息是 安全地在磁盘上。对于异步 I/O,情况并非如此。如果 程序崩溃或数据库断电后 写入,但在异步写入线程完成之前,则 数据库更改可能永远不会进入磁盘,而 数据库可能看不到您的更改。
异步 I/O 会失去持久性,但仍会保留 ACID 的其他部分:原子、一致和隔离。多 应用程序在没有耐用性的情况下相处得很好。
1.1 它是如何工作的
异步 I/O 的工作原理是创建一个 SQLite VFS 对象并将其注册到 sqlite3_vfs_register()。 当文件通过 此 VFS 被写入(使用 vfs xWrite() 方法),数据不是 直接写入磁盘,但被放置在“写入队列”中 由后台线程处理。
从中读取使用异步 VFS 打开的文件时 (使用 vfs xRead() 方法),从 磁盘和写入队列,因此从 vfs 读取器 xWrite() 似乎已经完成。
异步 I/O VFS 通过调用 API 函数 sqlite3async_initialize() 和 sqlite3async_shutdown()。 有关详细信息,请参阅下面的“编译和使用”部分。
1.2 限制
为了获得有关异步的主要思想的经验 IO,此实现特意保持简单。附加 将来可能会添加功能。
例如,按照当前实现的,如果写入发生在 超过后台编写器的 I/O 能力的稳定流 线程,则挂起的写入操作队列将无限制地增长。 如果这种情况持续足够长的时间,主机系统可能会耗尽内存。 一个更复杂的模块可以跟踪 挂起的写入,并在队列 挂起的写入变得太大。
1.3 锁定和并发
来自使用此功能的单个进程中的多个连接 异步 IO 的实现可以访问单个数据库 文件并发。从用户的角度来看,如果全部 连接来自单个进程,没有区别 在“普通”SQLite 和 SQLite 提供的并发性之间 使用异步后端。
如果启用了文件锁定(默认情况下启用),则连接 还可以从多个进程读取和写入数据库文件。 但是,并发性降低如下:
-
当使用异步 IO 的连接启动数据库时 事务,数据库立即被锁定。然而, 在所有相关操作之后,锁不会释放 在写入队列中已刷新到磁盘。这意味着 (例如)数据库可能在某些情况下保持锁定状态 发出“COMMIT”或“ROLLBACK”后的时间。
-
如果使用异步 IO 的应用程序执行事务 在快速连续的情况下,其他数据库用户可以有效地 锁定在数据库之外。这是因为当执行 BEGIN 时,会立即建立数据库锁。但 当发生相应的 COMMIT 或 ROLLBACK 时,锁定 直到写入队列的相关部分才被释放 已被冲洗。因此,如果遵循 COMMIT 在刷新写入队列之前,通过 BEGIN 将数据库 永不解锁,阻止其他进程访问 数据库。
可以在运行时使用 sqlite3async_control() 禁用文件锁定 API(见下文)。这可能会提高 NFS 或其他 NFS 或其他 网络文件系统,因为与服务器的同步往返是 避免了建立文件锁所需的条件。但是,如果多个 连接在文件锁定时尝试访问同一数据库文件 被禁用,应用程序崩溃和数据库损坏是可能的 结果。
2.0 编译与使用
异步 IO 扩展由 C 代码的单个文件组成 (sqlite3async.c) 和头文件 (sqlite3async.h),位于 SQLite 源代码树的 ext/async/ 子文件夹中,用于定义 应用程序用于激活和控制模块的 C API 功能性。
若要使用异步 IO 扩展,请将 sqlite3async.c 编译为 使用 SQLite 的应用程序的一部分。然后使用定义的 API 在 sqlite3async.h 中初始化和配置模块。
异步 IO VFS API 在注释中进行了详细描述。 sqlite3async.h. 使用 API 通常包括以下步骤:
-
通过调用 sqlite3async_initialize() 函数。
-
创建后台线程以执行写入操作和调用 sqlite3async_run()。
-
使用普通的 SQLite API 通过以下方式读取和写入数据库 异步 IO VFS。
有关详细信息,请参阅 sqlite3async.h 头文件中的注释。或本文编写时最新代码如下:
#ifndef __SQLITEASYNC_H_
#define __SQLITEASYNC_H_ 1
/*
** Make sure we can call this stuff from C++.
*/
#ifdef __cplusplus
extern "C" {
#endif
#define SQLITEASYNC_VFSNAME "sqlite3async"
/*
** THREAD SAFETY NOTES:
**
** Of the four API functions in this file, the following are not threadsafe:
**
** sqlite3async_initialize()
** sqlite3async_shutdown()
**
** Care must be taken that neither of these functions is called while
** another thread may be calling either any sqlite3async_XXX() function
** or an sqlite3_XXX() API function related to a database handle that
** is using the asynchronous IO VFS.
**
** These functions:
**
** sqlite3async_run()
** sqlite3async_control()
**
** are threadsafe. It is quite safe to call either of these functions even
** if another thread may also be calling one of them or an sqlite3_XXX()
** function related to a database handle that uses the asynchronous IO VFS.
*/
/*
** Initialize the asynchronous IO VFS and register it with SQLite using
** sqlite3_vfs_register(). If the asynchronous VFS is already initialized
** and registered, this function is a no-op. The asynchronous IO VFS
** is registered as "sqlite3async".
**
** The asynchronous IO VFS does not make operating system IO requests
** directly. Instead, it uses an existing VFS implementation for all
** required file-system operations. If the first parameter to this function
** is NULL, then the current default VFS is used for IO. If it is not
** NULL, then it must be the name of an existing VFS. In other words, the
** first argument to this function is passed to sqlite3_vfs_find() to
** locate the VFS to use for all real IO operations. This VFS is known
** as the "parent VFS".
**
** If the second parameter to this function is non-zero, then the
** asynchronous IO VFS is registered as the default VFS for all SQLite
** database connections within the process. Otherwise, the asynchronous IO
** VFS is only used by connections opened using sqlite3_open_v2() that
** specifically request VFS "sqlite3async".
**
** If a parent VFS cannot be located, then SQLITE_ERROR is returned.
** In the unlikely event that operating system specific initialization
** fails (win32 systems create the required critical section and event
** objects within this function), then SQLITE_ERROR is also returned.
** Finally, if the call to sqlite3_vfs_register() returns an error, then
** the error code is returned to the user by this function. In all three
** of these cases, intialization has failed and the asynchronous IO VFS
** is not registered with SQLite.
**
** Otherwise, if no error occurs, SQLITE_OK is returned.
*/
int sqlite3async_initialize(const char *zParent, int isDefault);
/*
** This function unregisters the asynchronous IO VFS using
** sqlite3_vfs_unregister().
**
** On win32 platforms, this function also releases the small number of
** critical section and event objects created by sqlite3async_initialize().
*/
void sqlite3async_shutdown(void);
/*
** This function may only be called when the asynchronous IO VFS is
** installed (after a call to sqlite3async_initialize()). It processes
** zero or more queued write operations before returning. It is expected
** (but not required) that this function will be called by a different
** thread than those threads that use SQLite. The "background thread"
** that performs IO.
**
** How many queued write operations are performed before returning
** depends on the global setting configured by passing the SQLITEASYNC_HALT
** verb to sqlite3async_control() (see below for details). By default
** this function never returns - it processes all pending operations and
** then blocks waiting for new ones.
**
** If multiple simultaneous calls are made to sqlite3async_run() from two
** or more threads, then the calls are serialized internally.
*/
void sqlite3async_run(void);
/*
** This function may only be called when the asynchronous IO VFS is
** installed (after a call to sqlite3async_initialize()). It is used
** to query or configure various parameters that affect the operation
** of the asynchronous IO VFS. At present there are three parameters
** supported:
**
** * The "halt" parameter, which configures the circumstances under
** which the sqlite3async_run() parameter is configured.
**
** * The "delay" parameter. Setting the delay parameter to a non-zero
** value causes the sqlite3async_run() function to sleep for the
** configured number of milliseconds between each queued write
** operation.
**
** * The "lockfiles" parameter. This parameter determines whether or
** not the asynchronous IO VFS locks the database files it operates
** on. Disabling file locking can improve throughput.
**
** This function is always passed two arguments. When setting the value
** of a parameter, the first argument must be one of SQLITEASYNC_HALT,
** SQLITEASYNC_DELAY or SQLITEASYNC_LOCKFILES. The second argument must
** be passed the new value for the parameter as type "int".
**
** When querying the current value of a paramter, the first argument must
** be one of SQLITEASYNC_GET_HALT, GET_DELAY or GET_LOCKFILES. The second
** argument to this function must be of type (int *). The current value
** of the queried parameter is copied to the memory pointed to by the
** second argument. For example:
**
** int eCurrentHalt;
** int eNewHalt = SQLITEASYNC_HALT_IDLE;
**
** sqlite3async_control(SQLITEASYNC_HALT, eNewHalt);
** sqlite3async_control(SQLITEASYNC_GET_HALT, &eCurrentHalt);
** assert( eNewHalt==eCurrentHalt );
**
** See below for more detail on each configuration parameter.
**
** SQLITEASYNC_HALT:
**
** This is used to set the value of the "halt" parameter. The second
** argument must be one of the SQLITEASYNC_HALT_XXX symbols defined
** below (either NEVER, IDLE and NOW).
**
** If the parameter is set to NEVER, then calls to sqlite3async_run()
** never return. This is the default setting. If the parameter is set
** to IDLE, then calls to sqlite3async_run() return as soon as the
** queue of pending write operations is empty. If the parameter is set
** to NOW, then calls to sqlite3async_run() return as quickly as
** possible, without processing any pending write requests.
**
** If an attempt is made to set this parameter to an integer value other
** than SQLITEASYNC_HALT_NEVER, IDLE or NOW, then sqlite3async_control()
** returns SQLITE_MISUSE and the current value of the parameter is not
** modified.
**
** Modifying the "halt" parameter affects calls to sqlite3async_run()
** made by other threads that are currently in progress.
**
** SQLITEASYNC_DELAY:
**
** This is used to set the value of the "delay" parameter. If set to
** a non-zero value, then after completing a pending write request, the
** sqlite3async_run() function sleeps for the configured number of
** milliseconds.
**
** If an attempt is made to set this parameter to a negative value,
** sqlite3async_control() returns SQLITE_MISUSE and the current value
** of the parameter is not modified.
**
** Modifying the "delay" parameter affects calls to sqlite3async_run()
** made by other threads that are currently in progress.
**
** SQLITEASYNC_LOCKFILES:
**
** This is used to set the value of the "lockfiles" parameter. This
** parameter must be set to either 0 or 1. If set to 1, then the
** asynchronous IO VFS uses the xLock() and xUnlock() methods of the
** parent VFS to lock database files being read and/or written. If
** the parameter is set to 0, then these locks are omitted.
**
** This parameter may only be set when there are no open database
** connections using the VFS and the queue of pending write requests
** is empty. Attempting to set it when this is not true, or to set it
** to a value other than 0 or 1 causes sqlite3async_control() to return
** SQLITE_MISUSE and the value of the parameter to remain unchanged.
**
** If this parameter is set to zero, then it is only safe to access the
** database via the asynchronous IO VFS from within a single process. If
** while writing to the database via the asynchronous IO VFS the database
** is also read or written from within another process, or via another
** connection that does not use the asynchronous IO VFS within the same
** process, the results are undefined (and may include crashes or database
** corruption).
**
** Alternatively, if this parameter is set to 1, then it is safe to access
** the database from multiple connections within multiple processes using
** either the asynchronous IO VFS or the parent VFS directly.
*/
int sqlite3async_control(int op, ...);
/*
** Values that can be used as the first argument to sqlite3async_control().
*/
#define SQLITEASYNC_HALT 1
#define SQLITEASYNC_GET_HALT 2
#define SQLITEASYNC_DELAY 3
#define SQLITEASYNC_GET_DELAY 4
#define SQLITEASYNC_LOCKFILES 5
#define SQLITEASYNC_GET_LOCKFILES 6
/*
** If the first argument to sqlite3async_control() is SQLITEASYNC_HALT,
** the second argument should be one of the following.
*/
#define SQLITEASYNC_HALT_NEVER 0 /* Never halt (default value) */
#define SQLITEASYNC_HALT_NOW 1 /* Halt as soon as possible */
#define SQLITEASYNC_HALT_IDLE 2 /* Halt when write-queue is empty */
#ifdef __cplusplus
} /* End of the 'extern "C"' block */
#endif
#endif /* ifndef __SQLITEASYNC_H_ */
3.0 移植
目前,异步 IO 扩展与 win32 系统兼容 以及支持 pthreads 接口的系统,包括 Mac OS X、Linux、 和其他 Unix 变体。
若要将异步 IO 扩展移植到另一个平台,用户必须 为新平台实现互斥锁和条件变量基元。 目前没有外部可用的接口可以允许这样做,但是 修改 sqlite3async.c 中的代码以包含新平台 并发原语相对容易。在 sqlite3async.c 中搜索 有关详细信息,请使用注释字符串“PORTING FUNCTIONS”。然后实施 以下各项的新版本:
static void async_mutex_enter(int eMutex);
static void async_mutex_leave(int eMutex);
static void async_cond_wait(int eCond, int eMutex);
static void async_cond_signal(int eCond);
static void async_sched_yield(void);
描述了上述每个功能所需的功能 在 sqlite3async.c 的注释中。