I'm studying about "Transaction and concurrency","Deadlock" and related stuff for some time. I have a question regarding this matter.
The situation is that two records (AccountNumber
AW00012597
and AW00012596
) are updated by two different sessions :
SESSION 60:
begin transaction update CustomerTest set ModifiedDate = getdate() where accountnumber = 'AW00012597'
SESSION 63:
begin transaction update CustomerTest set ModifiedDate = getdate() where accountnumber = 'AW00012596'
The problem is when I put a clustered index on column AccountNumber
(Or set this column as PrimaryKey) , Both Transaction complete without any problem but when I drop the index (When the table doesn't have any indexes or primarykeys) session 63 gets blocked !!
I want to know what exactly is the reason behind this? Cause the transactions are updating different rows and they are holding "Exclusive Locks" on different rows (According to what I've read so far) . So what would be the effect of a clustered index?
Thank in advance