Threading in Sqlite using Sqlite.Net

[Nobody/Anonymous]

I am trying the functionality of multithreading in sqlite with the help of "SQLite.NET".
I have written Mutilthread program where one thread inserts values in the database and other threads updates values in database. Below is my Program.
Whenever I run the program I get the exception :
Exception :Finisar.SQLite.SQLiteException: database is locked
   at Finisar.SQLite.sqlite3.Throw()
   at Finisar.SQLite.sqlite_vm.step()
   at Finisar.SQLite.SQLiteCommand.ExecuteNonQuery()
   at ConsoleApplication1.MultiThread.UpdateDatabase() in c:\visual stdio projects\consoleapplication1\multithread.
cs.cs:line 84
Type: Finisar.SQLite.SQLiteException
StackTrce:    at Finisar.SQLite.sqlite3.Throw()
   at Finisar.SQLite.sqlite_vm.step()
   at Finisar.SQLite.SQLiteCommand.ExecuteNonQuery()
   at ConsoleApplication1.MultiThread.UpdateDatabase() in c:\visual stdioprojects\consoleapplication1\multithread.
cs.cs:line 84
Hash code is : 4
Message is : database is locked

Unhandled Exception: Finisar.SQLite.SQLiteException: database is locked
   at Finisar.SQLite.sqlite3.Throw()
   at Finisar.SQLite.sqlite_vm.Dispose()
   at Finisar.SQLite.SQLiteCommand.UnCompile()
   at Finisar.SQLite.SQLiteConnection.Close()
   at ConsoleApplication1.MultiThread.UpdateDatabase() in c:\visual stdioprojects\consoleapplication1\multithread.
cs.cs:line 99

The Exception cleary states that when one thread does an operation it locks the database so that the other thread should not do any operation.

Can you please let me know what is the best way to use MultiThreading in Sqlite with the help of "Sqlite.Net".

Ways I thought of:

1. To Catch Specific Sqlite Exception(like Sqlite_DatabaseLocked) , sleep for some time and try again with the same thread. But I found that there is no way in Sqlite.Net to get specific Sqlite Error Codes in case of failures. They throw "Finisar.SQLite.SQLiteException" from which we cannot get  Sqlite Error Codes.

Please let me how can I get Sqlite specific error codes in case of failures? and what is the best way to do multithreading in sqlite.

Program which I used:
using System;
using Finisar.SQLite;
using System.Threading;

namespace ConsoleApplication1
{
    /// <summary>
    /// Summary description for MultiThread.
    /// </summary>
    public class MultiThread
    {
        [STAThread]
        static void Main(string[] args)
        {
            MakeThread();
            Thread.Sleep(10000);
        }

        public MultiThread()
        {
        }

        public static void MakeThread()
        {
            Thread updateThread = new Thread(new ThreadStart(UpdateDatabase));
            updateThread.Name = "Update Thread";
            updateThread.IsBackground = true;
            updateThread.Start();
            Thread insertThread = new Thread(new ThreadStart(InsertIntoDatabase));
            insertThread.Name = "Insert Thread";
            insertThread.IsBackground = true;
            insertThread.Start();
           
        }

        public static  SQLiteConnection ConnectingToDatabase()
        {
            SQLiteConnection C = new SQLiteConnection("Data Source=database.db;Version=3;New=False;Compress=True;");
            C.Open();
            return C;
        }
        public static void InsertIntoDatabase()
        {
            SQLiteConnection C = ConnectingToDatabase();
           SQLiteCommand Cmd = C.CreateCommand();     
            try
            {               
                for(int i = 0; i< 250; i++)
                {                   
                    Cmd.CommandText = "INSERT INTO test(id, text) VALUES(1, 'Test Text 1')";
                    Cmd.ExecuteNonQuery();                   
                }
            }
            catch(Finisar.SQLite.SQLiteException e)
            {                      
                Console.WriteLine("Exception :" +  e.ToString() + "\n Type: " + e.GetType().ToString() + "\n StackTrce: " + e.StackTrace + "\n Hash code is : " + e.GetHashCode().ToString() + "\n Message is : " + e.Message);
                Console.WriteLine("Hashcode is : " + e.GetHashCode().ToString());
                Console.WriteLine("Source is : " + e.Source);
            }
            catch(Exception e)
            {                      
                Console.WriteLine("Exception :" +  e.ToString() + "\n Type: " + e.GetType().ToString() + "\n StackTrce: " + e.StackTrace + "\n Hash code is : " + e.GetHashCode().ToString() + "\n Message is : " + e.Message);
            }
            finally
            {
                C.Close();
            }
        }
        public static void UpdateDatabase()
        {
         
            SQLiteConnection C = ConnectingToDatabase();
            SQLiteCommand Cmd = C.CreateCommand();     
            try
            {                   
                int newId = 10;
                int oldId = 1;
                for(int i = 0; i< 300; i++)
                {             
                    Cmd.CommandText = "update test set id = " + newId + " where id = " + oldId ;                                     
                    Cmd.ExecuteNonQuery();                                               
                }
            }
            catch(Finisar.SQLite.SQLiteException e)
            {
                Console.WriteLine("Exception :" +  e.ToString() + "\n Type: " + e.GetType().ToString() + "\n StackTrce: " + e.StackTrace + "\n Hash code is : " + e.GetHashCode().ToString() + "\n Message is : " + e.Message);
                Console.WriteLine("Hashcode is : " + e.GetHashCode().ToString());
                Console.WriteLine("Source is : " + e.Source);
            }
            catch(Exception e)
            {                      
                Console.WriteLine("Exception :" +  e.ToString() + "\n Type: " + e.GetType().ToString() + "\n StackTrce: " + e.StackTrace + "\n Hash code is : " + e.GetHashCode().ToString() + "\n Message is : " + e.Message);
            }
            finally
            {
                 C.Close();
            }
        }
    }
}

Thanks
Shaily

[Steven Bell]

I experienced the same problem. I posted much less info earlier and did not get any responses. So I dug a little and found some ideas in the forum. Here is what I did though.

I made a separate class, and in it defined a property. In essence I made my own DB lock. So my four threads start up and each time one of them goes to write to the database table, it checks the property to see if it is already locked. If it is, the thread sleeps for 10 miliseconds and tries again.

Here is a sample class I wrote to test the theory. NOTE - It does not contain any Database code. What it does is builds an instance of the Person class (aka dbLock in my app) and locks the person while one thread counts to  500. Once it hits 500, the first thread releases the lock, and so the second thread can now access the "database" and write it's message.

This is a workaround, since I can't find any decent documentation on how to get the database status (busy, locked etc). I am still looking though, and when I find out I will most likely switch over to it.

// person.cs - a Sample locked property.
using System;
using System.Threading;

class Person
{
    private Boolean dbAcc = false;
    private int myAge = 0;
    public static Thread p1;
    public static Thread p2;
    public static Person person;
   
    // Declare a Name property of type string:
    public Boolean dbAccess
    {
        get
        {
           return dbAcc;
        }
        set
        {
           dbAcc = value;
        }
    }

    // Declare an Age property of type int:
    public int Age
    {
        get
        {
           return myAge;
        }
        set
        {
           myAge = value;
        }
    }

    public override string ToString()
    {
        return "dbAccess = " + dbAcc + ", Age = " + Age;
    }

    public static void Main()
    {
        Console.WriteLine("Simple Properties");

        // Create a new Person object:
        person = new Person();
        p1 = new Thread(new ThreadStart(dbuno));
        p2 = new Thread(new ThreadStart(dbdos));
       
        // Print out the name and the age associated with the person:
        Console.WriteLine("Person details - {0}", person);
        p1.Start();
        Thread.Sleep(160);
        p2.Start();
       
        p1.Join();
        p2.Join();
        Console.WriteLine("Person details - {0}", person);
     }

   public static void dbuno(){
        person.dbAccess = true;
        for(int x = 1; x < 500; x++){
            person.dbAccess = true;
            Console.WriteLine("DB Transaction: {0}", x);
            Thread.Sleep(2);
            }
            Console.WriteLine("Finished locking the faux db. Moving on now.");
            person.dbAccess = false;
            }
           
    public static void dbdos(){
        Boolean isLocked = person.dbAccess;
       
        do {
            isLocked = person.dbAccess;
            Console.WriteLine("No Access Available.");
            Thread.Sleep(8);
           
            }while(isLocked);
            person.dbAccess = true;
            for(int y = 1; y < 11; y++){
                Console.WriteLine("Writing Transaction: {0}.",y);
            }
            Console.WriteLine("Current details: {0}",person);
            person.dbAccess = false;
        }           
}

[Steven Bell]

A couple more things. After some more testing. My property solution is about 85% effective. There are still instances where I hit the database and it's locked.

As for threading and the database, I am working on a graphical application which has 5 threads presently hitting the database at various times. Collisions are bound to happen though. And if I could trap the error codes I would be so much better off. I am using delegates in order to update the graphics while gathering data in external classes.

[Robert Simpson]

SQLite itself is the primary problem.  I think SQLite's locking mechanism (simplified) works something like this:

- Any resultset being step()'d through uses a shared read-only lock.
- Any insert/update being executed requires an exclusive write lock.

Therefore any open datareader stepping through a resultset will prevent a write operation from succeeding, and any pending write operation will prevent a read operation from proceeding.

You'll have to roll your own synchronization class if you want to do multithreading properly.  I recommend the .NET ReaderWriterLock class for sync'ing multiple threads to SQLite and ensuring only one writes at a time.

Robert