patternsqlMinor
Deadlocks on same wait resource
Viewed 0 times
samewaitresourcedeadlocks
Problem
I am experiencing a deadlock in SQL Server, but I do not understand why this is a deadlock. It looks like the two resources are actually the same. Meaning that they each have the lock and are asking for the same lock again and this is flagged by SQL as a deadlock.
The table:
The deadlock (Note, that the updates are not updating the same row):
```
UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1
unknown
(@p0 datetimeoffset(7),@p1 bigint)UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1
UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1
unknown
(@p0 datetimeoffset(7),@p1 bigint)UPDATE [Device] SET Last
The table:
CREATE TABLE [dbo].[Device](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[DeviceType] [nvarchar](255) NOT NULL,
[ControllerIdentifier] [nvarchar](255) NULL,
[Created] [datetimeoffset](7) NULL,
[LastModified] [datetimeoffset](7) NULL,
[DeviceId] [nvarchar](255) NULL,
[TypeDescription] [nvarchar](255) NULL,
[IpAddress] [nvarchar](255) NULL,
[Number] [bigint] NULL,
[SerialNumber] [bigint] NULL,
[SoftwareVersionApplication] [nvarchar](255) NULL,
[Type] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[StateUpdated] [datetimeoffset](7) NULL,
[MainType] [nvarchar](255) NULL,
[SubType] [nvarchar](255) NULL,
[Firmware] [nvarchar](255) NULL,
[ConfigurationUpdated] [datetimeoffset](7) NULL,
[ConsumptionType] [nvarchar](255) NULL,
[VendorId] [nvarchar](255) NULL,
[Owner] [nvarchar](255) NULL,
[SupportsAutoCollection] [bit] NULL,
[RepeaterStatus] [int] NULL,
[SoftwareVersion] [int] NULL,
[EnergyConsumption] [int] NULL,
[NetworkId] [int] NULL,
[AmbientTemperature] [int] NULL,
[InstallationText] [nvarchar](255) NULL,
[RepeaterListCapacity] [int] NULL,
[RepeaterListRevision] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]The deadlock (Note, that the updates are not updating the same row):
```
UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1
unknown
(@p0 datetimeoffset(7),@p1 bigint)UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1
UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1
unknown
(@p0 datetimeoffset(7),@p1 bigint)UPDATE [Device] SET Last
Solution
The code below will give you the two rows which were involved in the deadlock, which is snipped from The Curious Case of the Dubious Deadlock and the Not So Logical Lock
Good backgrounder on %%lockres%% from @RemusRusanu in Lockres collision probability marker.
SELECT *, %%lockres%%
FROM UtiliDriver.dbo.Device WITH (NOLOCK)
WHERE %%lockres%% IN('(7f25ce0760b0)','(94675634c2ef)')Good backgrounder on %%lockres%% from @RemusRusanu in Lockres collision probability marker.
Code Snippets
SELECT *, %%lockres%%
FROM UtiliDriver.dbo.Device WITH (NOLOCK)
WHERE %%lockres%% IN('(7f25ce0760b0)','(94675634c2ef)')Context
StackExchange Database Administrators Q#37100, answer score: 6
Revisions (0)
No revisions yet.