Delphi FireDAC SQLite “database is locked“ 解决办法

SQLite 多线程操作优化与事务处理策略

khzide

已于 2022-02-18 10:01:09 修改

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏： delphi 文章标签： sqlite 数据库 database

于 2022-02-17 14:57:59 首次发布

本文链接：https://blog.csdn.net/khzide/article/details/122979175

delphi 专栏收录该内容

44 篇文章

订阅专栏

本文探讨了在多线程环境中如何有效地处理SQLite数据库，重点介绍了事务处理、锁定机制、缓存策略和日志模式。推荐的设置组合包括：JournalMode WAL、Synchronous off/Normal、LockedMode normal、SharedCache true、TxOptions.isolation xiSnapshot、UpdateOptions.lockWait true及BusyTimeout 10秒，以避免databaseislocked错误。同时建议事务时间尽量短，确保并发性能。

为了解决多线程，多进程操作sqlite。进行了一些实测。以下为个人实测得出结论，非官方文档：

名词解释：

写状态：处理事务中，并执行了修改数据操作，但未提交事务。

写锁：处理事务中，写数据。

读锁：处理事务中，读数据。

相关参数解读：

UpdateOptions.lockwait : 其它进程处于事务或写状态时，会阻止当前进程进入事务或写数据，这时是否等待，等待时间为BusyTimeout,如果超时，会报database is locked错误。

BusyTimeout: 超时时间，默认为10000.(10秒),如果UpdateOptions.lockwait = false,则此参数无效。

TxOptions.isolation : 事务级别

xiSerializible 或xiSnapshot 同一时刻只能有一个进程处于事务中。当一个进程进入事务时，其它进程进入事务时会等待。

设置成其它参数时，可以两个进程同时进入事务状态，但如果一个进程于事务中执行了写数据。不管这个事务是否已commit. 其它进程中的事务进行写操作都会报database is locked.

SharedCache:共享缓存，建议设置上。实测进程间也是可以一个事务提交，另一个进程立刻就可以读出。

LockedMode: 这个简单，必须设置成normal,不然其它连接无法访问数据库。

Synchronous: 这个影响数据是否会实质写盘，实测设置为off没有任务影响。进程间数据依然是同步。这个会影响写入速度。

JournalMode:WAL 这个推荐就设置成WAL,具体不解释。几种方式这种还是最好的。

delphi中如果一条写语句没有包装在事务中，则会自动发起一个事务。

sqlite锁定方式为文件锁，即当一个进程处于写锁中，即写入操作后，尚未提交。其它进程不可以执行写入操作。与具体写入哪个表无关。

总结在避免出现database is locked. 以下设置组合最好：

JounalMode:WAL

Synchronous:off|Normal

LockedMode:normal

SharedCache:true

TxOptions.isolation:xiSnapshot.

UpdateOptions.lockWait=true

Busytimeout:10秒。

同时操作时注意事务时间尽可能短，不要超过10秒。

基本上以上设置后，不会出现database is locked错误了。

By default, all SQLite driver settings are set for the high-performance single connection access to a database in a stable environment. The PRAGMA command allows you to configure SQLite. Many FireDAC SQLite driver parameters correspond to the pragmas. Additionally, SQLiteAdvanced allows you to specify multiple pragmas separated by ';' as a single connection parameter.

The additional SQLite use cases are:

No	Application specifics	Description
1	Reading large DB.	Set CacheSize to a higher number of pages, which will be used to cache the DB data. The total cache size will be CacheSize * <db page size>.
2	Exclusive updating of DB.	Consider to set JournalMode to WAL (more).
3	Long updating transactions.	Set CacheSize to a higher number of pages, that will allow you to run transactions with many updates without overloading the memory cache with dirty pages.
4	A few concurrent updating processes.	Set LockingMode to Normal to enable shared DB access. Set Synchronous to Normal or Full to make committed data visible to others. Set UpdateOptions.LockWait to True to enable waiting for locks. Increase BusyTimeout to raise a lock waiting time. Consider to set JournalMode to WAL.
5	A few concurrent updating threads.	See (4). Also set SharedCache to False to minimize locking conflicts.
6	A few concurrent updating transactions.	See (4) or (5). Also set TxOptions.Isolation to xiSnapshot or xiSerializible to avoid possible transaction deadlocks.
7	High safety.	Set Synchronous to Full to protect the DB from the committed data losses. Also see (3). Consider encrypting the database to provide integrity.
8	High confidentiality.	Encrypt database to provide confidentiality and integrity.
9	Development time.	Set LockingMode to Normal to enable simultaneous use of the SQLite DB in the IDE and a debugged program.

事务可以是延迟的、立即的或排他的。默认事务行为是 DEFERRED。

DEFERRED 意味着事务直到第一次访问数据库才真正开始。在内部，BEGIN DEFERRED 语句仅在数据库连接上设置一个标志，该标志关闭通常在最后一条语句完成时发生的自动提交。这会导致自动启动的事务一直持续到显式 COMMIT 或 ROLLBACK 或直到由错误或 ON CONFLICT ROLLBACK 子句引发回滚。如果 BEGIN DEFERRED 之后的第一条语句是 SELECT，则启动读取事务。如果可能，后续的写入语句会将事务升级为写入事务，或者返回 SQLITE_BUSY。如果 BEGIN DEFERRED 之后的第一条语句是写入语句，则启动写入事务。

IMMEDIATE 导致数据库连接立即开始新的写入，而无需等待写入语句。如果另一个写入事务已在另一个数据库连接上处于活动状态，则 BEGIN IMMEDIATE 可能会因SQLITE_BUSY而失败。

EXCLUSIVE 与 IMMEDIATE 相似之处在于立即启动写入事务。EXCLUSIVE 和 IMMEDIATE 在WAL 模式下是相同的，但在其他日志模式下，EXCLUSIVE 会阻止其他数据库连接在事务进行时读取数据库。

插个题外话：在firedac连接mysql时，可以直接设置TXOptions.isolation = xiReadCommitted 就可以了。这个是记录级锁。一个事务修改一个条记录时，不影响其它进程的对其它表或其它记录的的更新。

xiReadCommitted：脏读。处于事务中时，读不到其它表的更新，但写数据时是受其它事务影响的，按数据库当前最新数据进行的。