首页 > 代码库 > SQLite在多线程环境下的应用

SQLite在多线程环境下的应用

这几天研究了一下SQLite这个嵌入式数据库在多线程环境下的应用,感觉里面的学问还挺多,于是就在此分享一下。

AD: 2014WOT全球软件技术峰会北京站 课程视频发布

 

先说下初衷吧,实际上我经常看到有人抱怨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来演示一下:

  1. # -*- coding: utf-8 -*- 
  2.  
  3. import sqlite3 
  4.  
  5. import threading 
  6.  
  7. def f(): 
  8.  
  9. con.rollback() 
  10.  
  11. con = sqlite3.connect(‘test.db‘, check_same_thread=False) # 允许在其他线程中使用这个连接 
  12.  
  13. cu = con.cursor() 
  14.  
  15. cu.execute(‘CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY)‘) 
  16.  
  17. print cu.execute(‘SELECT count(*) FROM test‘).fetchone()[0] # 0 
  18.  
  19. cu.execute(‘INSERT INTO test VALUES (NULL)‘) 
  20.  
  21. print cu.execute(‘SELECT count(*) FROM test‘).fetchone()[0] # 1 
  22.  
  23. thread = threading.Thread(target=f) 
  24.  
  25. thread.start() 
  26.  
  27. thread.join() 
  28.  
  29. print cu.execute(‘SELECT count(*) FROM test‘).fetchone()[0] # 0 
  30.  
  31. cu.close() 
  32.  
  33. 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)

连接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种方式:

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的优势。

先初始化数据库:

  1. #import 
  2.  
  3. static char dbPath[200]; 
  4.  
  5. static sqlite3 *database; 
  6.  
  7. static sqlite3 *openDb() { 
  8.  
  9. if (sqlite3_open(dbPath, &database) != SQLITE_OK) { 
  10.  
  11. sqlite3_close(database); 
  12.  
  13. NSLog(@"Failed to open database: %s", sqlite3_errmsg(database)); 
  14.  
  15.  
  16. return database; 
  17.  
  18.  
  19. - (void)viewDidLoad { 
  20.  
  21. [super viewDidLoad]; 
  22.  
  23. sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); 
  24.  
  25. NSLog(@"%d", sqlite3_threadsafe()); 
  26.  
  27. NSLog(@"%s", sqlite3_libversion()); 
  28.  
  29. NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); 
  30.  
  31. NSString *documentsDirectory = [paths objectAtIndex:0]; 
  32.  
  33. strcpy(dbPath, [[documentsDirectory stringByAppendingPathComponent:@"data.sqlite3"] UTF8String]); 
  34.  
  35. database = openDb(); 
  36.  
  37. char *errorMsg; 
  38.  
  39. if (sqlite3_exec(database, "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, value INTEGER);", NULL, NULL, &errorMsg) != SQLITE_OK) { 
  40.  
  41. NSLog(@"Failed to create table: %s", errorMsg); 
  42.  
  43.  

再插入1000条测试数据:

  1. static void insertData() { 
  2.  
  3. char *errorMsg; 
  4.  
  5. if (sqlite3_exec(database, "BEGIN TRANSACTION", NULL, NULL, &errorMsg) != SQLITE_OK) { 
  6.  
  7. NSLog(@"Failed to begin transaction: %s", errorMsg); 
  8.  
  9.  
  10. static const char *insert = "INSERT INTO test VALUES (NULL, ?);"; 
  11.  
  12. sqlite3_stmt *stmt; 
  13.  
  14. if (sqlite3_prepare_v2(database, insert, -1, &stmt, NULL) == SQLITE_OK) { 
  15.  
  16. for (int i = 0; i < 1000; ++i) { 
  17.  
  18. sqlite3_bind_int(stmt, 1, arc4random()); 
  19.  
  20. if (sqlite3_step(stmt) != SQLITE_DONE) { 
  21.  
  22. --i; 
  23.  
  24. NSLog(@"Error inserting table: %s", sqlite3_errmsg(database)); 
  25.  
  26.  
  27. sqlite3_reset(stmt); 
  28.  
  29.  
  30. sqlite3_finalize(stmt); 
  31.  
  32.  
  33. if (sqlite3_exec(database, "COMMIT TRANSACTION", NULL, NULL, &errorMsg) != SQLITE_OK) { 
  34.  
  35. NSLog(@"Failed to commit transaction: %s", errorMsg); 
  36.  
  37.  
  38. static const char *query = "SELECT count(*) FROM test;"; 
  39.  
  40. if (sqlite3_prepare_v2(database, query, -1, &stmt, NULL) == SQLITE_OK) { 
  41.  
  42. if (sqlite3_step(stmt) == SQLITE_ROW) { 
  43.  
  44. NSLog(@"Table size: %d", sqlite3_column_int(stmt, 0)); 
  45.  
  46. else { 
  47.  
  48. NSLog(@"Failed to read table: %s", sqlite3_errmsg(database)); 
  49.  
  50.  
  51. sqlite3_finalize(stmt); 
  52.  
  53.  

然后创建一个串行队列:

  1. static dispatch_queue_t queue; 
  2.  
  3. - (void)viewDidLoad { 
  4.  
  5. // ... 
  6.  
  7. queue = dispatch_queue_create("net.keakon.db", NULL); 
  8.  

再设置一个计数器,每秒执行一次:

  1. static int lastReadCount = 0; 
  2.  
  3. static int readCount = 0; 
  4.  
  5. static int lastWriteCount = 0; 
  6.  
  7. static int writeCount = 0; 
  8.  
  9. - (void)count { 
  10.  
  11. int lastRead = lastReadCount; 
  12.  
  13. int lastWrite = lastWriteCount; 
  14.  
  15. lastReadCount = readCount; 
  16.  
  17. lastWriteCount = writeCount; 
  18.  
  19. NSLog(@"%d, %d", lastReadCount - lastRead, lastWriteCount - lastWrite); 
  20.  
  21.  
  22. - (void)viewDidLoad { 
  23.  
  24. // ... 
  25.  
  26. [NSTimer scheduledTimerWithTimeInterval:1.0 target:self selector:@selector(count) userInfo:nil repeats:YES]; 
  27.  

这样就可以开始测试select和update了:

  1. static void readData() { 
  2.  
  3. static const char *query = "SELECT value FROM test WHERE value < ? ORDER BY value DESC LIMIT 1;"; 
  4.  
  5. void (^ __block readBlock)() = Block_copy(^{ 
  6.  
  7. sqlite3_stmt *stmt; 
  8.  
  9. if (sqlite3_prepare_v2(database, query, -1, &stmt, NULL) == SQLITE_OK) { 
  10.  
  11. sqlite3_bind_int(stmt, 1, arc4random()); 
  12.  
  13. int returnCode = sqlite3_step(stmt); 
  14.  
  15. if (returnCode == SQLITE_ROW || returnCode == SQLITE_DONE) { 
  16.  
  17. ++readCount; 
  18.  
  19.  
  20. sqlite3_finalize(stmt); 
  21.  
  22. else { 
  23.  
  24. NSLog(@"Failed to prepare statement: %s", sqlite3_errmsg(database)); 
  25.  
  26.  
  27. dispatch_async(queue, readBlock); 
  28.  
  29. }); 
  30.  
  31. dispatch_async(queue, readBlock); 
  32.  
  33.  
  34. static void writeData() { 
  35.  
  36. static const char *update = "UPDATE test SET value = ? WHERE id = ?;"; 
  37.  
  38. void (^ __block writeBlock)() = Block_copy(^{ 
  39.  
  40. sqlite3_stmt *stmt; 
  41.  
  42. if (sqlite3_prepare_v2(database, update, -1, &stmt, NULL) == SQLITE_OK) { 
  43.  
  44. sqlite3_bind_int(stmt, 1, arc4random()); 
  45.  
  46. sqlite3_bind_int(stmt, 2, arc4random() % 1000 + 1); 
  47.  
  48. if (sqlite3_step(stmt) == SQLITE_DONE) { 
  49.  
  50. ++writeCount; 
  51.  
  52.  
  53. sqlite3_finalize(stmt); 
  54.  
  55. else { 
  56.  
  57. NSLog(@"Failed to prepare statement: %s", sqlite3_errmsg(database)); 
  58.  
  59.  
  60. dispatch_async(queue, writeBlock); 
  61.  
  62. }); 
  63.  
  64. dispatch_async(queue, writeBlock); 
  65.  

这里是用dispatch_async()来异步地递归调用block。

因为block是在栈里生成的,异步执行时已经被销毁,所以需要copy到堆。因为需要一直执行,所以我就没release了。

此外,光copy的话还是无法正常执行,但是把block本身的存储类型设为__block后就正常了,原因我也不清楚。

测试结果为只读时平均每秒165次,只写时每秒68次,同时读写时每秒各47次。换成多线程或串行模式时,效率也差不多。

接着试试WAL模式:

  1. if (sqlite3_exec(database, "PRAGMA journal_mode=WAL;", NULL, NULL, &errorMsg) != SQLITE_OK) { 
  2.  
  3. NSLog(@"Failed to set WAL mode: %s", errorMsg); 
  4.  

sqlite3_wal_checkpoint(database, NULL); // 每次测试前先checkpoint,避免WAL文件过大而影响性能

测试结果为只读时平均每秒166次,只写时每秒244次,同时读写时每秒各97次。并发性增加了1倍有木有!更夸张的是写入比读取还快了。

在自编译的3.7.8版中,同时读写为每秒各102次,加上SQLITE_THREADSAFE=0参数后为每秒各104次,性能稍有提升。

第三种方式需要打开和关闭数据库连接,所以会额外消耗一些时间。此外还要维持各个连接间的互斥,事务也比较容易冲突,但能确保事务正确执行。

首先需要移除全局的database变量,并修改openDb()函数:

  1. static sqlite3 *openDb() { 
  2.  
  3. sqlite3 *database = NULL; 
  4.  
  5. if (sqlite3_open(dbPath, &database) != SQLITE_OK) { 
  6.  
  7. sqlite3_close(database); 
  8.  
  9. NSLog(@"Failed to open database: %s", sqlite3_errmsg(database)); 
  10.  
  11.  
  12. return database; 
  13.  

再配置成多线程模式:

  1. sqlite3_config(SQLITE_CONFIG_MULTITHREAD); 

队列改成可以乱序执行的:

  1. queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_BACKGROUND, 0); 

然后是访问数据库:

  1. static void readData() { 
  2.  
  3. static const char *query = "SELECT value FROM test WHERE value < ? ORDER BY value DESC LIMIT 1;"; 
  4.  
  5. dispatch_async(queue, ^{ 
  6.  
  7. sqlite3 *database = openDb(); 
  8.  
  9. sqlite3_stmt *stmt; 
  10.  
  11. if (sqlite3_prepare_v2(database, query, -1, &stmt, NULL) == SQLITE_OK) { 
  12.  
  13. while (YES) { 
  14.  
  15. sqlite3_bind_int(stmt, 1, arc4random()); 
  16.  
  17. int returnCode = sqlite3_step(stmt); 
  18.  
  19. if (returnCode == SQLITE_ROW || returnCode == SQLITE_DONE) { 
  20.  
  21. ++readCount; 
  22.  
  23.  
  24. sqlite3_reset(stmt); 
  25.  
  26.  
  27. sqlite3_finalize(stmt); 
  28.  
  29. else { 
  30.  
  31. NSLog(@"Failed to prepare statement: %s", sqlite3_errmsg(database)); 
  32.  
  33.  
  34. sqlite3_close(database); 
  35.  
  36. }); 
  37.  
  38.  
  39. static void writeData() { 
  40.  
  41. static const char *update = "UPDATE test SET value = ? WHERE id = ?;"; 
  42.  
  43. dispatch_async(queue, ^{ 
  44.  
  45. sqlite3 *database = openDb(); 
  46.  
  47. sqlite3_stmt *stmt; 
  48.  
  49. if (sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK) { 
  50.  
  51. while (YES) { 
  52.  
  53. sqlite3_bind_int(stmt, 1, arc4random()); 
  54.  
  55. sqlite3_bind_int(stmt, 2, arc4random() % 1000 + 1); 
  56.  
  57. if (sqlite3_step(stmt) == SQLITE_DONE) { 
  58.  
  59. ++writeCount; 
  60.  
  61.  
  62. sqlite3_reset(stmt); 
  63.  
  64.  
  65. sqlite3_finalize(stmt); 
  66.  
  67. else { 
  68.  
  69. NSLog(@"Failed to prepare statement: %s", sqlite3_errmsg(database)); 
  70.  
  71.  
  72. sqlite3_close(database); 
  73.  
  74. }); 
  75.  

这里就无需递归调用了,直接在子线程中循环即可。

测试结果为只读时平均每秒164次,只写时每秒68次,同时读写时分别为每秒14和30次(波动很大)。此外,这种方式因为最初启动的几个线程持续访问数据库,后加入的线程会滞后几秒才启动,且很难打开数据库连接或创建prepare statement。调试时发现只会启用2个线程,但是随队列中block数目的增加,读性能增高,写性能降低。读写各3个block时分别为每秒35和14次。

WAL模式下甚至连初始时启动2个线程都会被lock,因此只能改成不断重试:

  1. static void readData() { 
  2.  
  3. static const char *query = "SELECT value FROM test WHERE value < ? ORDER BY value DESC LIMIT 1;"; 
  4.  
  5. dispatch_async(queue, ^{ 
  6.  
  7. sqlite3 *database = openDb(); 
  8.  
  9. sqlite3_stmt *stmt; 
  10.  
  11. while (sqlite3_prepare_v2(database, query, -1, &stmt, NULL) != SQLITE_OK); 
  12.  
  13. while (YES) { 
  14.  
  15. sqlite3_bind_int(stmt, 1, arc4random()); 
  16.  
  17. int returnCode = sqlite3_step(stmt); 
  18.  
  19. if (returnCode == SQLITE_ROW || returnCode == SQLITE_DONE) { 
  20.  
  21. ++readCount; 
  22.  
  23.  
  24. sqlite3_reset(stmt); 
  25.  
  26.  
  27. sqlite3_finalize(stmt); 
  28.  
  29. sqlite3_close(database); 
  30.  
  31. }); 
  32.  
  33.  
  34. static void writeData() { 
  35.  
  36. static const char *update = "UPDATE test SET value = ? WHERE id = ?;"; 
  37.  
  38. dispatch_async(queue, ^{ 
  39.  
  40. sqlite3 *database = openDb(); 
  41.  
  42. sqlite3_stmt *stmt; 
  43.  
  44. while (sqlite3_prepare_v2(database, update, -1, &stmt, nil) != SQLITE_OK); 
  45.  
  46. while (YES) { 
  47.  
  48. sqlite3_bind_int(stmt, 1, arc4random()); 
  49.  
  50. sqlite3_bind_int(stmt, 2, arc4random() % 1000 + 1); 
  51.  
  52. if (sqlite3_step(stmt) == SQLITE_DONE) { 
  53.  
  54. ++writeCount; 
  55.  
  56.  
  57. sqlite3_reset(stmt); 
  58.  
  59.  
  60. sqlite3_finalize(stmt); 
  61.  
  62. sqlite3_close(database); 
  63.  
  64. }); 
  65.  

结果为只读时平均每秒169次,只写时每秒246次,同时读写时每秒分别为90和57次(波动较大)。并发效率有了显著提升,但仍不及第二种方式。

第四种方式相当于让SQLite来维护队列,只不过SQL的执行是乱序的,因此无法保证事务性。

先恢复全局的database变量,然后配置成串行模式:

sqlite3_config(SQLITE_CONFIG_SERIALIZED);

再是访问数据库:

  1. static void readData() { 
  2.  
  3. static const char *query = "SELECT value FROM test WHERE value < ? ORDER BY value DESC LIMIT 1;"; 
  4.  
  5. dispatch_async(queue, ^{ 
  6.  
  7. sqlite3_stmt *stmt; 
  8.  
  9. if (sqlite3_prepare_v2(database, query, -1, &stmt, NULL) == SQLITE_OK) { 
  10.  
  11. while (YES) { 
  12.  
  13. sqlite3_bind_int(stmt, 1, arc4random()); 
  14.  
  15. int returnCode = sqlite3_step(stmt); 
  16.  
  17. if (returnCode == SQLITE_ROW || returnCode == SQLITE_DONE) { 
  18.  
  19. ++readCount; 
  20.  
  21.  
  22. sqlite3_reset(stmt); 
  23.  
  24.  
  25. sqlite3_finalize(stmt); 
  26.  
  27. else { 
  28.  
  29. NSLog(@"Failed to prepare statement: %s", sqlite3_errmsg(database)); 
  30.  
  31.  
  32. }); 
  33.  
  34.  
  35. static void writeData() { 
  36.  
  37. static const char *update = "UPDATE test SET value = ? WHERE id = ?;"; 
  38.  
  39. dispatch_async(queue, ^{ 
  40.  
  41. sqlite3_stmt *stmt; 
  42.  
  43. if (sqlite3_prepare_v2(database, update, -1, &stmt, NULL) == SQLITE_OK) { 
  44.  
  45. while (YES) { 
  46.  
  47. sqlite3_bind_int(stmt, 1, arc4random()); 
  48.  
  49. sqlite3_bind_int(stmt, 2, arc4random() % 1000 + 1); 
  50.  
  51. if (sqlite3_step(stmt) == SQLITE_DONE) { 
  52.  
  53. ++writeCount; 
  54.  
  55.  
  56. sqlite3_reset(stmt); 
  57.  
  58.  
  59. sqlite3_finalize(stmt); 
  60.  
  61. else { 
  62.  
  63. NSLog(@"Failed to prepare statement: %s", sqlite3_errmsg(database)); 
  64.  
  65.  
  66. }); 
  67.  

测试结果为只读时平均每秒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其实也够用了。

SQLite在多线程环境下的应用