sqlite3 多线程和锁 ，优化插入速度及性能优化
不过这个线程安全仍然是有限制的，在这篇《Is SQLite thread-safe?》里有详细的解释。
另一篇重要的文档就是《SQLite And Multiple Threads》。它指出SQLite支持3种线程模式：
- 单线程：禁用所有的mutex锁，并发使用时会出错。当SQLite编译时加了SQLITE_THREADSAFE=0参数，或者在初始化SQLite前调用sqlite3_config(SQLITE_CONFIG_SINGLETHREAD)时启用。
- 多线程：只要一个数据库连接不被多个线程同时使用就是安全的。源码中是启用bCoreMutex，禁用bFullMutex。实际上就是禁用数据库连接和prepared statement（准备好的语句）上的锁，因此不能在多个线程中并发使用同一个数据库连接或prepared statement。当SQLite编译时加了SQLITE_THREADSAFE=2参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_MULTITHREAD)启用；或者在创建数据库连接时，设置SQLITE_OPEN_NOMUTEX flag。
- 串行：启用所有的锁，包括bCoreMutex和bFullMutex。因为数据库连接和prepared statement都已加锁，所以多线程使用这些对象时没法并发，也就变成串行了。当SQLite编译时加了SQLITE_THREADSAFE=1参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_SERIALIZED)启用；或者在创建数据库连接时，设置SQLITE_OPEN_FULLMUTEX flag。
现在3种模式都有所了解了，清楚SQLite并不是对多线程无能为力后，接下来就了解下事务吧。
数据库只有在事务中才能被更改。所有更改数据库的命令（除SELECT以外的所有SQL命令）都会自动开启一个新事务，并且当最后一个查询完成时自动提交。
而BEGIN命令可以手动开始事务，并关闭自动提交。当下一条COMMIT命令执行时，自动提交再次打开，事务中所做的更改也被写入数据库。当COMMIT失败时，自动提交仍然关闭，以便让用户尝试再次提交。若执行的是ROLLBACK命令，则也打开自动提交，但不保存事务中的更改。关闭数据库或遇到错误时，也会自动回滚事务。
一个SQLite数据库文件有5种锁的状态：
- UNLOCKED：表示数据库此时并未被读写。
- SHARED：表示数据库可以被读取。SHARED锁可以同时被多个线程拥有。一旦某个线程持有SHARED锁，就没有任何线程可以进行写操作。
- RESERVED：表示准备写入数据库。RESERVED锁最多只能被一个线程拥有，此后它可以进入PENDING状态。
- PENDING：表示即将写入数据库，正在等待其他读线程释放SHARED锁。一旦某个线程持有PENDING锁，其他线程就不能获取SHARED锁。这样一来，只要等所有读线程完成，释放SHARED锁后，它就可以进入EXCLUSIVE状态了。
- EXCLUSIVE：表示它可以写入数据库了。进入这个状态后，其他任何线程都不能访问数据库文件。因此为了并发性，它的持有时间越短越好。
另外，read-uncommitted和WAL模式会影响这个锁的机制。在这2种模式下，读线程不会被写线程阻塞，即使写线程持有PENDING或EXCLUSIVE锁。
提到锁就不得不说到死锁的问题，而SQLite也可能出现死锁。
下面举个例子：
连接1：BEGIN （UNLOCKED）现在2个连接都在等待对方释放锁，于是就死锁了。当然，实际情况并没那么糟糕，任何一方选择不继续等待，回滚事务就行了。
连接1：SELECT ... （SHARED）
连接1：INSERT ... （RESERVED）
连接2：BEGIN （UNLOCKED）
连接2：SELECT ... （SHARED）
连接1：COMMIT （PENDING，尝试获取EXCLUSIVE锁，但还有SHARED锁未释放，返回SQLITE_BUSY）
连接2：INSERT ... （尝试获取RESERVED锁，但已有PENDING锁未释放，返回SQLITE_BUSY）
不过要更好地解决这个问题，就必须更深入地了解事务了。
实际上BEGIN语句可以有3种起始状态：
- DEFERRED：默认值，开始事务时不获取任何锁。进行第一次读操作时获取SHARED锁，进行第一次写操作时获取RESERVED锁。
- IMMEDIATE：开始事务时获取RESERVED锁。
- EXCLUSIVE：开始事务时获取EXCLUSIVE锁。
现在考虑2个事务在开始时都使用IMMEDIATE方式：
连接1：BEGIN IMMEDIATE （RESERVED）这样死锁就被避免了。
连接1：SELECT ... （RESERVED）
连接1：INSERT ... （RESERVED）
连接2：BEGIN IMMEDIATE （尝试获取RESERVED锁，但已有RESERVED锁未释放，因此事务开始失败，返回SQLITE_BUSY，等待用户重试）
连接1：COMMIT （EXCLUSIVE，写入完成后释放）
连接2：BEGIN IMMEDIATE （RESERVED）
连接2：SELECT ... （RESERVED）
连接2：INSERT ... （RESERVED）
连接2：COMMIT （EXCLUSIVE，写入完成后释放）
而EXCLUSIVE方式则更为严苛，即使其他连接以DEFERRED方式开启事务也不会死锁：
连接1：BEGIN EXCLUSIVE （EXCLUSIVE）不过在并发很高的情况下，直接获取EXCLUSIVE锁的难度比较大；而且为了避免EXCLUSIVE状态长期阻塞其他请求，最好的方式还是让所有写事务都以IMMEDIATE方式开始。
连接1：SELECT ... （EXCLUSIVE）
连接1：INSERT ... （EXCLUSIVE）
连接2：BEGIN （UNLOCKED）
连接2：SELECT ... （尝试获取SHARED锁，但已有EXCLUSIVE锁未释放，返回SQLITE_BUSY，等待用户重试）
连接1：COMMIT （EXCLUSIVE，写入完成后释放）
连接2：SELECT ... （SHARED）
连接2：INSERT ... （RESERVED）
连接2：COMMIT （EXCLUSIVE，写入完成后释放）
顺带一提，要实现重试的话，可以使用sqlite3_busy_timeout()或sqlite3_busy_handler()函数。
由此可见，要想保证线程安全的话，可以有这4种方式：
- SQLite使用单线程模式，用一个专门的线程访问数据库。
- SQLite使用单线程模式，用一个线程队列来访问数据库，队列一次只允许一个线程执行，队列里的线程共用一个数据库连接。
- SQLite使用多线程模式，每个线程创建自己的数据库连接。
- SQLite使用串行模式，所有线程共用全局的数据库连接。
解决办法是，在调用sqlite3_open函数后添加下面一行代码：
很多人直接就使用了，并未注意到SQLite也有配置参数，可以对性能进行调整。有时候，产生的结果会有很大影响。
主要通过pragma指令来实现。
比如： 空间释放、磁盘同步、Cache大小等。
1 auto_vacuum
最好不要打开auto_vacuum, Vacuum的效率非常低！
PRAGMA auto_vacuum;
PRAGMA auto_vacuum = 0 | 1;
查询或设置数据库的auto-vacuum标记。
正常情况下，当提交一个从数据库中删除数据的事务时，数据库文件不改变大小。未使用的文件页被标记并在以后的添加操作中再次使用。这种情况下使用VACUUM命令释放删除得到的空间。
当开启auto-vacuum，当提交一个从数据库中删除数据的事务时，数据库文件自动收缩， (VACUUM命令在auto-vacuum开启的数据库中不起作用)。数据库会在内部存储一些信息以便支持这一功能，这使得数据库文件比不开启该选项时稍微大一些。
只有在数据库中未建任何表时才能改变auto-vacuum标记。试图在已有表的情况下修改不会导致报错。
2 cache_size
建议改为8000
PRAGMA cache_size;
PRAGMA cache_size = Number-of-pages;
查询或修改SQLite一次存储在内存中的数据库文件页数。每页使用约1.5K内存，缺省的缓存大小是2000. 若需要使用改变大量多行的UPDATE或DELETE命令，并且不介意SQLite使用更多的内存的话，可以增大缓存以提高性能。
当使用cache_size pragma改变缓存大小时，改变仅对当前对话有效，当数据库关闭重新打开时缓存大小恢复到缺省大小。 要想永久改变缓存大小，使用default_cache_size pragma.
3 case_sensitive_like
打开。不然搜索中文字串会出错。
PRAGMA case_sensitive_like;
PRAGMA case_sensitive_like = 0 | 1;
LIKE运算符的缺省行为是忽略latin1字符的大小写。因此在缺省情况下'a' LIKE 'A'的值为真。可以通过打开case_sensitive_like pragma来改变这一缺省行为。当启用case_sensitive_like，'a' LIKE 'A'为假而 'a' LIKE 'a'依然为真。
4 count_changes
打开。便于调试
PRAGMA count_changes;
PRAGMA count_changes = 0 | 1;
查询或更改count-changes标记。正常情况下INSERT, UPDATE和DELETE语句不返回数据。 当开启count-changes，以上语句返回一行含一个整数值的数据——该语句插入，修改或删除的行数。
注意：返回的行数不包括由（触发器产生的插入，修改或删除等改变的行数）。
5 page_size
PRAGMA page_size;
PRAGMA page_size = bytes;
查询或设置page-size值。只有在未创建数据库时才能设置page-size。页面大小必须是2的整数倍且大于等于512小于等于8192。 上限可以通过在编译时修改宏定义SQLITE_MAX_PAGE_SIZE的值来改变。上限的上限是32768.
6 synchronous
如果有定期备份的机制，而且少量数据丢失可接受，用OFF
PRAGMA synchronous;
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)
查询或更改"synchronous"标记的设定。第一种形式(查询)返回整数值。 当synchronous设置为FULL (2), SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。 这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。 当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停，但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上，在这种情况下很可能你的硬盘已经不能使用，或者发生了其他的不可恢复的硬件错误。 设置为synchronous OFF (0)时，SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃， 数据不会损伤，但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面，在synchronous OFF时 一些操作可能会快50倍甚至更多。
在SQLite 2中，缺省值为NORMAL.而在3中修改为FULL.
7 temp_store
使用2，内存模式。
PRAGMA temp_store;
PRAGMA temp_store = DEFAULT; (0)
PRAGMA temp_store = FILE; (1)
PRAGMA temp_store = MEMORY; (2)
查询或更改"temp_store"参数的设置。当temp_store设置为DEFAULT (0),使用编译时的C预处理宏 TEMP_STORE来定义储存临时表和临时索引的位置。当设置为MEMORY (2)临时表和索引存放于内存中。 当设置为FILE (1)则存放于文件中。temp_store_directorypragma 可用于指定存放该文件的目录。当改变temp_store设置，所有已存在的临时表，索引，触发器及视图将被立即删除。
经测试，在类BBS应用上，通过以上调整，效率可以提高2倍以上。
附指令表集：
|
序号 |
指令 |
含义 |
缺省值 |
|
1 |
auto_vacuum |
空间释放 |
0 |
|
2 |
cache_size |
缓存大小 |
2000 |
|
3 |
case_sensitive_like |
LIKE大小写敏感 |
（注意：SQLite3.6.22不支持） |
|
4 |
count_changes |
变更行数 |
0 |
|
5 |
page_size |
页面大小 |
1024 |
|
6 |
synchronous |
硬盘大小 |
2 |
|
7 |
temp_store; |
内存模式 |
0 |
