Background Job Deadlocked with SQL Server

jbrabant.dev · August 18, 2026, 12:07pm

Before I undertake a big investigation and changes, can you tell me what could be the cause of the deadlocks I now have since I moved from sqlite to sqlserver for the Jobs feature?
I get these errors and they pile up overtime. Also, if they stay there for a very long period, the durationMs will be greater than the INT datatype and it crashes. Had to change tables for BIGINT.

Transaction (Process ID 125) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

They come from Scheduled Tasks execution. I have tried to group some in their own Worker but I still get deadlocks. Is having one unique worker name per scheduled task the solution here or is there something else I need to look at?

mythz · August 18, 2026, 12:36pm

Not sure what’s the cause, although a difference between the 2 is that SQL Server defaults to pessimistic locking (READ COMMITTED) , whereas SQLite uses coarse-grained (DB-level) connection locking.

Do you know if you have Read Committed Snapshot Isolation (RCSI) enabled? Can check with SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = 'YourDbName'

If it isn’t, consider enabling it:

ALTER DATABASE [YourDatabaseName] 
SET READ_COMMITTED_SNAPSHOT ON 
WITH ROLLBACK IMMEDIATE;

Is your App load balanced (i.e. multiple instances connecting to same DB)?

What’s the approx number of tasks running when the deadlock happens?

If you can save a deadlock graph next time it happens, it should identify the specific queries causing it.

jbrabant.dev · August 18, 2026, 2:57pm

Here is one of the deadlock xml, they are pretty much all the same. RCSI is enabled. Not load balanced and 25 scheduled tasks.

<deadlock>
	<victim-list>
		<victimProcess id="" process2bfbc38b468""/>
	</victim-list>
	<process-list>
		<process id="" process2bfbc38b468"" taskpriority="" 0"" logused="" 4368"" waitresource="" KEY: 6:72057594055557120 (f3611e57f2db)"" waittime="" 118"" ownerId="" 279488902"" transactionname="" user_transaction"" lasttranstarted="" 2026-08-18T09:45:03.823"" XDES="" 0x2c1ed8b0470"" lockMode="" U"" schedulerid="" 4"" kpid="" 53832"" status="" suspended"" spid="" 163"" sbid="" 2"" ecid="" 0"" priority="" 0"" trancount="" 2"" lastbatchstarted="" 2026-08-18T09:45:03.823"" lastbatchcompleted="" 2026-08-18T09:45:03.823"" lastattention="" 1900-01-01T00:00:00.823"" clientapp="" PE"" hostpid="" 71824"" isolationlevel="" read committed (2)"" xactid="" 279488902"" currentdb="" 6"" currentdbname="" PEDB"" lockTimeout="" 4294967295"" clientoption1="" 671088672"" clientoption2="" 128056""><stackFrames><frame id="" 00"" address="" 0x7FFC21E43B04"" pdb="" ntdll.pdb"" age="" 1"" guid="" 801276B1-B18E-B373-48F4-08A0D236E8E8"" module="" ntdll"" rva="" 0xA3B04""/>
		<frame id="" 01"" address="" 0x7FFC1F6E8419"" pdb="" kernelbase.pdb"" age="" 1"" guid="" 2F04B864-E806-2D22-79C1-978312F4F54A"" module="" kernelbase"" rva="" 0xC8419""/>
		<frame id="" 02"" address="" 0x7FFC00241817"" pdb="" SqlDK.pdb"" age="" 2"" guid="" A54785C7-8660-42A7-8BBD-A08C3DA03954"" module="" SqlDK"" rva="" 0x11817""/>
		<frame id="" 03"" address="" 0x7FFC00241737"" pdb="" SqlDK.pdb"" age="" 2"" guid="" A54785C7-8660-42A7-8BBD-A08C3DA03954"" module="" SqlDK"" rva="" 0x11737""/>
		<frame id="" 04"" address="" 0x7FFC00232317"" pdb="" SqlDK.pdb"" age="" 2"" guid="" A54785C7-8660-42A7-8BBD-A08C3DA03954"" module="" SqlDK"" rva="" 0x2317""/>
		<frame id="" 05"" address="" 0x7FFC00233227"" pdb="" SqlDK.pdb"" age="" 2"" guid="" A54785C7-8660-42A7-8BBD-A08C3DA03954"" module="" SqlDK"" rva="" 0x3227""/>
		<frame id="" 06"" address="" 0x7FFBF30DA5C9"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x9A5C9""/>
		<frame id="" 07"" address="" 0x7FFBF30DA3CD"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x9A3CD""/>
		<frame id="" 08"" address="" 0x7FFBF3050864"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x10864""/>
		<frame id="" 09"" address="" 0x7FFBF3059448"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x19448""/>
		<frame id="" 10"" address="" 0x7FFBF31446D7"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x1046D7""/>
		<frame id="" 11"" address="" 0x7FFBF3059B22"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x19B22""/>
		<frame id="" 12"" address="" 0x7FFBF30500B0"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x100B0""/>
		<frame id="" 13"" address="" 0x7FFBF306D36B"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D36B""/>
		<frame id="" 14"" address="" 0x7FFBF306D286"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D286""/>
		<frame id="" 15"" address="" 0x7FFBF30DD42F"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x9D42F""/>
		<frame id="" 16"" address="" 0x7FFBF306D189"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D189""/>
		<frame id="" 17"" address="" 0x7FFBF30C2915"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x82915""/>
		<frame id="" 18"" address="" 0x7FFBF306D189"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D189""/>
		<frame id="" 19"" address="" 0x7FFBF30C269B"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x8269B""/>
		<frame id="" 20"" address="" 0x7FFBF306D189"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D189""/>
		<frame id="" 21"" address="" 0x7FFBF307C07F"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x3C07F""/>
		<frame id="" 22"" address="" 0x7FFBF01635D8"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0x135D8""/>
		<frame id="" 23"" address="" 0x7FFBF0179FAC"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0x29FAC""/>
		<frame id="" 24"" address="" 0x7FFBF0179D0A"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0x29D0A""/>
		<frame id="" 25"" address="" 0x7FFBF015CF2F"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0xCF2F""/>
		<frame id="" 26"" address="" 0x7FFBF0EFC638"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0xDAC638""/>
		<frame id="" 27"" address="" 0x7FFBF0EEF457"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0xD9F457""/>
		<frame id="" 28"" address="" 0x7FFBF015CCCC"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0xCCCC""/>
	</stackFrames>
	<executionStack>
		<frame procname="" adhoc"" line="" 1"" stmtstart="" 244"" stmtend="" 718"" sqlhandle="" 0x02000000b59cd339461a580b50b03903e3bac01b22c3f2d80000000000000000000000000000000000000000""> unknown
	</frame>
	<frame procname="" unknown"" line="" 1"" sqlhandle="" 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000""> unknown
</frame>
</executionStack>
<inputbuf>
(@0 bigint,@RequestId nvarchar(4000),@StartedDate datetime,@LastActivityDate datetime,@State nvarchar(6),@ParentId bigint)UPDATE ""BackgroundJob"" SET ""RequestId""=@RequestId, ""StartedDate""=@StartedDate, ""LastActivityDate""=@LastActivityDate, ""State""=@State, ""ParentId""=@ParentId WHERE (((""CompletedDate"" is null) AND (""RequestId"" is null)) AND (""DependsOn"" = @0))   </inputbuf>
</process>
<process id="" process2bfc3ecb088"" taskpriority="" 0"" logused="" 4372"" waitresource="" KEY: 6:72057594055557120 (057160c35b96)"" waittime="" 1194"" ownerId="" 279488847"" transactionname="" user_transaction"" lasttranstarted="" 2026-08-18T09:45:03.807"" XDES="" 0x2c1f3488470"" lockMode="" U"" schedulerid="" 4"" kpid="" 61776"" status="" suspended"" spid="" 162"" sbid="" 2"" ecid="" 0"" priority="" 0"" trancount="" 2"" lastbatchstarted="" 2026-08-18T09:45:03.807"" lastbatchcompleted="" 2026-08-18T09:45:03.807"" lastattention="" 1900-01-01T00:00:00.807"" clientapp="" PE"" hostpid="" 71824"" isolationlevel="" read committed (2)"" xactid="" 279488847"" currentdb="" 6"" currentdbname="" PEDB"" lockTimeout="" 4294967295"" clientoption1="" 671088672"" clientoption2="" 128056""><stackFrames><frame id="" 00"" address="" 0x7FFC21E43B04"" pdb="" ntdll.pdb"" age="" 1"" guid="" 801276B1-B18E-B373-48F4-08A0D236E8E8"" module="" ntdll"" rva="" 0xA3B04""/>
<frame id="" 01"" address="" 0x7FFC1F6E8419"" pdb="" kernelbase.pdb"" age="" 1"" guid="" 2F04B864-E806-2D22-79C1-978312F4F54A"" module="" kernelbase"" rva="" 0xC8419""/>
<frame id="" 02"" address="" 0x7FFC00241817"" pdb="" SqlDK.pdb"" age="" 2"" guid="" A54785C7-8660-42A7-8BBD-A08C3DA03954"" module="" SqlDK"" rva="" 0x11817""/>
<frame id="" 03"" address="" 0x7FFC00241737"" pdb="" SqlDK.pdb"" age="" 2"" guid="" A54785C7-8660-42A7-8BBD-A08C3DA03954"" module="" SqlDK"" rva="" 0x11737""/>
<frame id="" 04"" address="" 0x7FFC00232317"" pdb="" SqlDK.pdb"" age="" 2"" guid="" A54785C7-8660-42A7-8BBD-A08C3DA03954"" module="" SqlDK"" rva="" 0x2317""/>
<frame id="" 05"" address="" 0x7FFC00233227"" pdb="" SqlDK.pdb"" age="" 2"" guid="" A54785C7-8660-42A7-8BBD-A08C3DA03954"" module="" SqlDK"" rva="" 0x3227""/>
<frame id="" 06"" address="" 0x7FFBF30DA5C9"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x9A5C9""/>
<frame id="" 07"" address="" 0x7FFBF30DA3CD"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x9A3CD""/>
<frame id="" 08"" address="" 0x7FFBF3050864"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x10864""/>
<frame id="" 09"" address="" 0x7FFBF3059448"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x19448""/>
<frame id="" 10"" address="" 0x7FFBF31446D7"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x1046D7""/>
<frame id="" 11"" address="" 0x7FFBF3059B22"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x19B22""/>
<frame id="" 12"" address="" 0x7FFBF30500B0"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x100B0""/>
<frame id="" 13"" address="" 0x7FFBF306D36B"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D36B""/>
<frame id="" 14"" address="" 0x7FFBF306D286"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D286""/>
<frame id="" 15"" address="" 0x7FFBF30DD42F"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x9D42F""/>
<frame id="" 16"" address="" 0x7FFBF306D189"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D189""/>
<frame id="" 17"" address="" 0x7FFBF30C2915"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x82915""/>
<frame id="" 18"" address="" 0x7FFBF306D189"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D189""/>
<frame id="" 19"" address="" 0x7FFBF30C269B"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x8269B""/>
<frame id="" 20"" address="" 0x7FFBF306D189"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x2D189""/>
<frame id="" 21"" address="" 0x7FFBF307C07F"" pdb="" sqlmin.pdb"" age="" 2"" guid="" 185BC1F4-BACE-4CB0-890B-28E2BDF37D56"" module="" sqlmin"" rva="" 0x3C07F""/>
<frame id="" 22"" address="" 0x7FFBF01635D8"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0x135D8""/>
<frame id="" 23"" address="" 0x7FFBF0179FAC"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0x29FAC""/>
<frame id="" 24"" address="" 0x7FFBF0179D0A"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0x29D0A""/>
<frame id="" 25"" address="" 0x7FFBF015CF2F"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0xCF2F""/>
<frame id="" 26"" address="" 0x7FFBF0EFC638"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0xDAC638""/>
<frame id="" 27"" address="" 0x7FFBF0EEF457"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0xD9F457""/>
<frame id="" 28"" address="" 0x7FFBF015CCCC"" pdb="" sqllang.pdb"" age="" 2"" guid="" 3C9A1A89-327E-4E8A-8685-0E076EA3893E"" module="" sqllang"" rva="" 0xCCCC""/>
</stackFrames>
<executionStack>
	<frame procname="" adhoc"" line="" 1"" stmtstart="" 244"" stmtend="" 718"" sqlhandle="" 0x02000000b59cd339461a580b50b03903e3bac01b22c3f2d80000000000000000000000000000000000000000""> unknown
</frame>
<frame procname="" unknown"" line="" 1"" sqlhandle="" 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000""> unknown
</frame>
</executionStack>
<inputbuf>
(@0 bigint,@RequestId nvarchar(4000),@StartedDate datetime,@LastActivityDate datetime,@State nvarchar(6),@ParentId bigint)UPDATE ""BackgroundJob"" SET ""RequestId""=@RequestId, ""StartedDate""=@StartedDate, ""LastActivityDate""=@LastActivityDate, ""State""=@State, ""ParentId""=@ParentId WHERE (((""CompletedDate"" is null) AND (""RequestId"" is null)) AND (""DependsOn"" = @0))   </inputbuf>
</process>
</process-list>
<resource-list>
	<keylock hobtid="" 72057594055557120"" dbid="" 6"" objectname="" PEDB.dbo.BackgroundJob"" indexname="" PK__Backgrou__3214EC0795FC240A"" id="" lock2c038f87900"" mode="" X"" associatedObjectId="" 72057594055557120""><owner-list><owner id="" process2bfc3ecb088"" mode="" X""/>
</owner-list>
<waiter-list>
	<waiter id="" process2bfbc38b468"" mode="" U"" requestType="" wait""/>
</waiter-list>
</keylock>
<keylock hobtid="" 72057594055557120"" dbid="" 6"" objectname="" PEDB.dbo.BackgroundJob"" indexname="" PK__Backgrou__3214EC0795FC240A"" id="" lock2c03adaa480"" mode="" X"" associatedObjectId="" 72057594055557120""><owner-list><owner id="" process2bfbc38b468"" mode="" X""/>
</owner-list>
<waiter-list>
	<waiter id="" process2bfc3ecb088"" mode="" U"" requestType="" wait""/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>

mythz · August 18, 2026, 3:39pm

ok thanks for the deadlock xml, it highlighted the potential issue.

When a job finishes, it deletes the job and runs an UPDATE ... WHERE DependsOn = @jobId to check if there are any downstream tasks waiting on it.

Because DependsOn was missing an index, SQL Server was doing a full table scan on every completed job. Since RCSI only prevents locks on SELECT queries (not UPDATEs), whenever 2 or more of your 25 tasks finished at the same time, their table scans could have collided and caused the deadlock.

To prevent this I’ve added an [Index] on DependsOn and a [CompositeIndex(nameof(CompletedDate), nameof(RequestId), nameof(RunAfter))] (to speed up pending job lookups) in this commit.

Since you have existing tables, you can add these indexes with:

-- Should fix the ArchiveJob deadlock
CREATE NONCLUSTERED INDEX IX_BackgroundJob_DependsOn 
ON BackgroundJob (DependsOn);

-- Speeds up pending queue polling
CREATE NONCLUSTERED INDEX IX_BackgroundJob_Pending 
ON BackgroundJob (CompletedDate, RequestId, RunAfter);

jbrabant.dev · August 18, 2026, 4:17pm

Thanks @mythz but I still get the deadlocks with the indexes.

mythz · August 18, 2026, 4:23pm

What’s the latest XML graph saying now?

jbrabant.dev · August 18, 2026, 4:57pm

<deadlock><victim-list><victimProcess id="process2bfd80a1c28"/></victim-list><process-list><process id="process2bfd80a1c28" taskpriority="0" logused="2476" waitresource="KEY: 6:72057594055557120 (894d0f161517)" waittime="1114" ownerId="322702714" transactionname="user_transaction" lasttranstarted="2026-08-18T12:44:41.657" XDES="0x2c239000470" lockMode="U" schedulerid="2" kpid="104204" status="suspended" spid="131" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2026-08-18T12:44:41.657" lastbatchcompleted="2026-08-18T12:44:41.657" lastattention="1900-01-01T00:00:00.657" clientapp="PE" hostpid="46928"  isolationlevel="read committed (2)" xactid="322702714" currentdb="6" currentdbname="PEDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"><stackFrames><frame id="00" address="0x7FFC21E43B04" pdb="ntdll.pdb" age="1" guid="801276B1-B18E-B373-48F4-08A0D236E8E8" module="ntdll" rva="0xA3B04"/><frame id="01" address="0x7FFC1F6E8419" pdb="kernelbase.pdb" age="1" guid="2F04B864-E806-2D22-79C1-978312F4F54A" module="kernelbase" rva="0xC8419"/><frame id="02" address="0x7FFC00241817" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x11817"/><frame id="03" address="0x7FFC00241737" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x11737"/><frame id="04" address="0x7FFC00232317" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x2317"/><frame id="05" address="0x7FFC00233227" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x3227"/><frame id="06" address="0x7FFBF30DA5C9" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x9A5C9"/><frame id="07" address="0x7FFBF30DA3CD" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x9A3CD"/><frame id="08" address="0x7FFBF3050864" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x10864"/><frame id="09" address="0x7FFBF3059448" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x19448"/><frame id="10" address="0x7FFBF31446D7" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x1046D7"/><frame id="11" address="0x7FFBF3059B22" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x19B22"/><frame id="12" address="0x7FFBF30500B0" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x100B0"/><frame id="13" address="0x7FFBF306D36B" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D36B"/><frame id="14" address="0x7FFBF306D286" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D286"/><frame id="15" address="0x7FFBF30DD42F" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x9D42F"/><frame id="16" address="0x7FFBF306D189" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D189"/><frame id="17" address="0x7FFBF30C2915" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x82915"/><frame id="18" address="0x7FFBF306D189" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D189"/><frame id="19" address="0x7FFBF30C269B" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x8269B"/><frame id="20" address="0x7FFBF306D189" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D189"/><frame id="21" address="0x7FFBF307C07F" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x3C07F"/><frame id="22" address="0x7FFBF01635D8" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0x135D8"/><frame id="23" address="0x7FFBF0179FAC" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0x29FAC"/><frame id="24" address="0x7FFBF0179D0A" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0x29D0A"/><frame id="25" address="0x7FFBF015CF2F" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xCF2F"/><frame id="26" address="0x7FFBF015D37C" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xD37C"/><frame id="27" address="0x7FFBF015E008" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xE008"/><frame id="28" address="0x7FFBF015CCCC" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xCCCC"/><frame id="29" address="0x7FFBF0F8491E" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xE3491E"/><frame id="30" address="0x7FFBF0FAC63F" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xE5C63F"/><frame id="31" address="0x7FFBF0FACE9A" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xE5CE9A"/><frame id="32" address="0x7FFBF0167FD3" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0x17FD3"/><frame id="33" address="0x7FFBF015AA3F" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xAA3F"/><frame id="34" address="0x7FFBF015AB60" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xAB60"/><frame id="35" address="0x7FFC0023897B" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x897B"/></stackFrames><executionStack><frame procname="adhoc" line="1" stmtstart="244" stmtend="718" sqlhandle="0x02000000b59cd339461a580b50b03903e3bac01b22c3f2d80000000000000000000000000000000000000000">
unknown    </frame><frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@0 bigint,@RequestId nvarchar(4000),@StartedDate datetime,@LastActivityDate datetime,@State nvarchar(6),@ParentId bigint)UPDATE "BackgroundJob" SET "RequestId"=@RequestId, "StartedDate"=@StartedDate, "LastActivityDate"=@LastActivityDate, "State"=@State, "ParentId"=@ParentId WHERE ((("CompletedDate" is null) AND ("RequestId" is null)) AND ("DependsOn" = @0))   </inputbuf></process><process id="process2bfc3ecb848" taskpriority="0" logused="2988" waitresource="KEY: 6:72057594055557120 (d07387f87422)" waittime="1120" ownerId="322702738" transactionname="user_transaction" lasttranstarted="2026-08-18T12:44:41.660" XDES="0x2c24064c470" lockMode="U" schedulerid="2" kpid="29352" status="suspended" spid="129" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2026-08-18T12:44:41.660" lastbatchcompleted="2026-08-18T12:44:41.660" lastattention="1900-01-01T00:00:00.660" clientapp="PE" hostpid="46928"  isolationlevel="read committed (2)" xactid="322702738" currentdb="6" currentdbname="PEDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"><stackFrames><frame id="00" address="0x7FFC21E43B04" pdb="ntdll.pdb" age="1" guid="801276B1-B18E-B373-48F4-08A0D236E8E8" module="ntdll" rva="0xA3B04"/><frame id="01" address="0x7FFC1F6E8419" pdb="kernelbase.pdb" age="1" guid="2F04B864-E806-2D22-79C1-978312F4F54A" module="kernelbase" rva="0xC8419"/><frame id="02" address="0x7FFC00241817" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x11817"/><frame id="03" address="0x7FFC00241737" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x11737"/><frame id="04" address="0x7FFC00232317" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x2317"/><frame id="05" address="0x7FFC00233227" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x3227"/><frame id="06" address="0x7FFBF30DA5C9" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x9A5C9"/><frame id="07" address="0x7FFBF30DA3CD" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x9A3CD"/><frame id="08" address="0x7FFBF3050864" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x10864"/><frame id="09" address="0x7FFBF3059448" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x19448"/><frame id="10" address="0x7FFBF31446D7" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x1046D7"/><frame id="11" address="0x7FFBF3059B22" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x19B22"/><frame id="12" address="0x7FFBF30500B0" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x100B0"/><frame id="13" address="0x7FFBF306D36B" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D36B"/><frame id="14" address="0x7FFBF306D286" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D286"/><frame id="15" address="0x7FFBF30DD42F" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x9D42F"/><frame id="16" address="0x7FFBF306D189" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D189"/><frame id="17" address="0x7FFBF30C2915" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x82915"/><frame id="18" address="0x7FFBF306D189" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D189"/><frame id="19" address="0x7FFBF30C269B" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x8269B"/><frame id="20" address="0x7FFBF306D189" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x2D189"/><frame id="21" address="0x7FFBF307C07F" pdb="sqlmin.pdb" age="2" guid="185BC1F4-BACE-4CB0-890B-28E2BDF37D56" module="sqlmin" rva="0x3C07F"/><frame id="22" address="0x7FFBF01635D8" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0x135D8"/><frame id="23" address="0x7FFBF0179FAC" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0x29FAC"/><frame id="24" address="0x7FFBF0179D0A" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0x29D0A"/><frame id="25" address="0x7FFBF015CF2F" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xCF2F"/><frame id="26" address="0x7FFBF015D37C" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xD37C"/><frame id="27" address="0x7FFBF015E008" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xE008"/><frame id="28" address="0x7FFBF015CCCC" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xCCCC"/><frame id="29" address="0x7FFBF0F8491E" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xE3491E"/><frame id="30" address="0x7FFBF0FAC63F" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xE5C63F"/><frame id="31" address="0x7FFBF0FACE9A" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xE5CE9A"/><frame id="32" address="0x7FFBF0167FD3" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0x17FD3"/><frame id="33" address="0x7FFBF015AA3F" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xAA3F"/><frame id="34" address="0x7FFBF015AB60" pdb="sqllang.pdb" age="2" guid="3C9A1A89-327E-4E8A-8685-0E076EA3893E" module="sqllang" rva="0xAB60"/><frame id="35" address="0x7FFC0023897B" pdb="SqlDK.pdb" age="2" guid="A54785C7-8660-42A7-8BBD-A08C3DA03954" module="SqlDK" rva="0x897B"/></stackFrames><executionStack><frame procname="adhoc" line="1" stmtstart="244" stmtend="718" sqlhandle="0x02000000b59cd339461a580b50b03903e3bac01b22c3f2d80000000000000000000000000000000000000000">
unknown    </frame><frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@0 bigint,@RequestId nvarchar(4000),@StartedDate datetime,@LastActivityDate datetime,@State nvarchar(6),@ParentId bigint)UPDATE "BackgroundJob" SET "RequestId"=@RequestId, "StartedDate"=@StartedDate, "LastActivityDate"=@LastActivityDate, "State"=@State, "ParentId"=@ParentId WHERE ((("CompletedDate" is null) AND ("RequestId" is null)) AND ("DependsOn" = @0))   </inputbuf></process></process-list><resource-list><keylock hobtid="72057594055557120" dbid="6" objectname="PEDB.dbo.BackgroundJob" indexname="PK__Backgrou__3214EC0795FC240A" id="lock2c1ed21ee00" mode="X" associatedObjectId="72057594055557120"><owner-list><owner id="process2bfc3ecb848" mode="X"/></owner-list><waiter-list><waiter id="process2bfd80a1c28" mode="U" requestType="wait"/></waiter-list></keylock><keylock hobtid="72057594055557120" dbid="6" objectname="PEDB.dbo.BackgroundJob" indexname="PK__Backgrou__3214EC0795FC240A" id="lock2c1ef899900" mode="X" associatedObjectId="72057594055557120"><owner-list><owner id="process2bfd80a1c28" mode="X"/></owner-list><waiter-list><waiter id="process2bfc3ecb848" mode="U" requestType="wait"/></waiter-list></keylock></resource-list></deadlock>

AI tells me : dbo.BackgroundJob holds 25 rows — one page. At that size the optimizer will pick a clustered index scan over a seek for essentially any predicate, because scanning one page is cheaper than a seek plus lookup.
.. Wait. I had already created this index
create index idx_backgroundjob_dependson_pending on dbo.BackgroundJob (DependsOn, Id) where [CompletedDate] IS NULL AND [RequestId] IS NULL AND [DependsOn] IS NOT NULL
before yours and the plan show it takes mine. I will delete and see it yours does better.

jbrabant.dev · August 18, 2026, 5:30pm

With my Index, it was using the seek and not a scan so it was better. Yours, it is rejected and a scan occurs. But either way, I get deadlocks anyway.

Claude told me:

Ran SHOWPLAN for this exact statement. No UnmatchedIndexes warning this time — plan is:
Index Seek idx_backgroundjob_dependson_pending (nonclustered, filtered)
→ Nested Loop →
Clustered Index Seek PK__Backgrou… (row lookup by Id)
→ Clustered Index Update
Optimizer does match and seek the filtered index here, because this statement’s WHERE clause (CompletedDate IS NULL AND RequestId IS NULL AND DependsOn = @0) exactly implies the index’s filter predicate. TableCardinality 23 rows, cost trivial. No scan anywhere. So: nothing to force, index already doing its job for this query.

(Separately checked CancelDependentJobs’s SELECT WHERE DependsOn = @jobId — that one DOES get UnmatchedIndexes on this same filtered index, since it doesn’t reference CompletedDate/RequestId at all, so the optimizer can’t prove the filter holds. It falls back to a different plain index and still seeks fine. Not your deadlock path, just noting it’s dead weight for that query shape.)

Real cause: this is a genuine write-write conflict between two concurrent transactions each updating a different row of the same 23-row table, deadlocking on clustered-index key order — not a missing-seek problem. [Worker] serializes jobs of the same command type; it does nothing for SQL Server’s 3-second DispatchPendingJobs poll tick and job-completion cascade code (the dependency-activation UPDATE above), which fire from multiple worker queues independently and can still cross lock order on this shared table. Forcing/adding an index can’t fix a lock-ordering race between two already-efficient seeks.

mythz · August 18, 2026, 5:32pm

ok in that case we can eliminate the tablescan lock on the update by doing a SELECT (i.e. lock-free) and only update if there are any to update by specifying the PKs which avoids the scan, in this commit.

This change is available from v10.1.5+ that’s now available in pre-release packages.

jbrabant.dev · August 18, 2026, 6:28pm

I’m still amazed by that exceptional level of support.
No more deadlocks, thanks again and long live to SS!