Tables still locking after SQL LOCK_ESCALATION = DISABLE

thepatient

Hi everyone,

We are having some trouble with the Item Ledger Entry, G/L Entry, and Reservation Entry tables locking at different points in the day. It occurs when posting consumption or output, posting trucks, etc. While these are locked of course we cannot update invoices, journal entries, or anything else. I have run the ALTER TABLE SQL command with the command written in the subject line and the command was executed correctly on the problem tables, however we have seen no improvement in the everyday problems we experience. Is there any other solution I can try? We really need SQL to only lock the row it is writing and some of the rows around it instead of locking the entire table everytime it is written to.

Thanks,
Drew

Belias

From NAV, you can do File, Database, Alter, advanced tab. Enable "always rowlock". But keep in mind these considerations:
- It is applied on every table of nav. (I mean that ROWLOCK hint is added to every query sent to SQL by nav)
- You should have plenty of ram to always maintain this level of lock, and be sure you have a 64bit OS, because locks are maintained on directly allocated memory (that is only 4GB for a 32bit)
- Are you sure that this is the real performance problem? How did you analize performance?

P.S.: I don't know if that command works for nav DBs, sorry...maybe some sql expert can answer you.

Sog

[respons to a wrong interpretation]
Belias, record locking doesn't work for Native DB's. That's why the cmd Recordlevellocking is to know if you're on a SQL or native db
[/respons to a wrong interpretation]
Mixed the SQL LOCK_Escalation and record lock command

Belias

Sog wrote:

Belias, record locking doesn't work for Native DB's. That's why the cmd Recordlevellocking is to know if you're on a SQL or native db

who is talking about the native DBs and recordlevellocking? :-?
Am I missing something from the original post, maybe?

kriki

I have some doubt that "always rowlock" will help.

Posting some consumption/invoices/shipments/receipts/........ writes new entries to the Item Ledger Entry-table and the Value Entry-table.
To be able to write to it, codeunit 22 has does a LOCKTABLE and then a FINDLAST to get the last record. In reality the LOCKTABLE does NOT do a locktable in SQL. It just means that the records read AFTER the locktable must be exclusively locked in SQL.
Also the reservation-entries are locked in that way.
The only thing you can do is to speed up the posting process by optimizing the writing process (RAID10-disks SAS15000 disks, index maintenance, changing some FIND('-') or FIND('+') in FINDFIRST/FINDLAST/FINDSET, removing unnecessary indexes/SIFTS, maybe even creating new,better indexes/SIFTS.

Belias

Yes, it's the same doubt i've had!
there's also another possiblity, that i've never tested: i've only read it on stryk's book. the solution is to change the clustered key from "entry no." to a new autogenerated GUID field...

With entry no. clustered key:
Because ledger tables insert new records sequentially, if you lock the last record, no further records can be inserted until the lock is released. (as kriki said)

With guid clustered key
If the clustered index is a non sequential guid, the "problem" does not exist anymore.
Drawback: larger clustered index, be aware to set a correct fillfactor.

BTW, i think it's easier to buy some more hardware: this is always the starting point

kriki

[-X
NAV needs the locking for inserting new records in the ledger entries. Also reservation is based on it. So you can't use GUID's. Maybe in a newer version of NAV they change it so it is possible to post different documents at the same moment.

To be honest, I am not really happy with the GUID as a clustered index like proposed by Joerg Stryck for the following reasons:
-a BIG clustered index. And the clustered index is copied to all secondary indexes.
-it basically generates random values that have a big chance to generate a lot of page splits. Too many page splits slow writing down. The best clustered index is small and an every-increasing value (like "Entry No." but it would be better using the NAV property autoincrement but has the negative that NAV-bulk inserts cannot be used).
-you don't have the security to have a unique value. I have read an article about it and the person had the problem (it was not a NAV DB but a pure SQL DB and they were inserting records by the 1.000.000.000's!) but basically: GUID's are not guaranteed unique!


I have considered some times to use GUID as clustered index (in NEW tables) but always decided against it for the negatives about it.

stryk

Hi there,

well, we know that blocking could have a gazillion reasons. I never advised that using GUID CI would solve all those issues - that's definitely not the case! Using GUIDs solves very specific issues, mainly if you need to increase parallelism with concurrent write-transactions. With G/L or Item postings this does not help; there are other things to regard, thus other solutions might help.

IMHO it is most important to exactly find out what gets blocked and when and by whom. Then, and only then, we could think about various solutions.

E.g. as you already proved: just disabling LOCK_Escalation has been pointless, as obviously you have no escalation problem. Enabling "Always Rowlock" is smart (if you have 46bit system with more than 8GB RAM), but won't necessarily solve the problem.

Hence: first you need to IDENTIFY the problem, than you might be able to solve it.

More about this:
http://dynamicsuser.net/blogs/stryk/archive/tags/Locking/default.aspx

Check out the acrticles about block- & deadlock detection!

Belias

stryk wrote:

I never advised that using GUID CI would solve all those issues - that's definitely not the case! Using GUIDs solves very specific issues, mainly if you need to increase parallelism with concurrent write-transactions. With G/L or Item postings this does not help;

I apologize,I've probably misunderstood that part :?

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'SQL Performance' forum]

stryk

Belias wrote:

I apologize,I've probably misunderstood that part :?

No need to apologize. I often learn that my comments require some clarification :oops: , that's why I try to update my little book from time to time ...

Cheers,
Jörg