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 (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.
Full reproduction:
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);
}
}
Output:
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 error (261): database is locked
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.