Table Locked by other user

dhananjay

Dear All ,

We have created different batches for JV, cash & Bank . so that different users can work on different batches . But we are facing one problem in this type of situation is that If one of the user click on posting then other user will get message that " Table locked by other user with user id "
similiar problem is face while creating item journal entry.
It will prevent other user to creat transaction.
How to reslove this issue ?
Tell me solution how to overcome this problem.

rgds
dhananjay

ara3n

Turn on client monitor and see where and what record gets locked. Change the code to unlock it. Or move the code the end of transaction so that when it happens it's quick enough that other people won't notice the lock.

[Edit]
A lot of times the tables get locked because sift tables.
Also if you are looping through the item journal and deleting them one line at a time, I would change the code to deleteall and do it at the end.

cnicola

Rashed I don't think locking issues can be solved that quickly and easily.
And especially shouldn't be attempted without supervision (people on this forum seem to recommend SQL Perform but to me should be someone with decent amount of Navision experience).
In his case though if I understand correctly he is posting journal batches and those will most likely lock on G/L Entry and that cannot be avoided (well with conventional methods anyway). So the only way to address it in this case would be to just make things post faster or post large batches at night.

themave

Another common thing that slows posting and resulting in table locking is if you have analysis views set to update on posting.

you can trying changing any analysis views to not update on post, and then run you the update routine from the job schedular in the evening.

also, when posting about locking table problem, it would be good to actual mention what table is doing the locking, as without it we are just guessing.

ara3n

cnicola wrote:

Rashed I don't think locking issues can be solved that quickly and easily.
And especially shouldn't be attempted without supervision (people on this forum seem to recommend SQL Perform but to me should be someone with decent amount of Navision experience).
In his case though if I understand correctly he is posting journal batches and those will most likely lock on G/L Entry and that cannot be avoided (well with conventional methods anyway). So the only way to address it in this case would be to just make things post faster or post large batches at night.

Agreed, maybe I did not understand that it's two people posting,
I thought other people working on the journal while one person is posting a journal that locks other people. Which means the journal line table gets locked, and that shouldn't happen.

cnicola

Well as themave said we all just guessing right now
After your post I am thinking that could be another way to read the issue

OP I guess we need a lot more details to be able recommend anything.

dhananjay

Cincola / Rashed
Ya both of you on right track.
In our case one user is creating journal line in one batch & other user is doing posting of other batch. The message will feature on the screen while creating journal line.
Can we avoid this locking ?

cnicola

Ooops forgot the most important question: :oops:

What exact object version are you using and more importantly, is it SQL or C/Side?

DenSter

The only way to decrease the locking, is to make transcations go faster. There are a lot of things you can do to make the database go faster, and you will probably need to do a number of them to sovle your particular issue.

There are some application type things you can check, such as the number of dimensions, whether direct cost posting is turned on, whether analysis views are updated on posting, if you have change log turned on, what kind of add-ons you are using, how many users are connected, what kind of hardware setup, etcetera, etcetera

Then there are technical things you can do, such as do a hardware review and make sure that is not the issue, then there is database maintenance if you're on SQL Server. The biggest impact though, will be to tune the indexes of your database.