How To: 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

When upgrading an enterprise geodatabase to 10.4 in SQL Server, the Upgrade Geodatabase tool automatically sets both of these options in the database.

When creating a new 10.4 enterprise geodatabase in SQL Server using the Create Enterprise Geodatabase tool, the tool automatically sets both of these options in the database.

When using the Enable Enterprise Geodatabase tool to create a new 10.4 geodatabase, it is required to either set these two database options ON in the database before you run the tool, or grant the geodatabase administrator the permission to ALTER the database. If the geodatabase administrator has ALTER database permission, the Enable Enterprise Geodatabase sets READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to ON in the database.

Using ArcGIS 10.4 with SQL Server databases or 10.3.1 or earlier release geodatabases, requires manually setting READ_COMMITTED_SNAPSHOT to ON in the database.

Article ID: 000013039

Software: