Strange Locking problem

demy75

I have Navision 4.0 SP1 on SQL Server and I've this locking problem. Here an example that reproduce the problem:
2 users try to execute the code below, by applying different filters on the same table. Then a loop where filtered records are deleted is executed.
If the first user executes the DELETE loop, the second user is blocked.

CASE USERID OF
'user1' : MyTable.SETRANGE(Descr, 'uno');
'user2' : MyTable.SETRANGE(Descr, 'due');
END;

IF MyTable.FIND('-') THEN
REPEAT
MyTable1.GET(MyTable.Codice);
MyTable1.DELETE;
UNTIL MyTable.NEXT = 0;

IF CONFIRM('.........') THEN ;

Why this? SQL Server locks at the record level, is it true? Why this problem occours only with DELETE (the same with DELETEALL) and not with the MODIFY instruction?
Any ideas on how to solve this?

kriki

SQL starts with record-locking, but at a certain moment he can decide that it is better for performance to lock the whole table. And in this case the other session is locked out.
There is no way to know when SQL will decide to start with a tablelock.
The thing you might do is to do a COMMIT every N records. Negative of this is that the deleting will slow down because of the overhead of multiple transactions. And also if there is an error between the transactions, some of the records will have been deleted and others not.

demy75

Make a COMMIT is impossible, because if the transaction is aborted, I can't commit the changes.
So, are these problems not solvable?
If I check the Client Monitor, the SQL code generated for the NEXT instruction for the example on my post is this:

SELECT * FROM MyTable WITH (UPDLOCK, ROWLOCK) WHERE (("Descr"='uno')) AND "Codice">'1' ORDER BY "Codice" OPTION (FAST 80)

The clause "Codice">'1' seems to be the cause of the table lock. Any chance to avoid this?

DenSter

You could try to serialize the transaction, which makes the second user wait until the first user is done. In codeunit 80 there's a bit of code that does this on the General Ledger Entry table:

GLEntry.LOCKTABLE;
    IF GLEntry.FIND('+') THEN;

You could try to do this to your table, something like this:

MyTable.LOCKTABLE;
IF MyTable.FIND('+') THEN;
MyTable.RESET;
CASE USERID OF 
// etcetera

This changes the isolation level in the SQL query, and should cause the whole table to be locked before it is blocked later.

demy75

I've tryed to serialize the transaction as you've signalled, but without good results. Seems that the table is always locked during the cicle.
Can I force the DELETE instruction into che FIND cicle to lock only the single record and not the entire table?

kriki

demy75 wrote:

I've tryed to serialize the transaction as you've signalled, but without good results. Seems that the table is always locked during the cicle.
Can I force the DELETE instruction into che FIND cicle to lock only the single record and not the entire table?

The idea of Denster was to block the whole table at the beginning of the cycle. This to avoid deadlocks.
It is not possible anyway to force SQL only to lock the records needed. Like it is possible SQL decides to upgrade recordlocks to tablelocks and we have no control about it.

demy75

I can't understand why the MODIFY instruction (placed on the previous example instead of the DELETE for example) works correctly without locking. Why I have the lock only if I place the DELETE instruction into a FIND cicle?

kriki

demy75 wrote:

I can't understand why the MODIFY instruction (placed on the previous example instead of the DELETE for example) works correctly without locking. Why I have the lock only if I place the DELETE instruction into a FIND cicle?

I think this is one of the mysteries of SQL.

demy75

Navision with SQL, not only SQL I think... :shock:

kine

Of course, to be sure, you can use the Row locking option in File-Database-Alter to re-enable rowlocking (to enable old behaviour of navision - to use rowlock hinting).