如何防止 SQLite 数据库锁定? [英] How do I prevent SQLite database locks?

问题描述

从 sqlite FAQ 我知道:

From sqlite FAQ I've known that:

多个进程可以同时打开同一个数据库.多个进程可以同时执行 SELECT.但只有一个进程可以随时更改数据库然而，时间.

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

所以，据我所知，我可以:1)从多个线程读取数据库(SELECT)2) 多线程读数据库(SELECT)，单线程写(CREATE, INSERT, DELETE)

So, as far as I understand I can: 1) Read db from multiple threads (SELECT) 2) Read db from multiple threads (SELECT) and write from single thread (CREATE, INSERT, DELETE)

但是，我读到了 Write-Ahead Logging，它提供了更多的并发性，读者不会阻止作者，作者不会阻止读者.读写可以同时进行.

But, I read about Write-Ahead Logging that provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

最后，当我找到它时，我完全糊涂了:

Finally, I've got completely muddled when I found it, when specified:

以下是出现 SQLITE_LOCKED 错误的其他原因:

Here are other reasons for getting an SQLITE_LOCKED error:

• 在 SELECT 语句执行时尝试创建或删除表或索引还在等待中.
• 当 SELECT 在同一个表上处于活动状态时尝试写入该表.
• 尝试在同一张表上同时执行两个 SELECT多线程应用程序，如果 sqlite 未设置为这样做.
• fcntl(3,F_SETLK 调用 DB 文件失败.这可能是由 NFS 锁定引起的问题，例如.此问题的一种解决方案是将数据库 mv，并将其复制回来，使其具有新的 Inode 值

那么，我想为自己澄清一下，什么时候应该避免使用锁?我可以从两个不同的线程同时读取和写入吗?谢谢.

So, I would like to clarify for myself, when I should to avoid the locks? Can I read and write at the same time from two different threads? Thanks.

推荐答案

对于那些使用 Android API 的人:

For those who are working with Android API:

SQLite 中的锁定是在保证锁定的文件级别完成的来自不同线程和连接的更改.因此多个线程可以读取数据库，但只能写入.

Locking in SQLite is done on the file level which guarantees locking of changes from different threads and connections. Thus multiple threads can read the database however one can only write to it.

有关锁定 SQLite 的更多信息可以在 SQLite 文档中阅读，但我们最感兴趣的是由 OS Android 提供的 API.

More on locking in SQLite can be read at SQLite documentation but we are most interested in the API provided by OS Android.

可以从单个和多个数据库连接使用两个并发线程进行写入. 由于只有一个线程可以写入数据库，因此有两种变体:

Writing with two concurrent threads can be made both from a single and from multiple database connections. Since only one thread can write to the database then there are two variants:

1. 如果你从一个连接的两个线程写入，那么一个线程将等待另一个完成写作.
2. 如果你从不同连接的两个线程写入，那么会出现错误将 - 您的所有数据都不会写入数据库并且应用程序将被中断SQLiteDatabaseLockedException.很明显，应用程序应始终只有一份SQLiteOpenHelper(只是一个开放的连接)否则SQLiteDatabaseLockedException 随时可能发生.

单个 SQLiteOpenHelper 的不同连接

每个人都知道 SQLiteOpenHelper 有 2 个方法可以访问数据库getReadableDatabase() 和 getWritableDatabase()，分别用于读取和写入数据.然而，在大多数情况下，存在一种真正的联系.而且它是同一个对象:

Everyone is aware that SQLiteOpenHelper has 2 methods providing access to the database getReadableDatabase() and getWritableDatabase(), to read and write data respectively. However in most cases there is one real connection. Moreover it is one and the same object:

SQLiteOpenHelper.getReadableDatabase()==SQLiteOpenHelper.getWritableDatabase()

SQLiteOpenHelper.getReadableDatabase()==SQLiteOpenHelper.getWritableDatabase()

这意味着读取数据的方法的使用没有区别.然而，还有另一个更重要的未公开问题——在类 SQLiteDatabase 内部有自己的锁——变量 mLock.在对象 SQLiteDatabase 级别锁定写入，并且由于只有一个 SQLiteDatabase 副本用于读取和写入，因此数据读取也会被阻止.在事务中写入大量数据时，它更显眼.

It means that there is no difference in use of the methods the data is read from. However there is another undocumented issue which is more important – inside of the class SQLiteDatabase there are own locks – the variable mLock. Locks for writing at the level of the object SQLiteDatabase and since there is only one copy of SQLiteDatabase for read and write then data read is also blocked. It is more prominently visible when writing a large volume of data in a transaction.

让我们考虑这样一个应用程序的示例，它应该在首次启动时在后台下载大量数据(大约 7000 行包含 BLOB)并保存它到数据库.如果数据保存在事务中，那么保存大约需要.45 秒，但用户无法使用该应用程序，因为任何阅读查询都被阻止.如果数据被分成小部分保存，那么更新过程会拖延相当长的时间(10-15 分钟)，但用户可以不受任何限制和不便地使用该应用程序.双刃剑"——快速或方便.

Let’s consider an example of such an application that should download a large volume of data (approx. 7000 lines containing BLOB) in the background on first launch and save it to the database. If the data is saved inside the transaction then saving takes approx. 45 seconds but the user can not use the application since any of the reading queries are blocked. If the data is saved in small portions then the update process is dragging out for a rather lengthy period of time (10-15 minutes) but the user can use the application without any restrictions and inconvenience. "The double edge sword" – either fast or convenient.

Google 已经修复了与 SQLiteDatabase 功能相关的部分问题，因为添加了以下方法:

Google has already fixed a part of issues related to SQLiteDatabase functionality as the following methods have been added:

beginTransactionNonExclusive() – 在即时模式"下创建交易.

beginTransactionNonExclusive() – creates a transaction in the "IMMEDIATE mode".

yieldIfContendedSafely() – 临时占用事务以允许其他线程完成任务.

yieldIfContendedSafely() – temporary seizes the transaction in order to allow completion of tasks by other threads.

isDatabaseIntegrityOk() – 检查数据库完整性

isDatabaseIntegrityOk() – checks for database integrity

请阅读文档中的更多详细信息.

Please read in more details in the documentation.

但是对于旧版本的 Android，此功能也是必需的.

However for the older versions of Android this functionality is required as well.

解决方案

应关闭首次锁定并允许在任何情况下读取数据.

First locking should be turned off and allow reading the data in any situation.

SQLiteDatabase.setLockingEnabled(false);

SQLiteDatabase.setLockingEnabled(false);

使用内部查询锁定取消——在java类的逻辑级别上(与SQLite方面的锁定无关)

cancels using internal query locking – on the logic level of the java class (not related to locking in terms of SQLite)

SQLiteDatabase.execSQL(PRAGMA read_uncommitted = true;");

SQLiteDatabase.execSQL("PRAGMA read_uncommitted = true;");

允许从缓存中读取数据.事实上，改变了隔离级别.应该为每个连接重新设置此参数.如果有多个连接，那么它只会影响调用此命令的连接.

Allows reading data from cache. In fact, changes the level of isolation. This parameter should be set for each connection anew. If there are a number of connections then it influences only the connection that calls for this command.

SQLiteDatabase.execSQL(PRAGMA synchronous=OFF");

SQLiteDatabase.execSQL("PRAGMA synchronous=OFF");

改变写入数据库的方式——没有同步".激活此选项时，如果系统意外故障或电源关闭，数据库可能会损坏.但是，根据 SQLite 文档，如果未激活该选项，某些操作的执行速度会快 50 倍.

Change the writing method to the database – without "synchronization". When activating this option the database can be damaged if the system unexpectedly fails or power supply is off. However according to the SQLite documentation some operations are executed 50 times faster if the option is not activated.

不幸的是，并非所有 PRAGMA 都在 Android 中受支持，例如PRAGMA locked_mode = NORMAL"和PRAGMA journal_mode = OFF"和其他一些不受支持.在尝试调用 PRAGMA 数据时，应用程序失败.

Unfortunately not all of PRAGMA is supported in Android e.g. "PRAGMA locking_mode = NORMAL" and "PRAGMA journal_mode = OFF" and some others are not supported. At the attempt to call PRAGMA data the application fails.

在方法 setLockingEnabled 的文档中说，建议仅在您确定数据库的所有工作都是从单个线程完成的情况下使用此方法.我们应该保证一次只持有一笔交易.此外，应使用即时事务而不是默认事务(独占事务).在旧版本的 Android(低于 API 11)中，没有选项可以通过 java 包装器创建即时事务，但 SQLite 支持此功能.要在立即模式下初始化事务，应直接对数据库执行以下 SQLite 查询，例如通过方法 execSQL:

In the documentation for the method setLockingEnabled it is said that this method is recommended for using only in the case if you are sure that all the work with the database is done from a single thread. We should guarantee than at a time only one transaction is held. Also instead of the default transactions (exclusive transaction) the immediate transaction should be used. In the older versions of Android (below API 11) there is no option to create the immediate transaction thru the java wrapper however SQLite supports this functionality. To initialize a transaction in the immediate mode the following SQLite query should be executed directly to the database, – for example thru the method execSQL:

SQLiteDatabase.execSQL(立即开始事务");

SQLiteDatabase.execSQL("begin immediate transaction");

由于事务是由直接查询初始化的，所以它应该以同样的方式完成:

Since the transaction is initialized by the direct query then it should be finished the same way:

SQLiteDatabase.execSQL(提交事务");

SQLiteDatabase.execSQL("commit transaction");

然后 TransactionManager 是唯一需要实现的东西，它将启动和完成所需类型的事务.TransactionManager 的目的是保证所有的更改查询(插入、更新、删除、DDL 查询)都来自同一个线程.

Then TransactionManager is the only thing left to be implemented which will initiate and finish transactions of the required type. The purpose of TransactionManager – is to guarantee that all of the queries for changes (insert, update, delete, DDL queries) originate from the same thread.

希望这对未来的访问者有所帮助！！！

Hope this helps the future visitors!!!

这篇关于如何防止 SQLite 数据库锁定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！