Entering content frame

Function documentation Isolation Level Locate the document in its SAP Library structure

Use

The isolation determines when which locks are set.

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

Name

Description

0 (uncommitted)

Rows are read without the database system requesting shared locks implicitly.

When a row is read again within a transaction, it is not guaranteed that it will have the same status as for the first read, since it may have been changed in the meantime by a different transaction. It is also not guaranteed that the status of a row that has already been read was saved in the database instance already by a COMMIT statement.

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.

1 or 10 (committed)

During the read, it is ensured for each row that is read that no other transaction is holding an exclusive lock for this row when it is read. No statement can be made about whether the read triggers none or exactly one shared lock for a row in the specified table, or for which row this may happen.

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, shared locks are requested for all tables that are addressed by an SQL statement before the start of processing. If a result table is created by the SQL statement, which is not physically saved, then these locks are not released again until the end of the transaction or when the result table is closed. Otherwise, the locks are released immediately after the SQL statement is processed.

2 or 20 (repeatable)

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

If a result table is created by an SQL statement, which is not physically saved, then these locks are not released again until the end of the transaction or when the result table is closed. Otherwise, the locks are released immediately after the SQL statement is processed.

The 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>.

Furthermore, the transaction is implicitly assigned a shared lock for each row that is read as an SQL statement is being processed. These locks can only be released using an UNLOCK statement or by ending the transaction.

When inserting, changing or deleting rows, the exclusive locks are assigned implicitly to the transaction for the relevant rows that are not released until the end of the transaction. No locks are set 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 exclusive locks are assigned implicitly to the transaction for the relevant rows that are not released until the end of the transaction.

Note

1 and 10 are only different ways of writing the same isolation levels; their meaning is the same. 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 start a database session using the CONNECT statement or for an individual SQL statement.

See Reference Manual,

·        CONNECT Statement (connect_statement)

·        LOCK Option (lock_option)

You can define the isolation level of a Loader session using the SET command or the –I option, override this definition for individual users using the USE USER command, or specify the required isolation level in the DATAEXTRACT command using the LOCK option in the SELECT statement.

See Loader,

·        SET Command

·        Isolation Level: -I

·        USE USER Command

·        DATAEXTRACT Command

 

Leaving content frame