[SOLVED] Why can't two SQL transactions update different rows in SQL Server?

Why can't two SQL transactions update different rows in SQL Server?

Consider the following setup SQL (in Azure SQL or SQL Server):

CREATE TABLE TestTable(Id int NOT NULL PRIMARY KEY, Name nvarchar(5))

INSERT INTO TestTable(Id, Name) VALUES(1, 'T1')
INSERT INTO TestTable(Id, Name) VALUES(2, 'T2')

Now we open up two connections. In the first we run the following:

BEGIN TRAN
UPDATE TestTable SET Name = 'U' WHERE Name = 'T1'

and in the second we run the following:

BEGIN TRAN
UPDATE TestTable SET Name = 'U' WHERE Name = 'T2'

I would expect that both statements would run successfully given they are updating different rows, however the second transaction hangs and is blocked until the first transaction is completed. The transaction isolation level does not seem to make a difference.

If the WHERE clause in the UPDATE statement is based on the table's Primary Key (Id) instead of another column, both transactions are able to run simultaneously.

Why is SQL Server not able to update different rows in the same table in parallel when the condition looks at columns other than the Primary Key? And is there a way to resolve this? This sort of issue is causing deadlocks in our application.

Solution

Add an index on Name so that the UPDATE can locate the row(s) to be updated without scanning the entire table and touch unneeded rows that may be locked by other sessions:

CREATE TABLE TestTable(
    Id int NOT NULL CONSTRAINT PK_TestTable PRIMARY KEY
  , Name nvarchar(5) index idx_Name
);