Cannot delete a record in ON-hand Inventory Changes (InventSumDelta).

(0) Share

Report

Posted on by bjennings

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

I have the same question (0)

All responses (11)

Answers (0)

Klaas Deforche 2,433 on at

Like (0)

Report
Someone has, but is was on 2009 I think, you are on 2012, right?
The conversation is here but I stongly doubt it is still valid: community.dynamics.com/.../93517.aspx
Some questions :-)

I'd start by looking at SQL; Are there any long running queries there? Are there locks?

Check if you have customizations that might cause this (something that is doing inventory transactions), if so, disable them to test if they are the cause

Check if there are batches running

Also if you can reproduce the problem, use the tracing cockpit with the option "bind parameters" too see what sql statements are executed.

Synchronize your data dictionary, does it return errors

Check the event log, both on client, aos and sql for errors and/or warnings. Are there any?

When did these errors start occurring? Is it a production, test or dev environment? Does it happen all the time or just once every few days or so?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Amitt on at

Like (0)

Report

Hi Bjennings, I'm facing the same issue and am stuck.

Please let me know how you were able to fix it ?

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Suggested answer

Denis Macchinetti 16,444 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

I'm happy to report that disabling the RecId index on InventSumDelta and InventSumDeltaDim has entirely resolved the deadlock issues.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

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).

Was this reply helpful? Yes No