数据库锁定在WAL模式，只有读者 [英] Database locked in WAL mode with only readers
问题描述
使用 Write-Ahead Logging 模式中的System.Data.Sqlite 1.0.86.0（包括SQLite 3.7.17）我在同时读取时遇到数据库锁，如果我正确理解WAL，则不应该是这种情况。我不是写或提交任何东西， ReadCommitted 事务隔离模式正确使用，以避免序列化读取。
Using System.Data.Sqlite 1.0.86.0 (including SQLite 3.7.17) in Write-Ahead Logging mode, I'm experiencing database locks while reading concurrently, which shouldn't be the case if I understand WAL correctly. I'm not writing or committing anything and the ReadCommitted transaction isolation mode is correctly used to avoid serializing reads.
准备时，SQLite DB（带WAL）被锁定选择 statmement - 为什么？是一个类似的问题。唯一的答案是在每个 sqlite3_step 之后调用 sqlite3_reset ，这由System.Data.Sqlite正确完成
SQLite DB (with WAL) locked when preparing a "select" statmement - why? is a similar issue. The only answer talks about calling sqlite3_reset after each sqlite3_step, which is done correctly by System.Data.Sqlite as far as I saw in the source code.
完整再现：
internal static class Program {
private const string DbFileName = "test.sqlite";
private static readonly string _connectionString = BuildConnectionString(DbFileName);
internal static void Main() {
File.Delete(DbFileName);
ExecuteSql("CREATE TABLE Test (Id INT NOT NULL, Name TEXT);", true);
for (int i = 0; i < 10; i++)
Task.Run(() => ExecuteSql("SELECT Id, Name FROM Test;", false));
Console.ReadKey();
}
private static string BuildConnectionString(string fileName) {
var builder = new SQLiteConnectionStringBuilder {
DataSource = fileName,
DateTimeFormat = SQLiteDateFormats.ISO8601,
DefaultIsolationLevel = IsolationLevel.ReadCommitted,
ForeignKeys = true,
JournalMode = SQLiteJournalModeEnum.Wal,
SyncMode = SynchronizationModes.Full
};
return builder.ToString();
}
private static void ExecuteSql(string sql, bool commit) {
Stopwatch stopwatch = Stopwatch.StartNew();
using (var connection = new SQLiteConnection(_connectionString)) {
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)) {
using (SQLiteCommand command = connection.CreateCommand()) {
command.CommandText = sql;
command.ExecuteNonQuery();
}
if (commit)
transaction.Commit();
}
}
stopwatch.Stop();
Console.WriteLine("{0}: {1}", stopwatch.Elapsed, sql);
}
}
输出：
00:00:00.1927492: CREATE TABLE Test (Id INT NOT NULL, Name TEXT);
00:00:00.0054247: SELECT Id, Name FROM Test;
00:00:00.0055334: SELECT Id, Name FROM Test;
00:00:00.0056022: SELECT Id, Name FROM Test;
00:00:00.0054860: SELECT Id, Name FROM Test;
00:00:00.0053894: SELECT Id, Name FROM Test;
00:00:00.0056843: SELECT Id, Name FROM Test;
00:00:00.0006604: SELECT Id, Name FROM Test;
00:00:00.0006758: SELECT Id, Name FROM Test;
00:00:00.0097950: SELECT Id, Name FROM Test;
00:00:00.0980008: SELECT Id, Name FROM Test;
你可以看到最后一个是一个数量级的慢。如果在调试模式下执行，根据运行，输出窗口中会记录以下内容一次或多次：
You can see that the last one is an order of magnitude slower. If executed in debug mode, the following is logged in the output window one or more times depending on the run:
SQLite错误：数据库被锁定
SQLite error (261): database is locked
你有什么想法如何避免这种锁定？当然，在这个例子中，WAL可以简单地关闭，但在一个真正的项目中我不能：我需要潜在的写入成功，即使长的读事务正在进行。
Do you have any idea how to avoid this locking? Of course in this sample WAL can simply be turned off, but in a real project I can't: I need potential writes to succeed immediately even if a long read transaction is going on.
推荐答案
调查后，它不是读取锁定数据库，而只是打开一个连接。根据我的理解，在再次阅读WAL文档后，即使读者必须具有对WAL文件的写访问权限。打开连接的简单事实比非WAL模式具有大得多的成本。这个操作显然包括获取WAL文件上的排他锁，即使它的时间很短。
After investigation, it's not reading that locks the database, but simply opening a connection. As I understand it after reading the WAL documentation again, even readers must have write access to the WAL file. The simple fact of opening a connection has a much greater cost than in non-WAL mode. This operation apparently includes acquiring an exclusive lock on the WAL file, even if it's for a very short period.
一个简单的解决方案是启用池化（ Pooling = True ）。它在样本中没有任何效果，因为所有连接都同时打开，但在现实应用中，不再有任何锁，因为重新使用了现有的连接。大多数简单的查询从5ms到不到1ms（在SSD上），并且数据库已锁定消息已全部消失。
A simple solution is to enable pooling (Pooling=True in the connection string). It doesn't have any effect in the sample since all connections are opened at the same time, but in a real world application there isn't any lock anymore since existing connections are reused. Most simple queries went from 5ms to less than 1ms (on a SSD) and the "database is locked" messages are entirely gone.
这篇关于数据库锁定在WAL模式，只有读者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
