Solved

deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Posted on 2010-11-18
5
2,505 Views
Last Modified: 2012-05-10
Hello,

How can I modify this query for resolve 1205 : 40001 : com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 86) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 
Update TST_ENP.TST_ENP.ENT_ROW_COMM
Set  LDV_TECH_DATE_SUPP=CURRENT_TIMESTAMP
Where Not Exists (Select 'X'
From TST_ENP.dbo.I$_ENT_ROW_COMM_REG_650 S
Where TST_ENP.TST_ENP.ENT_ROW_COMM.AGE_CODE = S.AGE_CODE
And TST_ENP.TST_ENP.ENT_ROW_COMM.DEV_CODE = S.DEV_CODE
And TST_ENP.TST_ENP.ENT_ROW_COMM.LDV_NUM_INTERNE = S.LDV_NUM_INTERNE
)
And  LDV_TECH_DATE_SUPP Is Null
And  REG_CODE = SubString('REG_650', 5, 3)
 
Thanks

Regards

bibi
0
Comment
Question by:bibi92
  • 3
5 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34163035
Obviously this is a single statement. And i hear you say how can a single statement cause a deadlock. The answer..... Paralellism.

Try

Update TST_ENP.TST_ENP.ENT_ROW_COMM
Set  LDV_TECH_DATE_SUPP=CURRENT_TIMESTAMP
Where Not Exists (Select 'X'
From TST_ENP.dbo.I$_ENT_ROW_COMM_REG_650 S
Where TST_ENP.TST_ENP.ENT_ROW_COMM.AGE_CODE = S.AGE_CODE
And TST_ENP.TST_ENP.ENT_ROW_COMM.DEV_CODE = S.DEV_CODE
And TST_ENP.TST_ENP.ENT_ROW_COMM.LDV_NUM_INTERNE = S.LDV_NUM_INTERNE
)
And  LDV_TECH_DATE_SUPP Is Null
And  REG_CODE = SubString('REG_650', 5, 3) OPTION (MAXDOP 4)

Open in new window

0
 
LVL 6

Accepted Solution

by:
subhashpunia earned 500 total points
ID: 34163124
If using MAXDOP I would prefer to set it 1. I would recommond to use NOLOCK hint in subquery as below.

Update TST_ENP.TST_ENP.ENT_ROW_COMM
Set  LDV_TECH_DATE_SUPP=CURRENT_TIMESTAMP
Where Not Exists (Select 'X'
From TST_ENP.dbo.I$_ENT_ROW_COMM_REG_650 S with (nolock)
Where TST_ENP.TST_ENP.ENT_ROW_COMM.AGE_CODE = S.AGE_CODE
And TST_ENP.TST_ENP.ENT_ROW_COMM.DEV_CODE = S.DEV_CODE
And TST_ENP.TST_ENP.ENT_ROW_COMM.LDV_NUM_INTERNE = S.LDV_NUM_INTERNE
)
And  LDV_TECH_DATE_SUPP Is Null
And  REG_CODE = SubString('REG_650', 5, 3)
OPTION (MAXDOP 1)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34163136
Doh sorry. I mis-typed. I meant to put MAXDOP 1 rather than MAXDOP 4. Mental note. Re-read post before hitting submit.
0
 

Author Closing Comment

by:bibi92
ID: 34168453
thanks bibi
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34170961
Just out of interest, what did you use to fix this issue?
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now