Sqlite3 多线程访问 读写锁

Sqlite3 多线程访问

Sqlite3 线程模式#

sqlite3有三种线程模式，在编译时开启宏SQLITE_THREADSAFE=0/1/2来设置编译支持，
sqlite3_config(SQLITE_CONFIG_SINGLETHREAD/SQLITE_CONFIG_SERIALIZED/SQLITE_CONFIG_MULTITHREAD/)运行时设置启用

1. 单线程
   
   编译时SQLITE_THREADSAFE=0 默认启用
   
   SQLITE_THREADSAFE=0/1/2
   
   sqlite3_config(SQLITE_CONFIG_SINGLETHREAD)

2. 多线程
   
   编译时SQLITE_THREADSAFE=2 默认启用
   
   SQLITE_THREADSAFE=1/2
   
   sqlite3_config(SQLITE_CONFIG_MULTITHREAD)
   
   

3. 串行
   
   编译时SQLITE_THREADSAFE=1 默认启用
   
   SQLITE_THREADSAFE=1/2
   
   sqlite3_config(SQLITE_CONFIG_SERIALIZED)

Ubuntu 18.04 的Sqlite3 默认发行版 为串行模式

数据库文件锁状态#

SQLite数据库文件有5种锁的状态。一个线程只有在拥有低级别的锁的时候，才能获取更高一级的锁。SQLite就是靠这5种类型的锁，巧妙地实现了读写线程的互斥。同时也可看出，写操作必须进入EXCLUSIVE状态，此时并发数被降到1，这也是SQLite被认为并发插入性能不好的原因。另外，read-uncommitted和WAL模式会影响这个锁的机制。在这2种模式下，读线程不会被写线程阻塞，即使写线程持有PENDING或EXCLUSIVE锁。

1. UNLOCKED：表示数据库此时并未被读写。
2. SHARED：表示数据库可以被读取。SHARED锁可以同时被多个线程拥有。一旦某个线程持有SHARED锁，就没有任何线程可以进行写操作。
3. RESERVED：表示准备写入数据库。RESERVED锁最多只能被一个线程拥有，此后它可以进入PENDING状态。
4. PENDING：表示即将写入数据库，正在等待其他读线程释放SHARED锁。一旦某个线程持有PENDING锁，其他线程就不能获取SHARED锁。这样一来，只要等所有读线程完成，释放SHARED锁后，它就可以进入EXCLUSIVE状态了。
5. EXCLUSIVE：表示它可以写入数据库了。进入这个状态后，其他任何线程都不能访问数据库文件。因此为了并发性，它的持有时间越短越好。

多线程并发访问#

在默认串行模式下，多线程并发访问同一数据库，每个线程开启一个连接，同时访问时会返回错误 "database is locked".此时需要等待数据库可写/可读时才能访问.

根据数据库文件锁状态，这不典型的读写锁吗，哈哈哈,来个测试

测试代码如下#

全部代码
读写锁

Copy
// read thread
void read_thread() {
  Sqlite db;
  for (int i = 0; i < rw_count_; ++i) {
    if (use_lock_) {
      rwlock::LockRead _(lock_);
      db.test_read(i);
    } else {
      db.test_read(i);
    }
  }
}

// write thread
void write_thread() {
  Sqlite db;
  for (int i = 0; i < rw_count_; ++i) {
    if (use_lock_) {
      rwlock::LockWrite _(lock_);
      db.test_write(i);
    } else {
      db.test_write(i);
    }
  }
}

测试结果#

测试： 2个读线程，2个写线程，每个线程访问数据库10次

环境： WSL Ubuntu 18.04

Copy
# 不开启读写锁
Read  Write   Times: 10
Read  Thread  Count: 2
Write Thread  Count: 2
Use Read Write Lock: 0
[139956121044800] db opened
[139956121044800] drop table user.
[139956121044800] create table user.
[139956121044800] insert data to user.
[139956121044800] db closed
[139956097255168] db opened
[139956005570304] db opened
[139956088801024] db opened
[139955997116160] db opened
[139956097255168] 0 db read OK.
[139956088801024] 0 db read OK.
[139956097255168] 1 db read OK.
[139956097255168] 2 db read error: database is locked
[139956088801024] 1 db read OK.
[139956097255168] 3 db read error: database is locked
[139956088801024] 2 db read error: database is locked
[139956097255168] 4 db read error: database is locked
[139956088801024] 3 db read error: database is locked
[139956097255168] 5 db read error: database is locked
[139956088801024] 4 db read error: database is locked
[139956097255168] 6 db read error: database is locked
[139956097255168] 7 db read error: database is locked
[139956088801024] 5 db read error: database is locked
[139956097255168] 8 db read error: database is locked
[139956088801024] 6 db read error: database is locked
[139956097255168] 9 db read error: database is locked
[139956088801024] 7 db read error: database is locked
[139956097255168] db closed
[139956088801024] 8 db read error: database is locked
[139956088801024] 9 db read error: database is locked
[139956088801024] db closed
[139955997116160] 0 db write error: database is locked
[139956005570304] 0 db write error: database is locked
[139956005570304] 1 db write error: database is locked
[139956005570304] 2 db write error: database is locked
[139956005570304] 3 db write error: database is locked
[139955997116160] 1 db write error: database is locked
[139955997116160] 2 db write error: database is locked
[139955997116160] 3 db write error: database is locked
[139956005570304] 4 db write error: database is locked
[139956005570304] 5 db write error: database is locked
[139956005570304] 6 db write error: database is locked
[139956005570304] 7 db write error: database is locked
[139956005570304] 8 db write error: database is locked
[139955997116160] 4 db write error: database is locked
[139955997116160] 5 db write error: database is locked
[139955997116160] 6 db write error: database is locked
[139955997116160] 7 db write error: database is locked
[139955997116160] 8 db write error: database is locked
[139955997116160] 9 db write error: database is locked
[139955997116160] db closed
[139956005570304] 9 db write OK.
[139956005570304] db closed

Copy
# 开启读写锁
Read  Write   Times: 10
Read  Thread  Count: 2
Write Thread  Count: 2
Use Read Write Lock: 1
[140635615070016] db opened
[140635615070016] drop table user.
[140635615070016] create table user.
[140635615070016] insert data to user.
[140635615070016] db closed
[140635591280384] db opened
[140635565917952] db opened
[140635582826240] db opened
[140635574372096] db opened
[140635591280384] 0 db read OK.
[140635582826240] 0 db read OK.
[140635591280384] 1 db read OK.
[140635582826240] 1 db read OK.
[140635591280384] 2 db read OK.
[140635582826240] 2 db read OK.
[140635591280384] 3 db read OK.
[140635582826240] 3 db read OK.
[140635591280384] 4 db read OK.
[140635582826240] 4 db read OK.
[140635591280384] 5 db read OK.
[140635582826240] 5 db read OK.
[140635591280384] 6 db read OK.
[140635582826240] 6 db read OK.
[140635591280384] 7 db read OK.
[140635582826240] 7 db read OK.
[140635591280384] 8 db read OK.
[140635582826240] 8 db read OK.
[140635591280384] 9 db read OK.
[140635582826240] 9 db read OK.
[140635591280384] db closed
[140635582826240] db closed
[140635565917952] 0 db write OK.
[140635565917952] 1 db write OK.
[140635565917952] 2 db write OK.
[140635565917952] 3 db write OK.
[140635565917952] 4 db write OK.
[140635565917952] 5 db write OK.
[140635565917952] 6 db write OK.
[140635565917952] 7 db write OK.
[140635565917952] 8 db write OK.
[140635565917952] 9 db write OK.
[140635565917952] db closed
[140635574372096] 0 db write OK.
[140635574372096] 1 db write OK.
[140635574372096] 2 db write OK.
[140635574372096] 3 db write OK.
[140635574372096] 4 db write OK.
[140635574372096] 5 db write OK.
[140635574372096] 6 db write OK.
[140635574372096] 7 db write OK.
[140635574372096] 8 db write OK.
[140635574372096] 9 db write OK.
[140635574372096] db closed

由上可见，不加锁访问数据库返回错误 "database is locked"

加锁访问数据库可以正常访问了

读写锁成功的起到了作用

OVER