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