Db2 isolation level-test

Preparations
Run the following command in the db2 command line: list command options,
 

Note: Auto Commit is ON by default, that is, automatic submission. Run the following command to view the settings:

Run the following command:
Update command options using c off,

Run the following command: list command options,
 

Now we can see that Auto Commit has changed to OFF.

DB2 uses a special register called "current isolation" to record the ISOLATION level of the session. There are two ways to view the current isolation level:

VALUES CURRENT ISOLATION
Or
Select current isolation from sysibm. SYSDUMMY1

The command for setting the isolation level is as follows (explicit COMMIT is not required for this command ):

Set current isolation to ur/CS/RS/RR

Because this test requires concurrent transactions, we need to open another window in the same way. Session1 and session2.

Connect both sessions to the DINGTEST database and create a table TA as follows:

AC1 AC2
1 1
2 2
3 3
4
5 5


Now the preparation is complete. Let's start the experiment.

Note: Do not forget to clean up the environment after each experiment, that is, the session commit/rollback.

Lab 1
Test whether "Update loss" occurs at the "UR" isolation level.

Method: two sessions concurrently update the same record.
STEP 0: Set session1 and session2 to UR isolation level:
SET CURRENT ISOLATION TO UR

STEP 1: update in session1, but do not submit:
Update ta set AC2 = 2 WHERE AC1 = 1

STEP 2: update the same record in session2:
Update ta set AC2 = 3 WHERE AC1 = 1

Session2 is blocked ......

STEP 3: Perform the commit/rollback operation in session1, and session2 will continue immediately.

Conclusion: there will be no "loss of updates" in the "UR" isolation level.

Lab 2
Test whether the "read dirty data" issue occurs at the "UR" isolation level.

Method: query the data changed but not submitted in session1 in session2.

STEP 0: set both sessions to UR isolation level:
SET CURRENT ISOLATION TO UR

STEP 1: update in session1, but do not submit:
Update ta set AC2 = 4 WHERE AC1 = 1

STEP 2: query this record in session2:
SELECT * from ta where AC1 = 1

Session2 returns the query result immediately. The record value is the updated value of session1, which is actually a dirty data, and session1 may eventually roll back.

STEP 3: Perform the rollback operation in session1.

STEP 4: query the record again in session2.
SELECT * from ta where AC1 = 1

The record value is the value before session1 update. Because session1 has been rolled back, the first read is a dirty data.

Conclusion: "Dirty Data Reading" may occur at the "UR" isolation level.

Lab 3
Test whether "read dirty data" is generated at the CS isolation level.
Method: query the data changed but not submitted in session1 in session2.

STEP 0: set both sessions to CS isolation level:
SET CURRENT ISOLATION TO CS

STEP 1: update in session1, but do not submit:
Update ta set AC2 = 5 WHERE AC1 = 1

STEP 2: query this record in session2:
SELECT * from ta where AC1 = 1

Session2 is blocked ......

STEP 3: Perform the commit/rollback operation in session1, and session2 will continue immediately.

Conclusion: "read dirty data" is not generated at the CS isolation level.

Lab 4
Test whether the "non-repeated read" issue occurs at the CS isolation level.

Method: Modify the query records of session1 between two queries.

STEP 0: set both sessions to CS isolation level:
SET CURRENT ISOLATION TO CS

STEP 1: Do the query operation in session1, but do not submit:
SELECT * from ta where AC1 = 1
Assume that AC2 is 4 in the query result ".

STEP 2: update the record in session2 and submit the record. (If you do not submit the record, session1 will no longer be able to query the record ):
Update ta set AC2 = 5 WHERE AC1 = 1
COMMIT

STEP 3: query the record again in session1:
SELECT * from ta where AC1 = 1
In this query result, AC2 is "5 ". This leads to data inconsistency between the two queries in the same transaction.


Conclusion: The "non-repeated read" issue may occur at the CS isolation level.

Lab 5
Test whether the "Repeatable read" issue occurs at the "RS" isolation level.
Method: Modify the query records of session1 between two queries.

STEP 0: set both sessions to the RS isolation level:
SET CURRENT ISOLATION TO RS

STEP 1: Do the query operation in session1, but do not submit:
SELECT * from ta where AC1 = 1
Assume that the value of AC2 in the query result is 5.

STEP 2: update this record in session2:
Update ta set AC2 = 6 WHERE AC1 = 1

Session2 is blocked ......

STEP 3: query the record again in session1:
SELECT * from ta where AC1 = 1

In this query result, AC2 is still 5. This indicates that at the RS isolation level, all queried records are locked in the transaction to ensure that the data queried for multiple times is consistent in the same transaction.

STEP 4: Perform the commit/rollback operation in session1, then session2 continues immediately.

Conclusion: there will be no "repeatable reading" in the RS isolation level.

Lab 6
Test whether an "Phantom record" is generated at the RS isolation level.
Method: Try to add a record that meets the query conditions between two queries of session1.

STEP 0: set both sessions to the RS isolation level:
SET CURRENT ISOLATION TO RS

STEP 1: Do the query operation in session1, but do not submit:
SELECT * from ta where AC1> 0 AND AC1 <10
Assume that the query results have five records.

STEP 2: INSERT a record in session2 and submit it:
Insert into ta (AC1, AC2) VALUES (6, 1)
COMMIT

STEP 3: perform the same query operation in session1:
SELECT * from ta where AC1> 0 AND AC1 <10

In this query result, six records are generated. This leads to data inconsistency between the two queries in the same transaction. Although the records found for the first time are locked and won't be changed, if the records are inserted in other sessions between the two queries, they will also be found in the second query. This record is called a phantom record.

Conclusion: "Phantom records" may occur at the RS isolation level.

Lab 7
Test whether an phantom record is generated at the RR isolation level.
Method: Try to add a record that meets the query conditions between two queries of session1.

STEP 0: set both sessions to RR isolation level:
SET CURRENT ISOLATION TO RR

STEP 1: Do the query operation in session1, but do not submit:
SELECT * from ta where AC1> 0 AND AC1 <10
Assume that the query results have 6 records.

STEP 2: INSERT a record in session2:
Insert into ta (AC1, AC2) VALUES (7, 1)

Session2 is blocked ......

STEP 3: perform the same query operation in session1:
SELECT * from ta where AC1> 0 AND AC1 <10
The query results are still 6 records, and the data is consistent with the first query.

STEP 4: Perform the commit/rollback operation in session1

Session2 continues immediately.

Conclusion: there will be no phantom record issues at the RR isolation level.
Author: "java-true"