System.Data.SQLite

I have a database that is accessed by two threads. One thread writes to the database and regular intervals while the other thread performs read operations. Each thread has its own connection pointer, and I have synchronized access even further by using lock. In short:

lock (syncObject)
{
  using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
  {
     ...
  }
}

SyncObject is shared by the two threads.

Based on this I think I have followed what should be done when using multiple thread access to the database. However, from time to time I get an SQLite exception telling me that its trying to write an readonly database. Im guessing that the writer thread is trying to write a database that is still opened by the reader thread. The question is how is this possible?

navaraf:

Do you perhaps have TortoiseSVN installed on the same machine?

Im not using any subversion clients, or subversion for that matter.

catchertinator:

Do u have an antivirus?

Yes, I have antivirus on my machine. Can that be the reason, how, and is that a common issue?

Instead of repeatedly opening and closing the database connection, why not have each thread open the database once, and then use the syncObject to sync the commands you execute on the connections?

Repeatedly opening and closing the database connection, since it is file-based, will often trigger antivirus programs to repeatedly scan the file.  Frequently, the anti-virus program will briefly lock the file (especially the rollback journal) briefly during the process, and prevent SQLite from reading/writing/creating/locking that journal file.

You could also try changing the journal mode in your connection string to persist.

Robert Simpson:

Instead of repeatedly opening and closing the database connection, why not have each thread open the database once, and then use the syncObject to sync the commands you execute on the connections?

From what I've read and understood so far the SQLiteConnection class works as a synchronization against the database. So to follow your guideance on this issue, I open the database once and then use the SQLiteConnection to synchronize. However due to the current design I think I will synchronize using lock as im doing now, and have the threads share the connection pointer and close the connection on application termination. There may be more threads that is going to access the database in the future.

So in essence:

1. Open the database once upon application startup, and share the connection pointer between threads.
2. Use c# synchronization mechanism to synchronize access to the connection pointer (database), and perform database operations.
3. When application terminates/exits, close the connection.

Using this I should achieve the same, if I understood the pinned "Thread safety" thread. Correct me if im wrong, or its a bad idea. Again the main reason I want to do it like this is due to the current design. Let me know if I should look into a redesign to support multiple connection pointers.

Are there any drawbacks with setting the journal mode to persist?