To help ensure data integrity, all database management systems (DBMSs) apply locks to data. For example, when one editor begins updating rows, the rows lock to prevent another editor from changing them. Once the transaction is complete, the locks release.
Each DBMS applies locks and interprets isolation levels differently. As a result, you need to study the behavior of your DBMS to determine at what level to set locks, how to set isolation levels, and deal with lock time-outs and deadlocks. For more detailed information, refer to your DBMS documentation.
Additionally, ArcGIS does not work with all DBMSs the same way. As a result, the potential for concurrency problems when performing nonversioned edits differs slightly between the various DBMSs. This topic provides a brief introduction to how concurrency and locking apply within the context of ArcGIS, but database locking is a complex topic.
ArcGIS and isolation levels
When you edit a geodatabase in Oracle, DB2, or Informix in a nonversioned edit session, ArcGIS does not modify this environment by setting the isolation level. Instead, it uses the current isolation level set in the DBMS. As a result, you can set the isolation to any level and make use of that level when you edit in a nonversioned edit session.
Beginning with ArcGIS 10.4, geodatabases in SQL Server must have the SQL Server database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION set to ON. When you edit a geodatabase in SQL Server in a nonversioned edit session, ArcGIS uses the READ COMMITTED isolation level for transactions.
The following sections describe the potential for concurrency problems under common conditions. Unless otherwise stated, these explanations assume that the default isolation level of COMMITTED READ, or its equivalent, is set in the underlying DBMS.
Oracle
Writers block writers: When you perform an edit operation on a feature or a group of features, such as moving them or modifying their attributes, the DBMS locks the rows. The features remain locked until you either save or stop the edit session without saving. Therefore, any feature or record you edit is locked for the duration of your edit session.
When two people try to edit the same feature at the same time, the feature locks when the first editor completes an operation. The lock continues to be held, even as this editor is working on other features. The feature remains locked until the editor either saves, thereby committing changes to the database, or stops the edit session without saving, which rolls back all the edits performed in that edit session.
While the feature is locked, the second editor attempts to modify the same feature. The second editor's ArcMap session waits for the lock to release, displaying an hourglass to show the session is processing an action. The hourglass continues to be displayed until the lock is released when the first editor saves the changes (commits the changes to the database) or ends the edit session without saving changes (rolls back the edits). At that time, the second editor can edit the table. (Note that this means the second editor's changes overwrite the first editor's changes.)
This locking problem can also occur simultaneously between two editors whenever the following happens:
- Both edit concurrently.
- The editors have modified rows in their current edit session.
- Each editor attempts to modify a row that has already been modified by the other.
The first of the editors that attempts to modify a locked row sees an hourglass as the ArcMap session waits for the lock to release. Once the second editor attempts to modify a row locked by the first editor, a situation known as deadlocking arises, since both editors are now blocking each other. The DBMS promptly chooses one of the transactions to roll back so the other one can continue. The editor whose transaction was rolled back must redo any edits performed since the last edits were saved.
Writers do not block readers: Editors writing to the database do not prevent others from reading the same data, regardless of the isolation level. To the people or applications reading the locked data, it appears as it did before the current transaction began.
Readers do not block writers: People or applications reading the database do not prevent others from modifying the same data at any isolation level.
DB2 and Informix
Writers block writers: Writers block writers in DB2 and Informix similarly to the way in which writers block writers in Oracle. For details, see the explanation in the Oracle section.
Writers block readers: In DB2 and Informix, writers prevent other people and applications from reading the same data at any isolation level above UNCOMMITTED READ. At these higher isolation levels, locking data until edits are saved or rolled back can cause concurrency problems; while you're working in an edit session, nobody else can read the data you're editing. This can cause the following to occur:
- If you add the same layer to ArcMap, the hourglass appears and the layer does not draw until the locks are released.
- If you try to pan to the data being edited, ArcMap waits for the lock to release before it updates the display.
- If you identify a locked feature, the hourglass appears, and no information is returned until the lock is released.
Readers block writers: In DB2 and Informix, readers can prevent editors from modifying the same data at any isolation level above UNCOMMITTED READ. However, in reality this is rarely noticeable in ArcGIS, since a read lock on a row is held for such a short duration; by the time data appears, the lock has already been released. Readers can only truly block writers in an application that opens a cursor in the DBMS, fetches one row at a time, and iterates through the result set as it processes the data. In this case, DB2 and Informix start acquiring and holding locks as the result set is processed.
PostgreSQL
Writers block writers: In PostgreSQL, a row cannot be updated until the first transaction that made a change to the row is either committed to the database or rolled back. When two editors try to update or delete the same feature at the same time, the first editor blocks other updates on that row. Other editors cannot edit that row until this editor either saves, thereby committing the changes to the database, or stops the edit session without saving, which rolls back all the edits performed in that edit session.
While the feature is locked, the second editor attempts to modify the same feature. The second editor's ArcMap session waits for the lock to release, displaying an hourglass to show the session is processing an action. The hourglass continues to be displayed until the first editor saves his changes (commits the changes to the database) or ends the edit session without saving changes (rolls back the edits). At that time, the second editor can edit the table. (Note that this means the second editor's changes overwrite the first editor's changes.)
Writers do not block readers: If you use PostgreSQL's multiversion concurrency control (MVCC), which is the default and recommended behavior for the database, transactions that write to the database do not block readers from querying the database. This is true whether you use the default isolation level of READ COMMITTED in the database or set the isolation level to SERIALIZABLE.
Readers do not block writers: No matter which isolation level you set in the database, readers do not lock data.
SQL Server
Beginning with ArcGIS 10.4, geodatabases in SQL Server must have the SQL Server database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION set to ON, and ArcGIS uses the READ COMMITTED isolation level for transactions. For more information on the READ COMMITTED isolation level, see your SQL Server documentation.
Writers block writers: Writers block writers in SQL Server similarly to the way in which writers block writers in Oracle.
Writers do not block readers: Readers retrieve the committed version of the row that existed at the time the statement or transaction started.
Readers do not block writers: When transactions running under row versioning-based isolation read data, the read operations do not acquire shared locks on the data being read and, therefore, do no block editors from modifying data. Also, this reduces the number of locks acquired and minimizes the overhead of locking resources. Read committed isolation using row versioning and snapshot isolation are designed to provide statement-level or transaction-level read consistencies of versioned data.
Preventing concurrency problems
You can minimize concurrency problems by following these recommendations:
Design applications and workflows with locks in mind
Requests waiting for locks to be released are often the result of poorly designed applications or workflows. When developing an application or workflow, ensure that locks are requested in an organized manner. You can accomplish this by standardizing the sequence of updates across all tables. This should prevent deadlocks. To reduce the amount of time locks are held, it's best to issue all data modification requests at the end of any unit of application logic or workflow that executes a transaction.
Set the appropriate isolation level (Oracle, DB2, Informix)
The isolation level affects the amount of time a transaction locks data. The higher the isolation, the longer the transaction holds the lock. The longer the transaction holds the lock, the more it increases data integrity, but this is at the cost of decreasing concurrency. If it is acceptable to your application, you can reduce the isolation level to improve concurrency.
Register the data as versioned
Improve concurrency by registering the data as versioned. If you maintain data with third-party applications, consider registering the data as versioned with the option to move edits to the base table. This allows third-party applications to see changes applied to the Default geodatabase version, but it adds the ability for the ArcGIS and ArcObjects applications that would otherwise be affected by or cause concurrency problems to edit and manage versions of the data. When an editor modifies a versioned table, he or she does not apply locks, which allows data to be edited in complete isolation from others.