55
Hello Everyone, Has anyone seen the follow error...
Cannot delete a record in On-hand inventory changes (InventSumDelta). Created ID: 0. Deadlock, were one or more users having simutaneously locked the whole table or part of it
This error comes up when you are doing something with inventory...Entering a sales order..transfering inventory.
Thanks,
Bill
*This post is locked for comments
Hi, i have deadlock problem in SalesParmLine when concurrent users trying to Post sales invoice through web services.
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.
can i disable the RecID index ?
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)<c/>@P2 int<c/>@P3 bigint<c/>@P4 nvarchar(5)<c/>@P5 nvarchar(21)<c/>@P6 nvarchar(21)<c/>@P7 nvarchar(21))UPDATE SALESPARMLINE SET TABLEREFID=@P1<c/>RECVERSION=@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)<c/>@P2 int<c/>@P3 bigint<c/>@P4 nvarchar(5)<c/>@P5 nvarchar(21)<c/>@P6 nvarchar(21)<c/>@P7 nvarchar(21))UPDATE SALESPARMLINE SET TABLEREFID=@P1<c/>RECVERSION=@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.
I'm further happy to report that Microsoft has indeed disabled the RecId index on both tables InventSumDelta and InventSumDeltaDim in a subsequent hotfix (not sure exactly which one).
I'm happy to report that disabling the RecId index on InventSumDelta and InventSumDeltaDim has entirely resolved the deadlock issues.
Today I'm testing disabling the RECID indexes from SQL Server Management Studio (easily reversed if it goes poorly), which leaves only the proper indexes for selection by the database engine. So far, no deadlocks. If this approach survives a day or two of testing, then I can remove the RecId index from the AOT itself to make the change permanent.
While downloading that hotfix, I noticed it was marked CU11. Indeed when I installed it into my CU12 environment, several objects became changed in the SYP layer where they were SYS layer before, but upon comparison there were no actual changes. Thus, I'm pretty sure that hotfix was already baked into my CU12.
Guys,
the below hotfix fixed from my side the issue.
I have installed it months ago and if I remember well Microsoft changed the Cluster Index.
KB 3152654"Cannot delete a record in On-hand inventory changes (InventSumDelta)" error when using the mobile device and closing container from browser client
My effort to force the TTSDimIdx on both InventSumDelta and InventSumDeltaDim failed, thanks to index hint no longer having any effect in 2012 (something I probably knew and forgot).
I continue to get sporadic deadlocks on both tables, and always keylock against the RECID index. I've tried scheduling a frequent job to update statistics on both tables with fullscan, since I'm convinced statistics is contributing to the improper index selection.
This problem started with the installation of SQL Server 2014 SP2 CU12 a month ago. I have PERFMON logs going back years recording deadlocks/sec and I can prove that the issue started recently.
I'm debating now if I should simply disabled the RecId index on both tables entirely, thus leaving only the proper index to be selected.
Thoughts from anyone?
This looks to be related to statistics on the InventSumDelta table which causes SQL Server to select the wrong index, i.e. the RecId index.
Here is a good article that got me on the right track to hopefully fixing this problem.
axology.wordpress.com/.../locking-on-the-inventsumdelta-table
And the follow-up.
axology.wordpress.com/.../locking-on-the-inventsumdelta-table-additional-tweaks
Here is a snip from my own SQL Server ERRORLOG from today showing the deadlock type keylock on the I_2397RECID index.
2018-07-26 09:45:54.40 spid10s (@P1 bigint)DELETE FROM INVENTSUMDELTA WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'140')) AND (TTSID=@P1))
2018-07-26 09:45:54.40 spid10s resource-list
2018-07-26 09:45:54.41 spid10s keylock hobtid=72057596231221248 dbid=7 objectname=DAX2012R2_PROD.dbo.INVENTSUMDELTA indexname=I_2397RECID id=lock654fef980 mode=X associatedObjectId=72057596231221248
2018-07-26 09:45:54.41 spid10s owner-list
2018-07-26 09:45:54.41 spid10s owner id=process4fee8a7848 mode=X
2018-07-26 09:45:54.41 spid10s waiter-list
2018-07-26 09:45:54.41 spid10s waiter id=process1e1d088 mode=U requestType=wait
2018-07-26 09:45:54.41 spid10s keylock hobtid=72057596231221248 dbid=7 objectname=DAX2012R2_PROD.dbo.INVENTSUMDELTA indexname=I_2397RECID id=lock2e8dda080 mode=X associatedObjectId=72057596231221248
2018-07-26 09:45:54.41 spid10s owner-list
2018-07-26 09:45:54.41 spid10s owner id=process1e1d088 mode=X
2018-07-26 09:45:54.41 spid10s waiter-list
2018-07-26 09:45:54.41 spid10s waiter id=process4fee8a7848 mode=U requestType=wait
The more appropriate index by far for these delete_from statements it the TTSDimIdx, since the where clause specifies the PARTITION, DATAAREAID, and TTSID. It might also help to use forceliterals on those queries to eliminate a poor cached plan from causing repeated deadlocks, but I won't try that unless the specifying the index fails to resolve the issue.
The class InventUpdateOnhand contains 4 methods that delete from InventSumDelta and InventSumDeltaDim, where a forced index should resolve this issue. The cleanupAggrCounter method should probably use the AggregationIdx instead given the WHERE clause.
Just ideas.
do you have any customization around inventory update functionality.
If yes, please review the logic implemented.
Check windows event log for more detailed error information
Please update more with your information.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (