Deadlocks from calcsums

njhansen

NAV 2009R2 on SQL

We're experiencing excessive deadlocking on our system, mostly because of additional load on the servers. When I went hunting for the most common queries that SQL was detecting as deadlocks, one that continually appeared was clearly from a calcsums (no matching flowfield):

SELECT SUM("SUM$<fieldname>") FROM dbo."<tablename>$VSIFT$39" WITH (UPDLOCK, ROWLOCK, NOEXPAND ) WHERE...

This intrigued me, since I don't typically see an UPDLOCK hint on calcfields/calcsums queries, so I hunted down the code that matches it, which goes something like this:

Rec1.LockTable;
...
Rec2.SetCurrentKey
Rec2.SetFilter
Rec2.CalcSums

Where Rec1 and Rec2 are separate record variables of type <tablename> in the above query.

Why is Rec1 lock causing a lock hint on Rec2?
Is there any way to prevent NAV from doing this?

Unfortunately, this code occurs in vendor code that is in a CU for which we only have execute privileges (so I can't easily change it).

vaprog

njhansen wrote:

Why is Rec1 lock causing a lock hint on Rec2?
Is there any way to prevent NAV from doing this?

Unfortunately, this code occurs in vendor code that is in a CU for which we only have execute privileges (so I can't easily change it).

It is because you place the lock on a table, not on a certain instance of a record variable. After all, you want the lock to be effective even for other users.

So this is a a lock on a single table. A lock on a single table can cause blocking, but you need locks on two or more tables to cause a deadlock. So to prevent it, check which tables are involved. Look after the sequence in which locks take effect. Can you identify the other code involved in the deadlock? Does it use the same locking sequence (this is essential)? Which code uses the recommended locking sequence?
Now either fix the one witch you have access to or ask the vendor to fix his.

Marije_Brummel

This is still heritage from the old Navision native database where we had table locking. If you do Locktable on one table varable it counts for variables from that table during the transaction.

I don't know if that is changed in newer versions, but in 2013 you can use a query object to work around it.

rmv_RU

Locking sift view records is absolutely correct way to quarantie that the sift amount doesn't change until the end of transaction.
Number of records affected depends on key and filters (flowfilter in case of calcfields).
For example, code below lock all sift records with specified location:

ItemLedgerEntry.Locktable
ItemLedgerEntry.setcurrentkey("Location Code");
ItemLedgerEntry.setrange("Location Code", rec."Location Code");
ItemLedgerEntry.calcsums(Quantity);

njhansen

Thanks for the replies.

It is because you place the lock on a table, not on a certain instance of a record variable.

If you do Locktable on one table varable it counts for variables from that table during the transaction.

I figured that would be the answer, but wanted to check.

Can you identify the other code involved in the deadlock?

The other contender in the deadlock is usually another instance of the same code. It is in the middle of a multiple stage update to the same table that it is scanning. If you look at the NAV code, everything seems entirely reasonable, so the vendor wasn't just being sloppy. It isn't until you look at the effect on SQL Server that things start to look bad.

in 2013 you can use a query object to work around it

Interesting thought. We've used ADO connections to do some stuff in 2009 for which one would use a query object in 2013.