使用的NuGet包:System.Data.SQLite.Core,1.0.98.1
问题:我的程序中有时会启用SQLite连接池,并在某些情况下只读取数据库。通常情况下一切都正常,但如果有大量混合只读/读写请求,程序将出现以下异常:
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()
如果我禁用连接池,程序就能正常工作。我猜测是使用只读连接池来进行读写操作。我有什么遗漏吗?这是期望的行为吗?
最小复现代码(它在插入或删除时失败)。如果我引入延迟,例如
Thread.Sleep(10000)
,它就能正常工作。如果我移除循环,它也能正常工作。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();
}
}