0

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;
        }
    }
6
  • 1
    Let's see the code, you have written Commented Feb 8, 2018 at 14:49
  • Which part? There is way too much to put it all in. Commented Feb 8, 2018 at 14:53
  • Execute PRAGMA read_uncommitted=1 prior to reading from the database. Be advised that this could result in dirty reads. Commented Feb 8, 2018 at 15:20
  • Why aren't you using keyword to open and close connection? Commented Feb 8, 2018 at 15:35
  • If using a keyword to open the connection solves the problem I'm having I'm willing to explore that. If not, that will be a problem for a future date. Commented Feb 8, 2018 at 17:32

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

2 Comments

This is fantastic! This solved the problem of returning SQLITE_BUSY. Is there a way to also force a read lock to break or to deprioritize it when the child form exits or is the only way to break a lock to close the connection that established it?
Locks are held by transactions. You probably have some open cursor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.