Database deadlock during wave processing

(0) Share

Report

Posted on by Community Member

Hi Guys,

we experienced strange database locks during wave processing (Task 3) recently. We are using R3 CU8.

The lock occurres very rarely (the second time this year) but if it happens it's repeatable. It happens when WHSWorkCreate (createTempLine Line 139-149) trys to insert a WHSTmpWorkLine. Here is a screenshot.

The first condition is the standard case. If i force the debugger into the second condition the insert is executed successfully, so i suspect the independant transaction has a high potential to cause a lock.

Down below is the SQL statement which is blocked. We currently trying to find out which SQL statement causes the block.

(@P1 int,@P2 nvarchar(20),@P3 nvarchar(21),@P4 nvarchar(21),@P5 numeric(32,16),@P6 nvarchar(21),@P7 numeric(32,16),@P8 nvarchar(11),@P9 int,@P10 nvarchar(21),@P11 int,@P12 nvarchar(21),@P13 nvarchar(21),@P14 nvarchar(21),@P15 int,@P16 bigint,@P17 numeric(32,16),@P18 nvarchar(11),@P19 nvarchar(26),@P20 int,@P21 nvarchar(11),@P22 nvarchar(11),@P23 nvarchar(11),@P24 nvarchar(11),@P25 nvarchar(21),@P26 nvarchar(21),@P27 int,@P28 bigint,@P29 nvarchar(21),@P30 nvarchar(21),@P31 nvarchar(21),@P32 numeric(32,16),@P33 nvarchar(21),@P34 numeric(32,16),@P35 nvarchar(21),@P36 nvarchar(11),@P37 int,@P38 nvarchar(20),@P39 nvarchar(21),@P40 nvarchar(26),@P41 numeric(32,16),@P42 nvarchar(61),@P43 nvarchar(21),@P44 int,@P45 nvarchar(26),@P46 numeric(32,16),@P47 nvarchar(21),@P48 nvarchar(21),@P49 nvarchar(21),@P50 nvarchar(21),@P51 nvarchar(26),@P52 nvarchar(21),@P53 int,@P54 nvarchar(20),@P55 int,@P56 nvarchar(21),@P57 int,@P58 nvarchar(11),@P59 nvarchar(11),@P60 nvarchar(5),@P61 int,@P62 bigint,@P63 bigint)INSERT INTO WHSTMPWORKLINE (WORKTYPE,WMSLOCATIONID,INVENTTRANSID,ITEMID,INVENTQTY,WORKTEMPLATECODE,DIRECTIVEQTY,DIRECTIVEUOM,ORIGINTYPE,ORIGINREFID,WORKTRANSTYPE,WORKCREATEID,INVENTDIMID,INVENTLOCATIONID,REFTABLEID,REFRECID,HIGHESTQTY,HIGHESTUNITID,LICENSEPLATEID,WORKCREATED,WAVEATTRIBUTECODE,WAVEATTRIBUTECODE2_,WAVEATTRIBUTECODE3_,WAVEATTRIBUTECODE4_,INVENTBATCHID,INVENTSERIALID,WORKBREAK,LOADLINEREFRECID,WAVEID,LOADID,SHIPMENTID,PACKINGQTY,ORDERNUM,LINENUM,WORKID,REQUESTEDTYPEID,PACKQTYCOMPARE,STARTLOCATIONID,ZONEID,CONTAINERID,INVENTUNITIZEQTY,TRANSTXT,RELEASETOWAREHOUSEID,CONTAINERLEVEL,HIGHESTCONTAINERID,AVAILPHYSICAL,CARRIERCODE,CARRIERSERVICECODE,CUSTVENDAC,FEFOBATCHID,LOCATEDLPID,MODECODE,REPLENDEMAND,REPLENLOCATIONID,SORTCODE,TOWAREHOUSE,PRODBOMOPRNUM,WHSFILTERNUM,ECORESSIZENAME,DATAAREAID,RECVERSION,PARTITION,RECID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46,@P47,@P48,@P49,@P50,@P51,@P52,@P53,@P54,@P55,@P56,@P57,@P58,@P59,@P60,@P61,@P62,@P63)

Restarting the AOS didn't help. Deleting the WHSTmpWorkLine before processing a wave sometimes helped for a while but not reliable

Did anybody experienced this behavior?

Regards

Nils

*This post is locked for comments

I have the same question (0)

All responses (16)

Answers (1)

Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

What kind of lock is it? And what's the problem? Locks are good thing in general, only if they cause a signification blocking or a deadlock it becomes something to worry about - and then we should also talk about the other half (the process waiting for the locked resource).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

In this case it's a deadlock. We are trying to find the SQL statement which causes the lock.

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

SQL Server can log deadlocks for you; it will show SQL statements for both sides of the deadlock, types of locks, the locked resource and so on. See Detecting and Ending Deadlocks, for example.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you, i will try this out

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok we are quite sure what the problem is. A colleague has built a custom tool for wave creation. He starts an additional transaction which encases the standard wave creation code. Because of this, another transaction (a delete) cannot commit and keeps a lock on the table.

And because the standard code uses another connection to insert a record into the table later, we run into a deadlock situation.

We try to proof this assumption tomorrow.

Edit:

my assumption was wrong

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Unfortunatelly we couldn't find out which constellation leads to the deadlock situation. The lock occured in different situations and now it seem to disappear. We restored the database to be sure the conditions are the same like before but the deadlock doesn't occur anymore.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi again,

i'm close to the solution...but i have no idea why :-).

I debugged this problem for days and came to the conclusion that the deadlock appears when the first replenishment is inserted into WHSTmpWorkLine. We preserved a DB-state where the possibility of a deadlock was 100%.

It turned out that an "update_recordset", shortly executed before the deadlock happens, could be the problem. When i change the code to a "while select forUpdate" the deadlock disappears.

Still i have no idea why but it seems to change something. Down below is the code snipped. The commend is the original MS code which has a high possibility to end in a deadlock:

Regards

Nils

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

What resources get locked? The whole table vs. individual rows?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The whole table WHSTmpWorkLine

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

In both cases? If you get exactly the same lock, then there won't be any difference in behavior, right?

Therefore if both your statements are correct (there is a difference in behavior but it's not in this lock), then the assumption that this is lock is to blame must be false and the difference must be somewhere else.

Was this reply helpful? Yes No