title: Quartz.NET + SQLite Jobstore throwing JobPersistenceException
body: <p>When adding or removing a job to/from the scheduler, Quartz sporadically throws a JobPersistenceException (following a preceding SQLiteException).</p>
  
  <p>Things that seem noteworthy:</p>
  
  <ul>
  <li>Quartz.NET 2.01 + System.Data.SQLite 1.0.66 (<s>both latest version at time of writing</s> just noticed that there is a binary package for SQLite 1.0.82 available)</li>
  <li>the exception is also thrown if there is currently no job/trigger beeing executed (i am monitoring the Quartz listeners)</li>
  <li>the Jobs are manually added from UI context (i require about 10-20 repetitions to cause the error but it seems totally random)</li>
  <li><s>Everything seems to be running fine (multiple jobs, parallel execution, persistance after application restart) as long as i don't touch AddJob()/DeleteJob() </s> After extended testing i am sure its not related to adding/removing jobs. The db locking/access problems are a general problem.</li>
  </ul>
  
  <p>Is there any recommended procedure i am not aware that must be followed when adding/deleting jobs?</p>
  
  <p>Is there anything wrong with my ISchedulerFactory configuration? (see below)</p>
  
  <p><strong>Supplemental</strong></p>
  
  <ul>
  <li>I tried using System.Data.SQLite 1.0.82 which made things worse. I get "SQLite error (5): database is locked" almost constantly as soon as Quartz is executing a Job.</li>
  <li>Quartz.NET list System.Data.SQLite 1.0.56 as supported db provider so one might expect problems using a newer version. However, i don't consider going back from 1.0.66 as an option since there were lots of improvements/fixes IIRC.</li>
  <li>I took a look at the development trunk of Quartz.NET between the 2.0.1 release revision (624) and current head revision (669). There seem to be no related fixes.</li>
  <li>I suspect that its a System.Data.SQLite issue. I stumbled over several posts (concerning different SQLite versions) mentioning that there might be some issues with internal disposing of resources, keeping the DB file locked.</li>
  </ul>
  
  <p><strong>Supplemental 2</strong></p>
  
  <p>For now, I gave up on this. I tried a lot of things, but development has to go on. I switched to another database type (Firebird) which so far seems to work fine with Quartz.</p>
  
  <p>If somebody gets this working i would love to hear about it anyway.</p>
  
  <p>-</p>
  
  <p>Exception details:</p>
  
  <p>Quartz.JobPersistenceException: "Couldn't commit ADO.NET transaction. The database file is locked\r\ndatabase is locked"</p>
  
  <p>Stack</p>
  
  <pre class="lang-cs prettyprint-override"><code>bei Quartz.Impl.AdoJobStore.JobStoreSupport.CommitConnection(ConnectionAndTransactionHolder cth, Boolean openNewTransaction)
  bei Quartz.Impl.AdoJobStore.JobStoreSupport.ExecuteInNonManagedTXLock(String lockName, Func`2 txCallback)
  bei Quartz.Impl.AdoJobStore.JobStoreTX.ExecuteInLock(String lockName, Func`2 txCallback)
  bei Quartz.Impl.AdoJobStore.JobStoreSupport.RemoveJob(JobKey jobKey)
  bei Quartz.Core.QuartzScheduler.DeleteJob(JobKey jobKey)
  bei Quartz.Impl.StdScheduler.DeleteJob(JobKey jobKey)
  </code></pre>
  
  <p>InnerException SQLiteException: "The database file is locked\r\ndatabase is locked"</p>
  
  <p>Stack</p>
  
  <pre class="lang-cs prettyprint-override"><code>bei System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
  bei System.Data.SQLite.SQLiteDataReader.NextResult()
  bei System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
  bei System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
  bei System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
  bei System.Data.SQLite.SQLiteTransaction.Commit()
  bei Quartz.Impl.AdoJobStore.JobStoreSupport.CommitConnection(ConnectionAndTransactionHolder cth, Boolean openNewTransaction)
  </code></pre>
  
  <p>The source of the exception is "cth.Transaction.Commit();" in this Quartz.NET method.</p>
  
  <pre class="lang-cs prettyprint-override"><code>/// &lt;summary&gt;
  /// Commit the supplied connection.
  /// &lt;/summary&gt;
  /// &lt;param name="cth"&gt;The CTH.&lt;/param&gt;
  /// &lt;param name="openNewTransaction"&gt;if set to &lt;c&gt;true&lt;/c&gt; opens a new transaction.&lt;/param&gt;
  /// &lt;throws&gt;JobPersistenceException thrown if a SQLException occurs when the &lt;/throws&gt;
  protected virtual void CommitConnection(ConnectionAndTransactionHolder cth, bool openNewTransaction)
  {
      CheckNotZombied(cth);
  
      if (cth.Transaction != null)
      {
          try
          {
              IsolationLevel il = cth.Transaction.IsolationLevel;
              cth.Transaction.Commit();
              if (openNewTransaction)
              {
                  // open new transaction to go with
                  cth.Transaction = cth.Connection.BeginTransaction(il);
              }
          }
          catch (Exception e)
          {
              throw new JobPersistenceException("Couldn't commit ADO.NET transaction. " + e.Message, e);
          }
      }
  }
  </code></pre>
  
  <p>This is how i create the ISchedulerFactory:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public static ISchedulerFactory CreateSQLiteSchedFactory(SQLiteConnection sqlConn, string tablePrefix) {
      // db provider hinzufügen
      var metaData = new DbMetadata();
      metaData.AssemblyName = "System.Data.SQLite,Version=1.0.66.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139";
      metaData.BindByName = true;
      metaData.CommandBuilderType = typeof(SQLiteCommandBuilder);
      metaData.CommandType = typeof(SQLiteCommand);
      metaData.ConnectionType = typeof(SQLiteConnection);
      metaData.ExceptionType = typeof(SQLiteException);
      metaData.ParameterDbType = typeof(TypeAffinity);
      metaData.ParameterDbTypePropertyName = "DbType";
      metaData.ParameterNamePrefix = "@";
      metaData.ParameterType = typeof(SQLiteParameter);
      metaData.UseParameterNamePrefixInParameterCollection = true;
      DbProvider.RegisterDbMetadata("SQLite-1066", metaData);
  
      // konfiguration für factory erstellen
      NameValueCollection properties = new NameValueCollection();
      properties["quartz.scheduler.instanceName"] = "TestScheduler";
      properties["quartz.scheduler.instanceId"] = "instance_one";
      properties["quartz.threadPool.type"] = "Quartz.Simpl.SimpleThreadPool, Quartz";
      properties["quartz.threadPool.threadCount"] = "5";
      properties["quartz.threadPool.threadPriority"] = "Normal";
      properties["quartz.jobStore.misfireThreshold"] = "60000";
      properties["quartz.jobStore.type"] = "Quartz.Impl.AdoJobStore.JobStoreTX, Quartz";
      properties["quartz.jobStore.useProperties"] = "false";
      properties["quartz.jobStore.dataSource"] = "default";
      properties["quartz.jobStore.tablePrefix"] = tablePrefix;
      properties["quartz.jobStore.clustered"] = "true";
  
      properties["quartz.jobStore.lockHandler.type"] = "Quartz.Impl.AdoJobStore.UpdateLockRowSemaphore, Quartz";
  
      properties["quartz.dataSource.default.connectionString"] = sqlConn.ConnectionString;
      properties["quartz.dataSource.default.provider"] = "SQLite-1066";
  
      // factory erzeugen
      return new StdSchedulerFactory(properties);
  }
  </code></pre>
  
  <p>The SQLiteConnection is created with connectionstring similar to "Data Source=c:\mydb.db;Version=3;" and all the quartz tables are initialized using the supplied SQL script</p>