Optimistic concurrency in Sql Server

This article will quickly describe how to enable optimistic concurrency in Sql Server. Optimistic concurrency is implemented in Sql Server through the use of row versioning and snapshots. Snapshots take two forms and both modes can be enabled. Snapshot isolation (SI) and Read Commited Snapshot Isolation (RCSI). To enable these two modes do the following (Example AdventureWorks2012):

ALTER DATABASE AdventureWorks2012
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE AdventureWorks2012
SET ALLOW_SNAPSHOT_ISOLATION ON

To check the status of your databases to see if these two flags are enabled:

SELECT DATABASE_ID,
NAME,
SNAPSHOT_ISOLATION_STATE_DESC,
IS_READ_COMMITTED_SNAPSHOT_ON
FROM sys.databases

For example, on my database, running this script returned the following: The column SNAPSHOT_ISOLATION_DESC concerns SI (Snapshot Isolation) and can take the following values:

• IN_TRANSITION_TO_OFF
• OFF
• ON
• IN_TRANSITION_TO_ON
• OFF

The reason for having four states and not just two is that this command can take long time and a DBA can be blocked by running transactions in progress. To enable RCSI, a DBA must usually have exclusive access to the database and therefore no transactions in progress. The column READ_COMMITTED_SNAPSHOT_ON can have the values 0 or 1, where 0 means it is disabled and 1 means it is off. Make note that the database tempdb is created, if you have not enabled SI (and/or) RCSI earlier for your database instance. This database keeps the version history required to make snapshots work.

Share this article on LinkedIn.