SQL Server Deadlock

In SQL server the situation where two transactions wait for each other to give up their respective locks is known as Deadlock.

Why deadlock?

Sometime when we need only one action should occur the other one should wait until first gets completed.

In below example both the transactions are executed at the same time, the first transaction locks and updates Employee table whereas second transaction locks and updates EmployeeAttendance table. After a delay of 10 milliseconds.

BEGIN TRANSACTION

 UPDATE Employee SET FName = 'ved' WHERE EmpId='E001'

 WAITFOR DELAY '00:00:10' -- Wait for 10 milisecond

 UPDATE EmployeeAttendance SET EmpId = 'E001' WHERE AttId = 100

 COMMIT TRANSACTION

BEGIN TRANSACTION

 UPDATE EmployeeAttendance SET EmpId = 'E002' WHERE AttId = 101

 WAITFOR DELAY '00:00:10' -- Wait for 10 milisecond

 UPDATE Employee SET FName = 'ved raj' WHERE EmpId='E002'

 COMMIT TRANSACTION

The first transaction search for the Attendance table which is locked and second search for Employee table. In this case the sql Deadlock occurred and sql server print the error message.