Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3279
  • Last Modified:

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

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
bibi92
Asked:
bibi92
  • 3
1 Solution
 
EvilPostItCommented:
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
 
subhashpuniaCommented:
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
 
EvilPostItCommented:
Doh sorry. I mis-typed. I meant to put MAXDOP 1 rather than MAXDOP 4. Mental note. Re-read post before hitting submit.
0
 
bibi92Author Commented:
thanks bibi
0
 
EvilPostItCommented:
Just out of interest, what did you use to fix this issue?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now