Navision SQL Locking

Hi can anybody help me with this problem:
I have Navision 4.00 and I wont know.
When is locked Table in Navi. nobody else can insert in Table and must wait for unlocked in Navison is One Properties "Always locked rows" it is on File->Database->Properties->Improved ( The last tab)
You can to check this attribute can you give me any advice if it is good solutions or how do that ?
Thanks

I had the same problem once at a customer: transfer order with 3000 lines took too much time.

A few very strange lines of code was this (in CU5704 and CU5705):

IF RECORDLEVELLOCKING THEN BEGIN
  NoSeriesLine.LOCKTABLE;
  IF NoSeriesLine.FIND('+') THEN;
...
END;

It is going to lock the last record in the NoSerieLine table (very common table throughout the application). This record has nothing to do with the transfer.

I changed the code (for both codeunits) to:

InvtSetup.TESTFIELD("Posted Transfer Shpt. Nos.");
NoSeriesLine.LOCKTABLE;
//IF NoSeriesLine.FIND('+') THEN;
NoSeriesLine.SETRANGE("Series Code", InvtSetup."Posted Transfer Shpt. Nos.");
IF NoSeriesLine.FIND('-') THEN;

At least now it's going to lock the right record(s).

Something else you should know is:
While locking 1 record, SQL is going to lock its neighbours. So, it's also going to lock the record above and the record beneith. Is this is a problem, you could create dummy records above and beneith to avoid it.

Last but not least:
avoid all automatic value postings if not necessary. It makes the process longer, so your lockings will be longer as well.

You can also use some of the SQL Server tools and Navision tools to see what is going on.
Look at SQL Server profiler. Look at the keys being used. See if you can deactivate any keys or turn off maintaining them in SQL Server.
Same for sumindexes.

My experience with the lock Timeout is that users rather want to wait in stead of getting an error message saying that another users has locked the record ... .

Like kriki said,
do some tuning, see that only the necessary SIFTs and indexes are maintained ... that will get your process faster as well.
To get your reading faster, create the right indexes ... .

And yes, this is a job for experts.

anilkumar:

NAV is very "fussy" about the locking that occurs within the SQL database. The primary reason for this is the transaction level that we use, which is classified as SERIALIZABLE.

The SERIALIZABLE isolation level has a bad side-affect for insert transactions...it causes a KEY RANGE lock to be acquired as transactions are posting within the database. This means that a short-lived lock, such as that acquired in READ COMMITTED mode, does not exist. It basically locks the range until the COMMIT statement is sent to the SQL Server. Since NAV has Entry No. as the primary key in many ledger tables, it locks that key which causes blocking to occur within the database.

The benefit of the SERIALIZABLE isolation level is the fact that we cannot get "Phantom" reads in the database, which could cause inconsistent financial information to be conveyed within a report.

I have sent a request to Microsoft inquiring when they are looking at supporting the new SNAPSHOT isolation level that exists in SQL Server 2005. The use of this isolation level would give us all of the benefits of the SERIALIZABLE one, but would also reduce the blocking that occurs within the database (theoretically) as row-versioning is implemented in the DBMS.

You can read more about the locking here:
http://mssqltips.com/tip.asp?tip=1253

There are a couple of ways to get around this...at a technical level with the Dynamics NAV application, you cannot escape the locking very effectively. Instead, you can use an Application Server to queue the postings so that they are posting in the background. While this does not eliminate the locking, it does give the users a "perception" that the system is faster as the C/AL client is not actually completing the transaction.

We have a product called nTier Architecture Services that we use for 4 of our sites that allows multiple high-volume transactions to be posted in the database without the blocking. In the background, an app server posts the data and then informs the user if the posting fails, or succeeds. I'd be more than happy to forward you information related to the topic for your review.

Email me at sfrappier@symtech.us if you're interested.

Best of luck to you!

- Scott

papvre wrote:

Checkbox "always rowlock" was primary added for removing a bug in SQL2000 with tablelocking.

I have one more question if is other kinds of methods how to handle whit locking table problem. If are any properties on SQL Server 2005 (not programing) only something set
Thanks

colin leversuch-roberts wrote:

http://msdn2.microsoft.com/en-us/library/ms188783.aspx

explains the various index operations available to you. You could drop and recreate the index with new options in sql server. I'd personally advise against changing any of the locking settings unless you are absolutely sure of your facts. Forcing row locks at the expense of page locks may actually cause escalation to a table lock far quicker if resource is limited on your server. You should also consider the row width in your table, a page is just under 8k, if your row is 4k then only one row will fit a page so row locks and page locks are the same. On secondary indexes you should be careful as you may cause contention, there are a number of dmv's which can help you analyse index entries per page.

I thought Navision maintain it own index; will Navision understand new indexes if we use SQL to rebuild the indexes?

kriki wrote:

Waldo wrote:

You can create a key in C/SIDE. C/SIDE will create an index in SQL Server. That is a 1 to 1 mapping. When you change the index in SQL Server, you loose the mapping (the definition of the index is in the object table). You should always create/update the keys from within C/SIDE.

And you have also the option to define a key in C/SIDE with flowfields and not to maintain them in SQL or also to create another index in SQL.

Thanks, you need not answer for last question I found answer