Concurrency and locking

To help ensure data integrity, all database management systems (DBMSs) apply locks to data. For example, when one user begins updating rows, the rows lock to prevent another user from changing them. Once the transaction is complete, the locks release.

Each DBMS applies locks and interprets isolation levels differently. Furthermore, 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. For more detailed information, please refer to your DBMS documentation.

ArcGIS and isolation levels

When you edit an Oracle, DB2, or Informix geodatabase in a nonversioned edit session, the same underlying DBMS locking mechanisms are at work as when you work with any other application—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.

When you edit a SQL Server geodatabase in a nonversioned edit session, ArcGIS sets the isolation level to UNCOMMITTED READ prior to every transaction. There is no way to change or work around this behavior; if you set the isolation to another level prior to a transaction, ArcGIS sets it back to UNCOMMITTED READ before the transaction begins.

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 users try to edit the same feature at the same time, the feature locks when the first user completes an operation. The lock continues to be held, even as this user is working on other features. The feature remains locked until this user either saves, thereby committing his 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 user attempts to modify the same feature. The second user's ArcMap session waits for the lock to release, displaying the familiar hourglass. The hourglass continues to be displayed until the lock is released when the first user 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 hourglass on the second user's screen disappears and the second user's edit operation can now be performed. (Note that this means the second user's edits overwrite the first user's edits.)

This locking problem can also occur simultaneously between two users whenever the following happens:

• The two users are concurrently editing.
• The users have modified rows in their current edit session.
• Each user attempts to modify a row that has already been modified by the other user.

The first of the users that attempts to modify a locked row sees an hourglass as the ArcMap session waits for the lock to release. Once the second user attempts to modify a row locked by the first user, a situation known as deadlocking arises, since both users are now blocking each other. The DBMS promptly chooses one of the transactions to roll back so the other one can continue. The user whose transaction was rolled back must redo any edits performed since the last edits were saved.

Writers do not block readers: Users writing to the database do not prevent other users from reading the same data, regardless of the isolation level. To the users reading the locked data, it appears as it did before the current transaction began.

Readers do not block writers: Users reading the database do not prevent other users 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, please see the explanation under "Oracle".

Writers block readers: In DB2 and Informix, writers prevent other users 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 on an edit session, nobody else can read the data you're editing. This can cause the following to occur:

• If another user adds the same layer to ArcMap, the hourglass appears and the layer does not draw until the locks are released.
• If another user tries to pan to the data being edited, ArcMap waits for the lock to release before it updates the display.
• If another user identifies 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 other users 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 users try to edit the same feature at the same time, the first user blocks other updates on that row. Other users cannot edit that row until this user 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 user attempts to modify the same feature. The second user's ArcMap session waits for the lock to release, displaying the familiar hourglass. The hourglass continues to be displayed until the first user 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 hourglass on the second user's screen disappears, and the second user's edit operation can now be performed. (Note that this means the second user's edits overwrite the first user's edits.)

Writers do not block readers: If you use PostgreSQL's multiversion concurrency control (MVCC), which is the default and recommended behavior for the database, user 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

ArcGIS sets the isolation level to READ UNCOMMITTED prior to every transaction in a SQL Server geodatabase. The following describes some potential concurrency problems that can arise within the context of ArcGIS. For more information on the READ UNCOMMITTED 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. For details, please see the explanation under "Oracle".

Writers do not block readers: Since ArcGIS sets the isolation level to READ UNCOMMITTED prior to every transaction, writers do not block readers in SQL Server geodatabases. However, when some users are modifying data while others are reading the same data, it's possible for users to read data that has been changed but not yet committed. This is referred to as a dirty read and can result in the following returned from a query:

• Inaccurate data values
• No data returned when data exists
• Duplicate data returned where none exists

Readers do not block writers: Since ArcGIS sets the isolation level to READ UNCOMMITTED prior to every transaction, readers do not block writers in SQL Server geodatabases.

Preventing concurrency problems

Fortunately, there are ways to minimize concurrency problems:

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 with the option to move edits to the base table. This allows users to continue to maintain data with applications not using ArcGIS, but it adds the ability for the ArcGIS and ArcObjects application users that would otherwise be affected by or cause concurrency problems to edit and manage versions of the data. When a user edits a version, he or she does not apply locks, which allows data to be edited in complete isolation from other users.

Database locking is a complex topic. Furthermore, each DBMS performs locking 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.