question

satya avatar image
satya asked

Dead Lock Error Messages ?

Hi All, In sql server on dead lock victim, server will give two types of error messages like below, can anyone please tell me what is the difference between these two error messages and on what basis it will throw one of these, 1. Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 2. Transaction (Process ID 52) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
deadlock
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you Jonathan and Kev Rilley.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
A deadlock is where two processes are trying to edit the same thing. The error messages relate to the victim process and the winner process. In order to complete some work the SQL Server decides one of the process must fail and the other succeed. To simulate it you need 2 SSMS editor windows open with code as follows: In editor window # 1 \=================== USE [AdventureWorks] GO -- step #1 create some objects ------------------------------ CREATE TABLE dbo.dead ( col1 INT ); INSERT dbo.dead SELECT 1 ; CREATE TABLE dbo.lock ( col1 INT ); INSERT dbo.lock SELECT 1 ; -- step #2 start a transaction with an -- update and then start to other SQL process ---------------------------------------------- BEGIN TRAN UPDATE dbo.dead SET col1 = 1 switch to editor window #2 \========================= USE [AdventureWorks] GO -- step #3 (in second connection) with a transaction already started -- in the other window, this TSQL will wait until the other transaction -- completes before getting to complete itself. It is locked BEGIN TRAN UPDATE dbo.lock SET col1 = 1 UPDATE dbo.dead SET col1 = 1 switch back to SSMS editor window #1 \=================================== -- step #4 - TSQL in this transaction has precedence over process that started -- a later transaction so, executing something from here -- will cause the later process to be selected as the victim ----------------------------------------------------------------------------- UPDATE dbo.lock SET col1 = 1 -- clean up PRINT @@TRANCOUNT IF @@TRANCOUNT > 0 ROLLBACK for more detail and howt oresolve deadlocks I'd recommend reading the details here : http://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. But I am sure this is a typo 'A deadlock is where two processes are trying to "edit the same thing"' as it is not necessary. I remember an example given by you somewhere else :), but it would take some time for me to search it.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
The part of the error message between 'was deadlocked on' and 'resources' is chosen at runtime by SQL to give you an indication of the type(s) of deadlock occurring. The valid values here are: - lock - communication buffer (typical of parallelism deadlocks) - thread
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you please explain it in detail or please provide me any references.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.