Deadlock in sql

Question

What is Deadlock in sql?

keyur · Answer

Hello Transaction A BEGIN TRANSACTION UPDATE Customer SET LastName = 'John' WHERE CustomerId=111 WAITFOR DELAY '00:00:05' -- Wait for 5 ms UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221 COMMIT TRANSACTION Tsansaction B BEGIN TRANSACTION UPDATE Orders SET ShippingId = 12 WHERE OrderId = 221 WAITFOR DELAY '00:00:05' -- Wait for 5 ms UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111 COMMIT TRANSACTION If both the transactions are executed at the same time then Transaction A locks and updates Customer table whereas transaction B locks and updates Orders table. After a delay of 5 ms transaction A looks for the lock on Orders table which is already held by transaction B and transaction B looks for lock on Customer table which is held by transaction A. So both the transactions cannot proceed further the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction

keyur · Answer

Hello Can you please tell me is this help you to understand or not ?

Rakesh · Answer

Deadlocking occurs when two or more SQL Server processes have locks on separate database objects and each process is trying to acquire a lock on an object that the other processes have previously locked. For example process one has an exclusive lock on object one process two has an exclusive lock on object two and process one also wants an exclusive lock on object two and object two wants an exclusive lock on object one. Because two processes can't have an exclusive lock on the same object at the same time the two processes become entangled in a deadlock with neither process willing to yield of its own accord.