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.
Comments
Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. Thanks a lot......
SQL Server
Thanks a lot..