Strange Locking Issue

megawavez

Hi,

We are doing something like this on a table:

TableRec.SETRANGE("Field1", 'XXXX');
If TableRec.FINDSET() THEN REPEAT
TableRec.DELETE(TRUE);
UNTIL Table.NEXT() = 0;

This set of code looks to be doing something like a table lock - If I end up running this routine at the same time on another instance of navision classic (6.0) with a different range (i.e. 'YYYY' instead of 'XXXX'), the second instance of navision will end up with a "Another User Has Locked TableRec". I think this is related to SERIALIZATION ?

Thanks for any help.
Mega

rmv_RU

It's normal if you don't use setcurrentkey("Field1"). After locktable or any modification/deletion/insertion in table Navision use updatlock hint to reading data from SQL Server. So, if you use wrong key - lot of records may be blocked.
Try to rewrite code with setcurrentkey("Field1") or use temporary table.

megawavez

rmv_RU wrote:

It's normal if you don't use setcurrentkey("Field1"). After locktable or any modification/deletion/insertion in table Navision use updatlock hint to reading data from SQL Server. So, if you use wrong key - lot of records may be blocked.
Try to rewrite code with setcurrentkey("Field1") or use temporary table.

Well, that fixed it !

I had been told that sql picks the keys to use for navision operations automatically, but I guess that's really not the case.

Thanks,

Mega

rmv_RU

megawavez wrote:

I had been told that sql picks the keys to use for navision operations automatically.

It's not true. Always use key with best selectivity, especially for data modification.

megawavez

rmv_RU wrote:

megawavez wrote:

I had been told that sql picks the keys to use for navision operations automatically.

It's not true. Always use key with best selectivity, especially for data modification.

Thanks - I got some bad advice regarding this. I'll be putting fixes in.

Marije_Brummel

The advice has been correct for a while long time ago.

I remember 5 years ago each NAV build had different best practices.

Now (last years) they fixed it back to SQL prefering the SETCURRENTKEY to be there, it behaves more like the old Native database.

megawavez

Mark Brummel wrote:

The advice has been correct for a while long time ago.

I remember 5 years ago each NAV build had different best practices.

Now (last years) they fixed it back to SQL prefering the SETCURRENTKEY to be there, it behaves more like the old Native database.

This is a pretty significant change, so let's hope no changes in the future.

mdPartnerNL

so, by only adding setcurrentkey("Field1") this is solved.

And NAV doesn't use "Field1" as default if not specified in code?

Marije_Brummel

If you do not specify the setcurrentkey the primairy key will be used for the ORDER BY statement.

mdPartnerNL

ok but why did it solve his problem to add the primary key in the setcurrentkey() ?

Marije_Brummel

SQL Server analyses the query that comes in and determines the best key to use.

If you do for example

SELECT * FROM CUSTOMER WHERE ADDRESS = 'XX' ORDER BY NO

or

SELECT * FROM CUSTOMER WHERE ADDRESS = 'XX' ORDER BY ADDRESS

SQL Server is more likely to use the index in the second example.

mdPartnerNL

That's clear.

But why did it help the TS to add setcurrentkey() ? That's automatically the default? :-k

Marije_Brummel

I don't think that in this case it was the default. Must have been something else.

mdPartnerNL

@megawavez, could you provide more info?

When we do an update for records, we expect the primary key to be used.

As a result we do not use setcurrentkey(..with primary field...) as it's already used by default.