Post reply

Isolation Level

  • August 26, 2015 at 3:14 pm

    #320133

    Hi,

    One of our production database is using Readcommitted snapshot isolation level (RCSI) but one sp is beginning with hint use transaction isolation level read uncommitted.

    Is there any affect of using readuncommitted while it is RCSI other than dirty reads?

  • August 26, 2015 at 3:48 pm

    #1822632

    ramana3327 (8/26/2015)

    Hi,

    Is there any affect of using readuncommitted while it is RCSI other than dirty reads?

    Why even get near the problems associated with dirty reads and/or missing data? Since you are using RCSI, the Select statement will use the previously committed image of the row(s) in tempdb. Accordingly, SELECT statements can access the last committed value of the row while other transactions are updating the row without getting blocked! Of course this does not guarantee that you won't experience blocking. It just helps to minimize it!

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • August 27, 2015 at 4:15 am

    #1822696

    Same effects as if you were using it without snapshot turned on. Duplicate reads, missing rows, dirty reads and this time for no gain at all because under RCSI readers don't block writers. Hence by switching that proc to read uncommitted (which overrides the default isolation level), you're getting all the penalties of read uncommitted but there's no benefit.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 27, 2015 at 1:04 pm

    #1822892

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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