Post reply

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ????

  • July 1, 2021 at 4:53 pm

    Go to Answer

    #3902526

    Hi

    two questions on SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED when in a stored procedure

    1. Does it matter where in the SP it is placed?
    2. Will one statement apply for ALL select statement? So if I have a select within a select, does it still apply?

    Thanks

     

  • July 1, 2021 at 5:05 pm

    Answer

    #3902545

    Yes it matters. Put it before the statements you want it to apply to. It applies to all of the statements which follow it in the proc, unless you issue another command to change it.

    There are some other questions you should ask yourself, before using it:

    3) Am I happy for my SELECT queries to occasionally return data which has not yet been, and may never be, committed? So-called 'ghost data'.

    4) Am I happy for my SELECT queries to occasionally return multiple versions of the same data row?

    5) Am I happy for my SELECT queries to occasionally fail to return rows which should be in the resultset?

    As long as you understand that any of the above may happen, under certain circumstances, and accept that, you're all good.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 1, 2021 at 5:21 pm

    #3902550

    Thanks for getting back Phil.

    So a select within a select is covered as long as it's before...

    I get the drawbacks but it's for a table I build for a report with data at least  3 days old.

    Maybe you can point me where I'm having an issue.

    This SP(nightly job) used to run off a DB that was from a backup restore. Took a bit to run 45minutes

    We switched to a transactional Replicated DB, I create a backup and restore this a DB(the replicated DB) nightly  to another DB (call it DB_Backup) for my own jobs. The job now never finishes... at least 6+ hours into it.

    I guess my question is why so slow with replication and can I make the settings for my"DB_Backup"  so its quicker.

    Thanks...

     

     

  • July 1, 2021 at 5:27 pm

    #3902552

    OK, it's good that you understand the implications.

    My experience with replication is limited, and therefore so is my advice! I think that you need to be monitoring what is going on on the server while your job is running in order to start tracking this down. Unless the TRANSACTION ISOLATION LEVEL change works, of course.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 1, 2021 at 5:32 pm

    #3902556

    Thanks... ll' mess around in my sandbox 🙂

     

  • July 1, 2021 at 5:49 pm

    #3902563

    But note that you can get phantom reads and nonrepeatable reads in the default READ COMMITTED mode as well.

    And that the majority of issues with NOLOCK are caused by SQL using allocations scans, which you can prevent.

    So dirty reads are the real issue with UNCOMMITTED.  And, in a properly designed system that doesn't do many rollbacks, they are often not that big of a problem for most reports.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply