Strange Locking Issue

megawavez · 2013-10-30

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 · 2013-10-31

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 · 2013-10-31

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 · 2013-10-31

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 · 2013-10-31

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 · 2013-10-31

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 · 2013-10-31

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 · 2013-10-31

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

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

Marije_Brummel · 2013-10-31

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

mdPartnerNL · 2013-10-31

ok

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

Marije_Brummel · 2013-10-31

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 · 2013-10-31

That's clear.

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

Marije_Brummel · 2013-10-31

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

mdPartnerNL · 2013-10-31

@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.

Strange Locking Issue

Comments

Categories