Database locked in WAL mode with only readers

Question

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.

Answer 1

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.

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.