mardi 20 octobre 2015

Pooled read-only SQLite connection is used for read-write access

Used nuget package: System.Data.SQLite.Core, 1.0.98.1

Problem: In my program I use SQLite with enabled pooling and read-only access in some cases. Normally it works fine, but if there is a lot of mixed read-only/read-write intensive requests to database, then program fails with the following exception:

Unhandled Exception: System.Data.SQLite.SQLiteException: attempt to write a readonly database
attempt to write a readonly database
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()

If I disable pooling, then the program works fine. I assume that somehow pooled read-only connection is used for read-write connection. Do I miss something - i.e. is it expected behavior or not?

Minimum code to reproduce (it fails at INSERT or DELETE). If I introduce delay, for example Thread.Sleep(10000), it works fine. If I remove loop, it also works fine.

const string DbFilePath = "test.sqlite";
string readOnlyConnectionString = new SQLiteConnectionStringBuilder
    {
        DataSource = DbFilePath,
        Pooling = true,
        ReadOnly = true
    }.ConnectionString; // data source=test.sqlite;pooling=True;read only=True
string readWriteConnectionString = new SQLiteConnectionStringBuilder
    {
        DataSource = DbFilePath,
        Pooling = true,
        ReadOnly = false
    }.ConnectionString; // data source=test.sqlite;pooling=True;read only=False
File.Delete(DbFilePath);
using (SQLiteConnection conn = new SQLiteConnection(readWriteConnectionString))
using (SQLiteCommand cmd = new SQLiteCommand("CREATE TABLE items(id INTEGER NOT NULL PRIMARY KEY)", conn))
{
    conn.Open();
    cmd.ExecuteNonQuery();
}
while (true) // <= if we comment the loop, the program executes without error
{
    using (SQLiteConnection conn = new SQLiteConnection(readWriteConnectionString))
    using (SQLiteCommand cmd = new SQLiteCommand("INSERT INTO items(id) VALUES (1)", conn))
    {
        conn.Open();
        cmd.ExecuteNonQuery();
    }
    using (SQLiteConnection conn = new SQLiteConnection(readOnlyConnectionString))
    using (SQLiteCommand cmd = new SQLiteCommand("SELECT COUNT(*) FROM items", conn))
    {
        conn.Open();
        cmd.ExecuteScalar();
    }
    using (SQLiteConnection conn = new SQLiteConnection(readWriteConnectionString))
    using (SQLiteCommand cmd = new SQLiteCommand("DELETE FROM items", conn))
    {
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

Aucun commentaire:

Enregistrer un commentaire