%%LOCKRES%% - SQL Server - Row identifier causing Deadlocks

Question

Hi Team,

 

Recently I came across an issue where I am facing deadlocks in SQL Server databases (19.2.56.2) due to the internal hashing mechanism of the Row identifier virtual column - %%LOCKRES%%

 

On analysis I find that the row identifier - %%LOCKRES%% generates a hash value with the values of all the columns of the row belonging only to the NVARCHAR data types.

 

So the scenario where I had deadlocks is when there are two or multiple rows trying to be inserted in different sessions but the concatenated value across the NVARCHAR data type fields of each row insert are same, so internally each of these row insert generates the same hashvalue (LOCKRES row identifier) and when executed parallelly these transactions are deadlocked as it is virtually trying to insert the same row over and over as they have the same row identifier, instead of virtually inserting as different rows.

 

The hash value changes when I modified the data type to INT for one of the columns and there were no Deadlocks.

 

The below table shows an fictitious example based on real simulations

COLUMN 1(NVARCHAR)COLUMN 2(NVARCHAR)COLUMN 3(DATE)Concat of String based fields COLUMN 1 and COLUMN 2%%LOCKRES%%AB123.12112/2/2023AB123.1215694ad83e25sAB123.12112/2/2023AB123.1215694ad83e25sHere the concat of Row 1 and Row 2 is same and hence the same LOCKRES hash value is generated.

 

This feature has very less documentation so requesting for support on this behaviour and if possible, a solution to mitigate deadlocks in this scenario.

Answer 1

Yes. the lock resource is a hash, two different rows may get the same lock-resource value, and therefore with some bad luck, you can get blocking or even deadlocks with unrelated rows, because they have the same lock hash.

However, it is not correct that the hash is computed from nvarchar values only. The lock hash is computed from the key values. Look at this:

CREATE TABLE nisse (Col1 nvarchar(20) NOT NULL,
                    Col2 nvarchar(20) NOT NULL,
                    Col3 date         NOT NULL,
                    PRIMARY KEY(Col1, Col2, Col3))
go
INSERT nisse (Col1, Col2, Col3)
   VALUES('AB123.12', '1',  '2023-02-12'),
         ('AB123.1',  '21', '2023-02-12'),
         ('AB123.1',  '21', '2023-12-12')
go
SELECT *, %%LOCKRES%%
FROM  nisse
go
DROP TABLE nisse

The rows with the same date indeed has the same lock resource because of the accident with the strings. But the row with a different date also has a different lock resource.

It is difficult to give advice how you should deal with your deadlocks, since I don't know about your application. But I as said, I think it takes a bit of bad luck to end up in this situation, and some trickery may be needed to work around it.