Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
3,023 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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

926 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