NAV SQL recording/table locking problem

krussellm

Does someone know how to make NAV/SQL work in this scenario? This is a setup in another system and we need to bring this scenario into NAV. It is a high volume distributor so there are a lot of transactions. Thank you.

Typical Allocation Scenario to test if this “Deadly Embrace” will occur

Subject: explanation of deadly embrace concerns

Items
Item 1: Salmon H&G
Item 2: Salmon Fillet Sk/On
Item 3: Salmon Fillet Sk/Off
Item 4: Salmon Trim

Allocation Sequence for Salmon Trim (Item 4)
1st Allocates against Itself (Item 4)
2nd Allocates against Item 2 for Qty
3rd Allocates against Item 1 for Qty
4th Allocates against Item 3 for Qty

Allocation Sequence for Salmon Fillet Sk/Off (Item 3)
1st Allocates against itself (Item 3)
2nd Allocates against Item 2
3rd Allocates against Item 1


The problem we have with our allocation system and concurrent allocation requests is the potential for deadlocks as a result of our sequencing mechanisms. Any given item will allocate against itself and subsequent parents based on quantities available for allocation and the sequence of parents defined for that item.

In our current file structure table locking schemes wouldn’t work because of the frequency of allocation requests, the tables involved and unrelated processes that also utilize those same tables.
In our current file structure record locking schemes wouldn’t work as a result of the very real possibility of deadlocks occurring constantly.

As an example of the deadlock scenario suppose we had two processes running concurrently both at roughly the same speed
Process 1 might be trying to allocate Qty of Item 4
Process 2 might be trying to allocate Qty of Item 3

If we followed the sequence of events for Process 1 and Process 2 as imaged we would have the following

Step 1
Process 1 would lock the record for Item 4
Process 2 would lock the record for Item 3

Step 2
Process 1 would lock the record for item 2
Process 2 would attempt to lock the record for item 2 and fail due to its current lock state being utilized by Process 1

Step 3
Process 1 would lock the record for Item 1
Process 2 would still be waiting for a commit from Process 1 to release Item 2 Record

Step 4
Process 1 would attempt to lock the record for Item 1 and fail due to its current lock state being utilized by Process 2
Process 2 would still be waiting for a commit from Process 1 to release Item 2

At this point both processes are in a deadly embrace and SQL will kill one of the processes to allow the other to complete. This obviously has negative ramifications.

The way we currently handle this is via Single threading Allocation Jobs through a single queue. Process 2 would not be allowed to start until the successful completion of Process 1. No deadly embrace and both jobs complete successfully while utilizing a record locking scheme.

rdebath

The usual way of doing this outside Navision is to first scan, without locking, to find which records you might need then lock all of them in record number order. This can be done either directly or with a proxy table. With MS-SQL you probably need to ensure that record level locking is on.

The Navision way is to use this:

GLEntry.LOCKTABLE;
IF GLEntry.FINDLAST THEN;

(Bitter! Me! No I'm not bitter...)

BTW: This is a proxy table:

CREATE TABLE ItemProxy {
  ItemNo VarChar(20)
} WITH PRIMARY KEY ON (ItemNo);  -- Pseudo SQL syntax :-)

You insert into it to lock the item, make sure you can't commit (use CONSISTENT) then delete the records just before you COMMIT.

Just to warn you; Indexed views (and table SIFTs) can cause deadlocks too. You may need to delete them or remove the more summarised levels.