Reading of uncommited data

ta5ta5 Member Posts: 1,164
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

Answers

  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • KowaKowa Member Posts: 918
    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
    Kai Kowalewski
  • bbrownbbrown Member Posts: 3,268
    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".
    There are no bugs - only undocumented features.
  • ta5ta5 Member Posts: 1,164
    Hi
    Thanx so far. My version is 2013 R2 on SQL Server 2012.
    Regards
    Thomas
    Kowa wrote:
    ...About 3. Which version are you using? ...
  • ta5ta5 Member Posts: 1,164
    Anyone else?
    Do you think this is more kind of a pseudo problem rather than a real problem?
    Thx in advance.
    Thomas
  • bbrownbbrown Member Posts: 3,268
    Why do you see it as a problem? It is simply the way things work.
    There are no bugs - only undocumented features.
  • ta5ta5 Member Posts: 1,164
    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
  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • ta5ta5 Member Posts: 1,164
    Hi BBrown
    Thanks for the valuable input. I'll have a look on that.
    Regards
    Thomas
Sign In or Register to comment.