SQLite在多线程环境下的应用

概述先说下初衷吧，实际上我经常看到有人抱怨SQLite不支持多线程。而在iOS开发时，为了不阻塞主线程，数据库访问必须移到子线程中。为了解决这个矛盾，很有必要对此一探究竟。 关于这个问题，最权威的解答当然是SQLite官网上的“Is SQLite threadsafe?”这个问答。 简单来说，从3.3.1版本开始，它就是线程安全的了。而iOS的SQLite版本没有低于这个版本的： 3.4.0 - iP

先说下初衷吧，实际上我经常看到有人抱怨sqlite不支持多线程。而在iOS开发时，为了不阻塞主线程，数据库访问必须移到子线程中。为了解决这个矛盾，很有必要对此一探究竟。

关于这个问题，最权威的解答当然是sqlite官网上的“Is sqlite threadsafe?”这个问答。

简单来说，从3.3.1版本开始，它就是线程安全的了。而iOS的sqlite版本没有低于这个版本的：

3.4.0 - iPhone OS 2.2.1

3.6.12 - iPhone OS 3.0 / 3.1

3.6.22 - iPhone OS 4.0

3.6.23.2 - iOS 4.1 / 4.2

3.7.2 - iOS 4.3

3.7.7 - iOS 5.0

当然，你也可以自己编译最新版本。只是我发现自己编译出来的3.7.8居然比iOS 4.3.3内置的3.7.2慢了一半，不知道苹果做了什么优化。发现是我编译成了deBUG版本，改成release后性能比内置版本高5%左右，不过构建出来的app会大420k左右。

不过这个线程安全仍然是有限制的，在这篇《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。

而这里所说的初始化是指调用sqlite3_initialize()函数，这个函数在调用sqlite3_open()时会自动调用，且只有第一次调用是有效的。

另一个要说明的是prepared statement，它是由数据库连接(的pager)来管理的，使用它也可看成使用这个数据库连接。因此在多线程模式下，并发对同一个数据库连接调用sqlite3_prepare_v2()来创建prepared statement，或者对同一个数据库连接的任何prepared statement并发调用sqlite3_bind_*()和sqlite3_step()等函数都会出错(在iOS上，该线程会出现EXC_BAD_ACCESS而中止)。这种错误无关读写，就是只读也会出错。文档中给出的安全使用规则是：没有事务正在等待执行，所有prepared statement都被finalized。

顺带一提，调用sqlite3_threadsafe()可以获得编译期的sqlITE_THREADSAFE参数。标准发行版是1，也就是串行模式;而iOS上是2，也就是多线程模式;Python的sqlite3模块也默认使用串行模式，可以用sqlite3.threadsafety来配置。但是默认情况下，一个线程只能使用当前线程打开的数据库连接，除非在连接时设置了check_same_thread=False参数。

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

数据库只有在事务中才能被更改。所有更改数据库的命令(除SELECT以外的所有sql命令)都会自动开启一个新事务，并且当最后一个查询完成时自动提交。

而BEGIN命令可以手动开始事务，并关闭自动提交。当下一条COMMIT命令执行时，自动提交再次打开，事务中所做的更改也被写入数据库。当COMMIT失败时，自动提交仍然关闭，以便让用户尝试再次提交。若执行的是RolLBACK命令，则也打开自动提交，但不保存事务中的更改。关闭数据库或遇到错误时，也会自动回滚事务。

经常有人抱怨sqlite的插入太慢，实际上它可以做到每秒插入几万次，但是每秒只能提交几十次事务。因此在插入大批数据时，可以通过禁用自动提交来提速。

事务在改写数据库文件时，会先生成一个rollback journal(回滚日志)，记录初始状态(其实就是备份)，所有改动都是在数据库文件上进行的。当事务需要回滚时，可以将备份文件的内容还原到数据库文件;提交成功时，默认的delete模式下会直接删除这个日志。这个日志也可以帮助解决事务执行过程中断电，导致数据库文件损坏的问题。但如果 *** 作系统或文件系统有BUG，或是磁盘损坏，则仍有可能无法恢复。

而从3.7.0版本(对应iOS 4.3)开始，sqlite还提供了Write-Ahead Logging模式。与delete模式相比，WAL模式在大部分情况下更快，并发性更好，读和写之间互不阻塞;而其缺点对于iPhone这种嵌入式设备来说可以忽略，只需注意不要以只读方式打开WAL模式的数据库即可。

使用WAL模式时，改写 *** 是附加(append)到WAL文件，而不改动数据库文件，因此数据库文件可以被同时读取。当执行checkpoint *** 作时，WAL文件的内容会被写回数据库文件。当WAL文件达到sqlITE_DEFAulT_WAL_AUtochECKPOINT(默认值是1000)页(默认大小是1KB)时，会自动使用当前COMMIT的线程来执行checkpoint *** 作。也可以关闭自动checkpoint，改为手动定期checkpoint。

为了避免读取的数据不一致，查询时也需要读取WAL文件，并记录一个结尾标记(end mark)。这样的代价就是读取会变得稍慢，但是写入会变快很多。要提高查询性能的话，可以减小WAL文件的大小，但写入性能也会降低。

需要注意的是，低版本的sqlite不能读取高版本的sqlite生成的WAL文件，但是数据库文件是通用的。这种情况在用户进行iOS降级时可能会出现，可以把模式改成delete，再改回WAL来修复。

要对一个数据库连接启用WAL模式，需要执行“PRAGMA journal_mode=WAL;”这条命令，它的默认值是“journal_mode=DELETE”。执行后会返回新的journal_mode字符串值，即成功时为"wal"，失败时为之前的模式(例如"delete")。一旦启用WAL模式后，数据库会保持这个模式，这样下次打开数据库时仍然是WAL模式。

要停止自动checkpoint，可以使用wal_autocheckpoint指令或sqlite3_wal_checkpoint()函数。手动执行checkpoint可以使用wal_checkpoint指令或sqlite3_wal_checkpoint()函数。

还有一个很重要的知识点需要强调：事务是和数据库连接相关的，每个数据库连接(使用pager来)维护自己的事务，且同时只能有一个事务(但是可以用SAVEPOINT来实现内嵌事务)。

也就是说，事务与线程无关，一个线程里可以同时用多个数据库连接来完成多个事务，而多个线程也可以同时(非并发)使用一个数据库连接来共同完成一个事务。

下面用Python来演示一下：

      #-*-Coding:utf-8-*-        importsqlite3    importthreading    deff():        con.rollback()        con=sqlite3.connect('test.db',check_same_thread=False)#允许在其他线程中使用这个连接        cu=con.cursor()        cu.execute('CREATEtableIFNOTEXISTStest(IDINTEGERPRIMARYKEY)')    printcu.execute('SELECTcount(*)FROMtest').fetchone()[0]#0        cu.execute('INSERTINTOtestVALUES(NulL)')    #1        thread=threading.Thread(target=f)        thread.start()        thread.join()        cu.close()        con.close()  

在这个例子中，虽然是在子线程中执行rollback，但由于和主线程用的是同一个数据库连接，所以主线程所做的更改也被回滚了。

而如果是用不同的数据库连接，每个连接都不能读取其他连接中未提交的数据，除非使用read-uncommitted模式。

而要实现事务，就不得不用到锁。

一个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)

连接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：SELECT ... (尝试获取SHARED锁，但已有EXCLUSIVE锁未释放，返回sqlITE_BUSY，等待用户重试)

不过在并非很高的情况下，直接获取EXCLUSIVE锁的难度比较大;而且为了避免EXCLUSIVE状态长期阻塞其他请求，最好的方式还是让所有写事务都以IMMEDIATE方式开始。

顺带一提，要实现重试的话，可以使用sqlite3_busy_timeout()或sqlite3_busy_handler()函数。

由此可见，要想保证线程安全的话，可以有这4种方式：

sqlite使用单线程模式，用一个专门的线程访问数据库。

sqlite使用单线程模式，用一个线程队列来访问数据库，队列一次只允许一个线程执行，队列里的线程共用一个数据库连接。

sqlite使用多线程模式，每个线程创建自己的数据库连接。

sqlite使用串行模式，所有线程共用全局的数据库连接。

接下来就一一测试这几种方式在iPhone 4(iOS 4.3.3，sqlite 3.7.2)上的性能表现。

第一种方式太过麻烦，需要线程间通信，这里我就忽略了。

第二种方式可以用dispatch_queue_create()来创建一个serial queue，或者用一个maxConcurrentoperationCount为1的NSOperationQueue来实现。

这种方式的缺点就是事务必须在一个block或operation里完成，否则会乱序;而耗时较长的事务会阻塞队列。另外，没法利用多核cpu的优势。

先初始化数据库：

      #import    staticchardbPath[200];    staticsqlite3*database;    staticsqlite3*openDb(){        if(sqlite3_open(dbPath,&database)!=sqlITE_OK){        sqlite3_close(database);        NSLog(@"@R_404_5138@toopendatabase:%s",sqlite3_errmsg(database));        }    return    -(voID)vIEwDIDLoad{        [supervIEwDIDLoad];        sqlite3_config(sqlITE_CONfig_SINGLETHREAD);        NSLog(@"%d",sqlite3_threadsafe());        NSLog(@"%s",sqlite3_libversion());        NSArray*paths=NSSearchPathForDirectorIEsInDomains(NSdocumentDirectory,NSUserDomainMask,YES);        Nsstring*documentsDirectory=[pathsobjectAtIndex:0];        strcpy(dbPath,[[documentsDirectorystringByAppendingPathComponent:@"data.sqlite3"]UTF8String]);    database=openDb();    char*errorMsg;        if(sqlite3_exec(database,"CREATEtableIFNOTEXISTStest(IDINTEGERPRIMARYKEYautoINCREMENT,valueINTEGER);",NulL,&errorMsg)!=sqlITE_OK){        NSLog(@"@R_404_5138@tocreatetable:%s",errorMsg);        }  

再插入1000条测试数据：

    staticvoIDinsertData(){    "BEGINTRANSACTION",92); List-style-type:decimal; List-style-position:outsIDe!important; word-wrap:break-word; word-break:normal; border-top-style:none; border-right-style:none; border-bottom-style:none; border-left-style:none; border-wIDth:initial; border-color:initial; background-color:transparent; line-height:18px">    NSLog(@"@R_404_5138@tobegintransaction:%s",153); background-color:inherit; Font-weight:bold">staticconstchar*insert="INSERTINTOtestVALUES(NulL,?);";        sqlite3_stmt*stmt;        if(sqlite3_prepare_v2(insert,-1,&stmt,128); background-color:inherit">NulL)==sqlITE_OK){    for(inti=0;i<1000;++i){        sqlite3_bind_int(stmt,1,arc4random());        if(sqlite3_step(stmt)!=sqlITE_DONE){        --i;        NSLog(@"Errorinsertingtable:%s",92); List-style-type:decimal; List-style-position:outsIDe!important; word-wrap:break-word; word-break:normal; border-top-style:none; border-right-style:none; border-bottom-style:none; border-left-style:none; border-wIDth:initial; border-color:initial; background-color:transparent; line-height:18px">    sqlite3_reset(stmt);        sqlite3_finalize(stmt);    "COMMITTRANSACTION",92); List-style-type:decimal; List-style-position:outsIDe!important; word-wrap:break-word; word-break:normal; border-top-style:none; border-right-style:none; border-bottom-style:none; border-left-style:none; border-wIDth:initial; border-color:initial; background-color:transparent; line-height:18px">    NSLog(@"@R_404_5138@tocommittransaction:%s",153); background-color:inherit; Font-weight:bold">char*query="SELECTcount(*)FROMtest;";        if(sqlite3_step(stmt)==sqlITE_ROW){        NSLog(@"tablesize:%d",sqlite3_column_int(stmt,0));        }else{        NSLog(@"@R_404_5138@toreadtable:%s",51); background-color:transparent; text-indent:28px"> 然后创建一个串行队列：
 
staticdispatch_queue_tqueue;        //...        queue=dispatch_queue_create("net.keakon.db",128); background-color:inherit">NulL);     再设置一个计数器，每秒执行一次：
 
intlastReadCount=0;    intreadCount=0;    intlastWriteCount=0;    intwriteCount=0;        -(voID)count{    intlastRead=lastReadCount;    intlastWrite=lastWriteCount;        lastReadCount=readCount;        lastWriteCount=writeCount;        NSLog(@"%d,%d",lastReadCount-lastRead,lastWriteCount-lastWrite);        [NSTimerscheduledTimerWithTimeInterval:1.0target:selfselector:@selector(count)userInfo:nilrepeats:YES];     这样就可以开始测试select和update了：
 
staticvoIDreadData(){    char*query="SELECTvalueFROMtestWHEREvalue<?ORDERBYvalueDESclimIT1;";        voID(^__blockreadBlock)()=Block_copy(^{    intreturnCode=sqlite3_step(stmt);        if(returnCode==sqlITE_ROW||returnCode==sqlITE_DONE){        ++readCount;        NSLog(@"@R_404_5138@topreparestatement:%s",92); List-style-type:decimal; List-style-position:outsIDe!important; word-wrap:break-word; word-break:normal; border-top-style:none; border-right-style:none; border-bottom-style:none; border-left-style:none; border-wIDth:initial; border-color:initial; background-color:transparent; line-height:18px">    dispatch_async(queue,readBlock);        });    staticvoIDwriteData(){    update="UPDATEtestSETvalue=?WHEREID=?;";        voID(^__blockwriteBlock)()=Block_copy(^{    update,2,arc4random()%1000+1);        if(sqlite3_step(stmt)==sqlITE_DONE){        ++writeCount;         这里是用dispatch_async()来异步地递归调用block。
 
 因为block是在栈里生成的，异步执行时已经被销毁，所以需要copy到堆。因为需要一直执行，所以我就没release了。
 
 此外，光copy的话还是无法正常执行，但是把block本身的存储类型设为__block后就正常了，原因我也不清楚。
 
 测试结果为只读时平均每秒165次，只写时每秒68次，同时读写时每秒各47次。换成多线程或串行模式时，效率也差不多。
 
 接着试试WAL模式：
 
    if(sqlite3_exec("PRAGMAjournal_mode=WAL;",92); List-style-type:decimal; List-style-position:outsIDe!important; word-wrap:break-word; word-break:normal; border-top-style:none; border-right-style:none; border-bottom-style:none; border-left-style:none; border-wIDth:initial; border-color:initial; background-color:transparent; line-height:18px">    NSLog(@"@R_404_5138@tosetWALmode:%s",51); background-color:transparent; text-indent:28px"> sqlite3_wal_checkpoint(database,NulL); // 每次测试前先checkpoint，避免WAL文件过大而影响性能
 
 测试结果为只读时平均每秒166次，只写时每秒244次，同时读写时每秒各97次。并发性增加了1倍有木有!更夸张的是写入比读取还快了。
 
 在自编译的3.7.8版中，同时读写为每秒各102次，加上sqlITE_THREADSAFE=0参数后为每秒各104次，性能稍有提升。
 
 第三种方式需要打开和关闭数据库连接，所以会额外消耗一些时间。此外还要维持各个连接间的互斥，事务也比较容易冲突，但能确保事务正确执行。
 
 首先需要移除全局的database变量，并修改openDb()函数：
 
    sqlite3*database=NulL;     再配置成多线程模式：
 
    sqlite3_config(sqlITE_CONfig_MulTITHREAD);  
 
 队列改成可以乱序执行的：
 
    queue=dispatch_get_global_queue(disPATCH_QUEUE_PRIORITY_BACKGROUND,0);  
 
 然后是访问数据库：
 
    while(YES){         这里就无需递归调用了，直接在子线程中循环即可。
 
 测试结果为只读时平均每秒164次，只写时每秒68次，同时读写时分别为每秒14和30次(波动很大)。此外，这种方式因为最初启动的几个线程持续访问数据库，后加入的线程会滞后几秒才启动，且很难打开数据库连接或创建prepare statement。调试时发现只会启用2个线程，但是随队列中block数目的增加，读性能增高，写性能降低。读写各3个block时分别为每秒35和14次。
 
 WAL模式下甚至连初始时启动2个线程都会被lock，因此只能改成不断重试：
 
    while(sqlite3_prepare_v2(NulL)!=sqlITE_OK);         结果为只读时平均每秒169次，只写时每秒246次，同时读写时每秒分别为90和57次(波动较大)。并发效率有了显著提升，但仍不及第二种方式。
 
 第四种方式相当于让sqlite来维护队列，只不过sql的执行是乱序的，因此无法保证事务性。
 
 先恢复全局的database变量，然后配置成串行模式：
 
 sqlite3_config(sqlITE_CONfig_SERIAliZED);
 
 再是访问数据库：
 
 测试结果为只读时平均每秒164次，只写时每秒68次，同时读写时每秒分别为57和43次。读线程比写线程的速率更高，而且新线程的加入不需要等待。
 
 WAL模式下，只读时平均每秒176次，只写时每秒254次，同时读写时每秒分别为109和85次。
 
 由此可见，要获得最好的性能的话，WAL模式是必须启用的，为此也有必要自己编译sqlite 3.7.0以上的版本(除非不支持iOS 4.2及以下版本)。
 
 而在测试过的后3种方式中：第3种是效率最低的，不建议使用;第4种读取性能更高，适合无需使用事务的场合;第2种适用范围更广，效率也足够优秀，一般应采用这种方式。
 
 不过要注意的是，第2种方式在测试时的逻辑是完全与数据库相关的。实际中可能要做计算或IO访问等工作，在此期间其他线程都是被阻塞的，这样就会大大降低效率了。因此只建议把访问数据库的逻辑放入队列，其余工作在其他线程里完成。
 
 刚才洗澡时我又想到一点，既然第2种方式不能并行，第4种方式不能保证事务性，那么能否将各自的优点结合起来呢?
 
 于是一个新的实现方案又浮出水面了：使用2个串行队列，分别负责读和写，每个队列各使用一个数据库连接，线程模式可以采用多线程或串行模式。
 
 代码拿方式2稍做修改就行了，这里就不列出了。测试结果波动比较大(估计是checkpoint的影响)，多线程模式下平均约为89和73次，串行模式下为91和86次。
 
 但在iPad 2这种双核的机型上，多线程明显要比单队列更具优势：方式2的成绩是每秒各85次，方式3是94和124次(写波动较大)，方式4是95和72次，而新方案在多线程模式下是104和168次(写波动很大，40～280之间)，串行模式下为108和177次(写波动很大)。
 
 因此极端的优化情况下，可以根据cpu核心数来创建队列数，然后把数据库访问线程随机分配到某个队列中。不过考虑到iOS设备这种嵌入式平台并不需要密集地访问数据库，而且除数据库线程以外还有其他事要做，如果没遇到瓶颈的话，简单的方案2其实也够用了。
 
 

 
 
原文地址：http://mobile.51cto.com/iphone-299765.htm
          总结       
以上是内存溢出为你收集整理的SQLite在多线程环境下的应用全部内容，希望文章能够帮你解决SQLite在多线程环境下的应用所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
原文地址:https://54852.com/sjk/1182531.html