I have an application that uses an SQL transaction to make updates to the msdb database on SQL Server. This transaction is executed periodically, at irregular intervals. SQL Server 2012 (SP4) is used. After a while, SQL Server reports deadlocks on msdb.dbo.backupset table, and the only participants are the processes that execute the mentioned SQL transaction
Below is a functional example of the transaction, having the arguments replaced with example values so that it can be readily executed on msdb. The application passes new arguments with each call of the transaction.
BEGIN TRAN UpdateMediaTables
DECLARE @SqlVersion int
DECLARE @LogDevName nvarchar(512)
DECLARE @MediaSetId int
DECLARE @FamilySequenceNumber tinyint
DECLARE @MaxFamilySequence tinyint
DECLARE @Mirror tinyint
DECLARE @BackupFileName nvarchar(1024)
DECLARE @BackupFileNumber int
DECLARE @MediaFamilyCount int
DECLARE @MirrorCount int
DECLARE @CompressedSize numeric(20, 0)
DECLARE @sql as nvarchar(4000)
SET @SqlVersion = @@microsoftversion / 0x1000000
SET @MediaSetId = 3498233
SET @BackupFileNumber = 1
SET @MediaFamilyCount = 1
SET @MirrorCount = 1
SET @CompressedSize = 378880
SELECT @LogDevName = name
FROM master..sysdevices AS sdev
WHERE phyname = @BackupFileName
SET @sql = N'UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount'
IF @SqlVersion > 9 -- 2008
BEGIN
SET @sql = @sql N', compressed_backup_size = @CompressedSize'
END
SET @sql = @sql N' FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId'
EXEC sp_executesql @sql, N'@MediaFamilyCount int, @BackupFileNumber int, @CompressedSize numeric(20, 0), @MediaSetId int', @BackupFileNumber = @BackupFileNumber,@MediaFamilyCount = @MediaFamilyCount, @CompressedSize = @CompressedSize, @MediaSetId = @MediaSetId
UPDATE bms
SET media_family_count = @MediaFamilyCount, mirror_count = @MirrorCount
FROM msdb..backupmediaset AS bms
WHERE media_set_id = @MediaSetId
-- All bmf's
SET @FamilySequenceNumber = 1
SET @BackupFileName = N'\\testcase\Test_TLog_20211121180001.trn'
SET @Mirror = 0
UPDATE bmf
SET bmf.physical_device_name = @BackupFileName
FROM msdb..backupmediafamily AS bmf
WHERE bmf.media_set_id = @MediaSetId
AND bmf.family_sequence_number = @FamilySequenceNumber
AND bmf.mirror = @Mirror
UPDATE bmf
SET bmf.logical_device_name = @LogDevName
FROM msdb..backupmediafamily AS bmf
WHERE bmf.media_set_id = @MediaSetId
AND bmf.family_sequence_number = @FamilySequenceNumber
AND bmf.mirror = @Mirror
SET @MaxFamilySequence = 1
DELETE FROM msdb..backupmediafamily WITH (ROWLOCK)
WHERE media_set_id = @MediaSetId AND family_sequence_number > @MaxFamilySequence
COMMIT TRAN UpdateMediaTables
Here is the xml for the deadlock graph:
<deadlock>
<victim-list>
<victimProcess id="process2d890c8"/>
</victim-list>
<process-list>
<process id="process2d890c8" taskpriority="-5" logused="0" waitresource="PAGE: 4:1:8623 " waittime="1550" ownerId="1566457106" transactionname="UpdateMediaTables" lasttranstarted="2021-12-21T15:00:05.697" XDES="0xb16d09b80" lockMode="U" schedulerid="8" kpid="19856" status="suspended" spid="488" sbid="0" ecid="5" priority="5" trancount="0" lastbatchstarted="2021-12-21T15:00:05.660" lastbatchcompleted="2021-12-21T15:00:05.633" lastattention="1900-01-01T00:00:00.633" clientapp="Demo" hostname="AA-SQL1" hostpid="14200" isolationlevel="read uncommitted (1)" xactid="1566457106" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="190" sqlhandle="0x020000000aac851a57d841f58dc63ba4bbe4b4059b68c6ab0000000000000000000000000000000000000000">
UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount, compressed_backup_size = @CompressedSize FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="adhoc" line="36" stmtstart="1996" stmtend="2528" sqlhandle="0x02000000a5af092360e1ef5edb55dab1b8cfba609a9b02e50000000000000000000000000000000000000000">
EXEC sp_executesql @sql, N'@MediaFamilyCount int, @BackupFileNumber int, @CompressedSize numeric(20, 0), @MediaSetId int', @BackupFileNumber = @BackupFileNumber,@MediaFamilyCount = @MediaFamilyCount, @CompressedSize = @CompressedSize, @MediaSetId = @MediaSetId </frame>
</executionStack>
<inputbuf>
BEGIN TRAN UpdateMediaTables
DECLARE @SqlVersion int
DECLARE @LogDevName nvarchar(512)
DECLARE @MediaSetId int
DECLARE @FamilySequenceNumber tinyint
DECLARE @MaxFamilySequence tinyint
DECLARE @Mirror tinyint
DECLARE @BackupFileName nvarchar(1024)
DECLARE @BackupFileNumber int
DECLARE @MediaFamilyCount int
DECLARE @MirrorCount int
DECLARE @CompressedSize numeric(20, 0)
DECLARE @sql as nvarchar(4000)
SET @SqlVersion = @@microsoftversion / 0x1000000
SET @MediaSetId = 566952
SET @BackupFileNumber = 1
SET @MediaFamilyCount = 1
SET @MirrorCount = 1
SET @CompressedSize = 156160
SELECT @LogDevName = name
FROM master..sysdevices AS sdev
WHERE phyname = @BackupFileName
SET @sql = N'UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount'
IF @SqlVersion > 9 -- 2008
BEGIN
SET @sql = @sql N', compressed_backup_size = @CompressedSize'
END
SET @sql = @sql N' FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId'
EXEC sp_executesql @sql, </inputbuf>
</process>
<process id="process2daa928" taskpriority="-5" logused="0" waitresource="PAGE: 4:1:9263 " waittime="1550" ownerId="1566457106" transactionname="UpdateMediaTables" lasttranstarted="2021-12-21T15:00:05.697" XDES="0x14b82acbd0" lockMode="U" schedulerid="1" kpid="8248" status="suspended" spid="488" sbid="0" ecid="6" priority="5" trancount="0" lastbatchstarted="2021-12-21T15:00:05.660" lastbatchcompleted="2021-12-21T15:00:05.633" lastattention="1900-01-01T00:00:00.633" clientapp="Demo" hostname="AA-SQL1" hostpid="14200" isolationlevel="read uncommitted (1)" xactid="1566457106" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="190" sqlhandle="0x020000000aac851a57d841f58dc63ba4bbe4b4059b68c6ab0000000000000000000000000000000000000000">
UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount, compressed_backup_size = @CompressedSize FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="adhoc" line="36" stmtstart="1996" stmtend="2528" sqlhandle="0x02000000a5af092360e1ef5edb55dab1b8cfba609a9b02e50000000000000000000000000000000000000000">
EXEC sp_executesql @sql, N'@MediaFamilyCount int, @BackupFileNumber int, @CompressedSize numeric(20, 0), @MediaSetId int', @BackupFileNumber = @BackupFileNumber,@MediaFamilyCount = @MediaFamilyCount, @CompressedSize = @CompressedSize, @MediaSetId = @MediaSetId </frame>
</executionStack>
<inputbuf>
BEGIN TRAN UpdateMediaTables
DECLARE @SqlVersion int
DECLARE @LogDevName nvarchar(512)
DECLARE @MediaSetId int
DECLARE @FamilySequenceNumber tinyint
DECLARE @MaxFamilySequence tinyint
DECLARE @Mirror tinyint
DECLARE @BackupFileName nvarchar(1024)
DECLARE @BackupFileNumber int
DECLARE @MediaFamilyCount int
DECLARE @MirrorCount int
DECLARE @CompressedSize numeric(20, 0)
DECLARE @sql as nvarchar(4000)
SET @SqlVersion = @@microsoftversion / 0x1000000
SET @MediaSetId = 566952
SET @BackupFileNumber = 1
SET @MediaFamilyCount = 1
SET @MirrorCount = 1
SET @CompressedSize = 156160
SELECT @LogDevName = name
FROM master..sysdevices AS sdev
WHERE phyname = @BackupFileName
SET @sql = N'UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount'
IF @SqlVersion > 9 -- 2008
BEGIN
SET @sql = @sql N', compressed_backup_size = @CompressedSize'
END
SET @sql = @sql N' FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId'
EXEC sp_executesql @sql, </inputbuf>
</process>
<process id="process2bb4558" taskpriority="-5" logused="328" waitresource="PAGE: 4:1:9263 " waittime="948" ownerId="1566456428" transactionname="UpdateMediaTables" lasttranstarted="2021-12-21T15:00:04.410" XDES="0x14981ffb80" lockMode="U" schedulerid="7" kpid="19408" status="suspended" spid="470" sbid="0" ecid="7" priority="5" trancount="0" lastbatchstarted="2021-12-21T15:00:04.393" lastbatchcompleted="2021-12-21T15:00:04.393" lastattention="1900-01-01T00:00:00.393" clientapp="Demo" hostname="AA-SQL1" hostpid="14732" isolationlevel="read uncommitted (1)" xactid="1566456428" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="190" sqlhandle="0x020000000aac851a57d841f58dc63ba4bbe4b4059b68c6ab0000000000000000000000000000000000000000">
UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount, compressed_backup_size = @CompressedSize FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="adhoc" line="36" stmtstart="1996" stmtend="2528" sqlhandle="0x02000000a9093d2c0a0d22ed2f73dc20e182fd1d912553b10000000000000000000000000000000000000000">
EXEC sp_executesql @sql, N'@MediaFamilyCount int, @BackupFileNumber int, @CompressedSize numeric(20, 0), @MediaSetId int', @BackupFileNumber = @BackupFileNumber,@MediaFamilyCount = @MediaFamilyCount, @CompressedSize = @CompressedSize, @MediaSetId = @MediaSetId </frame>
</executionStack>
<inputbuf>
BEGIN TRAN UpdateMediaTables
DECLARE @SqlVersion int
DECLARE @LogDevName nvarchar(512)
DECLARE @MediaSetId int
DECLARE @FamilySequenceNumber tinyint
DECLARE @MaxFamilySequence tinyint
DECLARE @Mirror tinyint
DECLARE @BackupFileName nvarchar(1024)
DECLARE @BackupFileNumber int
DECLARE @MediaFamilyCount int
DECLARE @MirrorCount int
DECLARE @CompressedSize numeric(20, 0)
DECLARE @sql as nvarchar(4000)
SET @SqlVersion = @@microsoftversion / 0x1000000
SET @MediaSetId = 566948
SET @BackupFileNumber = 1
SET @MediaFamilyCount = 1
SET @MirrorCount = 1
SET @CompressedSize = 259072
SELECT @LogDevName = name
FROM master..sysdevices AS sdev
WHERE phyname = @BackupFileName
SET @sql = N'UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount'
IF @SqlVersion > 9 -- 2008
BEGIN
SET @sql = @sql N', compressed_backup_size = @CompressedSize'
END
SET @sql = @sql N' FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId'
EXEC sp_executesql @sql, </inputbuf>
</process>
<process id="process2daacf8" taskpriority="-5" logused="328" waitresource="PAGE: 4:1:8623 " waittime="972" ownerId="1566456428" transactionname="UpdateMediaTables" lasttranstarted="2021-12-21T15:00:04.410" XDES="0x14b82ace80" lockMode="U" schedulerid="1" kpid="7048" status="suspended" spid="470" sbid="0" ecid="4" priority="5" trancount="0" lastbatchstarted="2021-12-21T15:00:04.393" lastbatchcompleted="2021-12-21T15:00:04.393" lastattention="1900-01-01T00:00:00.393" clientapp="Demo" hostname="AA-SQL1" hostpid="14732" isolationlevel="read uncommitted (1)" xactid="1566456428" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="190" sqlhandle="0x020000000aac851a57d841f58dc63ba4bbe4b4059b68c6ab0000000000000000000000000000000000000000">
UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount, compressed_backup_size = @CompressedSize FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="adhoc" line="36" stmtstart="1996" stmtend="2528" sqlhandle="0x02000000a9093d2c0a0d22ed2f73dc20e182fd1d912553b10000000000000000000000000000000000000000">
EXEC sp_executesql @sql, N'@MediaFamilyCount int, @BackupFileNumber int, @CompressedSize numeric(20, 0), @MediaSetId int', @BackupFileNumber = @BackupFileNumber,@MediaFamilyCount = @MediaFamilyCount, @CompressedSize = @CompressedSize, @MediaSetId = @MediaSetId </frame>
</executionStack>
<inputbuf>
BEGIN TRAN UpdateMediaTables
DECLARE @SqlVersion int
DECLARE @LogDevName nvarchar(512)
DECLARE @MediaSetId int
DECLARE @FamilySequenceNumber tinyint
DECLARE @MaxFamilySequence tinyint
DECLARE @Mirror tinyint
DECLARE @BackupFileName nvarchar(1024)
DECLARE @BackupFileNumber int
DECLARE @MediaFamilyCount int
DECLARE @MirrorCount int
DECLARE @CompressedSize numeric(20, 0)
DECLARE @sql as nvarchar(4000)
SET @SqlVersion = @@microsoftversion / 0x1000000
SET @MediaSetId = 566948
SET @BackupFileNumber = 1
SET @MediaFamilyCount = 1
SET @MirrorCount = 1
SET @CompressedSize = 259072
SELECT @LogDevName = name
FROM master..sysdevices AS sdev
WHERE phyname = @BackupFileName
SET @sql = N'UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount'
IF @SqlVersion > 9 -- 2008
BEGIN
SET @sql = @sql N', compressed_backup_size = @CompressedSize'
END
SET @sql = @sql N' FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId'
EXEC sp_executesql @sql, </inputbuf>
</process>
<process id="process12ee786188" taskpriority="-5" logused="10000" waittime="730" schedulerid="12" kpid="1808" status="suspended" spid="470" sbid="0" ecid="0" priority="5" trancount="2" lastbatchstarted="2021-12-21T15:00:04.393" lastbatchcompleted="2021-12-21T15:00:04.393" lastattention="1900-01-01T00:00:00.393" clientapp="Demo" hostname="AA-SQL1" hostpid="14732" loginname="DEMO\test" isolationlevel="read uncommitted (1)" xactid="1566456428" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="190" sqlhandle="0x020000000aac851a57d841f58dc63ba4bbe4b4059b68c6ab0000000000000000000000000000000000000000">
UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount, compressed_backup_size = @CompressedSize FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="adhoc" line="36" stmtstart="1996" stmtend="2528" sqlhandle="0x02000000a9093d2c0a0d22ed2f73dc20e182fd1d912553b10000000000000000000000000000000000000000">
EXEC sp_executesql @sql, N'@MediaFamilyCount int, @BackupFileNumber int, @CompressedSize numeric(20, 0), @MediaSetId int', @BackupFileNumber = @BackupFileNumber,@MediaFamilyCount = @MediaFamilyCount, @CompressedSize = @CompressedSize, @MediaSetId = @MediaSetId </frame>
</executionStack>
<inputbuf>
BEGIN TRAN UpdateMediaTables
DECLARE @SqlVersion int
DECLARE @LogDevName nvarchar(512)
DECLARE @MediaSetId int
DECLARE @FamilySequenceNumber tinyint
DECLARE @MaxFamilySequence tinyint
DECLARE @Mirror tinyint
DECLARE @BackupFileName nvarchar(1024)
DECLARE @BackupFileNumber int
DECLARE @MediaFamilyCount int
DECLARE @MirrorCount int
DECLARE @CompressedSize numeric(20, 0)
DECLARE @sql as nvarchar(4000)
SET @SqlVersion = @@microsoftversion / 0x1000000
SET @MediaSetId = 566948
SET @BackupFileNumber = 1
SET @MediaFamilyCount = 1
SET @MirrorCount = 1
SET @CompressedSize = 259072
SELECT @LogDevName = name
FROM master..sysdevices AS sdev
WHERE phyname = @BackupFileName
SET @sql = N'UPDATE bs SET position = @BackupFileNumber, last_family_number = @MediaFamilyCount'
IF @SqlVersion > 9 -- 2008
BEGIN
SET @sql = @sql N', compressed_backup_size = @CompressedSize'
END
SET @sql = @sql N' FROM msdb..backupset AS bs
WHERE media_set_id = @MediaSetId'
EXEC sp_executesql @sql, </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="8623" dbid="4" subresource="FULL" objectname="msdb.dbo.backupset" id="lock5e1d530780" mode="U" associatedObjectId="72057594045595648">
<owner-list>
<owner id="process12ee786188" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process2d890c8" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="9263" dbid="4" subresource="FULL" objectname="msdb.dbo.backupset" id="lockf1b4c0980" mode="U" associatedObjectId="72057594045595648">
<owner-list>
<owner id="process12ee786188" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process2daa928" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="9263" dbid="4" subresource="FULL" objectname="msdb.dbo.backupset" id="lockf1b4c0980" mode="U" associatedObjectId="72057594045595648">
<owner-list>
<owner id="process2daa928" mode="U" requestType="wait"/>
</owner-list>
<waiter-list>
<waiter id="process2bb4558" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="8623" dbid="4" subresource="FULL" objectname="msdb.dbo.backupset" id="lock5e1d530780" mode="U" associatedObjectId="72057594045595648">
<owner-list>
<owner id="process2d890c8" mode="U" requestType="wait"/>
</owner-list>
<waiter-list>
<waiter id="process2daacf8" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe25c250400" WaitType="e_waitPipeGetRow" nodeId="2">
<owner-list>
<owner id="process2daacf8"/>
<owner id="process2bb4558"/>
</owner-list>
<waiter-list>
<waiter id="process12ee786188"/>
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
I don't see how this situation could lead to a deadlock. Even if the two processes coincide in parallel, the two transactions are identical and therefore execute their queries in the same sequential order. Furthermore, the query that updates the backupset table doesn't have who to deadlock with, since it is the only one in the entire transaction that queries this table. Lock yes, but not deadlock. Can someone please help me understand how this deadlock situation is possible?
CodePudding user response：
Instead of diagnosing this one, I would just hit the "easy button", and serialize the transactions with an application lock:
BEGIN TRAN UpdateMediaTables
exec sp_getapplock 'UpdateMediaTables', 'Exclusive'
. . .
Then the second session will be blocked before acquiring any locks until the first is complete.