System.Data.SQLite

Hi,

I use SQLite in a project, it works fine. But when i perform fast operations in a stress test (20.000 operation (insert, delete, select.. mixed) sequentially) i get this exception:

System.Data.SQLite.SQLiteException: Attempt to write a read-only 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() at System.Data.SQLite.SQLiteTransaction..ctor(SQLiteConnection connection, Boolean deferredLock) at System.Data.SQLite.SQLiteConnection.BeginDbTransaction(IsolationLevel isolationLevel) at System.Data.SQLite.SQLiteConnection.BeginTransaction()

I use Database only from one process and i lock an object on all database operations like that:

public int RemoveMessage(int id)
{
lock (_syncObj)
{
return ExecuteNonQuery(
"DELETE FROM messages WHERE Id = @Id",
new SQLiteParameter("@Id", id)
);
}
}

So, only one process and one thread can access to database on a time. The only potential problem may be in connection string:

var csb = new SQLiteConnectionStringBuilder
{
DataSource = Path.Combine(GeneralHelper.GetCurrentDirectory(), @"SqliteDB\MDS.s3db"),
Version = 3,
Pooling = true,
ReadOnly = false,
SyncMode = SynchronizationModes.Off
};
ConnectionString = csb.ConnectionString;

I disabled Synchronization. Because if synchronization open i perform maximum 3-5 operations in a second, when sycnhronization is off, i perform 200 operations. I need performance in my project.

So, how i can solve this problem?

Do you have an antivirus running? I have seen this problem in this forum because of antivirus Why don't you reuse the connection to improve perfomance and timings?

I use connection pooling but i open/close connection on all operations like that:

using (var connection = new SQLiteConnection(ConnectionString))
{
...
}

I will try to open connection on start of program and close on end of program to see what happens, i will share result here , thank you.

Hi again,

After i changed code to open Connection on startup and close it at the end of the application, i did not get any exception yet. Thank you very much. I searched forum and saw same problem and an solution like that:

Our solution was to open the file as soon as the app comes up - with read/write sharing - and keep the handle alive for the duration of the applications lifetime.
If the first attempt to open the file failes - we just retry this a few times. Normally it should succeed on the first attempt, but hey - this issue only proves how you should be extra cautious about this stuff...

Is that means same as i did? Or must i open file manually with File.Open... method.. If so, i think Sqlite can not open file and i get an exception..

Well, i think that they open the file with r/w because they want to open/close the connection so many times. If you plan open the connection in the start of the program and close it in the exit of the program you dont need to make the tricky of the file.

Ok, very very thanks for your helps. I performed 1.000.000 insert/delete/select and no problem yet.