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

相关文章