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)
  1. 多线程

    编译时SQLITE_THREADSAFE=2 默认启用

    SQLITE_THREADSAFE=1/2

    sqlite3_config(SQLITE_CONFIG_MULTITHREAD)

  1. 串行

    编译时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".此时需要等待数据库可写/可读时才能访问.

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

测试代码如下全部代码

// 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

# 不开启读写锁
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
# 开启读写锁
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

Sqlite3 多线程访问 读写锁

上一篇:mysql的安装与配置


下一篇:mysql-windows系统安装mysql数据库