Sqlite3 并发读写和事务死锁问题

最近项目中涉及到 sqlite 并发读写的问题，最终发现基线两个数据库使用同一个 db_connect() 接口，都存在并发访问冲突隐患，但只在 H11 平台上出现。是因为其它平台性能好，“只要你 CPU 执行速度够快，我 dhcp 就能完美错开 sipServer 初始化，避免冲突” 。
参考一些文档并结合自己的实践，对 sqlite3 并发问题总结了几点：

 

sqlite3 的锁及事务类型

 

sqlite3 总共有三种事务类型：BEGIN [DEFERRED /IMMEDIATE / EXCLUSIVE] TRANSCATION，五种锁，按锁的级别依次是：UNLOCKED /SHARED /RESERVERD /PENDING /EXCLUSIVE。当执行 select 即读操作时，需要获取到 SHARED 锁（共享锁），当执行 insert/update/delete 操作 (即内存写操作时)，需要进一步获取到 RESERVERD 锁（保留锁），当进行 commit 操作 (即磁盘写操作时)，需要进一步获取到 EXCLUSIVE 锁（排它锁）。
对于 RESERVERD 锁，sqlite3 保证同一时间只有一个连接可以获取到保留锁，也就是同一时间只有一个连接可以写数据库 (内存)，但是其它连接仍然可以获取 SHARED 锁，也就是其它连接仍然可以进行读操作（这里可以认为写操作只是对磁盘数据的一份内存拷贝进行修改，并不影响读操作）。
对于 EXCLUSIVE 锁，是比保留锁更为严格的一种锁，在需要把修改写入磁盘即 commit 时需要在保留锁 / 未决锁的基础上进一步获取到排他锁，顾名思义，排他锁排斥任何其它类型的锁，即使是 SHARED 锁也不行，所以，在一个连接进行 commit 时，其它连接是不能做任何操作的（包括读）。
PENDING 锁（即未决锁），则是比较特殊的一种锁，它可以允许已获取到 SHARED 锁的事务继续进行，但不允许其它连接再获取 SHARED 锁，当已存在的 SHARED 锁都被释放后（事务执行完成），持有未决锁的事务就可以获得 commit 的机会了。sqlite3 使用这种锁来防止 writer starvation（写饿死）。

 

死锁的情况

 

死锁的情况：当两个连接使用 begin transaction 开始事务时，第一个连接执行了一次 select 操作（已经获取到 SHARED 锁），第二个连接执行了一次 insert 操作（已经获取到了 RESERVERD 锁），此时第一个连接需要进行一次 insert/update/delete（需要获取到 RESERVERD 锁），第二个连接则希望执行 commit（需要获取到 EXCLUSIVE 锁），由于第二个连接已经获取到了 RESERVERD 锁，根据 RESERVERD 锁同一时间只有一个连接可以获取的特性，第一个连接获取 RESERVERD 锁的操作必定失败，而由于第一个连接已经获取到 SHARED 锁，第二个连接希望进一步获取到 EXCLUSIVE 锁的操作也必定失败。就导致了事务死锁。

 

事务类型的使用原则

 

在用”begin transaction” 显式开启一个事务时，默认的事务类型为 DEFERRED，锁的状态为 UNLOCKED，即不获取任何锁，如果在使用的数据库没有其它的连接，用 begin 就可以了。如果有多个连接都需要对数据库进行写操作，那就得使用 BEGIN IMMEDIATE/EXCLUSIVE 开始事务了。
使用事务的好处是：1. 一个事务的所有操作相当于一次原子操作，如果其中某一步失败，可以通过回滚来撤销之前所有的操作，只有当所有操作都成功时，才进行 commit，保证了操作的原子特性；2. 对于多次的数据库操作，如果我们希望提高数据查询或更新的速度，可以在开始操作前显式开启一个事务，在执行完所有操作后，再通过一次 commit 来提交所有的修改或结束事务。

 

对 SQLITE_BUSY 的处理

 

当有多个连接同时对数据库进行写操作时，根据事务类型的使用原则，我们在每个连接中用 BEGIN IMMEDIATE 开始事务，即多个连接都尝试取得保留锁的情况，根据保留锁同一时间只有一个连接可以获取到的特性，其它连接都将获取失败，即事务开始失败，这种情况下，sqlite3 将返回一个 SQLITE_BUSY 的错误，如果我们不希望操作就此失败而返回，就必须处理 SQLITE_BUSY 的情况，sqlite3 提供了 sqlite3_busy_handler 或 sqlite3_busy_timeout 来处理 SQLITE_BUSY，对于 sqlite3_busy_handler，我们可以指定一个 busy_handler 来处理，并可以指定失败重试的次数。而 sqlite3_busy_timeout 则是由 sqlite3 自动进行 sleep 并重试，当 sleep 的累积时间超过指定的超时时间时，最终返回 SQLITE_BUSY。需要注意的是，这两个函数同时只能使用一个，后面的调用会覆盖掉前次调用。从使用上来说，sqlite3_busy_timeout 更易用一些，只需要指定一个总的超时时间，然后 sqlite 自己会决定多久进行重试以及重试的次数，直到达到总的超时时间最终返回 SQLITE_BUSY。并且，这两个函数一经调用，对其后的所有数据库操作都有效，非常方便。

 

解决方法：

 

综上，我们不难发现并发读写的时候出现了事务死锁，最终解决方法如下：
法一：信号量实现互斥

 

sem_p(semid, 0);
sqlite3_exec(db, buf, 0, 0, &pErrMsg);
sem_v(semid, 0); 

 

法二：自定义循环访问

 

do
{
        ret = sqlite3_exec(db, buf, 0, 0, &pErrMsg);
        if (ret == SQLITE_BUSY)
        {
                sleep(1);
                continue;
        }
        break;
}while(1);

 

法三：使用 sqlite3 的 API，当检测到当前连接的数据库处于 SQLITE_BUSY 时等待，或自定义 busy 时的回调处理