Navision SQL Locking

anilkumar · 2007-07-03

Hi Experts!

We are facing a problem at Head office database, that table locking while posting in to Item ledger entry and General ledger entry table. Main area of operations are Transfer orders and Item Journals.

1. Normally a transfer order and item journal has 2500-3000 lines.

2. Multiple users work on one operational area. e.g., 4 users create Transfer orders and post them at a given point of time.

3. While posting in this scenario tables get locked.

System is taking more than 30 minutes in posting one transfer order or item journal. Lilliput business process needs that multiple users can post the orders and Journal at the same times.

Right now they have 106 concurrent user license but only one user can post the document at a time.

Its a Navision with SQL Server 2000 database.

Can any one help in this issue, suggest me what procedure should I follow for SQL Server to reduce the locking or avoid locking.

Thanks!

hefo · 2007-07-03

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

papvre · 2007-07-03

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

Waldo · 2007-07-03

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.

kriki · 2007-07-03

And get some performance tuning done on the tables (index and SIFT tuning). So it will be faster.

davmac1 · 2007-07-03

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.

diptish.naskar · 2007-07-04

Hi,

You can also use the Lock time out so that the other users doesn't have to wait too long for the lock. As Davmac said you can also use the SQL tuning tools to check where it is actually going wrong.

Waldo · 2007-07-04

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 · 2007-07-11

Mr.Waldo,

Excellent, your code given very good result, but still locking problem is there.

Scott_Frappier · 2007-07-14

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

hefo · 2007-07-17

papvre wrote:

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

Hi could you specify and tell me more about "always rowlock" I have SQL Server 2005
Thanks

hefo · 2007-07-17

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 · 2007-07-17

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.

vuacorona · 2007-07-17

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?

Waldo · 2007-07-18

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.

For rebuilding the index, you can use SQL Server. Rebuilding does nog change the definition, so this doesn't matter. Rebuilding will not "create" or "change" the index .. .

kriki · 2007-07-18

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.

Waldo wrote:

For rebuilding the index, you can use SQL Server. Rebuilding does nog change the definition, so this doesn't matter. Rebuilding will not "create" or "change" the index .. .

It just optimizes the indexes (=throwing them away and recreating them from scratch).

hefo · 2007-07-18

Hi everybody ,
Can anybody help me with this problem:
I have Navision 4.00, SQL Server 2000. I wont know all about Options on Tab->Improved it is File->Database->Properties->Improved ( The last tab) if is somewhere write about this parameters how should be set.
I have 157 GB database and I wont know if setting of this parameters something improve because if are many users conect on this database ens Run some Journal and other had Deadlock and last time application frozen. I don not know if this two things to connect
Thank for all the help

hefo · 2007-07-19

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

Howdy, Stranger!

Quick Links

Categories

Navision SQL Locking

Comments