Android：SQLite (ORMLite) 事务隔离级别
我在我的 Android 项目中使用 ORMLite。我知道 Sqlite 负责文件级锁定。多个线程可以读取，一个可以写入。锁可以防止多次写入。 有人可以解释一下，如果一个线程正在更新某些记录而另一个线程正在尝试读取该记录，会发生什么情况？线程（正在尝试读取）会获取过时的数据吗？或者它会被锁定直到第一个线程完成其写操作吗？ 据我所知，有 4 个事务隔离级别：可串行化、可重复读、读已提交、读未提交。有什么方法可以在 SQLite 或 ORMLite 中更改它吗？
I'm using ORMLite in my Android project. I know that Sqlite takes care of the file level locking. Many threads can read, one can write. Locks prevent more than one writing.
Could anybody please explain what will happen in case one thread is updating some record and another thread is trying to read this record? Will the thread (that is trying to read) get out-of-date data? Or will it be locked until the first thread completes its write-operation?
As I know there are 4 transaction isolation levels: Serializable, Repeatable read, Read committed, Read uncommitted. And is there any way to change it in SQLite or ORMLite?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQLite 有 5 种不同的锁定级别 - http://www.sqlite.org/lockingv3.html ：
解锁、共享、保留、待定、独占。
这个问题重要的锁是共享锁：
是表级的（因此，当对数据库中的单行执行某些操作时，整个表都会被锁定）。
因此，当您选择数据时，不允许其他进程更改数据。
读取数据的锁定步骤为：UNLOCKED→PENDING→SHARED→UNLOCKED（可以在事务中运行选择）。因此，您正在选择某些内容而有人会更改数据的情况不会发生。
您的问题是，如果您正在更新数据库并在同一个表上进行选择，会发生什么。在自动提交模式下，写入/更新的锁定机制是：
已解锁→待处理→共享→保留→待处理→独占→已解锁。
当处于排它锁状态时，没有新的读取器（连接）可以连接到数据库。一次只能存在一个 EXCLUSIVE 锁。然后，SQLite 将等待，直到释放所有其他读取连接的 PENDING 锁，并阻止任何新的锁。这时，它就会开始写入数据。
所以，我的答案是 - 只要更新过程没有完成，你的其他进程当然就会获取旧数据。请务必在事务中运行更新，这样就不会出现数据不一致的情况。 SQLite 符合 ACID，因此不会发生部分更新和数据不一致的情况。
关于这方面的一本很棒的书是“The Definitive Guide to SQLite”，特别是事务章节。
SQLite has 5 different lock levels - http://www.sqlite.org/lockingv3.html :
unlocked, shared, reserved, pending, exclusive.
The important lock for this question is the Shared lock:
The locks are table-level (so while doing something with a single row in the DB - the whole table is locked).
So while you are selecting data, no other process is allowed to alter the data.
The lock steps for reading data are: UNLOCKED→PENDING →SHARED →UNLOCKED (you can run selects in a transaction). So a situation where you are selecting something and someone will alter the data can not happen.
Your question is what happens if you are updating the database and do a select on the same table. In autocommit mode the lock mechanism for write/update is:
UNLOCKED →PENDING →SHARED →RESERVED →PENDING →EXCLUSIVE →UNLOCKED.
While in the Exclusive lock, no new readers (connections) can connect to the database. Only one EXCLUSIVE lock may exists at a single time. SQLite will then wait until all other PENDING locks from reading connections are released and will prevent any new. At this time, it will begin writing the data.
So, my answer would be - as long as the update process is not finished, your other process will get old data of course. Be sure to run the update in an transaction, so that inconsistency in data will not happen. SQLite is ACID compliant, so a situation where you get partially updated and incosisten data should not happen.
A great book on this is "The Definitive Guide to SQLite", especially the Transactions chapter.
SQLite 支持几种不同的隔离级别，可以在编译时和运行时选择。
我假设Android的SQLite位于默认情况下序列化模式。在多线程访问方面，它在文件系统级别进行读/写锁定，以允许多个读取器，但一次只能允许一个写入器：
但是，ORMLite建议强烈并尝试努力维护与数据库的单个连接，因此锁定问题可能不相关。
更具体地说，如果一个线程正在更新记录而另一个线程正在读取，那么这就是一种竞争条件。读取线程将在更新之前或更新完成之后获取记录。但是，读者将不会获得部分更新的数据。但我怀疑你知道这一点。
就 SQLite 支持的隔离级别而言，如上所述，默认值是序列化，但看起来它至少在某种程度上支持未提交读。
您可以使用ORMLite 的 Dao.executeRaw() 方法来启用它：
但是，我对此没有任何经验，也不确定它是否会给您提供一致的行视图如果在同一查询中多次访问该数据，则会更新该数据。它可能与写入事务期间的一致视图有关。
阅读这个类似的问题后，我不确定是否有可能如果在同一个查询中多次访问表中的行，则保证该行的视图相同。
SQLite supports a couple different isolation levels that are selectable at compile time and runtime.
I assume that Android's SQLite is in Serialized mode by default. In terms of multi-threaded access, it does read/write locking at the file system level to allow multiple readers but only one writer at a time:
However, ORMLite recommends strongly and tried hard to maintain a single connection to the database so the locking issues may not be relevant.
More specifically, if one thread is updating the record and another thread reading then it is a race condition. Either the reading thread will get the record prior to update or after the update has completed. However, the reader will not get partially updated data. But I suspect you knew that.
In terms of isolation levels supported by SQLite, the default is Serialized as mentioned but it looks like it supports read-uncommitted at least to some degree.
You can use ORMLite's Dao.executeRaw() method to enable it:
However, I don't have any experience with this nor am I sure if it will give you consistent view of the row that was updated if it is accessed multiple times in the same query. It may have more to do with consistent views during write transactions.
After reading this similar question, I'm not sure it is possible to be guaranteed the same view of a row in a table if it is accessed multiple times the in the same query.