B2 isolation level and performance

The following articles mainly describe the B2 isolation level and performance. The isolation level mainly determines how to lock data when accessing data or prevent data from being affected by other processes. This isolation level takes effect during unit operation. During the execution of the open cursor unit of work.

Applications that use the CURSOR declared by the declare cursor statement of the with hold clause maintain the selected DB2 isolation level. DB2®The following isolation levels are supported:

Repeatable read

Read Stability

Cursor Stability

Unimplemented read.

Note: Some host database servers do not support isolation levels. For other databases, this isolation level is the same as the unimplemented read isolation level.

Detailed descriptions of each isolation level are listed in descending order of their impact on performance, but in ascending order of the degree of interest you need to pay when accessing and updating data.

Repeatable read

Repeatable read RR) locks all rows referenced by the application in the work unit. With "repeatable", an application in the same unit of work that opens the cursor issues a SELECT statement twice, and the same result is returned each time. With "repeatable reading", it is impossible to lose updates and access unimplemented data and phantom rows.

The "repeatable" application can retrieve and operate these rows as many times as possible before the work unit is completed. However, other applications cannot update, delete, or insert rows that may affect the result table before the work unit is completed. The "repeatable" application cannot view the unimplemented changes of other applications.

With "repeatable", each row referenced will be locked, not just those rows retrieved. An appropriate lock is executed, so other applications cannot insert or update the row. The row may be added to the list of rows referenced by the query, if the query is re-executed ). This prevents phantom rows. For example, if you scan 10000 rows and apply predicates to them, although only 10 rows meet the conditions, all 10000 rows will be locked.

Note: The "Repeatable read" isolation level ensures that all returned data remains unchanged until the application sees the data, even if a temporary table or row block is used.

Because "Repeatable read" may obtain and suspend a large number of locks, these locks may exceed the number of locks that can be used as the valid result of the locklist and maxlocks configuration parameters. To avoid lock upgrade, the Optimizer may choose to immediately obtain a single table-Level Lock for index scanning when it deems that a lock upgrade is likely to occur. This is just as if the database manager issued a lock table statement. If you do not want to get a table-Level Lock, make sure there are enough locks available for the transaction or use the read stability isolation level.

When referencing constraints are evaluated, DB2 upgrades the DB2 isolation level used for internal scanning of External tables to "Repeatable read" RR ), regardless of the isolation level set by the user. This will cause other locks to be suspended until they are implemented, increasing the possibility of deadlock or lock timeout. To avoid this, we recommend that you create an index that contains only one or more foreign keys, so that RI can scan this index.

Read Stability

Read stability RS) only lock the rows retrieved by the application in the work unit. It ensures that any qualified row reads during the execution of a work unit are not modified by other application processes before a work unit is completed, make sure that no rows changed by another application process are read until the process implements these changes. That is to say, there is no possibility of "non-repeated reading.

Different from repeatable reading, if your application sends the same query multiple times when "read stability" is used, you may see additional phantom row phantom reading ). When you rereference the example of scanning 10000 rows, "Read stability" only locks the specified row. In this way, when "read stability" is used, only 10 rows are retrieved, and only the 10 rows are suspended for locking. Compare it with "repeatable". In this example, the Repeatable read suspends the lock on all 10000 rows. The pending locks can be shared, the next share, update, or mutex lock.

Note: The read stability isolation level ensures that all returned data remains unchanged before the application sees the data, even if temporary tables or row blocks are used.

One of the goals of the "read stability" isolation level is to provide a high degree of parallelism and a stable view of data. To help achieve this goal, the optimizer ensures that the table-Level Lock is not obtained before the lock upgrade occurs.

The read stability isolation level is most suitable for applications that include all of the following features:

Run in a concurrent Environment

It is necessary to limit the stability of some rows during unit operation

The same query is not sent multiple times in a work order, or the query is not required to obtain the same answer when multiple queries are sent in the same work order.

Cursor Stability

Cursor stability CS) when the cursor is located on the row, any row accessed by the application's transaction will be locked. This lock is valid until one row is read or the transaction is terminated. However, if any data on a row is changed, the lock must be suspended before changes are made to the database.

For rows that have been retrieved by an application with "cursor stability", when there is any updatable cursor on the row, no other application can update or delete the row. The "cursor stability" application cannot view the unimplemented changes of other applications.

Reference the example of scanning 10000 rows again. If "cursor stability" is used, only the rows below the current cursor position will be locked. When the cursor moves away from the row, the lock is removed unless the row is updated ).

When "cursor stability" is used, non-repeated reading and phantom reading may occur. The "cursor stability" is the default isolation level and should be used only when the maximum concurrency is required, but only when the implementation of other applications has been seen.

Unimplemented read

Unimplemented read UR) allows applications to access unimplemented changes of other transactions. Unless other applications attempt to delete or change the table, the application will not lock the row being read and make other applications unable to access the row. For read-only and updatable cursors, the "unimplemented read" operation method is different.

Read-Only cursors can access unimplemented changes of most other transactions. However, when the transaction is being processed, tables, views, and indexes that are being created or deleted by other transactions cannot be used. Any other changes to other transactions can be read before implementation or rollback.

Note: The updatable operation cursors at the "unimplemented read" isolation level will work in the way of cursor stability at the isolation level.

When it uses the isolation level UR to run the program, the application can use the isolation level CS. This occurs because the cursor used in the application is a fuzzy cursor. Because of the BLOCKING option, you can upgrade the fuzzy cursor to the isolation level CS. The default value of the BLOCKING option is UNAMBIG. This means that the fuzzy cursor is treated as updatable, And the DB2 isolation level is upgraded to CS. To prevent this upgrade, you have two options:

Modify the cursor in the application. So that these cursors are non-fuzzy cursors. Change the SELECT statement to include the for read only clause.

The Fuzzy cursor is retained in the application, but is pre-compiled or bound to it using the blocking all and staticreadonly yes options to allow any fuzzy cursor to be considered as a read-only cursor when running the program.

For example, the example of "Repeatable read" for scanning 10000 rows is the same. If "unimplemented read" is used, no row locking is required.

When "unimplemented reading" is used, non-repeated reading behaviors and phantom reading may occur. The "unimplemented read" isolation level is most commonly used for queries on read-only tables, or if you only execute the selection statement and do not care if you can see unimplemented data from other applications, it is also the most commonly used.

Isolation-level Summary

The following table outlines several different isolation levels based on unexpected results.

Table 1. isolation level Summary

Unimplemented data access at the isolation level cannot be read repeatedly.

Repeatable read RR) Impossible impossible

Stability RS) impossible

Cursor stability CS) impossible

Unimplemented read UR) may be possible

The following table provides a simple test method to help you select an initial isolation level for your application. First, consider the method listed in the following table, and refer to the previous discussion on factors at all levels, you may find another more appropriate isolation level.

Table 2. Selection of isolation level criteria

Application types require high data stability and do not require high data stability

Read/write transactions RS CS

Read-Only transaction RR or RS UR

Selecting an appropriate isolation level for an application is important for the application to avoid intolerance. Because the CPU and memory resources required for obtaining and Releasing locks vary with the isolation level, this isolation level not only affects the degree of isolation between applications, it also affects the performance characteristics of individual applications. Potential deadlocks vary with the DB2 isolation level.