CODEWITHSUNDEEP
c#sqlitequartz.netsystem.data.sqlite
Rev
Rev

Reputation: 6092

Quartz.NET + SQLite Jobstore throwing JobPersistenceException

When adding or removing a job to/from the scheduler, Quartz sporadically throws a JobPersistenceException (following a preceding SQLiteException).

Things that seem noteworthy:

Is there any recommended procedure i am not aware that must be followed when adding/deleting jobs?

Is there anything wrong with my ISchedulerFactory configuration? (see below)

Supplemental

Supplemental 2

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.

If somebody gets this working i would love to hear about it anyway.

-

Exception details:

Quartz.JobPersistenceException: "Couldn't commit ADO.NET transaction. The database file is locked\r\ndatabase is locked"

Stack

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)

InnerException SQLiteException: "The database file is locked\r\ndatabase is locked"

Stack

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)

The source of the exception is "cth.Transaction.Commit();" in this Quartz.NET method.

/// <summary>
/// Commit the supplied connection.
/// </summary>
/// <param name="cth">The CTH.</param>
/// <param name="openNewTransaction">if set to <c>true</c> opens a new transaction.</param>
/// <throws>JobPersistenceException thrown if a SQLException occurs when the </throws>
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);
        }
    }
}

This is how i create the ISchedulerFactory:

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);
}

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

Upvotes: 1

Views: 2806

Answers (2)

Claudiu Constantin
Claudiu Constantin

Reputation: 2208

You have to set this one to true in the properties:

properties["quartz.jobStore.txIsolationLevelSerializable"] = "true";

Upvotes: 2

Saw
Saw

Reputation: 6416

The cause of this error is most likely to be because of multiple concurrent writes on the SQLite db, sqlite can accept multiple read-only connections only, but the can't accept simultaneous writes!

http://www.sqlite.org/faq.html#q5

Upvotes: 1

Related Questions