Table Locking

ajayjain

I know there are lots of threads for performance & locking objects

I am looking for your views on this

In few threads it is said that NAV/SQL lock the record not the whole table.
but I believe it locks the whole table rather than just 1 record

if 100 users are working on the same table but with 100 different records they still get locking issues.

No. Series, Dimension quite common

Document Dimension primary key is always different from user to user but still get locking issue.

I wrote a function for No. series to get the new no.

GetNewNo(SeriesCode : Code[10])
NoSeriesLine.LOCKTABLE;
NoSeriesLine.GET(SeriesCode,10000);
NoSeriesLine."Last No. Used" := INCSTR(NoSeriesLine."Last No. Used");
NoSeriesLine.MODIFY;
NewNo := NoSeriesLine."Last No. Used";
CLEAR(NoSeriesLine);

and use this function everwhere
GetNewNo(Cust), GetNewNo(Vend), GetNewNo(SalesOrder) etc

and I get lots of locking issue.

I am running NAV 4 SP3 with SQL 2005 with 300 users

Please advise

davmac1

Do a search on the postings by Jorg Stryk (hope I spelled your name right!).
When you lock a record in the no. series, you are locking the page. If you throw in some dummy records to create separation between the no. series records you are using, you could cut down the problems.
When you post, you lock the G/L Entry table until posting is complete.
With 300 users, it would be worth while consulting with one of the NAV SQL Server tuning experts who make their living tuning the larger NAV systems.
Either that or buy books, google, and trial and error.
In NAV 2013, they have done a lot of work to cut down on locking problems, but you have to do a complete upgrade to move to it.

ajayjain

Thanks David,

What is the page size, 8KB?
how many records should we have around (before and after original record) to cut down the problems

davmac1

Page size is 8K. The space taken is not going to be a multiple of the record size - varchar fields and overhead.
You can get the minimum record size and use it.
You could also add dummy fields that are filled - at the expense of network transfer time.
If you are not going to hire an expert, then experiment - on a test database.