Question on Locking and Commit

jordi79

Hi, I have this funny scenario I want to share with you people, and feedbacks are welcome...

I am using NAV ver 5.0 on SQL2000.

Scenario 1
I have a batch job that imports from Excel in GenJnlLine table. I have a problem whereby, when this batch job is importing a large no. of lines into GenJnlLine, it prevents another user from posting into the GenJnlLine table. This is causing problems, as there are a lot of concurrent users in the database. We know that this is the nature of NAV, as the posting procedure will attempt to Lock the GenJnlLine table. and with the import batch job running, this is impossible.

Scenario 2
To solve the problem, I have added a COMMIT statement to the Import batch job, that will COMMIT the imported GenJnlLines to the database for every 20 records imported. This will allow another user to post GenJnlLine without facing a table lock error.

This is where the funny part comes...

While testing, this worked beautifully. But something puzzled me.

TEST1
In TEST1, I have 2 concurrent sessions running. If the posting is done first, and the Import batch job is run later, the Posting will have no issues, but the Import batch job will face a table lock error.

I expected this result, as the table lock is preventing the batch job from inserting records into GenJnlLine table.

TEST2
In TEST2, I have 2 concurrent sessions running. If the Import batch job is run first, and the posting is done later, the Import batch job faced no errors, and the Posting also faced no errors.

I would have expected the Import Batch job to face a table lock error in TEST2. As after the batch job has COMMIT, and the GenJnlLine posting has locked table, I would expect that the batch job could not insert any more records, and issue a table lock error. But to my surprise this did not happen.

Any ideas? The results in TEST2 contradicts the RDBMS rules of table locking. As once the posting procedure has locked GenJnlLine, I would expect that the import batch job would not be able to insert into GenJnlLine. But this is not the case.

:S

rdebath

Navision on SQL NEVER issues table locks. MS-SQL may promote lots of locks to a table lock, but this is quite rare.

The Navision LOCKTABLE command causes all future reads of the database to lock records and ranges such that if the generated SQL is repeated the results will not be altered by any other process. If you issue a range SELECT eg:

SalesInvoiceLine.SETRANGE("Document No.", SalesNo);
SalesInvoiceLine.FINDSET;

The database will lock all records in the range (ie: with SalesNo) PLUS one record either side of the range! If the records are far enough apart two range selects will not interfere with each other. If the second to attempt the lock isn't holding any problem locks it will wait until the first finishes. Only if both are holding locks on records that the other session requires will you get a locking error message.

It's very complex to work out exactly what the resulting locks of a set of statements will be (and the problem is even more difficult with sumindex tables and indexed views) which is one of the reasons that the Navision posting codeunits "lock the G/L" at the start of a posting. They use the code

GLEntry.LOCKTABLE;
GLEntry.FINDLAST;

This code DOES NOT lock the entire GLEntry table it locks the LAST record in the table and the gap after the last record. For posting this has the same effect but it doesn't stop another session modifying earlier records in the GLEntry table.

jordi79

Hi Robert,
You are correct. I found out that the LOCK placed by SQL affects only on filtered set of records. Well... I always was confused with nav locking and SQL locking. I found out that locking happened due to some codes in my import batch job. and finding out what caused it is also difficult. Certain command lines like NEXT and FIND also will place additional locks.