Enabling RCSI

  • Hi

    I'd like to be able to enable RCSI on some of our databases.

    I know that if your code is not developed to use snapshot isolation then unexpected results can be returned.

    Can anyone offer any advice, or point me to some documentation, on how to test code to see how it reacts to snapshot isolation?

    When I talk to our developers and testers nobody seems to know how to go about this. Is there certain sorts of code and constructs we should look out for?

    Cheers

    Alex

  • alex.palmer - Wednesday, December 20, 2017 4:40 AM

    Hi

    I'd like to be able to enable RCSI on some of our databases.

    I know that if your code is not developed to use snapshot isolation then unexpected results can be returned.

    Can anyone offer any advice, or point me to some documentation, on how to test code to see how it reacts to snapshot isolation?

    When I talk to our developers and testers nobody seems to know how to go about this. Is there certain sorts of code and constructs we should look out for?

    Cheers

    Alex

    RCSI is transparent to the user session, it will automatically convert a read committed session to read committed snapshot.
    Note that this isolation level is different to Snapshot Isolation

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi Perry

    Thanks for he reply. I know what RCSI is and the difference between that and the isolations level is

    My question is around reviewing existing code before switching on RCSI. How do you identify code that might suffer from the Black Marble issue.

    Cheers

    Alex

  • To implement RCSI there really isn't anything you need to do to your code. It's the more traditional snapshot isolation that requires code changes. As Perry already stated, this will be invisible to your users. The one thing you should check though is that the connection strings for your applications need to either accept the default isolation level or use RCSI. If they set a different level, that's what will be used for that connection. However, it's pretty rare to see any code that sets the isolation level independently (but not impossible).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Read this link and then tell me I don't need to be concerned about code 😉

    https://blogs.msdn.microsoft.com/craigfr/2007/05/16/serializable-vs-snapshot-isolation-level/

    I know to enable it I just a drop down  in SSMS, but doing so may impact results returned by code.

    Here's an extract from Brent Ozars article about snapshot isolation and RSCI

    .

    The biggest consideration with an existing code base is how much testing is available for the code. I think the READ COMMITTED SNAPSHOT isolation level is great, but it is going to change your default isolation level for every query right away. This may lead to incorrect results, depending on how your code is written

    I work in finance so incorrect results are a big deal. I'm looking for some guidance on what testing should look like, or how to identify the most likely bits of code that might be impacted.

    Cheers
    Alex

  • This is where things get fun.

    Defining what "correct" results look like is very entertaining. I've worked with people who put WITH NO_LOCK on every single query in the system. When they finally start using RCSI, they also start getting "incorrect" data. In fact, they were eliminating duplicate rows and adding back in missing rows, not to mention getting more consistent results. However, the business wasn't seeming the same results so they got very concerned.

    Assuming you're already using the default isolation level, your results shouldn't change at all. You're still only seeing committed rows from a query. It'll be the same with RCSI. The difference is that instead of being blocked while uncommitted transactions complete, you can still read that data. Now, again, that could lead to what a business considers incorrect results if they expected to see the data only after the commit is complete. However, that's the dance. You have to wait for the commit to complete, which means blocking, or, you can see the already committed data immediately.

    I too came out of the financial industry. When I changed from the default isolation level to RCSI, the only word I heard was how much faster things were. My business people didn't expect to see the uncommitted data, but only the committed data. If yours expect to see the uncommitted data, then you have an issue.

    However, none of this is directly related to the code, nor would changing the code change what's being returned by the queries. It is down to committed vs. uncommitted data and what the expectations around that are.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Interesting.........

    What would you consider "due diligence" before switching it on then?

    I guess there's tempdb considerations, but anything else apart from that?

  • alex.palmer - Wednesday, December 20, 2017 6:00 AM

    Read this link and then tell me I don't need to be concerned about code 😉

    https://blogs.msdn.microsoft.com/craigfr/2007/05/16/serializable-vs-snapshot-isolation-level/

    That's not even close, it compares serializable to snapshot and as I said RCSI works very differently to SI.

    Grant Fritchey - Wednesday, December 20, 2017 7:08 AM


    Defining what "correct" results look like is very entertaining. I've worked with people who put WITH NO_LOCK on every single query in the system. When they finally start using RCSI, they also start getting "incorrect" data. In fact, they were eliminating duplicate rows and adding back in missing rows, not to mention getting more consistent results. However, the business wasn't seeming the same results so they got very concerned

    you feel my pain then too Grant 😀

    alex.palmer - Wednesday, December 20, 2017 7:20 AM

    I guess there's tempdb considerations, but anything else apart from that?

    Yes, size it and ensure it's going to be at best performance

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • alex.palmer - Wednesday, December 20, 2017 06:00 AM


    I know to enable it I just a drop down  in SSMS, but doing so may impact results returned by code.

    You'll receive versioned rows that are consistent and won't block, how is that an issue?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Straight off the bat I worry that the overnight batch processes may "rely" on SELECTs being blocked by UPDATEs for consistent results.

    Also everything else I read says Optimistic Locking is great, but beware your old code written for pessimistic locking may not return the different results

    The Brent Ozar article builds on Craigs article and shows how you get a similar problem with READ COMMITTED to RCSI. It starts in section three.

    You both seem to be telling me there is no significant downside or risk to switching on RCSI. Do you enable it on all your databases?

  • alex.palmer - Wednesday, December 20, 2017 8:21 AM

    Straight off the bat I worry that the overnight batch processes may "rely" on SELECTs being blocked by UPDATEs for consistent results.

    Also everything else I read says Optimistic Locking is great, but beware your old code written for pessimistic locking may not return the different results

    The Brent Ozar article builds on Craigs article and shows how you get a similar problem with READ COMMITTED to RCSI. It starts in section three.

    You both seem to be telling me there is no significant downside or risk to switching on RCSI. Do you enable it on all your databases?

    The marbles scenario does target a very specific issue with RCSI, due to its statement level impact there can be issues where multiple statements are combined. In that respect you'll need to know your code.
    Don't enable it by default no

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Ok, then back to my original question.......

    Do you have any advice on how to identify code that might suffer from the issue described, or guidance of the kind of testing we'd need to undertake before switching on RCSI

  • alex.palmer - Wednesday, December 20, 2017 8:30 AM

    Ok, then back to my original question.......

    Do you have any advice on how to identify code that might suffer from the issue described, or guidance of the kind of testing we'd need to undertake before switching on RCSI

    The queries that are going to be susceptible to the "marble" scenario are going to be those that update or delete multiple rows per transaction on "high traffic" tables. The amount of time the individual transactions remain open, the number of rows being updated or deleted and the specific access patterns will all come into play.
    So, the question is, do you have business processes that would see the same set of rows being updated or deleted by more than one session at the exact same time?
    If the answer is yes, then you'd want to identify the procedures associated with those processes. 
    Assuming that you're currently using the default RCI, there's a high probability that those procedures are currently either causing blocking issues or are being blocked by other procedures... So, if you aren't already doing so, start capturing the blocking statistics and find out which queries are most frequently involved in the blocking chains.

  • alex.palmer - Wednesday, December 20, 2017 8:30 AM

    Ok, then back to my original question.......

    Do you have any advice on how to identify code that might suffer from the issue described, or guidance of the kind of testing we'd need to undertake before switching on RCSI

    Even after those examples, I'm back where I was. The overriding majority of my code doesn't notice RCSI. A bunch of it benefits, a few bits might be reliant on old mechanisms. Testing is the only way to be sure, but after years of using it, I haven't hit a problem.

    I can't say that it's on by default only because a lot of systems I used to manage were small enough with low enough volume & latency that switching it on was more trouble than it's worth. Remember, tempdb is shared space. As I keep explaining to my daughter away at college, you can't dictate what happens in the shared space as long as everyone using it is reasonable. In tempdb's case, you really can't dictate that, so I try to minimize RCSI only to those systems that will truly benefit.

    In short, I use it everywhere important, but not by default because of tempdb concerns, not data concerns.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for all the replies, they definitely give me food for thought.

Viewing 15 posts - 1 through 14 (of 14 total)

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