SQLite3 和多个进程
当多个进程访问一个 SQLite 数据库文件时，如何确保正确性?
How should one ensure correctness when multiple processes access one single SQLite database file?
推荐答案
首先，避免并发访问 sqlite 数据库文件.并发性是 sqlite 的弱点之一，如果您有一个高度并发的应用程序，请考虑使用其他数据库引擎.
First, avoid concurrent access to sqlite database files. Concurrency is one of sqlite's weak points and if you have a highly concurrent application, consider using another database engine.
如果您无法避免并发或丢弃 sqlite，请将您的 write 事务包装在 BEGIN IMMEDIATE; ... END; 中.sqlite 中的默认事务模式是 DEFERRED 这意味着只有在第一次实际写入尝试时才获取锁.使用 IMMEDIATE 事务，会立即获取锁，或者立即获取 SQLITE_BUSY.当有人持有对数据库的锁定时，其他锁定尝试将导致 SQLITE_BUSY.
If you cannot avoid concurrency or drop sqlite, wrap your write transactions in BEGIN IMMEDIATE; ... END;. The default transaction mode in sqlite is DEFERRED which means that a lock is acquired only on first actual write attempt. With IMMEDIATE transactions, the lock is acquired immediately, or you get SQLITE_BUSY immediately. When someone holds a lock to the database, other locking attempts will result in SQLITE_BUSY.
处理 SQLITE_BUSY 是您必须自己决定的事情.对于许多应用程序，等待一两秒钟然后重试工作正常，在 n 次尝试失败后放弃.有 sqlite3 API 帮助程序可以使这变得简单，例如sqlite3_busy_handler() 和 sqlite3_busy_timeout() 但也可以手动完成.
Dealing with SQLITE_BUSY is something you have to decide for yourself. For many applications, waiting for a second or two and then retrying works quite all right, giving up after n failed attempts. There are sqlite3 API helpers that make this easy, e.g. sqlite3_busy_handler() and sqlite3_busy_timeout() but it can be done manually as well.
您还可以使用操作系统级别的同步来获取数据库的互斥锁，或者使用操作系统级别的线程间/进程间消息传递在一个线程完成访问数据库时发出信号.
You could also use OS level synchronization to acquire a mutex lock to the database, or use OS level inter-thread/inter-process messaging to signal when one thread is done accessing the database.
相关文章