sqlite3多线程和锁，优化插⼊速度及性能优化
⼀、是否⽀持多线程？
SQLite官⽹上的这个问答。简单来说，从3.3.1版本开始，它就是线程安全的了。⽽没有低于这个版本的，当然，你也可以⾃⼰编译最新版本。
不过这个线程安全仍然是有限制的，在这篇⾥有详细的解释。
英文简历格式另⼀篇重要的⽂档就是。它指出SQLite⽀持3种线程模式：
1. 单线程：禁⽤所有的mutex锁，并发使⽤时会出错。当SQLite编译时加了SQLITE_THREADSAFE=0参数，或者在初始化SQLite前调
⽤sqlite3_config(SQLITE_CONFIG_SINGLETHREAD)时启⽤。
2. 多线程：只要⼀个数据库连接不被多个线程同时使⽤就是安全的。源码中是启⽤bCoreMutex，禁⽤bFullMutex。实际上就是禁⽤数据
库连接和prepared statement（准备好的语句）上的锁，因此不能在多个线程中并发使⽤同⼀个数据库
连接或prepared statement。当SQLite编译时加了SQLITE_THREADSAFE=2参数时默认启⽤。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调⽤sqlite3_config(SQLITE_CONFIG_MULTITHREAD)启⽤；或者在创建数据库连接时，设置SQLITE_OPEN_NOMUTEX flag。
3. 串⾏：启⽤所有的锁，包括bCoreMutex和bFullMutex。因为数据库连接和prepared statement都已加锁，所以多线程使⽤这些对象时
没法并发，也就变成串⾏了。当SQLite编译时加了SQLITE_THREADSAFE=1参数时默认启⽤。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调⽤sqlite3_config(SQLITE_CONFIG_SERIALIZED)启⽤；或者在创建数据库连接时，设置
SQLITE_OPEN_FULLMUTEX flag。
⽽这⾥所说的是指调⽤sqlite3_initialize()函数，这个函数在调⽤sqlite3_open()时会⾃动调⽤，且只有第⼀次调⽤是有效的。
调⽤sqlite3_threadsafe()可以获得编译期的SQLITE_THREADSAFE参数。标准发⾏版是1，也就是串⾏模式；⽽iOS上是2，也就是多线程模式；Python的sqlite3模块也默认使⽤串⾏模式，可以⽤sqlite3.threadsafety来配置。
另⼀个要说明的是prepared statement，它是由数据库连接（的pager）来管理的，使⽤它也可看成使⽤这个数据库连接。因此在多线程模式下，并发对同⼀个数据库连接调⽤sqlite3_prepare_v2()来创建prepared statement，或者对同⼀个数据库连接的任何prepared statement并发调⽤sqlite3_bind_*()和sqlite3_step()等函数都会出错（在iOS上，该线程会出现EXC_BAD_ACCESS⽽中⽌）。这种错误⽆关读写，就是只读也会出错。⽂档中给出的安全使⽤规则是：没有事务正在等待执⾏，所有prepared statement都被。
但是默认情况下，⼀个线程只能使⽤当前线程打开的数据库连接，除⾮在连接时设置了check_same_thread=Fal参数。如果是⽤不同的数据库连接，每个连接都不能读取其他连接中未提交的数据，除⾮使⽤模式。
现在3种模式都有所了解了，清楚SQLite并不是对多线程⽆能为⼒后，接下来就了解下吧。
⼆、事务
数据库只有在事务中才能被更改。所有更改数据库的命令（除SELECT以外的所有SQL命令）都会⾃动开启⼀个新事务，并且当最后⼀个查询完成时⾃动提交。
⽽BEGIN命令可以⼿动开始事务，并关闭⾃动提交。当下⼀条COMMIT命令执⾏时，⾃动提交再次
打开，事务中所做的更改也被写⼊数据库。当COMMIT失败时，⾃动提交仍然关闭，以便让⽤户尝试再次提交。若执⾏的是ROLLBACK命令，则也打开⾃动提交，但不保存事务中的更改。关闭数据库或遇到错误时，也会⾃动回滚事务。
经常有⼈抱怨，实际上它可以做到每秒插⼊⼏万次，但是每秒只能提交⼏⼗次事务。因此在插⼊⼤批数据时，可以通过禁⽤⾃动提交来提速。
还有⼀个很重要的知识点需要强调：事务是和数据库连接相关的，每个数据库连接（使⽤pager来）维护⾃⼰的事务，且同时只能有⼀个事务（但是可以⽤来实现内嵌事务）。也就是说，事务与线程⽆关，⼀个线程⾥可以同时⽤多个数据库连接来完成多个事务，⽽多个线程也可以同时（⾮并发）使⽤⼀个数据库连接来共同完成⼀个事务。
⽽要实现事务，就不得不⽤到。
⼀个SQLite数据库⽂件有5种锁的状态：
UNLOCKED：表⽰数据库此时并未被读写。
SHARED：表⽰数据库可以被读取。SHARED锁可以同时被多个线程拥有。⼀旦某个线程持有SHARED锁，就没有任何线程可以进⾏写操作。
RESERVED：表⽰准备写⼊数据库。RESERVED锁最多只能被⼀个线程拥有，此后它可以进⼊PENDING状态。
PENDING：表⽰即将写⼊数据库，正在等待其他读线程释放SHARED锁。⼀旦某个线程持有PENDING锁，其他线程就不能获取SHARED锁。这样⼀来，只要等所有读线程完成，释放SHARED锁后，它就可以进⼊EXCLUSIVE状态了。
EXCLUSIVE：表⽰它可以写⼊数据库了。进⼊这个状态后，其他任何线程都不能访问数据库⽂件。因此为了并发性，它的持有时间越短越好。
⼀个线程只有在拥有低级别的锁的时候，才能获取更⾼⼀级的锁。SQLite就是靠这5种类型的锁，巧妙地实现了读写线程的互斥。同时也可看出，写操作必须进⼊EXCLUSIVE状态，此时并发数被降到1，这也是SQLite被认为并发插⼊性能不好的原因。
另外，read-uncommitted和WAL模式会影响这个锁的机制。在这2种模式下，读线程不会被写线程阻塞，即使写线程持有PENDING或EXCLUSIVE锁。
提到锁就不得不说到死锁的问题，⽽SQLite也可能出现死锁。
下⾯举个例⼦：
连接1：BEGIN （UNLOCKED）
连接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）
现在2个连接都在等待对⽅释放锁，于是就死锁了。当然，实际情况并没那么糟糕，任何⼀⽅选择不继续等待，回滚事务就⾏了。
不过要更好地解决这个问题，就必须更深⼊地了解事务了。
实际上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）
连接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，写⼊完成后释放）
不过在并发很⾼的情况下，直接获取EXCLUSIVE锁的难度⽐较⼤；⽽且为了避免EXCLUSIVE状态长期阻塞其他请求，最好的⽅式还是让所有写事务都以IMMEDIATE⽅式开始。
顺带⼀提，要实现重试的话，可以使⽤sqlite3_busy_timeout()或sqlite3_busy_handler()函数。
由此可见，要想保证线程安全的话，可以有这4种⽅式：
1. SQLite使⽤单线程模式，⽤⼀个专门的线程访问数据库。
2. SQLite使⽤单线程模式，⽤⼀个线程队列来访问数据库，队列⼀次只允许⼀个线程执⾏，队列⾥的线程共⽤⼀个数据库连接。
3. SQLite使⽤多线程模式，每个线程创建⾃⼰的数据库连接。
4. SQLite使⽤串⾏模式，所有线程共⽤全局的数据库连接。
三、sqlite3插⼊速度慢
1.像上述⼀样显⽰的给多个inrt加上事务
sqlite在没有显式使⽤事务的时候会为每条inrt都使⽤事务操作，⽽sqlite数据库是以⽂件的形式存在磁盘中，就相当于每次访问时都要打开⼀次⽂件，如果对数据进⾏⼤量的操作，时间都耗费在I/O操作上，所以很慢。
解决⽅法是显式使⽤事务的形式提交：因为我们开始事务后，进⾏的⼤量操作的语句都保存在内存中，当提交时才全部写⼊数据库，此时，数据库⽂件也就只⽤打开⼀次。
2.如果加上事务还是不⾏，可以尝试修改同步模式
初⽤sqlite3插⼊数据时，插⼊每条数据⼤概需要100ms左右。如果是批量导⼊，可以引进事务提⾼速度。但是假设你的业务是每间隔⼏秒插⼊⼏条数据，显然100ms是不能容许的。
解决办法是，在调⽤sqlite3_open函数后添加下⾯⼀⾏代码：
sqlite3_exec(db, "PRAGMA synchronous = OFF; ", 0,0,0);
上⾯的解决办法貌似治标不治本，为什么加上上⾯的代码⾏，速度会提⾼那么多？
磁盘同步
1.如何设置：
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)
2.参数含义：
当synchronous设置为FULL (2), SQLite引擎在紧急时刻会暂停以确定数据已经写⼊磁盘。这使崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。
当synchronous设置为NORMAL(1), SQLite数据库引擎在⼤部分紧急时刻会暂停，但不像FULL模式下那么频繁。 NORMAL模式下有很⼩的⼏率(但不是不存在)发⽣电源故障导致数据库损坏的情况。但实际上，在这种情况下很可能你的硬盘已经不能使⽤，或者发⽣了其他的不可恢复的硬件错误。
设置为synchronous OFF (0)时，SQLite在传递数据给系统以后直接继续⽽不暂停。若运⾏SQLite的应⽤程序崩溃，数据不会损伤，但在系统崩溃或写⼊数据时意外断电的情况下数据库可能会损坏。另⼀⽅⾯，在synchronous OFF时⼀些操作可能会快50倍甚⾄更多。在SQLite 2中，缺省值为NORMAL.⽽在3中修改为FULL。
3.建议：
如果有定期备份的机制，⽽且少量数据丢失可接受，⽤OFF。
注意上⾯红⾊加粗的字样。总结：如果你的数据对安全性完整性等要求不是太⾼，可以采⽤设置为0的⽅法，毕竟只是“数据库可能会损坏”，⾄于损坏⼏率为多⼤，笔者也暂不知晓。。。。。。还没遇到过损坏，不知什么时候才会发⽣。
四、性能优化（可参考）
很多⼈直接就使⽤了，并未注意到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 ca_nsitive_like
打开。不然搜索中⽂字串会出错。
PRAGMA ca_nsitive_like;
PRAGMA ca_nsitive_like = 0 | 1;
LIKE运算符的缺省⾏为是忽略latin1字符的⼤⼩写。因此在缺省情况下'a' LIKE 'A'的值为真。可以通过打开ca_nsitive_like pragma 来改变这⼀缺省⾏为。当启⽤ca_nsitive_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倍以上。
附指令表集：
序号指令含义缺省值
1auto_vacuum空间释放0
2cache_size缓存⼤⼩2000
3ca_nsitive_like LIKE⼤⼩写敏感（注意：SQLite3.6.22不⽀
持）
4count_changes变更⾏数0
5page_size页⾯⼤⼩1024
6synchronous硬盘⼤⼩2
7temp_store；内存模式0
换路由器怎么重新设置>三国演义名人名言
