Issue: Getting error from SQLite that the DB is locked Desired Outcome: Multiple applications can read from DB and if only one writes to it the table will not be locked.
I have a C# application that reads and writes to a SQLite database. When multiple instances of the application run at the same time no instance of the application is able to make changes to the DB but all instances can read from it. Everything that I have found says that as long as only one application tries to write to the BD at a time it should not be locked. My connection string explicitly states LockingMode=Normal; Synchronous=Off. Any suggestions on what would be causing this? Below is the connection methods that run and the write that is encountering the lock:
private String _databasePath ; private SQLiteConnection _dbConn;
public dbPrismIIDirectory()
{
_databasePath = "";
try
{
_dbConn = new System.Data.SQLite.SQLiteConnection();
}
catch (Exception exc)
{
System.Windows.Forms.MessageBox.Show(exc.Message);
}
}
public Boolean open(String databasePath)
{
_databasePath = databasePath;
// Attempt to open the database
try
{
_dbConn.ConnectionString = String.Format("Data Source={0}; LockingMode=Normal; Synchronous=On", _databasePath);
_dbConn.Open();
return true;
}
catch
{
return false;
}
}
public Boolean CreateEntry()
{
try
{
sqlCMD.CommandText = //SET UP SQL HERE//;
sqlCMD.ExecuteNonQuery();
return true;
}
catch (Exception exc)
{
System.Windows.Forms.MessageBox.Show(exc.Message);
return false;
}
}
A writer needs exclusive access to the database, so it blocks any other readers and writers.
To allow one writer to run simultaneously with readers, and if you are not using a network, enable WAL mode.