1.RR Isolation Level: Under this isolation level, DB2 locks all relevant records. In aSQLDuring statement execution, all records executed by this statement are scanned with corresponding locks. In aSQLDuring statement execution, all records executed by this statement are scanned with corresponding locks. The type of the specific lock is still determined by the type of operation, and if it is read, a shared lock, or an exclusive lock if it is an update. The type of the specific lock is still determined by the type of operation, and if it is read, a shared lock, or an exclusive lock if it is an update. Because it locks all theSQLThe result of the statement is scanned, so the number of locks may be large, and this time, the increase in the index maySQLThe execution of the statement has a significant impact because the index affectsSQLThe number of records scanned by the statement. Because it will lock all to getSQLThe result of the statement is scanned, so the number of locks may be large, and this time, the increase in the index maySQLThe execution of the statement has a significant impact because the index affectsSQLThe number of records scanned by the statement.
2.RS Isolation Level: The requirement for this isolation level is slightly weaker than the RR isolation level, which locks all qualifying records under this isolation level. Whether it's reading, or updating, ifSQLStatement contains a query condition, the corresponding lock is added to all qualifying records. Whether it's reading, or updating, ifSQLStatement contains a query condition, the corresponding lock is added to all qualifying records. If there is no conditional statement, that is, all records in the table are processed, all records are locked. If there is no conditional statement, that is, all records in the watch are processed, all records are locked.
3.CS Isolation Level: This isolation level locks only the records that are currently being processed.
4.UR Isolation Level: At this isolation level, there is no row-level lock if it is a read operation. For a non-read-only operation, its lock processing is the same as CS. For non-read-only operations, its lock processing is the same as CS.
DB2 The default isolation level is CS. DB2 The default isolation level is CS. That is, cursor stability. That is, cursor stability.
In DB2, there are four isolation levels: Rs,rr,cs,ur
EG:DB2 SELECT * from TOPICIS.DM_QYLX with ur
ur:ur-uncommitted READ UNCOMMITTED reads
IsSQLAn isolation level at execution time, allowing dirty reads, no row locks
Without with Ur, select count (1) is a common s lock! Update should be an X lock; wait should happen!
Take with Ur,select count (1) should be in lock, update is x lock; just don't wait!
Just tested the next
C:\>DB2 CREATE Table A (a int)
Db20000iSQLCommand completed successfully.
C:\>DB2 INSERT into a values (1)
Db20000iSQLCommand completed successfully.
C:\>DB2 Commit
Db20000iSQLCommand completed successfully.
C:\>DB2 +c Select COUNT (*) from a
1
-----------
1
1 records have been selected.
The lock situation at this time is
C:\>db2pd-d Test-lock
Database Partition 0--Database TEST--Active--up 0 days 00:20:49
Locks:
Address tranhdl lockname Type Mode Sts Owner
Dur Holdcount Att RELEASEFLG
0x05190450 2 53514c4332463041f12cf8e241 Internal P.. S G 2
1 0 0x00 0x40000000
In another db2cmd.
C:\>DB2 +c Update a set a=2
Db20000iSQLCommand completed successfully.
At this point the lock situation is as follows
C:\>db2pd-d Test-lock
Database Partition 0--Database TEST--Active--up 0 days 00:21:03
Locks:
Address tranhdl lockname Type Mode Sts Owner
Dur Holdcount Att RELEASEFLG
0X051911E0 6 05000f00040000050000000052 Row. X G 6
1 0 0x00 0x40000000
0x05190450 2 53514c4332463041f12cf8e241 Internal P.. S G 2
1 0 0x00 0x40000000
0x05190300 6 53514c4332463041f12cf8e241 Internal P.. S G 6
1 0 0x00 0x40000000
0x051911b0 6 05000f00000000000000000054 Table. IX G 6
1 0 0x00 0x40000000
__________________
The more you Know the more you
' re Worth
Http://macrozeng.itpub.net
Macrozeng#itpub.net
The same test plus for read only with ur
C:\>DB2 Commit
Db20000iSQLCommand completed successfully.
C:\>DB2 +c Select COUNT (*) from a for read only with ur
1
-----------
1
1 records have been selected.
At this point the lock situation is as follows
C:\>db2pd-d Test-lock
Database Partition 0--Database TEST--Active--up 0 days 00:27:56
Locks:
Address tranhdl lockname Type Mode Sts Owner
Dur Holdcount Att RELEASEFLG
0x05191b70 2 53514c4332463041f12cf8e241 Internal P.. S G 2
1 0 0x00 0x40000000
In another db2cmd.
C:\>DB2 +c Update a set a=2
Db20000iSQLCommand completed successfully.
At this point the lock situation is as follows
C:\>db2pd-d Test-lock
Database Partition 0--Database TEST--Active--up 0 days 00:28:23
Locks:
Address tranhdl lockname Type Mode Sts Owner
Dur Holdcount Att RELEASEFLG
0x05190330 6 05000f00040000050000000052 Row. X G 6
1 0 0x00 0x40000000
0x05191b70 2 53514c4332463041f12cf8e241 Internal P.. S G 2
1 0 0x00 0x40000000
0x05190570 6 53514c4332463041f12cf8e241 Internal P.. S G 6
1 0 0x00 0x40000000
0x05191240 6 05000f00000000000000000054 Table. IX G 6
1 0 0x00 0x40000000
Summary:
<span style= "FONT-SIZE:18PX;" ><strong><span style= "FONT-SIZE:18PX;" >1, ur (uncommited read): is commonly known as "dirty read", can read the updated data when the data is not submitted, that is, the select data, do not wait for the update operation on the data. </span></strong></span>
<span style= "FONT-SIZE:18PX;" ><strong><span style= "FONT-SIZE:18PX;" >2, CS (cursor stability): When using the cursor stability isolation level, only the row that is being read in the result set of a transaction (the line the cursor points to) is added to the NS lock, and the other rows that are not processed are not locked. This isolation level only guarantees that the value of the row being processed will not be changed by other concurrent programs. The isolation level is the default isolation level of DB2. You can avoid dirty reads, but you cannot avoid the occurrence of non-repeatable reads and Phantom reads. </span></strong></span>
<span style= "FONT-SIZE:18PX;" ><strong><span style= "FONT-SIZE:18PX;" ></span><span style= "FONT-SIZE:18PX;" >3, RS (read stability): When querying in one transaction, it is not allowed to read data from other transactions in the update result set, allowing the reading of new data submitted to other transactions. Locks all rows in the result set, resolves non-repeatable read problems, but does not resolve phantom read issues. </span></strong></span>
<span style= "FONT-SIZE:18PX;" ><strong><span style= "FONT-SIZE:18PX;" >4, RR (repeatable Read): When querying in a transaction, no data modifications to the query table are allowed. The operation of the table is completely serialized, with the lowest efficiency and avoids any problems. </span></strong></span>
