Isolation Level 
Use
The isolation determines when the database system sets which locks.
The more locks that are set and the longer the locks remain, the less parallelism is possible during database operation. The fewer locks that are set, the more phenomena occur and the lower the degree of consistency that can be guaranteed.
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 but rather 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 |
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. |
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.
Activities
The default value for the isolation value depends on the SQL mode:
● ANSI: 3
● All others: 1
You can override the isolation level when you open up a database session using the CONNECT statement or for an individual SQL statement.
See the SQL Reference Manual,
CONNECT Statement (connect_statement)
You can set the isolation level for a Loader session with the SET command; override it for individual users with the USE USER command; or, in the EXPORT COLUMNS command, specify the desired isolation level with the LOCK option in the SELECT statement.
See Loader,
SET
Command