Isolation Level 
The isolation level determines when the database system sets which locks.
Number |
Name |
Description |
|---|---|---|
0 |
Uncommitted Read |
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. |
Committed Read |
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 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 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 degree of parallelism that is possible in the database decreases with the number of locks that are set and the length of time that they are held.
- |
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 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.
You can use the isolation level to configure which phenomena are possible.
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 |