Locking problems on sales line during invoice posting

Belias

Hi guys, i have a big problem on nav 2009r2.
Once a month, the invoicing starts and a user starts to post invioces over and over (some of them counts 700ish lines!). During the sales invoice posting, other users receive errors about sales line being locked by another user.
Codeunit 80/90 obviously locks this table (for qty updates and so on), but i am wondering why everybody gets errors on a "random access table" like sales line. The database is set to Always rowlock, and my personal thought is that sql scales the lock all the way up to the table. Am i correct? is the only solution to post overnight in order to avoid locking problems?

jglathe

Hi Mirko,

let me guess.... lock escalation of the SQL Server? Sounds like you have a heavy load when these postings run. When there are too many (as always with SQL Server, try to quantify this) row locks on a table, the whole table is locked. There is a (really old) blog where this is mentioned, can't find it at the moment... but the Microsoft KB article describing this. With this, you could try to set up T-SQL scripts that run on Sales Line (and other tables where this may be a problem) and prevent the lock escalation as described in the KB. If this works, we would like to know

with best regards

Jens

jglathe

Hi Mirko,

let me guess.... lock escalation of the SQL Server? Sounds like you have a heavy load when these postings run. When there are too many (as always with SQL Server, try to quantify this) row locks on a table, the whole table is locked. There is a (really old) blog where this is mentioned, can't find it at the moment... but the Microsoft KB article describing this. With this, you could try to set up T-SQL scripts that run on Sales Line (and other tables where this may be a problem) and prevent the lock escalation as described in the KB. If this works, we would like to know

with best regards

Jens

Belias

as i wrote in the original post, i had the feeling that the lock escalation was the problem...thanks for the kb, but the link shows me an empty page
NVM the link works...it's just my IE that is not getting it. LOL.

rmv_RU

Try to change transaction isolation level from Serializable to Repeatable read: http://forum.mibuso.com/discussion/45370/serializable-vs-repeatable-read.
If you use the Serializable isolation level SQL server always blocks a last line of previus order and a first line of next order (index range) to prevent phantoms during posting. If there are no next order lines then XXXXXXXX range will be blocked and it will be impossible to insert new lines. Also, using IsEmpy or setrange, setfilter functions with no selectivity key can cause a table lock - the SQL server lock whole table to prevent new records in selected criteria range.

Belias

The problem is solved. There was a weird customization done some time ago that prevented nav to Group up the lines with the same e.g. G/l account, dimension, blablabla when posting to the G/L entries. This DESTROYED performances during sales invoice posting.