PROBLEM
Compressing a versioned database while users are editing
Description
To ensure data consistency when the compress command is executed while users are editing simultaneously, explicit table locks must be acquired by the compress operation and by any edit session performing update or delete operations. These locks ensure the correct serialization to protect the logical representation for each row’s versioned lineage. The locks are explicitly released when the transaction is committed.
Without the locking semantics there is potential for inconsistent values in the deletes table. The result without the locking can be duplicate versioned rows or versioned rows being present which should have been deleted.
This problem is only applicable for ArcSDE and Oracle because of Oracle's read consistency model.
Cause
Executing a compress while users are editing and the user is editing a row which is also currently being trimmed by the compress operation, can result in inconsistent values in the versioned class's deletes table.
Solution or Workaround
When a versioned row is updated or deleted during an edit operation, ArcSDE acquires a ROW EXCLUSIVE MODE lock on the deletes table.LOCK TABLE <table_owner>.d<registration_id> IN ROW EXCLUSIVE MODE
Where <registration_id> is the registration_id attribute value in the SDE.TABLE_REGISTRY table, <table_owner> is the user name of the table owner, and "d" signifies deletes. Collectively, this is the name of a deletes table.
Similarly, when the compress operation is executed, the compress process acquires a SHARE MODE lock on the deletes table.
LOCK TABLE <table_owner>.d<registration_id> IN SHARE MODE
The ROW EXCLUSIVE MODE lock allows multiple users to obtain the lock simultaneously, thereby letting each session to proceed with the user's edit operation (which is performed within a transaction in the database).
The SHARE MODE lock, when used in conjunction with the ROW EXCLUSIVE MODE lock, will only allow the compress operation to modify the table for the duration of the transaction. While the SHARE MODE lock is held, other sessions can read the deletes table but will be prevented from their session obtaining the ROW EXCLUSIVE MODE lock and modifying any rows in the table.
Therefore the ROW EXCLUSIVE MODE lock prevents the compress operation from proceeding to trim the specified table. If a user is performing updates and deletes when the ArcSDE administrator is running a compress operation, the compress operation will be forced to wait until the user commits their transaction and thus releasing the ROW EXCLUSIVE lock and allowing the compress operation to obtain the SHARE MODE lock.
While the compress operation holds the SHARE MODE lock for the given deletes table, users attempting to edit a versioned row for the table will be blocked and will wait until the compress operation commits its transaction (which is the trim step for just the given table) and thus releasing the SHARE MODE lock and allowing the edit operation to obtain the ROW EXCLUSIVE MODE lock.
The locks help ensure the correct representation of the versioned table during compress and during update or delete operations. Users who are currently editing and performing updates or deletes may encounter extended wait delays until the compress operation completes its transaction for the current versioned table and current lineage being compressed. This is solely dependent on the volume of rows being trimmed for the specific lineage during compress.
The locking behavior is necessary to ensure data consistency in the event the compress operation is executed while users are connected and editing. One can disable the locking behavior by setting the environment variable SDETRIMLOCKING to NONE (set SDETRIMLOCKING=NONE and restart your ArcSDE application server).
One might choose to disable the locking if the organization’s workflow does not compress while users are connected and editing for optimal performance. The performance benefit is simply one less statement executed for each update or delete operation per table (the LOCK TABLE <table_owner>.d<registration_id> IN ROW EXCLUSIVE MODE statement is not executed).
Warning:
Setting the SDETRIMLOCKING environment variable to NONE can introduce data inconsistencies if compress is executed while users are editing, in this case, ESRI advises against disabling this parameter to ensure data correctness.
Article ID:000008535
- ArcMap 9 x
- Legacy Products
Get help from ArcGIS experts
Download the Esri Support App
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback