[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3173
  • 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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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