When using Microsoft SQL Server, enabling Read Committed Snapshot Isolation (RCSI) is one way to prevent reads (
SELECT statements) from escalating into full table locks. Depending on your application this can either be a good or a bad thing. I’m not going to get into the why’s and why-nots of each strategy - this is a good article to read if you’re having a hard time deciding which strategy to choose and why.
So let’s say you want to enable RCSI on a fictional database
MyDB. This can be achieved by simply issuing the following T-SQL:
ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON GO
To check that it was successfully enabled, you can check the System View
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE [name] = 'MyDB'
If it returns
1 then RCSI was successfully applied, you’re done! Unless…
Can’t get database lock
Oftentimes, the above T-SQL will just hang forever. 99% of the time this is because there are still active connections to the target database (
MyDB in this case).
You can apply the RCSI change and rollback any active transactions at the same time by running:
ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE GO
Depending on your application this might not be the best way and there are a few different ways you can tackle this that could give you more control over how any when the existing transactions get
- Shut your app down, take the downtime and enable RCSI
- Disconnect your app’s database connections and enable RCSI (hopefully it can reconnect automatically)
- (Advanced) enable RCSI on a hot-replica or mirror database and fail your app over to that server, enable RCSI on the primary, and then fail back (urge to drink: rising)
Having needed to enable RCSI on an active application with active database connections in the past, I’ve found that option 2 works well as long as you do it during off-hours (obviously - who does database maintenance during peak anyways right? Right? Guys??)
Here’s a script that can do this (using
MyDB again as example):
-- Switch over to master to avoid hanging connection problems USE master GO /** * Cut off live connections * This will roll back any open transactions after 30 seconds and * restricts access to the DB to logins with sysadmin, dbcreator or * db_owner roles */ ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK AFTER 30 SECONDS GO -- Enable RCSI for MyDB ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON GO -- Allow connections to be established once again ALTER DATABASE MyDB SET MULTI_USER GO -- Check the status afterwards to make sure it worked SELECT is_read_committed_snapshot_on FROM sys.databases WHERE [name] = 'MyDB'
Note: you could also put the DB into
SINGLE_USER mode but if you have active client connections, this is probably a bad idea because one of them might end up snatching up the single available connection.
If that last
1 then you are done! Pat yourself on the back and enjoy your favorite beverage.
The problem with Mirroring
Of course, if you try and enable RCSI on a database that is part of a mirroring partnership, then you’re likely going to run into this awesome error message:
The operation cannot be performed on database "MyDB" because it is involved in a database mirroring session.
This is a relatively easy one to solve. You just need to temporarily disable, and then reenable the mirroring partnership.
USE master GO -- Break the partnership ALTER DATABASE MyDB SET PARTNER OFF GO -- Restrict access ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK AFTER 30 SECONDS GO -- Enable RCSI for MyDB ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON GO -- Allow connections to be established once again ALTER DATABASE MyDB SET MULTI_USER GO -- Re-enable the partnership ALTER DATABASE MyDB SET PARTNER = 'tcp://MyDBPartnerHost:5022' GO
Again, check the
sys.databases view for confirmation that it worked.
These steps also apply many other database-level configuration changes. Many of them block on open client connections and so will need to be taken into
SINGLE_USER while the change takes effect.
Hopefully this article helped someone - please leave a comment if it helped you, or if you have any issues.