C# + SQLite - Databaselock

Question

I'm facing a problem with my app. It's a desktop application, made from c# with Sqlite DB for caching and is multi-threaded.

My problem is sometimes the caching operation is conflicting with the operations from the other thread.

Anyone can help me or how to solve this dilemma?

I'm thinking of unlocking the database (maybe restart the program) but I know this is not a good approach.

I think you should create queue with sql commands and execute one sql for time. Have you tried use / create concurrent collections? — tray2002, Oct 03 '13 at 05:05
hmmm if im gonna create a queue for sql commands, that means also that im going to wait me command to be executed so that i can use it result? looks like not a good solution? — Fadz, Oct 03 '13 at 06:53
You then should use transactions when interacting to the database. — Max, Oct 03 '13 at 07:26
It's not because of the database not being closed properly is it? http://stackoverflow.com/questions/8511901/system-data-sqlite-close-not-releasing-database-file — dwxw, Oct 03 '13 at 09:07

Amith George · Answer 1 · 2013-10-03T09:32:41.200

Searcing SO for similar questions, the consensus seems that you will need to do the locking yourself. Some answers pointed to passing around the same SqliteConnection object to all threads that do the writing. Though I don't think that will solve the problem.

I would suggest rethinking the concurrent write/read. I am assuming that your threads do some work and then save to the db within that thread. I would rewrite it such that the threads do some work AND return the output. The process of saving data to db needn't be coupled with the process of performing the work. Concurrent reads should work without change as the lock is a shared one for reads. Sure, there could be a scenario where the write and the reads happen at the same time. In which case the error would pop up again.

I think it might be simpler to just use a global lock object and use that to sync/serialize all your writes/reads. However, the moment you do that, you have effectively made db access single threaded. This is one of those questions where the answer depends on you what your end goal is.

Btw, shouldn't you be using a database level transaction instead of an application level? Something like http://msdn.microsoft.com/en-us/library/86773566.aspx

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    SqlCommand command = connection.CreateCommand();
    SqlTransaction transaction;

    // Start a local transaction.
    transaction = connection.BeginTransaction("SampleTransaction");

    // Must assign both transaction object and connection 
    // to Command object for a pending local transaction
    command.Connection = connection;
    command.Transaction = transaction;

    try
    {
        command.CommandText =
            "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
        command.ExecuteNonQuery();
        command.CommandText =
            "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
        command.ExecuteNonQuery();

        // Attempt to commit the transaction.
        transaction.Commit();
        Console.WriteLine("Both records are written to database.");
    }
    catch (Exception ex)
    {
        Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
        Console.WriteLine("  Message: {0}", ex.Message);

        // Attempt to roll back the transaction. 
        try
        {
            transaction.Rollback();
        }
        catch (Exception ex2)
        {
            // This catch block will handle any errors that may have occurred 
            // on the server that would cause the rollback to fail, such as 
            // a closed connection.
            Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
            Console.WriteLine("  Message: {0}", ex2.Message);
        }
    }
}

Thanks @Amith, Its my bad, I have the transactions mechanism, begin and commit, but i forgot to implement the rollback after catching the exeptions. thanks for reminding. looks like its performing well now. — Fadz, Oct 03 '13 at 10:53

score 1 · Answer 2 · answered Oct 03 '13 at 07:30

1

I wanted to do the same thing, linking a SQLiteDatabase to a C# Application. I got the:

Database is locked(5)

aswell, I fixed this using Transactions in my code, here is an example of a Transaction I used:

 using (TransactionScope tran = new TransactionScope())
 {
     //Perform action on SQLite Database here.


     tran.Complete();
 }

answered Oct 03 '13 at 07:30

Max

12,622
16
73
101

I already have transaction in placed and there's no problem with this. I think the problem exist because of its multi threading, see the scenario. Thread 1: doing inserting a list of items. Thread 2: is deleting some items. because the table is locked by thread 1, the same time the thread 2 is accessing it then it causes the problem. (though im just guessing this, because i really dont know how the processor is handling this scenario) – Fadz Oct 03 '13 at 08:02
Did you use transaction for all actions to the database? That solved the problem for me. – Max Oct 03 '13 at 08:41
yeah.. looks like the forgot the use the rollback. thanks anyway. – Fadz Oct 03 '13 at 10:57

C# + SQLite - Databaselock

2 Answers2