Deadlock, where one or more users have simultaneously locked the whole table

hi friends,

I am creating sales order shipment process using batch job from Load creation to Packing slip and I am consistently getting below error. even I have increased the schedule time to 1 hour even getting same error.

Cannot delete a record in Sales parm line information for India (SalesParmLine_IN). Reference: 0, 0. Deadlock, where one or more users have simultaneously locked the whole table or part of it. Posting Update has been cancelled.

Pls help me in this regard. Thanks in advance

B K Sharma

If you get it consistently, it probably means that your own batch is causing the deadlock.

You have to collect more information, because it seems that you don’t even know yet what commands are causing the deadlock.

If I was you, I would start with deadlock logging in SQL Server - it will tell you exactly which statements are blocking each other and what’s the locked resource. Then you should be able to locate it in AX.

Nevertheless you may also catch deadlock exceptions in AX, use AX debugger, tracing etc.

We can plan how to avoid the problem only when we know who is doing the locking. Maybe we can avoid the lock completely. Maybe we can change the order, therefore it will wait instead of deadlocking. Maybe we can add an index so it doesn’t lock the whole table. And so on.

Hi Martin, Even i got the same issue.

Cannot edit a record in Sales order line - update table (SalesParmLine). Line number: 0,0000000000.
Deadlock, where one or more users have simultaneously locked the whole table or part of it.

Any solution?

I already gave suggestions above. First think about what happened - whether it was on one-off issue, you get it consistently, what you were doing etc.

If it needs attention, start capturing deadlock details, e.g. using extended events. When you know what’s going on and you start thinking about designing a fix, consider remarks in the last paragraph of my previous reply.

below is the sql log of deadlock:

04/16/2019 17:06:40,spid27s,Unknown,waiter id=process2ab6207fc28 mode=U requestType=wait

04/16/2019 17:06:40,spid27s,Unknown,waiter-list

04/16/2019 17:06:40,spid27s,Unknown,owner id=process2ab62077c28 mode=X

04/16/2019 17:06:40,spid27s,Unknown,owner-list

04/16/2019 17:06:40,spid27s,Unknown,keylock hobtid=72057597066412032 dbid=7 objectname=MicrosoftDynamicsAX.dbo.SALESPARMLINE indexname=I_361RECID id=lock2a663dc0900 mode=X associatedObjectId=72057597066412032

04/16/2019 17:06:40,spid27s,Unknown,waiter id=process2ab62077c28 mode=U requestType=wait

04/16/2019 17:06:40,spid27s,Unknown,waiter-list

04/16/2019 17:06:40,spid27s,Unknown,owner id=process2ab6207fc28 mode=X

04/16/2019 17:06:40,spid27s,Unknown,owner-list

04/16/2019 17:06:40,spid27s,Unknown,keylock hobtid=72057597066412032 dbid=7 objectname=MicrosoftDynamicsAX.dbo.SALESPARMLINE indexname=I_361RECID id=lock2a911084900 mode=X associatedObjectId=72057597066412032

04/16/2019 17:06:40,spid27s,Unknown,resource-list

04/16/2019 17:06:40,spid27s,Unknown,(@P1 nvarchar(21)@P2 int@P3 bigint@P4 nvarchar(5)@P5 nvarchar(21)@P6 nvarchar(21)@P7 nvarchar(21))UPDATE SALESPARMLINE SET TABLEREFID=@P1RECVERSION=@P2 WHERE (((PARTITION=@P3) AND (DATAAREAID=@P4)) AND (((PARMID=@P5) AND (ORIGSALESID=@P6)) AND (TABLEREFID=@P7)))

04/16/2019 17:06:40,spid27s,Unknown,inputbuf

04/16/2019 17:06:40,spid27s,Unknown,unknown

04/16/2019 17:06:40,spid27s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

04/16/2019 17:06:40,spid27s,Unknown,unknown

04/16/2019 17:06:40,spid27s,Unknown,frame procname=adhoc line=1 stmtstart=208 stmtend=536 sqlhandle=0x0200000026a0e400c91685ae48678f82cab70369d35a62980000000000000000000000000000000000000000

04/16/2019 17:06:40,spid27s,Unknown,executionStack

04/16/2019 17:06:40,spid27s,Unknown,process id=process2ab6207fc28 taskpriority=0 logused=5748 waitresource=KEY: 7:72057597066412032 (ef20a890fc75) waittime=6278 ownerId=155027538 transactionname=user_transaction lasttranstarted=2019-04-16T17:06:33.620 XDES=0x2ab626b0430 lockMode=U schedulerid=13 kpid=5692 status=suspended spid=125 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2019-04-16T17:06:34.590 lastbatchcompleted=2019-04-16T17:06:34.560 lastattention=1900-01-01T00:00:00.560 clientapp=Microsoft Dynamics AX hostname=AXPRODAPP hostpid=27156 loginname=GLWESTSTARDUBAI\Administrator isolationlevel=read committed (2) xactid=155027538 currentdb=7 currentdbname=MicrosoftDynamicsAX lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

04/16/2019 17:06:40,spid27s,Unknown,(@P1 nvarchar(21)@P2 int@P3 bigint@P4 nvarchar(5)@P5 nvarchar(21)@P6 nvarchar(21)@P7 nvarchar(21))UPDATE SALESPARMLINE SET TABLEREFID=@P1RECVERSION=@P2 WHERE (((PARTITION=@P3) AND (DATAAREAID=@P4)) AND (((PARMID=@P5) AND (ORIGSALESID=@P6)) AND (TABLEREFID=@P7)))

04/16/2019 17:06:40,spid27s,Unknown,inputbuf

04/16/2019 17:06:40,spid27s,Unknown,unknown

04/16/2019 17:06:40,spid27s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

04/16/2019 17:06:40,spid27s,Unknown,unknown

04/16/2019 17:06:40,spid27s,Unknown,frame procname=adhoc line=1 stmtstart=208 stmtend=536 sqlhandle=0x0200000026a0e400c91685ae48678f82cab70369d35a62980000000000000000000000000000000000000000

04/16/2019 17:06:40,spid27s,Unknown,executionStack

04/16/2019 17:06:40,spid27s,Unknown,process id=process2ab62077c28 taskpriority=0 logused=5124 waitresource=KEY: 7:72057597066412032 (b61e207e9d40) waittime=6278 ownerId=155027557 transactionname=user_transaction lasttranstarted=2019-04-16T17:06:33.623 XDES=0x2a8a2824430 lockMode=U schedulerid=12 kpid=1004 status=suspended spid=120 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2019-04-16T17:06:34.590 lastbatchcompleted=2019-04-16T17:06:34.560 lastattention=1900-01-01T00:00:00.560 clientapp=Microsoft Dynamics AX hostname=AXPRODAPP hostpid=27156 loginname=GLWESTSTARDUBAI\Administrator isolationlevel=read committed (2) xactid=155027557 currentdb=7 currentdbname=MicrosoftDynamicsAX lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

04/16/2019 17:06:40,spid27s,Unknown,process-list

04/16/2019 17:06:40,spid27s,Unknown,deadlock victim=process2ab62077c28

04/16/2019 17:06:40,spid27s,Unknown,deadlock-list

Please Suggest.

below is the sql log of deadlock:

04/16/2019 17:06:40,spid27s,Unknown,waiter id=process2ab6207fc28 mode=U requestType=wait

04/16/2019 17:06:40,spid27s,Unknown,waiter-list

04/16/2019 17:06:40,spid27s,Unknown,owner id=process2ab62077c28 mode=X

04/16/2019 17:06:40,spid27s,Unknown,owner-list

04/16/2019 17:06:40,spid27s,Unknown,keylock hobtid=72057597066412032 dbid=7 objectname=MicrosoftDynamicsAX.dbo.SALESPARMLINE indexname=I_361RECID id=lock2a663dc0900 mode=X associatedObjectId=72057597066412032

04/16/2019 17:06:40,spid27s,Unknown,waiter id=process2ab62077c28 mode=U requestType=wait

04/16/2019 17:06:40,spid27s,Unknown,waiter-list

04/16/2019 17:06:40,spid27s,Unknown,owner id=process2ab6207fc28 mode=X

04/16/2019 17:06:40,spid27s,Unknown,owner-list

04/16/2019 17:06:40,spid27s,Unknown,keylock hobtid=72057597066412032 dbid=7 objectname=MicrosoftDynamicsAX.dbo.SALESPARMLINE indexname=I_361RECID id=lock2a911084900 mode=X associatedObjectId=72057597066412032

04/16/2019 17:06:40,spid27s,Unknown,resource-list

04/16/2019 17:06:40,spid27s,Unknown,(@P1 nvarchar(21)@P2 int@P3 bigint@P4 nvarchar(5)@P5 nvarchar(21)@P6 nvarchar(21)@P7 nvarchar(21))UPDATE SALESPARMLINE SET TABLEREFID=@P1RECVERSION=@P2 WHERE (((PARTITION=@P3) AND (DATAAREAID=@P4)) AND (((PARMID=@P5) AND (ORIGSALESID=@P6)) AND (TABLEREFID=@P7)))

04/16/2019 17:06:40,spid27s,Unknown,inputbuf

04/16/2019 17:06:40,spid27s,Unknown,unknown

04/16/2019 17:06:40,spid27s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

04/16/2019 17:06:40,spid27s,Unknown,unknown

04/16/2019 17:06:40,spid27s,Unknown,frame procname=adhoc line=1 stmtstart=208 stmtend=536 sqlhandle=0x0200000026a0e400c91685ae48678f82cab70369d35a62980000000000000000000000000000000000000000

04/16/2019 17:06:40,spid27s,Unknown,executionStack

04/16/2019 17:06:40,spid27s,Unknown,process id=process2ab6207fc28 taskpriority=0 logused=5748 waitresource=KEY: 7:72057597066412032 (ef20a890fc75) waittime=6278 ownerId=155027538 transactionname=user_transaction lasttranstarted=2019-04-16T17:06:33.620 XDES=0x2ab626b0430 lockMode=U schedulerid=13 kpid=5692 status=suspended spid=125 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2019-04-16T17:06:34.590 lastbatchcompleted=2019-04-16T17:06:34.560 lastattention=1900-01-01T00:00:00.560 clientapp=Microsoft Dynamics AX hostname=AXPRODAPP hostpid=27156 loginname=GLWESTSTARDUBAI\Administrator isolationlevel=read committed (2) xactid=155027538 currentdb=7 currentdbname=MicrosoftDynamicsAX lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

04/16/2019 17:06:40,spid27s,Unknown,(@P1 nvarchar(21)@P2 int@P3 bigint@P4 nvarchar(5)@P5 nvarchar(21)@P6 nvarchar(21)@P7 nvarchar(21))UPDATE SALESPARMLINE SET TABLEREFID=@P1RECVERSION=@P2 WHERE (((PARTITION=@P3) AND (DATAAREAID=@P4)) AND (((PARMID=@P5) AND (ORIGSALESID=@P6)) AND (TABLEREFID=@P7)))

04/16/2019 17:06:40,spid27s,Unknown,inputbuf

04/16/2019 17:06:40,spid27s,Unknown,unknown

04/16/2019 17:06:40,spid27s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

04/16/2019 17:06:40,spid27s,Unknown,unknown

04/16/2019 17:06:40,spid27s,Unknown,frame procname=adhoc line=1 stmtstart=208 stmtend=536 sqlhandle=0x0200000026a0e400c91685ae48678f82cab70369d35a62980000000000000000000000000000000000000000

04/16/2019 17:06:40,spid27s,Unknown,executionStack

04/16/2019 17:06:40,spid27s,Unknown,process id=process2ab62077c28 taskpriority=0 logused=5124 waitresource=KEY: 7:72057597066412032 (b61e207e9d40) waittime=6278 ownerId=155027557 transactionname=user_transaction lasttranstarted=2019-04-16T17:06:33.623 XDES=0x2a8a2824430 lockMode=U schedulerid=12 kpid=1004 status=suspended spid=120 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2019-04-16T17:06:34.590 lastbatchcompleted=2019-04-16T17:06:34.560 lastattention=1900-01-01T00:00:00.560 clientapp=Microsoft Dynamics AX hostname=AXPRODAPP hostpid=27156 loginname=GLWESTSTARDUBAI\Administrator isolationlevel=read committed (2) xactid=155027557 currentdb=7 currentdbname=MicrosoftDynamicsAX lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

04/16/2019 17:06:40,spid27s,Unknown,process-list

04/16/2019 17:06:40,spid27s,Unknown,deadlock victim=process2ab62077c28

04/16/2019 17:06:40,spid27s,Unknown,deadlock-list

Please Suggest.