SQLite 同时读写 [英] SQLite simultaneous reading and writing
问题描述
我阅读了很多主题，但找不到问题的答案:是否可以同时读写?
I've read a lot of topics, but can't figure out answer for question: is it possible to read and write simultaneous?
我有后台线程来更新一些数据，而 UI 需要存储在 DB 中的小块数据.所以在 UI 线程中执行 SELECT 操作.但是在更新过程中它会阻塞.结果，用户界面冻结了几秒钟.
I have background thread that updates some data and UI needs small piece of data stored in DB. So in UI thread SELECT operation is performed. But it blocks when update is in progress. As result, UI freezes for several seconds.
有没有人在写的时候从数据库读取成功?
Does anyone has success in reading from DB when writing?
可以在 iPhone 上读取和写入数据库.造成这种差异的原因是原生sqlite函数的wrapper是同步实现的吗?
Its possible to read and write to DB on iPhone. Does the reason of such difference is in synchronious implementation of wrapper on native sqlite functions?
推荐答案
在 Android 3.0 及更高版本上 SQLiteDatabases 支持 WAL 模式(预写日志):
On Android 3.0 and higher SQLiteDatabases support WAL mode (write-ahead logging):
如果未启用预写日志记录(默认)，则不会可以同时在数据库上进行读取和写入时间.在修改数据库之前，作者隐式地获取了一个数据库上的排他锁阻止读者访问数据库直到写入完成.
When write-ahead logging is not enabled (the default), it is not possible for reads and writes to occur on the database at the same time. Before modifying the database, the writer implicitly acquires an exclusive lock on the database which prevents readers from accessing the database until the write is completed.
相反，当启用 write-ahead logging 时，写操作发生在允许读取继续的单独日志文件中同时.在写入过程中，其他线程上的读者将感知数据库的状态，就像写入之前一样开始了.写入完成后，其他线程上的读者将感知数据库的新状态.
In contrast, when write-ahead logging is enabled, write operations occur in a separate log file which allows reads to proceed concurrently. While a write is in progress, readers on other threads will perceive the state of the database as it was before the write began. When the write completes, readers on other threads will then perceive the new state of the database.
要在 WAL 模式下启动事务，请使用 beginTransactionNonExclusive() 而不是 beginTransaction().beginTransaction() 在 EXCLUSIVE 模式下启动一个事务， beginTransactionNonExclusive() 在 IMMEDIATE 模式下启动一个事务
To start a transaction in WAL mode use beginTransactionNonExclusive() instead of beginTransaction(). While beginTransaction() starts a transaction in EXCLUSIVE mode, beginTransactionNonExclusive() starts one in IMMEDIATE mode
- EXCLUSIVE 模式使用排他锁 (http://www.sqlite.org/lockingv3.html#excl_lock) 意味着除了 read_uncommitted 连接之外没有其他数据库连接能够读取数据库，并且在事务完成之前没有其他连接无一例外地能够写入数据库
- 即时模式使用保留锁(http://www.sqlite.org/lockingv3.html#reserved_lock) 意味着没有其他数据库连接能够写入数据库或执行 BEGIN IMMEDIATE 或 BEGIN EXCLUSIVE，但是其他进程可以继续从数据库中读取.
- EXCLUSIVE mode uses exclusive locks (http://www.sqlite.org/lockingv3.html#excl_lock) meaning no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete
- IMMEDIATE mode uses reserved locks (http://www.sqlite.org/lockingv3.html#reserved_lock) meaning no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE, other processes can continue to read from the database, however.
简单地说:为立即模式调用 beginTransactionNonExclusive()，我们可以在另一个线程正在写入时读取(写入事务开始之前的状态，因为我们不会使用 read_uncommitted 连接 -> http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Dirty_reads).
In simpler words: call beginTransactionNonExclusive() for IMMEDIATE mode and we can read while another thread is writing (the state before the write transaction started because we won't use read_uncommitted connections -> http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Dirty_reads).
这篇关于SQLite 同时读写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
