LockTable Error

chiramel

Hi Friends
I'm getting error when i'm posting my sales order "G/l entry table is Locked by another user".same error is getting to all my users
it is stopping my work.
Around 42 users r working concurrently and Databse size is also 30 gB
all my users getiing same error when they are posting sales or purchase orders.............
Please help to solve this issue.

Belias

maybe some job is running or waiting for an input...or everyone are posting at the same time

Control if there are processes running on the g/l entry table.
Did you try to get all users out & in from navision?

hawwa

I suggest you try to find out who is locking other people first.
1) Go to File-->Database-->Information.
2) Select the Sessions tab.
3) Drill down to the current sessions.
4) Check on the Blocking user id column to find out who is locking other people.

From there, maybe you check with the user who lock other people to find out what process / function the user is running. Ask the user to stop the process or close navision to release the lock.

chiramel

I tried that
I can see 4 to 5 users are blocked by another user and Blocking user id is changing continously so i cant trace out where is problem.
my wiat time is 60 sec .stiil users r gettinng lock table error after 60 sec
same time 4 to 5 people posting inoices .i think thats is making problem how can i increase the posting speed or how can i reduce the errror by changing some parameter .Restarting server or closing navision is not an easy task l when more than 50 users working concurrently

Jock

Hi Guys

Looks like i may have the same problem. Any more than 2 users posting at the same time seems to lock everyone else for about 10 mins. It happens whenever something is being posted.
Users are not using a SQL database. Nav version is 4 SP3

Im just about to start looking into why its happening. Will let you know if i find anything.

If you guys find anything please let me know. [-o<

hawwa

Hi Chiramel,

do your user take more than 60 sec to post a purchase or sales order ? How many lines your users are posting at a time. It shouldn't take so long to post a purchase order.


Table Locking is a common mechanism used in multiuser environment to prevent more than one user to update the same record at the same time. When the first user want to update the table, Navision will issue a table lock to prevent 2nd user from updating the same data. After the first user finish updating, Navision will release the lock so that other users can update the table. The Locking and Unlocking occurs very fast because table lock is usually issued only at the point where data really need to be updated, unless you explicitly put a locktable command to lock the table long before the updating command is issued.

I remembered last time when we first implemented Navision, our vendor did the mistake by explicitly issueing a locktable command at the wrong place causing the table lock issue. After study the code, I removed the explicit lock command and let Navision handle the locking automatically. Explicit Locking must be use carefully. Then, the locking table problem solve. Now, I have more than 100 concurrent users using Navision on SQL Server and do not have table locking issue.

chiramel

Is there any way to speed up the process in Sql by tuning or setting some parameters because i'm feeling total process got slow after database become big to 28 GB
sql server having 4 GB ram and at the time of process it is using 2.3gb only

DenSter

You can modify memory allocations in the server properties. We recommend setting it to a static value (i.e. minimum and maximum values are the same) and leaving about 1.5-2GB of RAM for the OS. You might also try to turn maximum degree of parallelism to 1, so that you don't have different threads for the same transactions locking themselves.

chiramel

when I restart Pc , for two days it is working fine. sales Posting is taking only 15secs.after 2 or 3 days total system seems slow and salesposting is taking more than 3min.i cant understand why it is slow.system is getting slow and every body getting locktable error.
Please suggest some ideas to resolve this issue

hawwa

It seems that your Locktable is caused by the your Navision slowness. How is your server resources utilization. Maybe you can use Performance Monitor to check the SQL Server Page Split activities. If the Page Split is high, it may cause system to slow.

johanna

hawwa wrote:

Hi Chiramel,

do your user take more than 60 sec to post a purchase or sales order ? How many lines your users are posting at a time. It shouldn't take so long to post a purchase order.


Table Locking is a common mechanism used in multiuser environment to prevent more than one user to update the same record at the same time. When the first user want to update the table, Navision will issue a table lock to prevent 2nd user from updating the same data. After the first user finish updating, Navision will release the lock so that other users can update the table. The Locking and Unlocking occurs very fast because table lock is usually issued only at the point where data really need to be updated, unless you explicitly put a locktable command to lock the table long before the updating command is issued.

I remembered last time when we first implemented Navision, our vendor did the mistake by explicitly issueing a locktable command at the wrong place causing the table lock issue. After study the code, I removed the explicit lock command and let Navision handle the locking automatically. Explicit Locking must be use carefully. Then, the locking table problem solve. Now, I have more than 100 concurrent users using Navision on SQL Server and do not have table locking issue.

Hi, hawwa..

What version of NAV and SQL Server are you using?

Could you explain more about explicit locking and give me an example?

Thank you