Enable row versioning-based isolation in a SQL Server enterprise geodatabase

Summary

Row versioning-based isolation levels improve read concurrency by eliminating locks for read operations. Microsoft SQL Server 2008 R2 and later releases introduce two transaction isolation levels that use row versioning:

• A new implementation of read committed isolation that uses row versioning when the READ_COMMITTED_SNAPSHOT database option is ON.
• A new isolation level, snapshot, that is enabled when the ALLOW_SNAPSHOT_ISOLATION database option is ON.

Beginning with ArcGIS 10.4, read-committed is the default isolation level for transactions. There are also times when networks in the geodatabase use snapshot isolation.

As a result the SQL Server database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION must bet set ON for all 10.4 geodatabases.

The READ_COMMITTED_SNAPSHOT database option determines the behavior of the default read committed isolation level when snapshot isolation is enabled in a database. If you do not explicitly specify READ_COMMITTED_SNAPSHOT ON, read committed isolation is applied to all implicit transactions and ArcGIS 10.4 will encounter locking issues that will affect performance and scalability.

• The following Transact-SQL statement enables READ_COMMITTED_SNAPSHOT:

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

The ALLOW_SNAPSHOT_ISOLATION database option enables the database to use snapshot isolation. If you do not explicitly specify ALLOW_SNAPSHOT_ISOLATION ON, ArcGIS 10.4 could encounter issues while working with network data.

• The following Transact-SQL statement enables ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

Procedure