Reading of uncommited data

ta5

Hi

I know, there have been some threads about this, but I'd really appreciate some more details about this behaviour. Let's make an example, a simple codeunit.

Rec1.INIT;
Rec1.pk := 1;
Rec1.INSERT;

Rec2.LOCKTABLE;
Rec2.GET(1);

Rec2.GET(1) is successful, altthough Rec1.INSERT is not commited yet.

My questions so far:
1: Is this normal NAV on SQL Server behaviour?
2: Could a LOCKTABLE command help to avoid reading uncommited records?
3: Do other sessions also see uncommited records?

Thx in advance
Thomas

bbrown

The INSERT and READ are in the same transaction. So the new record is able to be read within that transaction. Nothing to do with the isolation level.

The default isolation for NAV is "read uncommitted", so others would be able to read that new record.

Kowa

About 3. Which version are you using?
In NAV 2013 the default isolation level is REPEATBALEREAD, and this option was already introduced in older versions as an alternative configuration.
http://blogs.msdn.com/b/nav/archive/201 ... rsion.aspx
viewtopic.php?f=32&t=46217
http://blogs.msdn.com/b/nav/archive/201 ... tions.aspx

bbrown

Kowa wrote:

...In NAV 2013 the default isolation level is REPEATBALEREAD, and this option was already introduced in older versions as an alternative configuration...

The isolation level of REPEATABLEREAD is only inside an active transaction. This was SERIALIZABLE in older versions. The default isolation level is still "READ UNCOMMITTED".

ta5

Hi
Thanx so far. My version is 2013 R2 on SQL Server 2012.
Regards
Thomas

Kowa wrote:

...About 3. Which version are you using? ...

ta5

Anyone else?
Do you think this is more kind of a pseudo problem rather than a real problem?
Thx in advance.
Thomas

bbrown

Why do you see it as a problem? It is simply the way things work.

ta5

bbrown wrote:

Why do you see it as a problem? It is simply the way things work.

Hi
Sorry, for not answering a long time...
Of course you are right, it's just as it is.
I have one special case in mind:

Lets think about a background process or nas, it starts every 2 minutes and exports all posted sales invoices to a a file. When reading uncommited data, the process could happen just to find a posted sales invoice header with 4 lines, because the posting routine was just posting a sales invoice with 5 lines, but the 5th line is not yet written. Is this a possibly pittfall to avoid?

Thanks
Thomas

bbrown

In your scenario, I wouldn't have NAS reading the posted sales invoice table directly. Rather I would have the posting process write a record off to a staging table as the last thing it did. Then have NAS process that table and retrieve the sales invoice data as needed. Also clearing processed records from the staging table.

Another approach would be to have your NAS process and the posting lock a common semaphore table. The posting already uses GL Entry for this purpose. This would insure that both functions never ran together. This, of course, would could have some concurrency impact. But that would depend on your transaction volume and how often the NAS runs.

ta5

Hi BBrown
Thanks for the valuable input. I'll have a look on that.
Regards
Thomas