Background documentationIsolation Level Locate this document in the navigation structure

 

The isolation level determines when the database system sets which locks.

Isolation Level

Number

Name

Description

0

Read Uncommitted

The database system reads rows without requesting shared locks.

When a row is read again within a transaction, it is not guaranteed that it will have the same state as for the first read, since it may have been changed in the meantime by a different transaction.

It is also not certain that the database system will have saved the state of a read row by means of a COMMIT statement. In some cases, the read value is not saved by the database system. Instead, it is reset to a previous value by means of a ROLLBACK.

When rows are inserted, updated or deleted, the database system implicitly sets exclusive locks on the affected rows for the duration of the transaction. The database system only releases these locks when the transaction is ended.

1 or 10

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.

Read Committed

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

When inserting, changing or deleting rows, the database implicitly sets exclusive locks for the corresponding transaction for the rows concerned, which it does not release again until the end of the transaction.

15

-

In addition to the behavior described for isolation level 1, the database system requests shared locks for all tables that are addressed by an SQL statement before the start of processing.

If an SQL statement generates a result table which 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, shared 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 which 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 shared 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 shared 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 shared lock for each table that is addressed by an SQL statement.

These shared locks can only be released by ending the transaction. This table shared 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.

The more locks are set, and the longer these stay in place, the lower the degree of parallelism possible in the database.

Which Locks Can Exist in Parallel?

-

A transaction has...

-

In a table

In a row

In a database catalog

Another transaction can...

Exclusive Locks

Shared Locks

Exclusive Locks

Shared Locks

Exclusive Locks

Shared Locks

Request an exclusive lock for the table

No

No

No

No

No

Yes

Request a shared lock for the table

No

Yes

No

Yes

No

Yes

Request an exclusive lock for any row in the table

No

No

-

-

No

Yes

Request an exclusive lock for the locked row

-

-

No

No

-

-

Request an exclusive lock for a different row

-

-

Yes

Yes

-

-

Request a shared lock for any row in the table

No

Yes

-

-

No

Yes

Request a shared lock for the locked row

-

-

No

Yes

-

-

Request a shared lock for a different row

-

-

Yes

Yes

-

-

Change the definition of the table in the database catalog

No

No

No

No

No

No

Read the definition of the table in the database catalog

Yes

Yes

Yes

Yes

No

Yes

The fewer locks that 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 gets 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.

You can use the isolation level to configure which phenomena are possible.

Relationship Between the Isolation Level and Phenomena

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

More Information

Locks