March 8, 2011 at 1:16 pm
Hello,
I was reading this article below and it got me thinking more about how the RCSI works.
It appears to me that if I wanted to use RCSI then it's probably safer to make sure that on the DML stored procedures (DELETE, INSERT, UPDATE) that have a select statement or inner join in them to force the READCOMMITTEDLOCK hint. Otherwise, you could potentially read the version record from the version store when changing data. The change might be blocked but the read operation would happen first.
On SELECT stored procedures reading the version store might be approperate.
I'm curious how people handle these challenges and do you take any special steps in your DML statements.
My other thought was that maybe the DML stored procedures should run under a higher isolation level and those who are running SELECT searches could run under the RCSI level to avoid blocking writers.
Obviously testing is key, and how we write the code..
March 9, 2011 at 8:12 am
We've been using it quite a while with no issues. I suppose it's remotely possible that you could mixed data if you had a lot of transactions all trying to modfy the same data all the time, but that's a fairly unusual situation. I wouldn't suggest any type of hints in a query until you've identified an actual problem and there is no other way to solve it. SQL Server does a surprisingly good job of managing transactions all on its own. Hints are an attempt to wrest control from the system and more often than not create more problems than they solve.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply