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。

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.

出现在单独的日志文件中，该日志文件允许并发读取
。当写入正在进行时，其他线程
上的读者将感知数据库的状态，因为它在写入
开始之前。当写入完成时，其他线程上的读者将
感知数据库的新状态。

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.

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#enableWriteAheadLogging （）

要在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连接将能够读取数据库，没有其他连接，无异常将能够写入数据库，直到事务完成


• IMMEDIATE模式使用保留锁a href =http://www.sqlite.org/lockingv3.html#reserved_lock =noreferrer> 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.

用更简单的话：对于IMMEDIATE模式调用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屋！