SQLite数据库锁机制研究

SQLite事务锁机制研究

SQLite 事务

SQLite锁与事务是紧密联系的，在探究锁之前先要了解一下事务。事务定义了一组SQL命令，这组命令会作为一个整体被全部执行。事务用begin开启，用commit提交，用rollback回滚。

下面一个例子很好地解释了事务的提交（commit）和回滚（rollback）：

bash: /opt/ros/kinetic/setup.bash: No such file or directory
vito@EliteBook:/media/vito/新加卷/sqlite3/sqlite3_c_prj$ sqlite3 test.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> creat table test(id integer primary key, value text);
Error: near "creat": syntax error
sqlite> create table test(id integer primary key, value text);
sqlite> insert into test (id, value) values(1, 'one');
sqlite> begin;
sqlite> delete from test;
sqlite> rollback;
sqlite> select count(*) from test;
1
sqlite> begin;
sqlite> delete from test;
sqlite> commit;
sqlite> select count(*) from test;
0
sqlite> begin;
sqlite> table test(id integer primary key, value text);
Error: near "table": syntax error
sqlite> insert into test (id, value) values(1, 'one');
sqlite> select count(*) from test;
1
sqlite> commit;
sqlite> select count(*) form test;
Error: near "test": syntax error
sqlite> select count(*) from test;
1

第一次select的时候，由于事务rollback了，导致begin和rollback之间所做的修改取消了，因此 count为0。第二次select的时候，由于事务commit了，因此count为1。第三次，在commit之前select，在同一个事务内同一个连接所做的修改是可以及时生效的，因此count为1；在事务commit之前，其他连接是检索不到这个修改的，这一点请看。

SQLite中单条SQL语句自成一个事务，采用自动提交模式，即单条语句执行成功时commit，遇到错误执行失败时rollback。

SQLite连接

在前面事务内容中讲到连接，连接和事务是造成SQLite数据锁问题的根源。在同一个连接下不管如何提交事务，回滚事务，都不会涉及到SQLite事务锁问题，只有在不同连接下才会涉及到事务锁问题。因此，在探究SQLite事务锁之前，先了解一下SQLite连接。
如下所示是SQLite C语言API对象模型示意图：
从图中可以看出，与SQLite C语言API执行时相关的两个数据结构是连接（Connection）和语句（Statement)，分别对应sqlite3和sqlite_stmt句柄。一个连接对象代表到数据库的连接和事务上下文，当一个连接需要执行一条语句时，会先将语句翻译成VDBE字节码，然后再尝试尝试获取 Table Locks 锁，Table Locks 保证了同一个连接执行的sql语句是有序执行的。每个statement都会包含一个指向硬盘记录的B-Tree游标（Cursor），在执行SQL语句时，会使用游标遍历B-Tree，从而可以实遍历数据库的记录。SQLite数据库的数据保存在硬盘上的页面（page）内，Pager负责读写数据库，维护内存缓存或者页面，管理事务，管理锁和故障恢复。statement执行时通过游标遍历B-Tree，再通过Pager读取或者修改页面，就可以实现数据库内容的查询和修改了。从Pager到硬盘上的数据库文件，还需要通过Database Locks（数据库锁，也称事务锁），来确保不同的连接之间读写数据库是有序执行的。

一个连接对象（sqlite3）可以连接到多个数据库对象，可以有一个主数据库和多个附加数据库（附加数据库可以通过执行attach sql语句实现） ，每一个数据库对象都有B-Tree和相应的一个Pager对象，但是一个连接对象只能有一个打开的事务。

SQLite C API中用于连接或者打开一个数据库的函数是 sqlite3_open()，其函数原型为

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

该函数有两个参数，第一个是输入参数，输入一个数据库名称字符串，第二个是输出参数，输出一个sqlite3指针，该指针指向的对象便是一个连接对象。需要断开和数据库的连接时，使用sqlite3_close可以销毁连接对象，其函数原型为：

int sqlite3_close(sqlite3*);

SQLite事务锁

SQLite采用粗粒度的锁，当一个连接想要写数据库时，所有的其他的连接被锁住，直到写连接结束它的事务。SQLite有5种不同的锁状态：未加锁（unlocked）、共享（shared）、预留（reserved）、未决（pending）和排他（exclusive）。每个数据库连接在同一时刻只能处于其中一种状态，每种状态（未加锁状态除外）都有一种锁与之对应。SQLite采用加锁表，来帮助不同的写数据库事务能够在最后一刻再加锁，以保证最大的并发性。SQLite加锁表如下图所示：

锁级别	状态描述
UNLOCKED（未加锁）	连接还没有访问数据库。
SHARED（共享锁）	连接获得共享锁之后可以从数据库中读取数据，多个连接可以同时获得并保持共享锁，可以同时从数据库中读取数据，但是只要有一个连接共享锁没有释放，也不允许任何连接写数据库。
RESERVED（预留锁）	预留锁是写数据库的第一阶段，可以与共享锁共存，也不阻止其他连接获取新的共享锁。一旦一个连接获取了共享锁，便可以处理数据库修改工作了，将修改保存到内存缓冲区（缓冲区大小可通过cache_size设置），而不是实际写到硬盘中。
PENDING（未决锁）	当连接想要提交修改或者事务时，从预留锁升级为排他锁，升级为排他锁之前需要先获取未决锁。获取未决锁之后，其他连接就不能再获取共享锁了，因此不会产生新的读事务。等所有读连接释放保留锁退出读操作之后，写连接就会获取到排它锁（EXCLUSIVE），huo得排他锁