Parallel updates causing deadlock when no clustered index in where clause column

Question

We came across a scenario where a deadlock occurs when attempting to update a table from two simultaneous connections twice within the same transaction and its reproducible every time the query is run on 2 query windows in SSMS. (The AccountId column is a non-clustered key)

See below.

After creating a clustered key on the AccountId column the deadlock doesn't occur anymore. What causes this behavior?

Answer 1

Without a clustered index on AccountId and non clustered index on this column, SQL Server has to lock index key and then row.

So 1st update will be successful and after update you will have only one row locked in the table.
2nd update will try to lock this rows and will be waiting on release of the lock from 1st update. It will be able to obtain key lock on the index.
3rd update will try to lock index key and will be waiting on release of the lock from 2nd update. Deadlock.

I was able to reproduce it using following table:

create table test5 (x int,y int)
insert into test5 values (10,15)
GO

insert into test5 values (11,15)
GO 10000

create index ix on test5(x)

select * from test5

begin transaction

update test5
set y = 5
where x = 10

-- wait here

update test5
set y = 5
where x = 10

rollback