How do i stop the The database file is locked exception?

Question

I have a multithreaded app that uses sqlite. When two threads try to update the db at once i get the exception

Additional information: The database file is locked

I thought it would retry in a few milliseconds. My querys arent complex. The most complex one (which happens frequently) is update, select, run trivial code update/delete, commit. Why does it throw the exception? How can i make it retry a few times before throwing an exception?

Answer 1

SQLite isn't thread safe for access, which is why you get this error message.

You should synchronize the access to the database (create an object, and "lock" it) whenever you go to update. This will cause the second thread to block and wait until the first thread's update finishes automatically.

Answer 2

try to make your transaction / commit blocks as short as possible. The only time you can deadlock/block is with a transaction -- thus if you don't do them you won't have the problem.

That said, there are times when you need to do transactions (mostly on data updates), but don't do them while you are "run trivial code" if you can avoid it.

Answer 3

A better approach may be to use an update queue, if you can do the database updates out of line with the rest of your code. For example, you could do something like:

m_updateQueue.Add(()=>InsertOrder(o));

Then you could have a dedicated update thread that processed the queue.

That code would look similar to this (I haven't compiled or tested it):

class UpdateQueue : IDisposable
{
     private object m_lockObj;
     private Queue<Action> m_queue;
     private volatile bool m_shutdown;
     private Thread m_thread;

     public UpdateQueue()
     {
         m_lockObj = new Object();
         m_queue = new Queue<Action>();
         m_thread = new Thread(ThreadLoop);
         m_thread.Start();
     }

     public void Add(Action a)
     {
         lock(m_lockObj)
         {
             m_queue.Enqueue(a);
             Monitor.Pulse(m_lockObj);
         }
     }

     public void Dispose()
     {
         if (m_thread != null)
         {
             m_shutdown = true;
             Monitor.PulseAll(m_lockObj);
             m_thread.Join();
             m_thread = null;
         }
     }

     private void ThreadLoop()
     {
         while (! m_shutdown)
         {
             Action a;
             lock (m_lockObj)
             {
                 if (m_queue.Count == 0)
                 {
                     Monitor.Wait(m_lockObj);
                 }

                 if (m_shutdown)
                 {
                     return;
                 }

                 a = m_queuue.Dequeue();
             }
             a();
         }
     }

}

Or, you could use something other than Sql Lite.