Show TOC

Isolation LevelLocate this document in the navigation structure

Use

The isolation level determines when the database system sets which SQL locks (see SQL Locks).

Number

Name

Description

0

Uncommitted Read

The database system reads rows without requesting share locks.

When a row is read twice within a transaction, it is not guaranteed that it will have the same content during the second read as during the first read: the content may have been changed by a another transaction in the meantime.

It is also not certain that the database system will save the content of a row that was read by a transaction using a COMMIT. In some cases, the content is later reset to a previous value by a ROLLBACK.

When rows are inserted, updated or deleted, the database system sets exclusive locks on the affected rows for the duration of the transaction. The database system releases these locks at the end of the transaction.

1 or 10*

Committed Read

For each row that the database system reads, it ensures that at the time of reading, no other transaction is holding an exclusive lock for that row.

When rows are inserted, updated or deleted, the database system sets exclusive locks on the affected rows for the duration of the transaction. The database system releases these locks at the end of the transaction.

15

-

In addition to the behavior described for isolation level 1, the database system requests share locks for all tables that are addressed by an SQL statement before it starts.

If an SQL statement generates a result table that is not permanently saved, then the database system only releases these locks at the end of the transaction or when the result table is closed. Otherwise, it releases the locks immediately after the SQL statement is processed.

2 or 20*

Repeatable Read

In addition to the behavior described for isolation level 1, share locks are implicitly requested for all tables that are addressed by an SQL statement for querying data before the start of processing.

If an SQL statement generates a result table that is not permanently saved, then the database system only releases these locks at the end of the transaction or when the result table is closed. Otherwise, it releases the locks immediately after the SQL statement is processed.

For the following SQL statements, the database system does not assign the table share lock to the transaction: SQL statements with which exactly one row is processed in a table that is determined by key specifications or using CURRENT OF <result_table_name>.

Furthermore, the database system implicitly assigns a share lock for the transaction for each row that is read during the processing of an SQL statement. These locks can only be released using an UNLOCK statement or by ending the transaction.

When inserting, changing or deleting rows, the database system implicitly assigns exclusive locks to the transaction for the affected rows that are not released until the end of the transaction. It does not assign locks for the whole table, however.

3 or 30*

Serializable

In addition to the behavior described for isolation level 2, a transaction is assigned implicitly to a table share lock for each table that is addressed by an SQL statement.

These share locks can only be released by ending the transaction. This table share lock is not assigned to the transaction with SQL statements, where exactly one row in a table is processed that is determined by key specifications or using CURRENT OF <result_table_name>.

When inserting, changing or deleting rows, the database system implicitly assigns exclusive locks to the transaction for the affected rows that are not released until the end of the transaction.

* 1 and 10 are merely different ways of writing the same isolation level. They have the exact same meaning. The same applies to 2 and 20 as well as 3 and 30.

Phenomena

The more locks are set and the longer they are held, the more the degree of parallelism that is possible in the database decreases.

The fewer locks are set, the more phenomena occur and the lower the degree of consistency that can be guaranteed. If multiple transactions access the same database object, such as a table, at the same time, this can result in inconsistencies in the results. The following phenomena can occur:

  • Dirty Read

    Transaction T1 changes a row. Transaction T2 reads this row before T1 ends the transaction with COMMIT. T1 then performs a ROLLBACK. In this case, T2 read a row that never actually existed.

  • Non Repeatable Read

    Transaction T1 reads a row. Transaction T2 then changes or deletes this row and closes the transaction with COMMIT. If T1 then reads the row again, it either receives the modified row or a message indicating that the row no longer exists.

  • Phantom

    Transaction T1 performs an SQL statement S with a search condition that returns a result set M of rows. Transaction T2 then creates at least one additional row that meets this search condition, for example, by adding or changing data. If T1 subsequently executes S again, then the new result set is different to M.

Phenomenon

Isolation Level

-

0

1 or 10

2 or 20

3 or 30

Dirty Read

Yes

No

No

No

Non Repeatable Read

Yes

Yes

No

No

Phantom

Yes

Yes

Yes

No

Configuring the Isolation Level

To temporarily override the isolation level for individual rows, you can use the LOCK option within an SQL statement (see SQL Reference Manual, LOCK Option (lock_option)).