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