title: SQLite "Database is locked" error in multithreads application
body: <p>There is a multithreads application, that works with large DB file (>600 Mb). "Database is locked" problem started when I added blob data, and started operate with >30 Kb of BLOB data per request. I think problem related to small HDD speed. It looks like SQLite deletes -journal file, one thread of my application got out of lock (because -journal file was applied and deleted), and other my thread want to do smth with DB, but SQLite still updates DB file... Sure, I can do a minute delays after each DB call, but this is not a solution, because I need more speed. </p>
  
  <p>Now I use session per conversation (per thread) implementation. So there is one ISessionFactory per application object and many ISession objects. </p>
  
  <p>There are my helper classes (as you can see I use IsolationLevel.Serializable and CurrentSessionContext = ThreadStaticSessionContext):</p>
  
  <pre><code>public abstract class nHibernateHelper
  {
      private static FluentConfiguration _configuration;
      private static IPersistenceContext _persistenceContext;
  
      static nHibernateHelper() {}
  
      private static FluentConfiguration ConfigurePersistenceLayer()
      {
          return Fluently.Configure().Database(FluentNHibernate.Cfg.Db.SQLiteConfiguration.Standard.ShowSql().UsingFile(_fileName).IsolationLevel(IsolationLevel.Serializable).MaxFetchDepth(2)).
                  Mappings(m =&gt; m.FluentMappings.AddFromAssemblyOf&lt;Foo&gt;()).CurrentSessionContext(typeof(ThreadStaticSessionContext).FullName);
      }
  
      public static ISession CurrentSession
      {
          get { return _persistenceContext.CurrentSession; }
      }
  
      public static IDisposable OpenConnection()
      {
          return new DbSession(_persistenceContext);
      }
  }
  
  public class PersistenceContext : IPersistenceContext, IDisposable
  {
      private readonly FluentConfiguration _configuration;
      private readonly ISessionFactory _sessionFactory;
  
      public PersistenceContext(FluentConfiguration configuration)
      {
          _configuration = configuration;
          _sessionFactory = _configuration.BuildSessionFactory();
      }
  
      public FluentConfiguration Configuration { get { return _configuration; } }
      public ISessionFactory SessionFactory { get { return _sessionFactory; } }
  
      public ISession CurrentSession
      {
          get
          {
              if (!CurrentSessionContext.HasBind(SessionFactory))
              {
                  OnContextualSessionIsNotFound();
              }
              var contextualSession = SessionFactory.GetCurrentSession();
              if (contextualSession == null)
              {
                  OnContextualSessionIsNotFound();
              }
              return contextualSession;
          }
      }
  
      public void Dispose()
      {
          SessionFactory.Dispose();
      }
  
      private static void OnContextualSessionIsNotFound()
      {
          throw new InvalidOperationException("Ambient instance of contextual session is not found. Open the db session before.");
      }
  
  }
  
  public class DbSession : IDisposable
  {
      private readonly ISessionFactory _sessionFactory;
  
      public DbSession(IPersistenceContext persistentContext)
      {
          _sessionFactory = persistentContext.SessionFactory;
          CurrentSessionContext.Bind(_sessionFactory.OpenSession());
      }
  
      public void Dispose()
      {
          var session = CurrentSessionContext.Unbind(_sessionFactory);
          if (session != null &amp;&amp; session.IsOpen)
          {
              try
              {
                  if (session.Transaction != null &amp;&amp; session.Transaction.IsActive)
                  {
                      session.Transaction.Rollback();
                  }
              }
              finally
              {
                  session.Dispose();
              }
          }
      }
  }
  </code></pre>
  
  <p>And there is repository helper class. As you can see there are locks by every DB call, so concurrence DB call can't appear, for different threads too, because _locker object is static. </p>
  
  <pre><code>public abstract class BaseEntityRepository&lt;T, TId&gt; : IBaseEntityRepository&lt;T, TId&gt; where T : BaseEntity&lt;TId&gt;
  {
      private ITransaction _transaction;
      protected static readonly object _locker = new object();
  
      public bool Save(T item)
      {
          bool result = false;
  
          if ((item != null) &amp;&amp; (item.IsTransient()))
          {
              lock (_locker)
              {
                  try
                  {
                      _transaction = session.BeginTransaction();
                      nHibernateHelper.CurrentSession.Save(item);
                      nHibernateHelper.Flush();
                      _transaction.Commit();          
                      result = true;
                  } catch 
                  {
                      _transaction.Rollback();
                      throw;
                  }
                  //DelayAfterProcess();
              }
          }
          return result;
      }
  
      //same for delete and update 
  
      public T Get(TId itemId)
      {
          T result = default(T);
  
          lock (_locker)
          {
              try
              {
                  result = nHibernateHelper.CurrentSession.Get&lt;T&gt;(itemId);
              }
              catch 
              {
                  throw;
              }
          }
          return result;
      }
  
      public IList&lt;T&gt; Find(Expression&lt;Func&lt;T, bool&gt;&gt; predicate)
      {
          IList&lt;T&gt; result = new List&lt;T&gt;();
          lock (_locker)
          {
              try
              {
                  result = nHibernateHelper.CurrentSession.Query&lt;T&gt;().Where(predicate).ToList();
              }
              catch 
              {
                  throw;
              }
          }
          return result;
      }
  
  
  }
  </code></pre>
  
  <p>I use previous classes like this (I call nHibernateHelper.OpenConnection() once per thread). Repository is instantiated by singletone:</p>
  
  <pre><code>using (nHibernateHelper.OpenConnection())
  {
      Foo foo = new Foo();
      FooRepository.Instance.Save(foo);
  }    
  </code></pre>
  
  <p>I tried to change IsolationLevel to ReadCommited, but this not changes a problem. Also I tried to solve this problem by change SQLite journal mode from journal to WAL:</p>
  
  <pre><code>using (nHibernateHelper.OpenConnection()) 
  {
      using (IDbCommand command = nHibernateHelper.CurrentSession.Connection.CreateCommand())
      {
          command.CommandText = "PRAGMA journal_mode=WAL";
          command.ExecuteNonQuery();
      }
  }
  </code></pre>
  
  <p>This helped on computers with fast HDD, but on some I got same error. Then I tried to add "DB update file exist" check to repository, and delay after each save/update/delete procedure:</p>
  
  <pre><code>    protected static int _delayAfterInSeconds = 1;
      protected void DelayAfterProcess()
      {
          bool dbUpdateInProcess = false;
          do
          {
              string fileMask = "*-wal*";
              string[] files = Directory.GetFiles(Directory.GetCurrentDirectory(), fileMask);
              if ((files != null) &amp;&amp; (files.Length &gt; 0))
              {
                  dbUpdateInProcess = true;
                  Thread.Sleep(1000);
              }
              else
              {
                  dbUpdateInProcess = false;
              }
          } while (dbUpdateInProcess);
          if (_delayAfterInSeconds &gt; 0)
          {
              Thread.Sleep(_delayAfterInSeconds * 1000);
          }
      }
  </code></pre>
  
  <p>Same solution (check for DB update file) not worked for -journal file. It reported, that -journal file was deleted, but I still got errors. For -wal file it works (as I think. I need more time to test it). But this solution seriously brake program. </p>
  
  <p>Maybe you can help me?</p>