Branch versioned editing may encounter locking, blocking, and deadlocking when the data is stored in an enterprise geodatabase in SQL Server

Description

ArcGIS client applications may encounter locking, blocking and deadlocking behaviors while editing branch versioned data that is stored in an enterprise geodatabase in SQL Server. These locking issues can lead to errors while editing and potential loss of edits.  

Cause

Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency. In SQL Server, blocking occurs when one session holds a lock on a specific resource and a second session attempts to acquire a conflicting lock type on the same resource. The duration and transaction context of a query determine how long its locks are held and, thereby, their impact on other queries.

• If the query is not executed within a transaction (and no lock hints are used), the locks for SELECT statements will only be held on a resource at the time it is being read, not for the duration of the query. For INSERT, UPDATE, and DELETE statements, the locks are held for the duration of the query, both for data consistency, and to allow the query to be rolled back if necessary. 

• For queries executed within a transaction, the duration for which the locks are held are determined by the type of query, the transaction isolation level, and whether lock hints are used in the query.  

ArcGIS applications will edit branch versioned data using transactions and with READ COMMITTED SNAPSHOT ISOLATION (RCSI). The architecture of branch versioning provides that two sessions would never be editing the same row within a given table, but occasionally the SQL Server optimizer may decide to use page locks instead of row locks, and even occasionally would choose or escalate further to a table lock. 

Workaround

If you are encountering issues related to exclusive page and table locks that result in severe locking and blocking behavior while working with branch versioned data in SQL Server, contact Esri Technical Support providing the details of your blocking issue and mention BUG-000138033. If the locking behavior is confirmed, then a script can be provided to disable page locks and lock escalation for these tables.

Note:
The potential for encountering SQL Server 1204 error increases after implementing this locking strategy. This indicates that SQL Server cannot obtain a LOCK resource at this time, and the processing of the current statement is stopped, and the transaction is rolled back. This rollback itself may also block users or can lead to a long database recovery time if you restart the SQL Server service.

Note:
ALTER INDEX … REORGANIZE is not supported for indexes with ALLOW_PAGE_LOCKS set to OFF.

Note:
Feature classes registered as branch version using ArcGIS Pro 2.8 or later will have this locking configuration by default, in SQL Server. If a feature class is registered as branch versioned (or new indexes added to a branch versioned class) using releases prior to 2.8, the script mentioned above must be executed to configure this row locking scheme.