Blocking and Deadlock In SQL Server With Example

Introduction
In this article we will learn what is blocking in sql and how can we see a live demo ofblocking using sql server. Also we will learn Deadlock in sql server with example. Difference between deadlock and blocking with example.

Previous Updates
In previous articles we have learnt what is connection pooling in c# . What is Lock and how to achieve lock on sql table. How high quality content affects your Website. Why every business needed digital Marketing and Why digital marketing Required. Authorization in Asp.Net. 

Blocking In SQL
Blocking occurs when a transaction tries to request the resource and use it which has already locked by other transaction. Then the blocked transaction remains blocked until the first transaction not release the lock.

Means if you have a table named TestTable1. And you made a transaction (like insert/update query) on TestTable1  (Transaction here is transaction1 for table TestTable1) and at the same time or after acquiring the lock by transaction1 again the request came from any Transaction which will be transaction 2 or other  then it will wait for the transaction1 to complete.
Until the transaction1 will not complete no other transaction happend on TestTable1.

-- This is Transaction1 BEGIN TRAN UPDATE EmpDetail SET Address = 'Eng' WHere ID= 4

After executing the above lines ,execute the same query on second instance of sql server.

-- This is Transaction2 BEGIN TRAN UPDATE EmpDetail SET Address = 'Eng' WHere ID= 4

When you run the Transaction2 it will Executes query and will be on processing until the commit will not done for Transaction1. When Transaction1 commit done then autometically Transaction2 will executed.

-- This commit Transaction release the Transaction1 Commit Transaction

Deadlock In SQL
Deadlock is one step ahead from Blocking. In deadlock two or more transaction have a  resource locked. Means in other words you can say when two or more then two process have a locked resources then deadlock occurs.
In deadlock each process requesting for a resource which is already locked by another process. Until the resource not released by the process the other process will not locked or use that resource.
You may also interested Learn Lock In SQL
You can understand this by given below image.

Image Source -http://csharp-video-tutorials.blogspot.in

Sql Server understand the deadlock condition, so when deadlock happens sql server automatically assumes one of the process as a victim and rollback the victim process and after this rollback the another process can executed
And when this deadlock happens that time the victim process gets the message similar to this.
Msg 1205, Level 13, State 51, Line 1

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 

Best example for the deadlock practice in sql given below.
Open 2 instances of SQL server same you did with blocking. Now paste the below code on first SQL instance.

-- This is Process 1 Begin Tran UPDATE EmpDetail SET Address = 'Eng' WHere ID= 1 -- Now From Process 2 Window execute the first Begin statement -- Do not execute this before Process2 execution UPDATE EmpDetail1 SET Address = 'Eng' WHere ID= 1 -- From Transaction 2 window execute the second update statement Commit Transaction

Now on second instance of sql server paste the below given code

-- This is Process 2 Begin Tran UPDATE EmpDetail1 SET Address = 'Eng' WHere ID= 1 -- On Process 1 Window execte the second Update statment. UPDATE EmpDetail SET Address = 'Eng' WHere ID= 1 -- SQL assumes this instance as Victim and rollback the Process2 Commit Transaction

Change the tables and conditions base on your need. After doing this first run the EmpDetail table transaction on  Process 1 window. Then executes the EmpDetail1 table transaction on Process2 window.
After this executes the same table Update in Process1 window and then execute the EmpDetail update in Process2 Window. And when you hit on process2 last statement the deadlock occur. 
Because before start the execution both tables have no lock but when execution start EmpDetail is locked by Process1 and EmpDetail1 is locked by Process2. After this when Process1 wants to exute the EmpDetail1 which is laready locked by Process2 will not excuted but when you run the EmpDetail table query on Process2 window the deadlock occurs and 1 resource passed and other one rollback by SQL.