sqlite 获取插入的id_SQLite深入学习4（事务和锁）

来源：微信公众号(cpp软件架构狮)

前言

在前几篇中讲解了SQLite 常用核心API和整体架构，现在您应该对API布局有了一个整体的认识。了解了执行API的不同方式和一些有用的实用函数。但是，SQL执行的过程远比API看起来复杂的多。其中就会涉及到事务和锁的处理。查询总是会在事务中执行的，事务又涉及到锁的使用，如果锁使用不当，会导致一系列问题。当然用户也可以通过SQL和API接口来控制锁的类型和使用时间。

热身

我们先来看一个简单的锁的例子：

sqlite> begin;

sqlite> delete from students;

sqlite> rollback;

sqlite> select count(*) from students;

SQL事务是由3个命令控制的：begin,commit,rollback。begin开启事务，rollback回滚begin之后操作的所有。注意：如果一个数据库链接在取消之前没有commit也会被rollback。

在例子中，就是一个最简单的事务操作使用实例。也就是最后的select 所有结果的个数是没有发生任何变化的。

在没有显示的使用begin，commit操作情况下，SQLite是默认是自动对于每条SQL语句使用事务的，也就是所谓-自动提交模式。在这种情况下，所有成功完成的命令都是自动提交的。同样在遇到错误的命令都会回滚。这种操作模式就叫自动提交模式。

违反约束解决(冲突解决)

违反约束会导致事务的非法结束，多数其他数据库都是简单的将之前的修改进行回滚。但SQLite的处理方法不同。下面例子：

sqlite> update students set id=3-id;SQL error:PRIMARY KEY must be unique

当update语句执行到第2个记录时，它试图将id更新为1，但是id为1的记录已经存在了，这违法了唯一性约束。so 这条命令就会停止。那停止的默认的行为是什么呢？--回滚之前所有的修改，保证事务的完整性。如想保留之前的修改，SQLite还是提供了丰富的选择的。

SQLite 提供 5 种冲突解决方案： REPLACE、 IGNORE、 FAIL、 ABORT 和 ROLLBACK。这5种不同的方案，就不在文中阐述了。之后的文章单独对这个详细的讲解。

数据库锁

在SQLite中，事务和锁是紧紧联系在一起的。要想对事务运用熟练，锁必须要掌握。同其他的服务型数据库不同，SQLite的锁就相对简单很多，采用的是粗粒度的锁。简单粗暴，这也是SQLite小巧，精简的原因。当一个连接要操作数据库时，所有其他的连接被禁止获取操作，直到写连接结束事务。

在SQLite3.7.0以及之后的版本中，采用了预先写日志WAL(write-ahead log)，这就提升了SQLite并发效率，这个内容比较多，也是放在之后的文章中讲解。这里暂且略过。

SQLite有5种不同的锁状态：unlocked，shared，reserved，pending，exclusive。看下图：

1.UNLOCKED：无锁

文件没有持有任何锁，即当前数据库不存在任何读或写的操作。其它的进程可以在该数据库上执行任意的读写操作。此状态也为缺省状态。

2. SHARED：共享锁

在此状态下，该数据库可以被读取但是不能被写入。在同一时刻可以有任意数量的进程在同一个数据库上持有共享锁，因此读操作是并发的。换句话说，只要有一个或多个共享锁处于活动状态，就不再允许有数据库文件写入的操作存在。

3. RESERVED：保留锁

假如某个进程在将来的某一时刻打算在当前的数据库中执行写操作，然而此时只是从数据库中读取数据，那么我们就可以简单的理解为数据库文件此时已经拥有了保留锁。当保留锁处于活动状态时，该数据库只能有一个或多个共享锁存在，即同一数据库的同一时刻只能存在一个保留锁和多个共享锁。

在Oracle中此类锁被称之为预写锁，不同的是Oracle中锁的粒度可以细化到表甚至到行，因此该种锁在Oracle中对并发的影响程序不像SQLite中这样大。

4. PENDING：等待锁(写等待)

PENDING锁的意思是说，某个进程正打算在该数据库上执行写操作，然而此时该数据库中却存在很多共享锁(读操作)，那么该写操作就必须处于等待状态，即等待所有共享锁消失为止，与此同时，新的读操作将不再被允许，以防止写锁饥饿的现象发生。在此等待期间，该数据库文件的锁状态为PENDING，在等到所有共享锁消失以后，PENDING锁状态的数据库文件将在获取排他锁之后进入EXCLUSIVE状态。

5. EXCLUSIVE：排它锁(写)

在执行写操作之前，该进程必须先获取该数据库的排他锁。然而一旦拥有了排他锁，任何其它锁类型都不能与之共存。因此，为了最大化并发效率，SQLite将会最小化排他锁被持有的时间总量。

死锁

锁的机制确实在SQLite比较重要也是比较复杂，但是还是需要我们去理解和熟悉数据库的锁机制，如果你不知道这些原理，很可能造成死锁问题，而你却还不知道为什么。

考虑下面的例子(伪SQL)：

A连接：begin；B连接：begin；B连接：insert；A连接：select；B连接：commit；SQL error：database is lockedA连接：insert；SQL error：database is locked

最后两个连接都死锁了。

B首先尝试写数据库，也就拥有了一个pending锁。A 再试图写， 但当其 INSERT 语句试图将共享锁提升为保留锁时失败。

为了讨论的方便，假设连接 A 和 B 都一直等待数据库可写。那么此时，其它的连接甚至都不能够再读数据库了， 因为 B 拥有未决锁(它能阻止其它连接获得共享锁)。 那么时此， 不仅A 和 B 不能工作，其它所有进程都不能再操作此数据库了。

如果避免此情况呢？当然不能让 A 和 B 通过协商解决，因为它们甚至不知道彼此的存在。答案是采用正确的事务类型来完成工作。

事务类型

不同的事务类型，开始获取的锁类型不同：DEFERRED，MMEDIATE或EXCLUSIVE。可以看上图中的锁流程。

一个DEFERRED事务不获取任何锁(直到它需要锁的时候)，BEGIN语句本身也不会做什么事情——它开始于UNLOCK状态。默认情况下就 是这样的，如果仅仅用BEGIN开始一个事务，那么事务就是DEFERRED的，同时它不会获取任何锁；当对数据库进行第一次读操作时，它会获取 SHARED锁；同样，当进行第一次写操作时，它会获取RESERVED锁。

由BEGIN开始的IMMEDIATE事务会尝试获取RESERVED锁。如果成功，BEGIN IMMEDIATE保证没有别的连接可以写数据库。但是，别的连接可以对数据库进行读操作；但是，RESERVED锁会阻止其它连接的BEGIN IMMEDIATE或者BEGIN EXCLUSIVE命令，当其它连接执行上述命令时，会返回SQLITE_BUSY错误。这时你就可以对数据库进行修改操作了，但是你还不能提交，当你 COMMIT时，会返回SQLITE_BUSY错误，这意味着还有其它的读事务没有完成，得等它们执行完后才能提交事务。

EXCLUSIVE事务会试着获取对数据库的EXCLUSIVE锁。这与IMMEDIATE类似，但是一旦成功，EXCLUSIVE事务保证没有其它的连接，所以就可对数据库进行读写操作了。