你的位置:首页 > 数据库

[数据库]sqlite3 多线程和锁 ,优化插入速度及性能优化


一、 是否支持多线程?
 
SQLite官网上的“Is SQLite threadsafe?”这个问答。 简单来说,从3.3.1版本开始,它就是线程安全的了。而iOS的SQLite版本没有低于这个版本的,当然,你也可以自己编译最新版本。

不过这个线程安全仍然是有限制的,在这篇《Is SQLite thread-safe?》里有详细的解释。
另一篇重要的文档就是《SQLite And Multiple Threads》。它指出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都被finalized。
  但是默认情况下,一个线程只能使用当前线程打开的数据库连接,除非在连接时设置了check_same_thread=False参数。如果是用不同的数据库连接,每个连接都不能读取其他连接中未提交的数据,除非使用read-uncommitted模式。

现在3种模式都有所了解了,清楚SQLite并不是对多线程无能为力后,接下来就了解下事务吧。
 
二、事务

  数据库只有在事务中才能被更改。所有更改数据库的命令(除SELECT以外的所有SQL命令)都会自动开启一个新事务,并且当最后一个查询完成时自动提交。
  而BEGIN命令可以手动开始事务,并关闭自动提交。当下一条COMMIT命令执行时,自动提交再次打开,事务中所做的更改也被写入数据库。当COMMIT失败时,自动提交仍然关闭,以便让用户尝试再次提交。若执行的是ROLLBACK命令,则也打开自动提交,但不保存事务中的更改。关闭数据库或遇到错误时,也会自动回滚事务。
  
  经常有人抱怨SQLite的插入太慢,实际上它可以做到每秒插入几万次,但是每秒只能提交几十次事务。因此在插入大批数据时,可以通过禁用自动提交来提速。
  还有一个很重要的知识点需要强调:事务是和数据库连接相关的,每个数据库连接(使用pager来)维护自己的事务,且同时只能有一个事务(但是可以用SAVEPOINT来实现内嵌事务)。也就是说,事务与线程无关,一个线程里可以同时用多个数据库连接来完成多个事务,而多个线程也可以同时(非并发)使用一个数据库连接来共同完成一个事务。
 
而要实现事务,就不得不用到锁。
一个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.像上述一样显示的给多个insert加上事务
  sqlite在没有显式使用事务的时候会为每条insert都使用事务操作,而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。  www.2cto.com  
 
3.建议:
如果有定期备份的机制,而且少量数据丢失可接受,用OFF。
     注意上面红色加粗的字样。总结:如果你的数据对安全性完整性等要求不是太高,可以采用设置为0的方法,毕竟只是“数据库可能会损坏”,至于损坏几率为多大,笔者也暂不知晓。。。。。。还没遇到过损坏,不知什么时候才会发生。
 
四、性能优化(可参考http://blog.csdn.net/tietao/article/details/6890350)

很多人直接就使用了,并未注意到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