rollback日志模式下的五种锁状态介绍 #

• UNLOCKED
  • 没锁状态
• SHARED
  • 获取SHARED锁才能执行读操作，一个数据库可同时存在多个SHARED锁
• RESERVED
  • 获取RESERVED锁才能在未来写数据库，一个数据库同一时间只能存在一个RESERVED锁
  • 有RESERVED锁时说明还没开始写，所以有RESERVED锁时可以获取新的SHARED锁
• PENDING
  • 有PENDING锁意味着要开始写了，但是此时有其他连接拥有SHARED锁在读数据，此时写操作只能等待所有SHARED释放。
  • PENDING阻塞其他连接获取新的SHARED锁，当SHARED锁释放完时转为EXCLUSIVE锁开始写操作。
• EXCLUSIVE
  • 同一时间只能存在一个EXCLUSIVE锁，并且有EXCLUSIVE锁存在时不允许其他任何锁类型存在。

所以总结一下就是读读可并发，读写不可并发，写写不可并发。

优化篇 #

SQLITE_BUSY 问题 #

看到上面这么多锁不能共存的情况应该会想到，冲突会很频繁，如 EXCLUSIVE 锁存在时不允许其他连接获取任何锁，当其他进程需要读写操作时就会获取锁失败，立即报 SQLITE_BUSY 错误。

设置 busy_timeout 就不会立即返回 SQLITE_BUSY，会定时retry失败的操作，如果在设置的 busy_timeout 时间内还没执行成功，依然会返回 SQLITE_BUSY。

使用不同sqlite驱动，设置 busy_timeout 的方式不同

• modernc.org/sqlite database.db?_pragma=busy_timeout%3d50000
• github.com/mattn/go-sqlite3 database.db?_busy_timeout=50000

Shared cache mode 支持 table level locks，暂时还没研究。

针对写操作慢的问题 #

解决方案：将多个写操作放入一个事务里执行。sqlite官方FAQ对其解释如下

(19) INSERT is really slow - I can only do few dozen INSERTs per second Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second. Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite.. By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN…COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced. Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.

...