sqlite线程模式

Qter

https://mp.weixin.qq.com/s/62-mXHj_hujnfuWsZskwNg

这篇主要来讨论SQLite的多线程中的疑惑。

1. SQLite 支持三种线程模式

单线程（Single-thread）模式。

在此模式下，所有的互斥锁都将被禁用，相关代码被删除，SQLite 在多线程并发访问时将不再安全。但根据马克思辩证法，此模式下代码量最小，对数据库的增删改查的单次运行效率最高。

线序化（Serialized）模式

在此模式下，应用程序的多线程可以使用同一个 SQLite 数据库连接，然后一起操作数据库，此时 SQLite 内部将保证数据库的安全性。其本质是，SQLite 利用内部互斥锁使得同时想要操作数据库的线程“线序化”，也就是一个一个来，保证数据库数据的完整性和安全性。

多线程（Multi-thread）模式。

在此模式下，SQLite 数据库可被多线程连接多次，并支持多线程随机访问。

SQLite的线程模式可以在编译时、启动时或者运行时对以上线程模式做出修改。编译时意味着从 SQLite 的源码编译生成 SQLite 库文件的时候，启动时意味着某个即将要使用 SQLite 的程序初始化的时候，运行时意味着要创建一个新的 SQLite 数据库连接的时候。一般而言，运行时所做的修改将覆盖启动时的设置，而启动时的设置将覆盖编译时的设置。但单线程模式是例外，单线程模式一旦被设定就无法被修改了。

SQLite 默认的线程模式是线序化模式。

2. 编译时对线程模式的选择

使用 SQLITE_THREADSAFE 来选择不同的线程模式，具体如下：

-DSQLITE_THREADSAFE=0 单线程模式

-DSQLITE_THREADSAFE=1 线序化模式（默认）

-DSQLITE_THREADSAFE=2 多线程模式

函数 sqlite3_threadsafe() 可以返回编译时的线程模式，如果是单线程模式则其返回 false，否则它将返回 true。由于该函数的执行比启动时和运行时要早，因此无法对这两个时刻的线程模式的修改做出判断。

如果在编译时选择了单线程模式，那么用于保护临界资源的互斥锁及其相关代码将被移除，因此此后在启动时、运行时都将无法改为线序化或者多线程模式。

3. 启动时对线程模式的选择

使用 thesqlite3_config() 来修改线程模式。具体如下：

SQLITE_CONFIG_SINGLETHREAD 设置为单线程模式

SQLITE_CONFIG_MULTITHREAD  设置为多线程模式

SQLITE_CONFIG_SERIALIZED   设置为线序化模式

4. 运行时对线程模式的选择

只要在编译时没有选择单线程模式，每一个数据库在被连接时都可设置为多线程或线序化模式。

每一个数据库连接的线程模式都可以通过 sqlite3_open_v2() 的第三个函数来选择，具体如下：

SQLITE_OPEN_NOMUTEX 意味着多线程模式

SQLITE_OPEN_FULLMUTEX 意味着线序化模式

如果以上选项都没设置，或者应用程序使用了 sqlite3_open() 或者 sqlite3_open16() 接口来连接数据库，那么就使用编译时和启动时的线程模式。

今天先聊到这儿，后续关于SQLite的常见问题会陆续更新。欢迎小伙伴关注、转发、点赞、收藏、吐槽、扔鸡蛋……

Qter

https://mp.weixin.qq.com/s/JQQMEPny6xgVyaP5KvSJyg

1. 问：怎么创建一个自动递增的域？

1. 答：对于这个问题，简短的回答是：任何一个被声明为 INTEGER PRIMARY KEY 的域都将是自动递增的。

而更完整的回答是：如果你在一个表中，声明了一个 INTEGER PRIMARY KEY  的域，那么无论何时当你插入一个NULL到该域时，NULL都将被自动转换为一个整数，并且其值为该域中的最大值+1，当然如果表为空时，将被设置为1。再者，如果当前该域中的最大值已经达到 9223372036854775807 （天知道你在干什么！）的话，那将会随机挑选一个未使用过的值来用。

例如，你有个表长成这个样：

CREATE TABLE t1(

    a INTEGER PRIMARY KEY,

    b INTEGER);

那么以下两条语句，在逻辑上将会是等价的：

INSERT INTO t1 VALUES(NULL, 123);

INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1, 123);

函数 sqlite3_last_insert_rowid() 可以返回最近一次插入操作的整数主键的值。

还有一点要注意，新建的主键的值等于原先存在的最大的主键的值+1，这个新的主键当然是当前全表唯一的，但却有可能跟之前已经被删除的记录的键值相等，如此一来可能会导致查询时不必要的误会。如果要创建一个表全生命周期唯一的键值，就要在声明中再加上这个约束关键字： AUTOINCREMENT。这样一来，新建的主键键值就不仅是当前全表唯一，并且在表的全生命周期内也具备唯一性，即：是所有创建过的最大的键值+1。另外，如果最大的键值已经被使用过了无法在递增，那么此时的 INSERT 操作将会失败，并且返回错误码 SQLITE_FULL 。

2. 问：SQLite究竟支持什么数据类型？

2. 答：

SQLite有所谓动态类型匹配机制，数据库中的数据可以被储存为 INTEGER（整数）,REAL（实数）, TEXT（文本字符串）, BLOB（二进制数据）, 或者 NULL。

3. 问：我刚刚将一个文本字符串插入了一个整型（INTEGER）域中！怎么回事？

3. 答：别紧张，相信我这绝对是一个特色，而不是一个BUG。

SQLite 支持所谓动态类型匹配。这意味着它并不会对数据类型做强制性约束，一般而言，任意类型的数据，都可以被插入到任意一个域中，例如你可以将任意长度的字符串插入到一个整数域中，将一个浮点实数插入到一个文本域，或者将一个日期插入到字符域中。

在你使用命令 CREATE TABLE 来创建表时对域的类型的定义，并不成为日后插入数据的约束条件。所有的域都可以储存任意长度的文本字符串。

这种情况只有一个例外：被声明为 INTEGER PRIMARY KEY 的域只能存储一个 64-bit 的有符号整数。如果你试图将一个非整数强行插入到这样的整数主键域中，恭喜你，你将收获一个关于类型不匹配的大大的 error 。

这么说来，创建 table 时指定的数据类型还有什么鸟用呢？严格说来还是有用的，SQLite会将你声明时指定的类型，作为该域的“倾向性”类型的依据。比如，如果一个域的类型被声明为 INTEGER 但是你正试图插入一串文本，那么SQLite会倾向于将此文本转换为整数，如果成功了，那么实际存储的就是一个整数，否则就存储这串文本。

4. 问：为什么SQLite不准我使用 '0' 和 '0.0' 作为两个不同记录的主键？

4. 答：是的，'0' 和 '0.0' 的确是两个完全不同的文本字符串，但是当表的主键是一个数字类型的时候，SQLite不允许你这么做。非要这么干的话，可以将主键的类型修改为 TEXT 。

这个疑惑，实际上可以从上面的第3个问题得到指引和解答。

对数据库而言，每一个行记录必须有一个唯一的主键是，这是最基本的要求。但当一个域的类型是一个数字型（包括整数、实数），而你要插入 '0' 和 '0.0' 时，SQLite将会倾向于把它们视为数字型数据，因此他们都将被记录成无法区分的零值，这，显然违反了主键的基本定义。

5. 问：可不可以让多个程序同时访问同一个数据库文件？

5. 答：这没什么不可以。

多个程序可以安全地同时执行 SELECT 的动作。但是，任何时候都只能有一个程序可以对数据库做出修改性的行为。

实际上，SQLite使用了读写锁来控制对数据库的访问。但这里必须给出警告：这个机制在NFS（网络文件系统）中工作得并不理想。

因此，你需要避免在NFS中使用多任务同时并发访问 SQLite 数据库。在 Windows 的FAT文件系统中，据说，运行一个叫Share.exe的后台精灵进程可以解决这个问题，否则锁机制将不稳定。而据我的经验，以上场景是一个货真价实的大坑，你有一万个理由不要碰它。关于这个话题，早已有无数的 Windows 砖家们给出过警告，任何想用锁机制来锁住网络文件的人都必定会被无数的莫名其妙的错误、崩溃、异常折磨成精神病，陷入噩梦般的抑郁之中。简而言之吧，避免在多端 Windows 中共享 SQLite 数据库是你先要绕过去的火坑。

而在嵌入式当中，据我所知还没有任何一款 SQL 数据库引擎在并发性上可以和 SQLite 匹敌。SQLite 允许多任务同时连接到同一个数据库文件，并且允许多任务并发读操作。当任意一个任务试图进行写操作时，它必须将整个数据库锁起来直到操作完毕，这听起来貌似不是很屌炸天，但一般而言这仅需几个毫秒而已，其他的任务只需要等待这么一小段时间即可做它们该做的事情。其他的嵌入式 SQL 数据库引擎，一般都只能做到每次让一个任务连接到一个数据库文件。

当然，基于 C/S 模型的大型数据库引擎（例如 PostgreSQL、MySQL或者Oracle）一般能支持更大程度上的并发性，支持多任务同时并发写操作。这对于 C/S 模型而言是可以办到的，因为它们有一个强大的 Server 来协调所有的访问。

如果你有如此高并发的需求，那么你应该考虑使用这样的 C/S 模型的数据库引擎，但一般而言，也许项目的真正并发需求比你想象的要低得多得多。

当 SQLite 试图对一个已经被其他任务加了锁的数据库访问时，将会得到一个 SQLITE_BUSY 的错误，你可以使用以下两个函数来控制此时你的程序的下一步行为。

sqlite3_busy_handler( )

sqlite3_busy_timeout( )

Qter

https://mp.weixin.qq.com/s/WYFuQUcjeHtsY7A6K4q3KQ

1. 问：女神SQLite是线程安全的吗？

1. 答：SQLite是线程安全的，这点确凿无疑。但我要补充的一句话是：线程有时候是恶魔，不要让女神轻易接近他！

说线程是恶魔可能有点危言耸听的味道，难道线程不是我们广大编程群众喜闻乐见的基本工具么？都用了多少年啦没啥问题！的确如此，但世界上总有头上长角的牛人，可以在早已被认为平平无奇的地方硬生生找出普通人发现不了的深层逻辑谬误，并且能装订成册警示后人，来膜拜下：

www2.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf

言归正传，SQLite本身是线程安全的，但要获得这个技能，你必须在编译的时候定义宏 SQLITE_THREADSAFE 为1。如果你不确定即将链接到你程序的 SQLite 库文件是否拥有了线程安全技能，你可以调用以下函数来确认。

sqlite3_threadsafe()

SQLite 使用互斥锁来确保多线程可以顺序地访问普通数据结构，从而确保安全性。然而，频繁地索取和释放这些互斥锁势必会轻微地降低 SQLite的性能。因此，如果你不需要 SQLite 为你提供线程安全的保障，你可以用下面的编译选项来关闭它们以获得最高性能

-DSQLITE_THREADSAFE=0

另外要额外提醒一句，在 Unix/Linux 系统下，不要将一个打开的 SQLite 数据库连接，通过调用 fork() 函数传递到子进程去使用，谨记。

2. 问：怎么列出一个数据库中所有的表和索引？

2. 答：这分两种情况，① 使用SQLite命令行的时候；② 使用C/C++编程API的时候。

第一种情况，你直接使用SQLite的内置命令 ".tables" 即可查看当前数据库中的所有表，或者使用内置命令 ".schema" 来查看当前数据库中所有的表和索引的创建语句。

第二种情况，可以在一个特殊的表 "SQLITE_MASTER" 获得所有的的表和索引。每一个SQLite数据库都有一个称为 SQLITE_MASTER 的表，它统管了数据库中所有其他的元素，它的内部定义如下：

CREATE TABLE sqlite_master (

  type TEXT,

  name TEXT,

  tbl_name TEXT,

  rootpage INTEGER,

  sql TEXT

);

对于一个表来说， type 域就是 'table' ，name 域就是表的名字。因此可以使用以下 SQL 语句来查询当前数据库库中所有的表：

SELECT name FROM sqlite_master

WHERE type='table';

对于一个索引来说，type 域就是 'index'，name 域就是索引的名字，而 tbl_name 域则表示该索引所在的表的名字。

对于表和索引，sql 域都是创建他们的原始 SQL 语句。对于自动创建的索引（比如自动递增的主键）而言，该域为 NULL。

表 SQLITE_MASTER 是只读的，你无法对其进行诸如 UPDATE、INSERT或者DELETE。当你创建或者销毁表和索引时，SQLite 系统将自动更新它。

注意，所有的临时表都不会出现在 SQLITE_MASTER 中，临时表及其索引的 schema 将被存储在另一个被称为 SQLITE_TEMP_MASTER 的表中。SQLITE_TEMP_MASTER 只对创建它的程序可见，除此之外它用起来跟 SQLITE_MASTER 没有任何区别。

可以使用以下语句，来查看当前数据库中所有永久的和临时的表：

SELECT name FROM

   (SELECT * FROM sqlite_master UNION ALL

    SELECT * FROM sqlite_temp_master)

WHERE type='table'

ORDER BY name;

3. 问：怎么在一个表中添加和删除一个域（列）？

3. 答：抱歉，作为一个正常的数据库，SQLite 不能删除表中已存在的域。

换言之，SQLite 的 ALTER TABLE 指令只能用来①在表的末尾添加一个新的域和②修改表的名称。如果你想要对表做出更加出格的行为，对不起你只能另建一张表。

例如，你有个表 t1 拥有三个域："a"、"b" 和 "c"，此时你想删除域 c ，你可以这么做：

BEGIN TRANSACTION;

CREATE TEMPORARY TABLE t1_backup(a,b);

INSERT INTO t1_backup SELECT a,b FROM t1;

DROP TABLE t1;

CREATE TABLE t1(a,b);

INSERT INTO t1 SELECT a,b FROM t1_backup;

DROP TABLE t1_backup;

COMMIT;

哇哇？！搞什么鬼为什么这么麻烦？ 就不能提供一个 DELETE COLUMN 来一键删除么？

不能！因为像 删除 这样的面目狰狞的可怕命令，对于视安全比生命更为重要的数据库而言是不能原生支持的，记录在数据库的东西，就像胎记一般，不会因为你洗个澡就洗没了，实在不想要不嫌麻烦不怕痛可以动刀子切掉，那大家都没话说。

4. 问：我在数据库中删除了很多数据，但数据库却一点儿没变小，谁出来说句公道话？

4. 答：别急听我说，当你从 SQLite 数据库中删除信息时，SQLite 内部会记录这个空出来的区域，以便于下次你插入新数据时可以使用。但在你没有断开数据库链接（close）之前，这片存储区域暂时不还给操作系统。

这好像是很多收押金的APP的套路。。。

对于强迫症患者来说，这不是一件好事，他们的理想情况是，我一旦删除数据，必须要看到实实在在的数据库变小！并且一定要删多少小多少，因为这样才能感觉整个世界尽在掌握之中，怎么才能做到呢？也好办，只要一个 SQL 命令就可以了：

VACUUM;

如果你有更高的要求，你要求每次删除数据时必须强迫 SQLite 自动释放相应的存储空间，那可以使用 auto_vacuum 来达到地。

PRAGMA auto_vacuum = FULL;

但是凡事都是要付出代价的，每次严格缩减存储空间带来的后果除了使得 SQLite 系统变慢之外，在缩减空间时实际上还会产生最多两倍于已用空间大小的临时存储空间需求。

5. 问：SQLite那么棒，我能不能偷偷把它用到我的商业项目中，额。。。我指的是不掏任何费用的情况下？

5. 答：虽然问得略显猥琐，但答案是肯定的。

SQLite是彻底的开源，你不需要为他付出任何费用，它的作者在源码的开头处仅仅写下对使用它的人的三个“祝福”：

❤愿你用来行善除恶

❤愿你原谅自己并宽恕他人

❤愿你宽心与人分享，所取不多于所施。

可能你会觉得作者矫情，但请注意，SQLite 不是普通的软件，世界上所有的安卓手机和苹果手机全部都使用 SQLite，这还仅仅是手机而已，还有海量电子设备都用到了这款快准狠的数据库！想想吧！作者为了开源事业，放弃了多么大的现实利益！敬佩！

6. 问：怎么在字符串中包含一个单引号？

6. 答：SQL 标准使用单引号来引用字符串，因此在字符串中包含单引号是需要特殊的写法：写两遍。请看：

INSERT INTO t values('苹果''香蕉');

注意到插入的字符串中红色的一堆单引号，它表示一个单引号，因此他相当于插入了这样的字符串：

苹果'香蕉