Difference between blocking and deadlocking
we will discuss the difference between blocking and deadlocking. This is one of the common SQL Server interview question. Let us understand the difference with an example.
We will be using the following 2 tables in this demo
SQL Script to create the tables and populate them with test data
Create table TableA
(
Id int identity primary key,
Name nvarchar(50)
)
Go
Insert into TableA values (‘Mark’)
Go
Create table TableB
(
Id int identity primary key,
Name nvarchar(50)
)
Go
Insert into TableB values (‘Mary’)
Go
Blocking : Occurs if a transaction tries to acquire an incompatible lock on a resource that another transaction has already locked. The blocked transaction remains blocked until the blocking transaction releases the lock. The following diagram explains this.
Example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code. Notice that Transaction 2 is blocked by Transaction 1. Transaction 2 is allowed to move forward only when Transaction 1 completes.
–Transaction 1
Begin Tran
Update TableA set Name=’Mark Transaction 1′ where Id = 1
Waitfor Delay ’00:00:10′
Commit Transaction
–Transaction 2
Begin Tran
Update TableA set Name=’Mark Transaction 2′ where Id = 1
Commit Transaction
Deadlock : Occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. So in this case, SQL Server intervenes and ends the deadlock by cancelling one of the transactions, so the other transaction can move forward. The following diagram explains this.
Example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code. Notice that there is a deadlock between Transaction 1 and Transaction 2.
— Transaction 1
Begin Tran
Update TableA Set Name = ‘Mark Transaction 1’ where Id = 1
— From Transaction 2 window execute the first update statement
pdate TableB Set Name = ‘Mary Transaction 1’ where Id = 1
— From Transaction 2 window execute the second update statement
Commit Transaction
— Transaction 2
Begin Tran
Update TableB Set Name = ‘Mark Transaction 2’ where Id = 1
— From Transaction 1 window execute the second update statement
Update TableA Set Name = ‘Mary Transaction 2’ where Id = 1
— After a few seconds notice that one of the transactions complete
— successfully while the other transaction is made the deadlock victim
Commit Transaction