Database locked in WAL mode with readers only

Question

Using System.Data.Sqlite 1.0.86.0 (including SQLite 3.7.17) in "Write in Forward mode in mode , I experience a database lock while reading at the same time, which should not be if I understand WAL correctly. I don’t I write and do nothing, and ReadCommitted transaction isolation mode is used correctly to avoid serialization of reading.

SQLite DB (with WAL) is locked while preparing a "select" statmement - why? - a similar problem. The only answer is talking about calling sqlite3_reset after each sqlite3_step , which correctly executes System.Data.Sqlite, as far as I saw in the source code.

Full play:

 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 latter is an order of magnitude slower. If it is executed in debug mode, it is registered one or several times in the output window depending on the launch:

SQLite error (261): database is locked

Do you know how to avoid this lock? Of course, in this example, WAL can simply be disabled, but in a real project I can’t: I need a potential write to be successful right away, even if a long read transaction continues.

Answer 1

After research, he does not read that he is blocking the database, but simply opens the connection. As I understand it, after reading the WAL documentation again, even readers should have write access to the WAL file. The simple fact of opening a connection is much more expensive than in a mode other than WAL. This operation, apparently, involves obtaining an exclusive lock in the WAL file, even if it is for a very short period.

A simple solution is to include the pool ( Pooling=True in the connection string). This has no effect in fetching since all connections open simultaneously, but in the real world the application is no longer blocked because existing connections are reused. Most simple queries went from 5 ms to less than 1 ms (on an SSD), and the messages "database locked" completely disappeared.