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 (
AW00012596) are updated by two different sessions :
begin transaction update CustomerTest set ModifiedDate = getdate() where accountnumber = 'AW00012597'
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