Re: [quartznet:3314] Deadlock in SelectTrigger

[Marko Lahma]

There's a lot of discussion about the matter on the mailing list.
Without knowing more about the setup I would suggest searching for
older posts. I'd might be just a matter of adding indexes to database,
changing from varchar to nvarchar etc.

One path would be to try with 2.x series, there has been some
performance work done.

-Marko

On Fri, Dec 14, 2012 at 12:27 AM, Chawla Sumit <sumitk...@gmail.com> wrote:
> Hi All
>
> We are using Quartz scheduler. We are seeing this error very frequently we
> are seeing following error.
>
> uartz.JobPersistenceException: Couldn't acquire next trigger: Couldn't
> retrieve trigger: Transaction (Process ID 61) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction. ---> Quartz.JobPersistenceException: Couldn't
> retrieve trigger: Transaction (Process ID 61) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction. ---> System.Data.SqlClient.SqlException: Transaction
> (Process ID 61) was deadlocked on lock resources with another process and
> has been chosen as the deadlock victim. Rerun the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection, Action`1 wrapCloseInAction)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
> at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
> at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
> at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean
> setTimeout, Boolean& more)
> at System.Data.SqlClient.SqlDataReader.Read()
> at
> Quartz.Impl.AdoJobStore.StdAdoDelegate.SelectTrigger(ConnectionAndTransactionHolder
> conn, String triggerName, String groupName)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.RetrieveTrigger(ConnectionAndTransactionHolder
> conn, String triggerName, String groupName)
> --- End of inner exception stack trace ---
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.RetrieveTrigger(ConnectionAndTransactionHolder
> conn, String triggerName, String groupName)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.RetrieveTrigger(ConnectionAndTransactionHolder
> conn, SchedulingContext ctxt, String triggerName, String groupName)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.AcquireNextTrigger(ConnectionAndTransactionHolder
> conn, SchedulingContext ctxt, DateTime noLaterThan)
> --- End of inner exception stack trace ---
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.AcquireNextTrigger(ConnectionAndTransactionHolder
> conn, SchedulingContext ctxt, DateTime noLaterThan)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.AcquireNextTriggerCallback.Execute(ConnectionAndTransactionHolder
> conn)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.ExecuteInNonManagedTXLock(String
> lockName, ITransactionCallback txCallback)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.AcquireNextTrigger(SchedulingContext
> ctxt, DateTime noLaterThan)
> at Quartz.Core.QuartzSchedulerThread.Run() [See nested exception:
> Quartz.JobPersistenceException: Couldn't retrieve trigger: Transaction
> (Process ID 61) was deadlocked on lock resources with another process and
> has been chosen as the deadlock victim. Rerun the transaction. --->
> System.Data.SqlClient.SqlException: Transaction (Process ID 61) was
> deadlocked on lock resources with another process and has been chosen as the
> deadlock victim. Rerun the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection, Action`1 wrapCloseInAction)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
> at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
> at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
> at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean
> setTimeout, Boolean& more)
> at System.Data.SqlClient.SqlDataReader.Read()
> at
> Quartz.Impl.AdoJobStore.StdAdoDelegate.SelectTrigger(ConnectionAndTransactionHolder
> conn, String triggerName, String groupName)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.RetrieveTrigger(ConnectionAndTransactionHolder
> conn, String triggerName, String groupName)
> --- End of inner exception stack trace ---
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.RetrieveTrigger(ConnectionAndTransactionHolder
> conn, String triggerName, String groupName)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.RetrieveTrigger(ConnectionAndTransactionHolder
> conn, SchedulingContext ctxt, String triggerName, String groupName)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.AcquireNextTrigger(ConnectionAndTransactionHolder
> conn, SchedulingContext ctxt, DateTime noLaterThan) [See nested exception:
> System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 61)
> was deadlocked on lock resources with another process and has been chosen as
> the deadlock victim. Rerun the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection, Action`1 wrapCloseInAction)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
> at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
> at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
> at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean
> setTimeout, Boolean& more)
> at System.Data.SqlClient.SqlDataReader.Read()
> at
> Quartz.Impl.AdoJobStore.StdAdoDelegate.SelectTrigger(ConnectionAndTransactionHolder
> conn, String triggerName, String groupName)
> at
> Quartz.Impl.AdoJobStore.JobStoreSupport.RetrieveTrigger(ConnectionAndTransactionHolder
> conn, String triggerName, String groupName)
>
>
> I obtained a sql trace and saw that following two queries are locking up:
>
> 1, exec sp_executesql N'UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = @state
> WHERE JOB_NAME = @jobName AND JOB_GROUP = @jobGroup AND TRIGGER_STATE =
> @oldState',N'@state nvarchar(7),@jobName nvarchar(26),@jobGroup
> nvarchar(8),@oldState
> nvarchar(7)',@state=N'WAITING',@jobName=N'My-Job',@jobGroup=N'My-Jobs',@oldState=N'BLOCKED'
>
> 2. exec sp_executesql N'SELECT * FROM QRTZ_TRIGGERS WHERE TRIGGER_NAME =
> @triggerName AND TRIGGER_GROUP = @triggerGroup',N'@triggerName
> nvarchar(43),@triggerGroup
> nvarchar(7)',@triggerName=N'FireNow-MyJobs-Jobs-MyJob',@triggerGroup=N'DEFAULT'
>
> The first query seems to be fired by ClusterRecover method in file
> JobStoreSupport, and i see the following log also before the deadlock
>
> 2012-12-13 12:13:13,530 INFO Quartz.Impl.AdoJobStore.JobStoreTX:
> ClusterManager: detected 1 failed or restarted instances.
> 2012-12-13 12:13:13,530 INFO Quartz.Impl.AdoJobStore.JobStoreTX:
> ClusterManager: Scanning for instance "Instanc29292929"'s failed in-progress
> jobs.
>
>
> Has anyone got this issue? Any fix for this. We are using Quartz.Net 1.0.2
>
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Quartz.NET" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/quartznet/-/-W584hdJqYoJ.
> To post to this group, send email to quar...@googlegroups.com.
> To unsubscribe from this group, send email to
> quartznet+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/quartznet?hl=en.

[Chawla Sumit]

I see you have purposed this solution:

<add key="quartz.jobStore.acquireTriggersWithinLock" value=> "true"/>

I am not sure what is the possible performance impact of this.  And searching for "SelectTrigger Deadlock" gave no indication of purposed indices for this change.