嵌入式 关于sqlite多线程编程相关小结

相关接口说明：

sqlite3_open()
通常这个函数被第一个调用，这个操作打开一个数据库文件的链接，输出一个database connection对象，database connection对象在调用之后的接口时使用。
函数原型：
int sqlite3_open(
const char *filename, /*Database filename (UTF-8) */
sqlite3 **ppDb /* OUT:SQLite db handle */
);
int sqlite3_open16(
const void *filename, /*Database filename (UTF-16) */
sqlite3 **ppDb /* OUT:SQLite db handle */
);
int sqlite3_open_v2(
constchar *filename, /* Database filename(UTF-8) */
sqlite3 **ppDb, /* OUT:SQLite db handle */
intflags, /* Flags */
const char *zVfs /* Name ofVFS module to use */
);
通过filename参数指定要打开的数据库，sqlite3_open()和sqlite3_open_v2()的filename参数是utf8编码格式，sqlite3_open16()的filename参数是UTF-16编码。
如果成功，sqlite3*被创建，返回值SQLITE_OK，如果失败，返回错误码，调用sqlite3_errmsg() 或 sqlite3_errmsg16()将得到错误信息。
无论成功与否， database connection句柄资源已经被分配，在不使用时应该将数据库连接句柄传递给sqlite3_close()释放资源。

sqlite3_open_v2的第三个参数，有3个值和其他的值（SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_FULLMUTEX, SQLITE_OPEN_SHAREDCACHE,SQLITE_OPEN_PRIVATECACHE, and/or SQLITE_OPEN）组合：
SQLITE_OPEN_READONLY
SQLITE_OPEN_READWRITE
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
如何不是用上面的3个值之一和其他值组合，其行为是未定义的。

sqlite3_prepare()
这个操作第一个参数输入database connection对象，第二个参数输入sql语句，该接口将输入的sql语句转变成prepared statement对象，输出prepared statement对象，在调用之后的接口时使用，记住，这个函数不执行sql语句，只为执行sql做准备。
函数原型：
int sqlite3_prepare(
sqlite3 *db, /*Database handle */
const char *zSql, /* SQLstatement, UTF-8 encoded */
intnByte, /* Maximum length ofzSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT:Statement handle */
const char **pzTail /* OUT:Pointer to unused portion of zSql */
);
int sqlite3_prepare_v2(
sqlite3 *db, /*Database handle */
const char *zSql, /* SQLstatement, UTF-8 encoded */
intnByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT:Statement handle */
const char **pzTail /* OUT:Pointer to unused portion of zSql */
);
int sqlite3_prepare16(
sqlite3 *db, /*Database handle */
const void *zSql, /* SQLstatement, UTF-16 encoded */
intnByte, /* Maximum length ofzSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT:Statement handle */
const void **pzTail /* OUT:Pointer to unused portion of zSql */
);
int sqlite3_prepare16_v2(
sqlite3 *db, /*Database handle */
const void *zSql, /* SQLstatement, UTF-16 encoded */
intnByte, /* Maximum length ofzSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT:Statement handle */
const void **pzTail /* OUT: Pointer to unused portion of zSql*/
);

sqlite3_step()
这个操作执行sqlite3_prepare()接口返回的prepared statement，如果是SELECT操作，结果集的第一行将被返回，如果想得到第二行，还须再调用一次该函数，也就是每调用一次返回一行，直到完成。
有些操作仅需调用1次该函数，如：INSERT, UPDATE, DELETE语句。
使用老接口得到的statement，执行sqlite3_step返回SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE；
使用v2接口得到的statement，执行sqlite3_step返回任意的result code或extended result codes。

sqlite3_column()
sqlite3接口并没有sqlite3_column()函数，这里所说的是一组函数：
sqlite3_column_blob()
sqlite3_column_bytes()
sqlite3_column_bytes16()
sqlite3_column_count()
sqlite3_column_double()
sqlite3_column_int()
sqlite3_column_int64()
sqlite3_column_text()
sqlite3_column_text16()
sqlite3_column_type()
sqlite3_column_value()
这个操作输入执行过sqlite3_step()的prepared statement对象，返回单列的结果，要得到一行中全部列的值需要调用多次这个函数。

sqlite3_finalize()
销毁之前调用sqlite3_prepare()创建的prepared statement，prepared statement必须由该函数销毁，否则会导致内存泄露。

sqlite3_close()
关闭之前调用sqlite3_open()创建的database connection，在调用该函数之前，必须保证所有的prepared statements都被finalized。

开发SQLite应用一般的流程：
首先用sqlite3_open()创建一个数据库连接，该函数根据输入参数可以打开一个已存在的数据库，也可以创建一个新的数据库文件。不用数据库的时候记得调用 sqlite3_close() 关闭数据库连接。
执行一个SQL statement，有以下步骤：
1. 调用sqlite3_prepare()创建 preparedstatement。
2. 调用sqlite3_step()一次或多次执行preparedstatement。
3. 对于查询操作，在两次sqlite3_step()之间调用sqlite3_column()获取结果。
4. 调用sqlite3_finalize()销毁preparedstatement。

在调用 sqlite3_step()之后，可以调用sqlite3_reset()初始化prepared statement。
很多时候，每次调用的sql语句都是相似的，比如使用INSERT语句，每次执行只是插入不同的值，为了适应这种灵活性，SQLite允许SQL statements带参数，在执行statements前给这些参数绑定一个值，绑定的值在 prepared statement第二次执行的时候还可以改变。

获得错误信息的函数：
int sqlite3_errcode(sqlite3 *db);
int sqlite3_extended_errcode(sqlite3 *db);
const char *sqlite3_errmsg(sqlite3*);
const void *sqlite3_errmsg16(sqlite3*);

三．SQLite3的线程模式
sqlite支持3种不同的线程模式：
Single-thread：这种模式下，所有的互斥被禁用，多线程使用sqlite是不安全的。
Multi-thread：这种模式下，sqlite可以安全地用于多线程，但多个线程不能共享一个database connect。
Serialized：这种模式下，sqlite可以安全地用于多线程，无限制。

sqlite线程模式的选择可以在编译时（当SQLitelibrary 源码被编译时）或启动时（使用SQLite的应用初始化时）或运行时（新的 database connection被创建时）。
一般来说，运行时覆盖启动时，启动时覆盖编译时，不过，Single-thread模式一旦被选择了，就不能被重改。

编译时设置线程模式：
用SQLITE_THREADSAFE选择线程模式，
如果SQLITE_THREADSAFE没有被设置或设置了-DSQLITE_THREADSAF=1，则是Serialized模式；
如果设置了-DSQLITE_THREADSAF=0，线程模式是Single-thread；
如果设置了-DSQLITE_THREADSAF=2，线程模式是Multi-thread.

启动时设置线程模式：
假设编译时线程模式被设置为非Single-thread模式，在初始化时可以调用sqlite3_config()改变线程模式，参数可以为SQLITE_CONFIG_SINGLETHREA，SQLITE_CONFIG_MULTITH，SQLITE_CONFIG_SERIALIZED.

运行时设置线程模式：
如果Single-thread模式在编译时和启动时都没被设置，则可以在database connections被创建时设置Multi-thread或Serialized模式，但不可以降级设置为Single-thread模式，也不可以升级编译时或启动时设置的Single-thread模式。
sqlite3_open_v2()的第三个参数决定单个databaseconnection的线程模式，SQLITE_OPEN_NOMUTEX使database connection为Multi-thread模式，SQLITE_OPEN_FULLMUTE使database connection为Serialized模式。
如果不指定模式或使用sqlite3_open()或sqlite3_open16()接口，线程模式为编译时或启动时设置的模式。

在IOS上的示例：
先说下初衷吧，实际上我经常看到有人抱怨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 - iPhone OS 4.1 / 4.2
3.7.2 - iPhone OS 4.3
3.7.7 - iPhone OS 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种线程模式：

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()时会自动调用，且只有第一次调用是有效的。
另一个要说明的是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 -*-