My developers have only ever worked with Read Committed, but my new server has Read Committed Snapshot Isolation (RCSI) enabled. What do they need to do differently when writing application code?

  • Minus four without any comments? Helpful.
    – J. Mini
    Commented Mar 1 at 18:52

2 Answers 2

  1. Stop using NOLOCK hints
  2. Stop setting transaction isolation levels to READ UNCOMMITTED
  3. Add appropriate READCOMMITTEDLOCK hints to queries that require non-versioned data
  4. Mind transaction duration, as it will increase version store size
  5. Be mindful that not everything will use versioned rows, like reads to validate foreign key integrity
  6. Understand that no isolation level is perfect for an entire workload, and certain queries may need to use read committed, repeatable read, or serializable in some circumstances
  7. When available, use Accelerated Database Recovery alongside optimistic isolation levels so that the version store is local to the database, and not sent to tempdb (especially if many databases are intending to use one) to reduce the load there
  • 1
    Maybe I've missed something, but where did the OP say anything about his devs using READ UNCOMMITTED? "2. Stop setting transaction isolation levels to READ UNCOMMITTED".
    – Vérace
    Commented Feb 29 at 20:10
  • 1
    @Vérace many SQL Server shops that use the default isolation level (read committed) end up absolutely swaddling their queries in NOLOCK hints to avoid blocking. Local query hints (and setting the transaction isolation level) will override use of database isolation level settings. It's practically default advice at this point to tell people to stop doing that. Commented Feb 29 at 20:30
  • Hmmm... I see - doesn't strike one as best practice all right! "Use the database Luke..."
    – Vérace
    Commented Feb 29 at 20:48
  • 2
    @Vérace everyone thinks they're a jedi, but i don't see any light sabers. Commented Feb 29 at 22:00

Any trigger that controls some logic in modified table will get the last committed version and will not "see" any change that is made contemporarily, so for example it can lead to have overlapping time periods/duplicate rows even if there is a trigger after insert that controls this.

We've found pairs of rows completely identical that were inserted at the same time and the trigger did not stop the second record from being inserted because at the time of control its reading was not blocked and it successfuly read all the rows excluding that identical row that was inserted in other session, so now we use with(readcommittedlock) while reading the table in the trigger. We cannot implement overlapping control in other way that is not a trigger and it was a big surprise for developers that did not even know about RCSI enabled in every db.

  • Good to know, but I worry for any place that lets developers write triggers.
    – J. Mini
    Commented Mar 1 at 18:52
  • @J.Mini I must confess that I'm a bit confused - isn't writing triggers part of a dev's job description?
    – Vérace
    Commented Mar 1 at 19:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.