// Week 18: Optimistic Concurrency

Hello and welcome back to the SQL Server Performance Tuning series. Last week I have kicked-off the 5th month of the training plan by talking about pessimistic concurrency. Today we will continue by talking about Optimistic Concurrency.

Row Versioning Optimistic concurrency was introduced back with SQL Server 2005 and is based on the principle of Row Versioning. The idea behind row versioning is that readers (SELECT queries) don’t acquire shared locks anymore. Instead of waiting until a shared lock is acquired successfully, readers get back a previously committed version of the row. Older, previous versions are stored in the so-called Version Store, which is persisted in TempDb. Writers (UPDATE, DELETE statements) transparently copy old versions to the version store, and the new version points to the older version in TempDb. The following picture illustrates this concept.

One side-effect of adding this pointer is that every record gets expanded by 14 additional bytes. This may introduce:

1. Forwarding Records on Heap Tables
2. Page Splits on Clustered Tables

In addition you also have to plan and size TempDb accordingly, because you will introduce additional I/O, which can lead to contention problems in a default configuration. Let’s have a look now at the 2 new optimistic isolation levels that SQL Server provides you.

Optimistic Isolation Levels

Since SQL Server 2005, the relational engine provides 2 new optimistic isolation levels that are based on the concept of row versioning as discussed in the previous section:

1. Read Committed Snapshot Isolation (RCSI)
2. Snapshot Isolation (SI)

Let’s have a more detailed look at both isolation levels. RCSI provides you a statement-level based snapshot isolation. In other words, SQL Server will always return to you the version that was valid at the beginning of the statement. It is an optimistic implementation of the Read Committed Isolation Level. Therefore you also have Non-Repeatable Reads with this isolation level.

One of the nice things about RCSI is that it is completely transparent to the database/application itself: you just enable it on the database, and afterwards the new default isolation level for every query is Read Committed Snapshot Isolation. Therefore you can overcome locking & blocking problems, and even deadlocking problems very easily by enabling RCSI for a specific database. The following code shows how you can enable RCSI for a given database.

-- Enable Read Committed Snapshot Isolation (RCSI)
ALTER DATABASE AdventureWorks2012 SET READ_COMMITTED_SNAPSHOT ON
GO

-- Check if RCSI is now enabled
SELECT
     name,
     is_read_committed_snapshot_on
FROM sys.databases
WHERE database_id = DB_ID('AdventureWorks2012')
GO

If you want to have Repeatable Reads for your SELECT queries in an optimistic way, you can use the isolation level Snapshot Isolation. Snapshot isolation provides you with Repeatable Reads out of the box, which means you always get back the row version, which was valid at the beginning of your transaction. Unfortunately enabling Snapshot Isolation is not completely transparent:

The isolation level Snapshot Isolation must be requested by a session explicitly. Therefore you need a code change in your application. Your queries can run into so-called Update Conflicts, where SQL Server rolls back a transaction. You therefore also have to handle that situation in your application accordingly.

The following code shows how you can enable Snapshot Isolation for a specific database, and how to request this new isolation level.

-- Enable Snapshot Isolation (SI)
ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON
GO

-- Check if SI is now enabled
SELECT
     name,
     snapshot_isolation_state,
     snapshot_isolation_state_desc
FROM sys.databases
WHERE database_id = DB_ID('AdventureWorks2012')

GO

USE AdventureWorks2012
GO

-- Setting the Isolation Level to Snapshot Isolation
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO

Summary

Today you have learned about the 2 optimistic isolation levels that SQL Server supports since SQL Server 2005. Read Committed Snapshot Isolation provides you with statement-level based isolation, and Snapshot Isolation provides transaction-level based isolation. Because both isolation levels use a version store that is persisted in TempDb, you need to be careful planning and sizing for TempDb.

Next week I will talk about a problem that occurs with locking & blocking in SQL Server: Lock Escalations. Stay tuned!