This past week I had a great Client ask an even greater question, and it got me thinking. The question is straightforward (albeit nerdy, which I love). “We’ve enabled RCSI, and we still get serializable connections from our apps. What gives?” Well, it turns out that you can’t just stop at enabling Read Committed Snapshot Isolation (RCSI) in your environment to get the full effects.
Basically, the use of the WITH (NOLOCK) query hint performs a dirty read, of which I’m sure you can find oodles of examples on the Internet about. Microsoft introduced RCSI in SQL Server 2005 to help you reduce the amount of blocks, and with RCSI exclusively in use, database readers no longer block other readers or writers. I love enabling RCSI wherever appropriate, as long as the TempDB database is monitored to make sure that RCSI’s version store is not causing any issues (and it usually is just fine). It also goes without saying that with RCSI enabled, you should work to remove the WITH (NOLOCK) query hints from your code, as RCSI is superseded by NOLOCK and can still invoke dirty reads.
But… this Client is still seeing serializable connections that will occasionally cause issues. What else can we check?
First, check if connection pooling is in use by your connected applications. The transaction isolation level does not get reset when a connection is returned to the connection pool. If you see this, have a different defined connection string with a different application name defined in the connection string that clearly lists serializable so that the serializable connections are pooled separately. Or… stop using connection pooling.
If this isn’t the case, sample the DMV sys.dm_exec_session and search for serializable connections and see what applications is invoking them. Many times the application itself defines a serializable connection as part of it’s connection properties, and other times it will define a serializable isolation level when an explicit transaction is used (even if the default is read committed when you don’t explicitly open a transaction scope).
So… I hope you can enable RCSI for your critical applications, but don’t stop there! Continue to monitor and validate that no serializable connections are being established by your applications, and work to clean this up if you see it occurring!
Thanks Rick Lowe for your valuable insights on this!
The post Don’t Stop at Just Enabling Read Committed Snapshot Isolation (RCSI) first appeared on Convergence of Data, Cloud, and Infrastructure.