The effect of a Primary Key on a blocking situation

Question

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

Answer 1

_{Community wiki answer - please edit to improve:}

The reason is when you create an index, either specifically or by declaring it as primary key (which internally creates an index to enforce the constraint), it makes both update statements fast, and they finish quickly without blocking.

With no index, each query has to scan all the records to locate what you have specified in the where clause, and thus there is blocking, which I guess would eventually go. Or it's lock escalation: without the index, it has to escalate to a table lock, and that's what blocks. You really need to look at what resource is being held by the block/deadlock to see what is happening.