Which Isolation Levels Are Available?

1.	Which Isolation Levels Are Available?
Answer» Isolation level 0: In isolation level 0 (uncommitted), lines are read without an implicit request of shared LOCKS (dirty read). This does not ensure that when a line is read again within a transaction, it has the same state as when it was read the first time because it may have been changed by a concurrent transaction in the meantime. This does not ensure that read records are actually committed in the database. When you insert, change, and delete lines, the data records are exclusively locked. The locks are retained until the transaction ends to ensure that no concurrent changes can be MADE. The SAP system is operated at isolation level 0 because a SEPARATE lock mechanism that is at a higher level than the lock mechanism of the database is implemented for the SAP application. Isolation level 1: If an SQL statement is used for a data retrieval, it is ensured for each read line that (when a line is read) no other transaction holds an exclusive lock for this line. The shared lock is removed after the record was read. If lock COLLISIONS occur, a lock (REQ ROW SHARE) is requested and the transaction must wait until this lock is assigned to it before it can access the record. Isolation level 15: Isolation level 15 ensures that the resulting set is not changed while it is being processed. Reading backwards and positioning in the resulting set creates unique results. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends. Isolation level 2: Isolation level 2 protects against the non-repeatable read. A record that is read several times within a transaction obtains always the same values. If isolation level 2 or 20 (repeatable) is specified, before processing starts, shared locks are implicitly requested for all the tables that are addressed by a SQL statement for the data retrieval. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends. Isolation level 3: If isolation level 3 or 30 (serializable) is specified, a table shared lock is implicitly assigned to the transaction for each table that is addressed by an SQL statement. These shared locks can be d only when the transaction ends. Isolation level 3 protects against three abnormal access types (dirty read, non-repeatable read, and phantom), but is not suitable for an operation with a high degree of parallelism. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends. New isolation level as of the usage of MVCC: When you start using these new isolation levels, the isolation levels mentioned above are no longer available. Isolation level 50: Isolation level 50 corresponds to COMMITTED READ. Isolation level 60: Isolation level 60 corresponds to "Serializable" that is known from Oracle. This is a lesser requirement than ISO/ANSI "Serializable". The old isolation levels that are lower than 3 are mapped to the new isolation level 50. The old isolation level 3 is mapped to the new isolation level 60. Isolation level 0: In isolation level 0 (uncommitted), lines are read without an implicit request of shared locks (dirty read). This does not ensure that when a line is read again within a transaction, it has the same state as when it was read the first time because it may have been changed by a concurrent transaction in the meantime. This does not ensure that read records are actually committed in the database. When you insert, change, and delete lines, the data records are exclusively locked. The locks are retained until the transaction ends to ensure that no concurrent changes can be made. The SAP system is operated at isolation level 0 because a separate lock mechanism that is at a higher level than the lock mechanism of the database is implemented for the SAP application. Isolation level 1: If an SQL statement is used for a data retrieval, it is ensured for each read line that (when a line is read) no other transaction holds an exclusive lock for this line. The shared lock is removed after the record was read. If lock collisions occur, a lock (REQ ROW SHARE) is requested and the transaction must wait until this lock is assigned to it before it can access the record. Isolation level 15: Isolation level 15 ensures that the resulting set is not changed while it is being processed. Reading backwards and positioning in the resulting set creates unique results. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends. Isolation level 2: Isolation level 2 protects against the non-repeatable read. A record that is read several times within a transaction obtains always the same values. If isolation level 2 or 20 (repeatable) is specified, before processing starts, shared locks are implicitly requested for all the tables that are addressed by a SQL statement for the data retrieval. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends. Isolation level 3: If isolation level 3 or 30 (serializable) is specified, a table shared lock is implicitly assigned to the transaction for each table that is addressed by an SQL statement. These shared locks can be d only when the transaction ends. Isolation level 3 protects against three abnormal access types (dirty read, non-repeatable read, and phantom), but is not suitable for an operation with a high degree of parallelism. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends. New isolation level as of the usage of MVCC: When you start using these new isolation levels, the isolation levels mentioned above are no longer available. Isolation level 50: Isolation level 50 corresponds to COMMITTED READ. Isolation level 60: Isolation level 60 corresponds to "Serializable" that is known from Oracle. This is a lesser requirement than ISO/ANSI "Serializable". The old isolation levels that are lower than 3 are mapped to the new isolation level 50. The old isolation level 3 is mapped to the new isolation level 60.

Answer»

Isolation level 0:

In isolation level 0 (uncommitted), lines are read without an implicit request of shared LOCKS (dirty read). This does not ensure that when a line is read again within a transaction, it has the same state as when it was read the first time because it may have been changed by a concurrent transaction in the meantime. This does not ensure that read records are actually committed in the database.

When you insert, change, and delete lines, the data records are exclusively locked. The locks are retained until the transaction ends to ensure that no concurrent changes can be MADE.

The SAP system is operated at isolation level 0 because a SEPARATE lock mechanism that is at a higher level than the lock mechanism of the database is implemented for the SAP application.

Isolation level 1:

If an SQL statement is used for a data retrieval, it is ensured for each read line that (when a line is read) no other transaction holds an exclusive lock for this line. The shared lock is removed after the record was read. If lock COLLISIONS occur, a lock (REQ ROW SHARE) is requested and the transaction must wait until this lock is assigned to it before it can access the record.

Isolation level 15:

Isolation level 15 ensures that the resulting set is not changed while it is being processed. Reading backwards and positioning in the resulting set creates unique results. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends.

Isolation level 2:

Isolation level 2 protects against the non-repeatable read. A record that is read several times within a transaction obtains always the same values.

If isolation level 2 or 20 (repeatable) is specified, before processing starts, shared locks are implicitly requested for all the tables that are addressed by a SQL statement for the data retrieval. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends.

Isolation level 3:

If isolation level 3 or 30 (serializable) is specified, a table shared lock is implicitly assigned to the transaction for each table that is addressed by an SQL statement. These shared locks can be d only when the transaction ends. Isolation level 3 protects against three abnormal access types (dirty read, non-repeatable read, and phantom), but is not suitable for an operation with a high degree of parallelism. When you insert, change, or delete lines, exclusive locks for the relevant lines are implicitly assigned to the transaction; these exclusive locks are d only when the transaction ends.

New isolation level as of the usage of MVCC:

When you start using these new isolation levels, the isolation levels mentioned above are no longer available.

Isolation level 50:

Isolation level 50 corresponds to COMMITTED READ.

Isolation level 60:

Isolation level 60 corresponds to "Serializable" that is known from Oracle. This is a lesser requirement than ISO/ANSI "Serializable".

The old isolation levels that are lower than 3 are mapped to the new isolation level 50. The old isolation level 3 is mapped to the new isolation level 60.

Isolation level 0:

In isolation level 0 (uncommitted), lines are read without an implicit request of shared locks (dirty read). This does not ensure that when a line is read again within a transaction, it has the same state as when it was read the first time because it may have been changed by a concurrent transaction in the meantime. This does not ensure that read records are actually committed in the database.

When you insert, change, and delete lines, the data records are exclusively locked. The locks are retained until the transaction ends to ensure that no concurrent changes can be made.

The SAP system is operated at isolation level 0 because a separate lock mechanism that is at a higher level than the lock mechanism of the database is implemented for the SAP application.