SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enabling RCSI


Enabling RCSI

Author
Message
alex.palmer
alex.palmer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 469
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202638 Visits: 18548
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" ;-)
alex.palmer
alex.palmer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 469
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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339589 Visits: 33992
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
alex.palmer
alex.palmer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 469
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

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339589 Visits: 33992
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
alex.palmer
alex.palmer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 469
Interesting.........

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

I guess there's tempdb considerations, but anything else apart from that?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202638 Visits: 18548
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 BigGrin


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" ;-)
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202638 Visits: 18548
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" ;-)
alex.palmer
alex.palmer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 469
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search