Personal data on DB2 isolation-level interpretation and Experimentation

This article focuses on the interpretation and experiment of DB2 isolation level. I saw the DB2 isolation level interpretation and experiment materials on the relevant website two days ago. I think it is quite good and I will share it with you, if you are interested, you can click the following articles to view them.

We all know that there are four isolation levels in the DB2 database: RS, RR, CS, UR. the following articles mainly describe these four isolation levels, and attach the individual's test results. Isolation level is an important part that affects the locking policy. It directly affects the lock range and lock duration.

Basic Information

Abstract: In DB2, there are four isolation levels: RS, RR, CS, UR. The following describes the four isolation levels, and attaches the results of the individual's experiments. Isolation level is an important part that affects the locking policy. It directly affects the lock range and lock duration. Even if the two applications perform the same operation, the locking results may vary depending on the selected isolation level.

Isolation-level interpretation and Testing

Isolation level is an important part that affects the locking policy. It directly affects the lock range and lock duration. Even if the two applications perform the same operation, the locking results may vary depending on the selected isolation level. In the interpretation of DB2 isolation level, there are four isolation levels: RS, RR, CS, UR. The following describes the four isolation levels, and the results of the individual's experiments are attached.

Read Reliability)

If this isolation level is used, the NS lock will be applied to all read rows in a transaction until the transaction is committed or rolled back, the row lock will be released. This ensures that the value in a transaction will not change even if the same row is read multiple times.

However, if this isolation level is used, in a transaction, if the processed cursor is re-opened using the same search criteria, the result set may change. (Some rows may be added. These rows are called Phantom rows (Phantom )). This is because the RS isolation level cannot prevent new rows from being added to the result set through insert or update operations.

Personal notes:

According to the actual test conditions, records in the result set that meet the conditions are locked after the query is completed, and those that do not meet the conditions are not locked.

You can update records that do not meet the conditions or insert new records. Others can query records that have been locked but cannot update them.

Repeated Read (RR-Repeat Read)

If this isolation level is used, the S lock will be applied to all read rows in a transaction, knowing that the transaction is committed or rolled back, the row lock will be released. This ensures that the value in a transaction will not change even if the same row is read multiple times.

In addition, if you re-open the processed cursor with the same search criteria in the same transaction, the result set will not change. Compared with read reliability, duplicate read has a larger lock range.

For read reliability, the application only locks all the rows that meet the requirements, and for repeated reads, the application locks all the rows that have been scanned. For example, if an application scans 10000 rows of data in a table and finds 100 rows that meet the search criteria. If the application uses a read reliability isolation level, the application locks only the 100 rows that meet the conditions. If the application uses a duplicate read isolation level, the application locks all the 10000 rows scanned.

Personal notes

Based on the actual test conditions, in RR mode: After the query is completed, you cannot update the items that do not meet the conditions or insert new records. Possible cause: If you allow others to update a record or insert a new record, the original result set may be damaged. The re-reading is different from the previous one.

Select for update with rr/rs is used to implement record lock. Is a special case. Even the RR can still operate on other records.

CS-Cursor Stability)

If this isolation level is used, only the row (the row pointed to by the cursor) being read in the result set will be added with the NS lock in a transaction, other unprocessed rows are not locked. This isolation level can only ensure that the value of the row being processed will not be changed by other concurrent programs. This isolation level is the default isolation level for DB2 isolation level interpretation.

Personal notes

The cursor is locked only when the row is located. This is a very weak isolation state.

Uncommitted Read (UR-Uncommitted Read)

If this isolation level is used, no row lock is applied for read-only operations. Typical read-only operations include:

The result set of the SELECT statement is read-only (for example, the statement contains the order by clause );

The definition cursor indicates that the start is for fetch only.

This isolation level can improve the performance of the application and allow concurrency to the maximum extent. However, the data integrity of applications is threatened. To read uncommitted data, this isolation level is the only choice.

Personal notes

There are no restrictions on reading, and the integrity of the same row of records cannot be guaranteed.

Summary

The lock range and duration at the isolation level mentioned above are for read operations.

For the change operation, the modified row will be added with the X lock. No matter what isolation level is used, the X lock will not be released until it is submitted or rolled back. The above content is an introduction to the DB2 isolation-level interpretation and experiment. I hope you will have some gains.