asked on

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

EvilPostIt

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

ASKER CERTIFIED SOLUTION

subhashpunia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EvilPostIt

Doh sorry. I mis-typed. I meant to put MAXDOP 1 rather than MAXDOP 4. Mental note. Re-read post before hitting submit.

bibi92

ASKER

thanks bibi

EvilPostIt

Just out of interest, what did you use to fix this issue?