1

We have a Quartz.NET cluster with the AdoJobStore backed by SQL Server 2012 Enterprise (with AlwaysOn synchronous replication, if that matters). It experiences frequent deadlocks. We run Quartz.NET across 3 application servers for redundancy.

Is it safe to enable Read Committed Snapshot Isolation on Quartz's database to reduce locking?

Configuration:

{"quartz.scheduler.instanceId", "AUTO"},
{"quartz.threadPool.type", "Quartz.Simpl.SimpleThreadPool, Quartz"},
{"quartz.threadPool.threadCount", "5"},
{"quartz.threadPool.threadPriority", "2"},
{"quartz.jobStore.misfireThreshold", "900000"},
{"quartz.jobStore.type", "Quartz.Impl.AdoJobStore.JobStoreTX, Quartz"},
{"quartz.jobStore.driverDelegateType", "Quartz.Impl.AdoJobStore.SqlServerDelegate, Quartz"},
{"quartz.jobStore.tablePrefix", "QRTZ_"},
{"quartz.jobStore.dataSource", "myDS"},
{"quartz.jobStore.useProperties", "true"},
{"quartz.jobStore.clustered", "true"},
{"quartz.dataSource.myDS.connectionString", "<sanitized>"},
{"quartz.dataSource.myDS.provider", "SqlServer-20"}

SQL Logs:

2017-08-05 17:54:04.29 spid5s      Deadlock encountered .... Printing deadlock information
2017-08-05 17:54:04.29 spid5s      Wait-for graph
2017-08-05 17:54:04.29 spid5s      
2017-08-05 17:54:04.29 spid5s      Node:1
2017-08-05 17:54:04.29 spid5s      KEY: 6:72057594097500160 (73605a770a1a) CleanCnt:2 Mode:X Flags: 0x1
2017-08-05 17:54:04.29 spid5s       Grant List 1:
2017-08-05 17:54:04.29 spid5s         Owner:0x00000014BE73D6C0 Mode: X        Flg:0x40 Ref:1 Life:02000000 SPID:270 ECID:0 XactLockInfo: 0x000000BEFB1A23E8
2017-08-05 17:54:04.29 spid5s         SPID: 270 ECID: 0 Statement Type: UPDATE Line #: 1
2017-08-05 17:54:04.29 spid5s         Input Buf: Language Event: (@newState nvarchar(8),@triggerName nvarchar(36),@triggerGroup nvarchar(7),@oldState nvarchar(7))UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = @newState WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_NAME = @triggerName AND TRIGGER_GROUP = @triggerGroup AND 
2017-08-05 17:54:04.29 spid5s      Requested by: 
2017-08-05 17:54:04.29 spid5s        ResType:LockOwner Stype:'OR'Xdes:0x00000006649E6D28 Mode: S SPID:262 BatchID:0 ECID:0 TaskProxy:(0x000000FB1C910608) Value:0xd736d700 Cost:(0/0)
2017-08-05 17:54:04.29 spid5s      
2017-08-05 17:54:04.29 spid5s      Node:2
2017-08-05 17:54:04.29 spid5s      KEY: 6:72057594071482368 (8a8658a6e703) CleanCnt:3 Mode:S Flags: 0x1
2017-08-05 17:54:04.29 spid5s       Grant List 2:
2017-08-05 17:54:04.29 spid5s         Owner:0x00000029D16CF6C0 Mode: S        Flg:0x40 Ref:1 Life:00000001 SPID:262 ECID:0 XactLockInfo: 0x00000006649E6D68
2017-08-05 17:54:04.29 spid5s         SPID: 262 ECID: 0 Statement Type: SELECT Line #: 1
2017-08-05 17:54:04.29 spid5s         Input Buf: Language Event: (@state nvarchar(7),@noLaterThan bigint,@noEarlierThan bigint)SELECT TOP 1  TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_STATE = @state AND NEXT_FIRE_TIME <= @noLaterThan AND (MI
2017-08-05 17:54:04.29 spid5s      Requested by: 
2017-08-05 17:54:04.29 spid5s        ResType:LockOwner Stype:'OR'Xdes:0x000000BEFB1A23A8 Mode: X SPID:270 BatchID:0 ECID:0 TaskProxy:(0x00000009007D2608) Value:0xd728dec0 Cost:(0/2024)
2017-08-05 17:54:04.29 spid5s      
2017-08-05 17:54:04.29 spid5s      Victim Resource Owner:
2017-08-05 17:54:04.29 spid5s       ResType:LockOwner Stype:'OR'Xdes:0x00000006649E6D28 Mode: S SPID:262 BatchID:0 ECID:0 TaskProxy:(0x000000FB1C910608) Value:0xd736d700 Cost:(0/0)
2017-08-05 17:54:04.29 spid27s     deadlock-list
2017-08-05 17:54:04.29 spid27s      deadlock victim=process3766b3dc38
2017-08-05 17:54:04.29 spid27s       process-list
2017-08-05 17:54:04.29 spid27s        process id=process3766b3dc38 taskpriority=0 logused=0 waitresource=KEY: 6:72057594097500160 (73605a770a1a) waittime=4293 ownerId=13820140164 transactionname=user_transaction lasttranstarted=2017-08-05T17:54:00.003 XDES=0x6649e6d28 lockMode=S schedulerid=22 kpid=9824 status=suspended spid=262 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2017-08-05T17:54:00.003 lastbatchcompleted=2017-08-05T17:54:00.003 lastattention=1900-01-01T00:00:00.003 clientapp=.Net SqlClient Data Provider hostname=ORDERING-WEB5 hostpid=2612 loginname=quartz isolationlevel=read committed (2) xactid=13820140164 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2017-08-05 17:54:04.29 spid27s         executionStack
2017-08-05 17:54:04.29 spid27s          frame procname=adhoc line=1 stmtstart=124 sqlhandle=0x020000007c120a08d2feb48489094525cc10509b9197e57f0000000000000000000000000000000000000000
2017-08-05 17:54:04.29 spid27s     SELECT TOP 1  TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_STATE = @state AND NEXT_FIRE_TIME <= @noLaterThan AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR <> -1 AND NEXT_FIRE_TIME >= @noEarlierThan)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC     
2017-08-05 17:54:04.29 spid27s          frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
2017-08-05 17:54:04.29 spid27s     unknown     
2017-08-05 17:54:04.29 spid27s         inputbuf
2017-08-05 17:54:04.29 spid27s     (@state nvarchar(7),@noLaterThan bigint,@noEarlierThan bigint)SELECT TOP 1  TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_STATE = @state AND NEXT_FIRE_TIME <= @noLaterThan AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR <> -1 AND NEXT_FIRE_TIME >= @noEarlierThan)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC    
2017-08-05 17:54:04.29 spid27s        process id=process10b15c97498 taskpriority=0 logused=2024 waitresource=KEY: 6:72057594071482368 (8a8658a6e703) waittime=4293 ownerId=13820140149 transactionname=user_transaction lasttranstarted=2017-08-05T17:53:59.997 XDES=0xbefb1a23a8 lockMode=X schedulerid=10 kpid=8944 status=suspended spid=270 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-08-05T17:54:00.003 lastbatchcompleted=2017-08-05T17:54:00.003 lastattention=1900-01-01T00:00:00.003 clientapp=.Net SqlClient Data Provider hostname=ORDERING-WEB6 hostpid=2528 loginname=quartz isolationlevel=read committed (2) xactid=13820140149 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2017-08-05 17:54:04.29 spid27s         executionStack
2017-08-05 17:54:04.29 spid27s          frame procname=adhoc line=1 stmtstart=194 sqlhandle=0x020000006c85b217504f346c9e36b17f373c23e3a979ec600000000000000000000000000000000000000000
2017-08-05 17:54:04.29 spid27s     UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = @newState WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_NAME = @triggerName AND TRIGGER_GROUP = @triggerGroup AND TRIGGER_STATE = @oldState     
2017-08-05 17:54:04.29 spid27s          frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
2017-08-05 17:54:04.29 spid27s     unknown     
2017-08-05 17:54:04.29 spid27s         inputbuf
2017-08-05 17:54:04.29 spid27s     (@newState nvarchar(8),@triggerName nvarchar(36),@triggerGroup nvarchar(7),@oldState nvarchar(7))UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = @newState WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_NAME = @triggerName AND TRIGGER_GROUP = @triggerGroup AND TRIGGER_STATE = @oldState    
2017-08-05 17:54:04.29 spid27s       resource-list
2017-08-05 17:54:04.29 spid27s        keylock hobtid=72057594097500160 dbid=6 objectname=Quartz.dbo.QRTZ_TRIGGERS indexname=PK_QRTZ_TRIGGERS id=lock30de68ee80 mode=X associatedObjectId=72057594097500160
2017-08-05 17:54:04.29 spid27s         owner-list
2017-08-05 17:54:04.29 spid27s          owner id=process10b15c97498 mode=X
2017-08-05 17:54:04.29 spid27s         waiter-list
2017-08-05 17:54:04.29 spid27s          waiter id=process3766b3dc38 mode=S requestType=wait
2017-08-05 17:54:04.29 spid27s        keylock hobtid=72057594071482368 dbid=6 objectname=Quartz.dbo.QRTZ_TRIGGERS indexname=IDX_QRTZ_T_NFT_ST id=lockae3cffd900 mode=S associatedObjectId=72057594071482368
2017-08-05 17:54:04.29 spid27s         owner-list
2017-08-05 17:54:04.29 spid27s          owner id=process3766b3dc38 mode=S
2017-08-05 17:54:04.29 spid27s         waiter-list
2017-08-05 17:54:04.29 spid27s          waiter id=process10b15c97498 mode=X requestType=wait

It appears the two queries that are deadlocking are:

SELECT TOP 1 TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_STATE = @state AND NEXT_FIRE_TIME <= @noLaterThan AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR <> -1 AND NEXT_FIRE_TIME >= @noEarlierThan)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC

and

UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = @newState WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_NAME = @triggerName AND TRIGGER_GROUP = @triggerGroup AND TRIGGER_STATE = @oldState

realworldcoder
  • 699
  • 5
  • 16
  • 1
    Could be probably worth the shot to try to fix the cause of deadlocks. When Quartz.NET is correctly configured you shouldn't see deadlocks - unless you are accessing Quartz's tables without using Quartz's own facilities. – Marko Lahma Aug 01 '17 at 11:31
  • @MarkoLahma can confirm we're not accessing the Quartz tables directly. – realworldcoder Aug 01 '17 at 17:16
  • Can you post the relevant configuration used (no need for DB passwords). Are the server clocks synced? – Marko Lahma Aug 02 '17 at 14:26
  • @MarkoLahma i've updated the post with configuration and a few additional details about our setup. Appreciated. – realworldcoder Aug 04 '17 at 01:36
  • Do you share the database with application data? Have your checked SQL server locks what is locking the database? – Marko Lahma Aug 04 '17 at 15:17
  • @MarkoLahma i've added the SQL deadlock logs. Nothing else is using the database (though there are other databases on the same server cluster.) – realworldcoder Aug 05 '17 at 18:08
  • 1
    And what version of Quartz are you using? You can probably get past this by setting jobStore's acquireTriggersWithinLock to true or by setting quartz.scheduler.batchTriggerAcquisitionMaxCount to value larger than one. Are all schedulers connecting to DB set as clustered = true? – Marko Lahma Aug 07 '17 at 05:14
  • @MarkoLahma we're on v2.3.3. Yes, they're connecting as clustered=true. Also to answer the earlier question, all server clocks are in sync. We'll take a look at those config values - thanks! – realworldcoder Aug 07 '17 at 13:55

0 Answers0