migration to sql & deadlock

Hi, we are testing migration to mssql and got into troubles with deadlock on table reservation entry (337). We have compared our solution with Cronus 2.60 and we found that Cronus have the same problem. When person A posting shipping in sales orders and person B reserving in another sales order person B get deadlocked Does anybody know some fix? berk

The fixes for deadlocks in Navision are the usual ones: - Avoid locking key resources if possible - Lock resources in the same order - Move to a more course grained locking The troubleshooting guide can help with deadlock detection and suggestions for resolving them, mainly using the ‘same order’ principle.

Hi Robert, Thank you for an advice. The problem is that we have tested the original Navision Financials CZ 2.60.E version with no customization. We have tried posting sale orders with reservations (one process) and auto-reservation of items (other process in the same time) and we have got deadlocks. Due to no customization there should be no problems with locking order or wrong usage of locking. We are using: - SQL 2000 Server with SP3 - Attain 3.60 Client - Financials CZ 2.60.E Database Any other suggestion? Best regards DAn

Hi, this problem can not be fixed. Both operations try to insert record in the same table so table get locked. This code in the “Create Reserv. Entry” codeunit cause this problem: ReservEntry3.LOCKTABLE; IF ReservEntry3.FIND('+') THEN LastEntryNo := ReservEntry3."Entry No."; So you can only minimize time people are locked.

Hi Valentin,

quote:


Originally posted by Valentin Gvozdev
this problem can not be fixed. Both operations try to insert record in the same table so table get locked.


I hoped this could be solved by migration to SQL option. One process inserts a new record to reservation item and the other only modifies an existing record. So there should be neither locking nor deadlocking.

quote:


Originally posted by Valentin Gvozdev
This code in the “Create Reserv. Entry” codeunit cause this problem: ReservEntry3.LOCKTABLE; IF ReservEntry3.FIND('+') THEN LastEntryNo := ReservEntry3."Entry No.";


Our customer’s solution is based on the Financials 2.60.E version. There is the codeunit 400 (Reservation manager) in this version. Best regards DAn

You can try share “Entry No.” number range baetween users, before FIND insert setrange filter => for user A 0…999999; b 1000000…1999999 … in this way, if you don’t have huge amount of transactions, you can avoid some of locks. But if SQL using page locking you will get in locks again, so all what you can do is forse sql to row locking. I have done this, and now is about 60 % less locks. Another way is use another table for “Entry No” handling. So befre entering to routine, make a small transaction with comit that give you “Entry No”. I havn’t tried this but I think that can help.

how you can force sql server to use row locking instead of table locking ??? Nico

Hi, SQL Server is doing row level locking - not table locking. That is, Attain is not utilizing table locks in the SQL option. (You can verify this very easily by running the SQL profiler). The lower-grained locking you have, the more likely that deadlocks will occur. If everyone is serialized in the application by having to read the last record of a table for example, to get the next number series, then you have effectivley turned row locking into table locking anyway. This reduces concurrency by introduces blocking but increases the chance of deadlocks. The only way to solve this is to serialize the code - which is why providing a predefined table locking order works because thats what it does. Its a bit more difficult in SQL server to get this scheme to work because a LOCKTABLE() does not actually lock the table. Only rows that are read or modified will be locked, and only at the time those operations are performed. So deadlocks are more likely. The fact that the base application without customizations has deadlocks, is just an indication that multi user tests in the application areas are not being performed to expose deadlock or blocking problems.

Hmmmm, I have checked with profiler. it uses ROWLOCKING and READUNCOMITED. So deadlocks coused by dirty reads ? To avoid this use: DummyRec.GET(1);DummyRec.MODIFY; It’s creates semaphore.

Dalius, I don’t follow you. READUNCOMMITTED is never placed in the same statement as ROWLOCK so of course a dirty read does not contribute towards deadlocks. ROWLOCK can be used in a WITH on a table as follows (all are within the SQL Server SERIALIZABLE isolation level): WITH (ROWLOCK) WITH (UPDLOCK, ROWLOCK) WITH (REPEATABLEREAD, ROWLOCK) WITH (UPDLOCK, REPEATABLEREAD, ROWLOCK) Your GET is unnecessary since a GET is always made internaly by C/SIDE before a MODIFY. However, since the MODIFY will place an exclusive lock anyway, no other ‘semaphore’ is neeed.

:slight_smile: I mean Navision, not Selecet or Update, => update with ROWLOCKING and Select with READUNCOMITED. If Navision have dirty reads, then what about registers ??? If two users posting to G/L → no entry range for users, all rec’s mixed, so G/L register record pointers to G/L recs is cover each other. Have you seen this ?

When you run C/AL code, Navision uses a TransactionType called UpdateNoLocks. This means that although the SQL Server isolation level is SERIALIZABLE (the strongest) with this transaction type, any table you read from that is not yet locked does not place any SQL Server locks but uses READUNCOMMITTED to do, as you say, dirty reads. When you lock the table, although it is not locked on SQL Server immediately, any subsequent reads from it will no longer use READUNCOMMITTED which means the SERIALIZABLE isolation level applies to the read (giving rise to record locks and key range locks). This reflects Navision Server behaviour, in that if you do not lock a table but read from it you do not necessarily get the current (latest) version of the data and you cannot rely on that read to make decisions later in your code. If your reads should be reliable, you should lock the table, on both server platforms.

So, what means that Navision API before making sp. procedure checks table is locked or not ?

Sorry, could you expand on your question a little.

Lets look from Navision API. When no table lock, select goes with READUNCOMMITTED. When table is locked, READUNCOMMITTED isn’t used. It’s right ? So, Navision before calling select, checks table is locked or not. And table locking have influence only to local API behaviour ?

Thats right; the lock status of the table determines the SELECT lock hints. I don’t know what you mean by table locking having influence only on local API behaviour. The lock hints placed with SELECT have an influence on any other session reading the same record (or key range), unless they are doing dirty reads. So no, locking does not have only a local behaviour. Am I misunderstanding you? By the way, when you say API, are you refering to C/FRONT? Since C/AL is not usually referred to as an API (although it is that too of course), just usually a language.

:slight_smile: I see what we have mixed two diffrent things: Table locking on SQL database and LockTable Navision function. I’m talking about navision func. You talking about sql locking. Or I’m wrong ? Navion func. don’t lock any table, it’s only changes my session data reading => No dirty reads. P.S.: API i mean any navision application.

Well, I’m talking about the effect Navision’s LOCKTABLE has on SQL SELECT statements, so I’m refering to both.