Deadlock in sql
What is Deadlock in sql?
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
keyurPosted Feb 10, 2015, 6:38 AM
Can you please tell me is this help you to understand or not ?
keyurPosted Feb 9, 2015, 11:58 PM
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
Orderstable. After a delay of 5 ms, transaction A looks for the lock onOrderstable which is already held by transaction B and transaction B looks for lock onCustomertable 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 transactionRakeshPosted Feb 9, 2015, 7:30 PM
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.