Compressing a geodatabase in Oracle can lead to blocking conditions in ArcGIS causing it to stop responding

Description

Users editing a versioned class may encounter blocking conditions in Oracle when the versioned geodatabase is being compressed. The user experience with ArcGIS when an Oracle session becomes blocked is that the application does not respond.

Note:
ArcSDE software, including the application server, command tools, and SDK with C and Java APIs, was deprecated at ArcGIS 10.2.2 and is no longer distributed.

The situation can only occur when the editing user is performing a versioned update or delete against the same versioned class that the geodatabase is currently compressing or the compress process itself is blocked waiting to compress.

The duration of the blocking condition is dependent on the duration of the edit session, which currently holds the lock on the versioned class or the duration of the compress operation's transaction for the specific class.

Cause

To ensure versioned data consistency while users are editing and performing a compress operation, locks must be acquired on a versioned class' deletes table to ensure read consistency.

Because Oracle uses an optimistic locking model where writers do not block readers, to ensure a session does not read a row while a second session is updating the row, the application must implement a locking model to prevent racing conditions.

ArcGIS prevents the racing condition by placing a row exclusive lock on a versioned class' deletes table when performing an update or delete, whereas the compress operation obtains a share mode lock on the versioned class' deletes table when trimming the table. Both locks are only held for the duration of the transaction being performed. In the case of an application performing an update or delete, the transaction's duration is the life span of the edit operation (calling stopeditoperation commits the transaction and aborteditoperation rollsback the transaction). The compress holds the lock only while compressing the individual table, not the life span of the entire compress operation.

The row exclusive lock does not lead to blocking conditions when multiple users edit a versioned class. It only blocks the compress operation, as does the compress operation's lock prevent editors from performing updates and deletes.

Once the compress obtains the share mode lock, editors will not be blocked from performing update or delete operations. The end user experience is that the application is not responding. The duration of the block is completely dependent on the time required for the compress to trim the versioned table, which is directly related to the volume of rows in the class being trimmed and removed (if an organization is compressing frequently/nightly, then the volume should be minimal).

Another condition that can lead to blocking conditions is when an editor has obtained the row exclusive lock on the class but has not stopped their edit operation (resulting in a commit and removal of the lock). If the session performing the edit retains the lock for extended periods of time (possibly because of the volume of data being updated or deleted), the editor's session will block the compress operation. Once the compress operation becomes blocked, Oracle maintains a first in/first out queue of sessions requesting locks. Therefore, the compress is blocked by the edit session and the compress' request blocks other edit sessions that are requesting the lock. This cascading effect is an expected behavior in Oracle (the only mechanism to ensure lock requests are processed in order).

The end result of the locking behavior is the impact it can have on editors and extended waits when requesting locks to ensure data consistency.

Solution or Workaround

Schedule the compress operation to execute during off-peak production hours when few users are editing versioned classes.

If one discovers the compress operation is causing blocking conditions in the Oracle instance, preventing users from editing, it is safe to end the session executing the compress command.

The compress operation has been written to be transactionally consistent, ensuring data integrity if the process is ever aborted.