Isolation levels

Suppose a transaction in one program updates data in an ArcSDE geodatabase, but before the transaction commits, another program reads the same data. Will the second program read the new but uncommitted data, or will it read the old data? The answer depends on the isolation level of the transaction.

If the transaction allows other programs to read uncommitted data, the second program will read the updated data. This may improve performance, because the second program doesn't have to wait until the transaction ends to read the data. But there's a trade-off: if the second program reads data that is not ultimately committed, it would have either read incorrect data or data that doesn't exist.

To allow users to choose what is best for their application, DBMSs provide isolation levels defining how subsequent processes are to be isolated from other concurrently executing processes. Isolation levels can be set by the database administrator and apply to all transactions within the database. They can also be set within an application or before an individual transaction. An isolation level specifies the following:

• The degree to which the rows read and updated by the application are available to other concurrently executing processes
• The degree to which updates from other concurrently executing application processes are available to the application

DBMSs allow you to set one of four isolation levels, listed below from lowest to highest isolation. Each level adds to the features of the previous level. Higher isolation levels offer a greater degree of data integrity, but at the cost of decreased concurrency, since they hold locks longer.

UNCOMMITTED READ: This allows minimum isolation from concurrent transactions. The transaction can read data that has been changed by concurrent transactions even before they commit.

COMMITTED READ: The transaction will read committed data only; it will not read uncommitted data.

REPEATABLE READ: Places an additional restriction that applies when the same rows are read multiple times during the course of the transaction. This ensures that when the same rows are read a subsequent time, they are the same.

SERIALIZABLE: This isolation level offers the highest degree of isolation from concurrent transactions. All reads in the transaction only see data committed before the transaction began and never see concurrent transaction changes committed during transaction execution.

Your DBMS may refer to these by other names. Each level works similarly across DBMSs, but there are important differences. To avoid mistakes in application and workflow design, be sure to understand how isolation levels impact locking and concurrency in your DBMS. See your DBMS documentation for more information.

For information on how isolation levels apply to ArcGIS, as well as the potential for concurrency problems, see Concurrency and locking.