Don't be affraid of RCSI

Enabling SQL Server RCSI

RCSI is not something you get from typing too much, it's a setting in MS SQL Server that is disabled by default. Arguably it shouldn't be. It stands for Read Committed Snapshot Isolation and when enabled means that you can select data from the database with less risk of locks and also know that the data you're getting was accurate when you started the request.

I enabled it after we we started to see a lot of failed transactions in the logs due to timeouts on requests for locked resources. The thing that confused me was the locks were being taken out for transactions that were only doing selects. Enabling it made all the locks and the problem go away, but I wasn't sure it was best practice and if it was I was confused why it wasn't the default.

I saw an excellent talk on SQL for Developers last night by Phil Grayson, of xTen, a SQL expert who goes from company to company sorting all their SQL woes. When he recommended using this setting and explained why, I felt better about my decision to use it earlier this year. It also made me feel better when he said that in all the companies that he'd enabled this feature it has always helped and it had never had any negative effects. It's always a bit scary fiddling with database settings and I normally assume that the default setting was chosen by someone with more database smarts than me.

This less than catchy but informative article explains exactly what the benefits of RCSI are. The default isolation level for a transaction in SQL Server is called Read Committed. The isolation level is how SQL server decides what order to do things when it is serving more than one transaction. With read committed the priority is making sure that the results are accurate, rather than being quick. This means it waits till all writes on a resource are complete before reading data for any other queries. This can even mean that SQL server will lock tables when a transaction is reading multiple tables even if it's not doing any writing. SQL Server is doing all it can to make sure that a read transaction does not get any data that is rolled back by another transaction.

With RSCI a snapshot is taken before data is changed and then if another transaction tries to read that data before the change transaction is complete it is served the data from the snapshot. This means there is the potential for that data to be seconds out of date, but in most cases that doesn't matter, and there is a big speed and efficiency saving of not having to use as many locks and waiting less.

You can turn it on in SQL Server management studio in your database properties under Options > Miscellaneous, set 'Is Read Committed Snapshot On' to True. You can also do it with code following these instructions. The server needs to have no active connections before this can be enabled, and it's not easy to roll back from. For all new projects I set this as standard now, but for existing ones you'll probably want to do some testing first. If you're already setting different isolation levels on different queries you should test that. You may find you don't need to anymore.

I'm not a DB expert, so I'm going to stop short of saying you should definitely use RCSI, but you should check it out.

No Comments