locked
database -allow_snapshot_isolation on and read_committed_snapshot RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi  our sql server versions 2016 SP2 enterprise and having deadlock  our the period of time. so Application team adviced to enable following setting DB level . Pleas note these DB are  always on enabled 

    ALTER DATABASE <> SET allow_snapshot_isolation ON
    ALTER DATABASE <> SET read_committed_snapshot ON

    I would like to know what are the negative impact  performance of the DB and accuracy of the reports or transaction level of the DB. ( change from default settings ) 

     

    Any impact of Always on setup read replicas which any impact with behind some row versions 

    Thank you for the advice 

    Thanks 

    Friday, August 14, 2020 10:36 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Deadlocks something you try to avoid, but are normal and expected in a multi-user database environment.  The application code should trap the deadlock victim error and retry the transaction.

    https://www.mssqltips.com/sqlservertip/3188/implementing-sql-server-transaction-retry-logic-for-failed-transactions/

    https://stackoverflow.com/questions/13159326/implementing-retry-logic-for-deadlock-exceptions

    Before changing the isolation level, you should understand the full impact. 

    Please see:

    https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-ver15

    Friday, August 14, 2020 12:21 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Any impact of Always on setup read replicas which any impact with behind some row versions 

    I don't think so.

    When you enable any form of snapshot, this causes SQL Server to write a 14-byte pointer to a row when it is updated. This introduces page splits, and when you rebuild the index, the 14-byte pointers goes away.

    This may sound a little scary, and indeed there can be an impact. But as I understand it, you already have a readable secondary in your AG. And in that case, you have already some form of snapshot enabled. That is, you are already getting these 14-byte pointers and the extra load in tempdb for the version store.

    Then again, if you already have a readable secondary, one like to think that you would not get very many deadlocks between readers and writers, because the readers should be on the secondary. So enabling snapshot may not resolved the deadlocks. But I don't know anything about your system.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 14, 2020 9:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi I understand (RCSI)  will resolved blocking issues. But Do you need to enable SI  to stop locking  issues ? What would be any issue by enabling this SI with RCSI

    regards


    Monday, August 17, 2020 5:36 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi I understand (RCSI)  will resolved blocking issues. But Do you need to enable SI  to stop locking  issues ?

    That depends on what the application team has in mind. Maybe they plan to rewrite code to use true snapshot isolation. Or, who knows, may be they check if the database permits snapshot isolation and use it if so.

    But as such, enabling snapshot isolation will not change anything.

    What would be any issue by enabling this SI with RCSI

    Not really.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 17, 2020 9:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Erland Its a great information  and good discussion. However no reason why Micorsoft document say to enable both as following information 

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

    According to this url, application team want to change it the SI  with out change their apps code.  I understand no impact on locking either. But I am having concern any issues with figures  changes with calculations  if both SI & RCSI .

    regards

    Wednesday, August 19, 2020 1:56 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sorry, I don't understand what your hangup is. You already have row-
    versioning enabled in the database, since it is in an AG with a readable secondary. So there will be no impact with enabled RSCI and/or SI.

    It may be true that the apps team will not be able to take benefit of snapshot isolation initially, but maybe they think they may have use for it in the future.

    So just enable it and make the apps team happy.

    /Erland

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 19, 2020 9:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi  Erland Thank you for the reply. 

    That fine no impact on Alwayson secondaries . 

    As you said enabling snapshot isolation does not make difference unless change the code to use it itself. Elabling DB level does not help . Therefore what is the motivation to do SI  as well?  

    Thanks 

    Thursday, August 20, 2020 1:02 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    As you said enabling snapshot isolation does not make difference unless change the code to use it itself. Elabling DB level does not help . Therefore what is the motivation to do SI  as well?  

    Obviously, I cannot answer for your application team. Then again, I have offered a few ideas of why in this thread.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 20, 2020 9:01 PM