DB2 v9.7 new feature Cur_commit can implement uncommitted read new feature Cur_commit can implement uncommitted read

DB2 Get DB Cfg|find "Cur_commit"
Currently implemented (cur_commit) = On

DB2 Update DB CFG using cur_commit off
db20000i UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W does not dynamically change one or more parameters that are submitted for immediate modification. For these configuration parameters, you must

After the application has been disconnected from this database, the changes will not take effect.

DB2 Get DB Cfg|find "Cur_commit"
Currently implemented (cur_commit) = DISABLED

C:\USERS\ADMINISTRATOR>DB2 Force Application All
Db20000i Force application command completed successfully.
Db21024i This command is asynchronous and may not take effect immediately.

---session 1 delete a record from the TAB2 table
C:\users\administrator>db2 +c "Delete from javan.tab2 where id=3"
Db20000i SQL Command completed successfully.

C:\users\administrator>

---session 2 check from the TAB2 table that the record you just deleted will wait until you know the lock time-out period to

C:\USERS\ADMINISTRATOR>DB2 +c "SELECT COUNT (*) from JAVAN.TAB2"
SQL0911N the current transaction has been rolled back because of a deadlock or timeout.  The reason code is "68". sqlstate=40001

C:\users\administrator>db2pd-db Testdb-locks

Database Partition 0--Database TESTDB--Active--up 0 days 00:07:59

Locks:
Address tranhdl lockname Type Mode Sts Owner
Dur holdcount Att RELEASEFLG rriid
0x7fb71e80 2 02000800060000000000000052 Row. X G 2----------look at that record id=3 row plus row row level x exclusive lock and S lock are excluded so session 2 Check this record will wait until timeout
1 0 0x00000020 0x40000000 0
0x7fb72380 2 41414141415a425a7f4760b841 Internal P.. S G 2
1 0 0x00000000 0x40000000 0
0x7fb72300 2 02000800000000000000000054 Table. IX G 2
1 0 0x00002000 0x40000000 0

We now enable the DB2 v9.7 version of the uncommitted read feature

DB2 Update DB CFG using Cur_commit on
db20000i UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W does not dynamically change one or more parameters that are submitted for immediate modification. For these configuration parameters, the changes will not take effect until all applications have been disconnected from the database.

C:\USERS\ADMINISTRATOR>DB2 Get db cfg |find "Cur_commit"
Currently implemented (cur_commit) = On

C:\users\administrator>

C:\USERS\ADMINISTRATOR>DB2 Connect to TestDB

Database connection Information

Database Server = Db2/nt 9.7.1
SQL Authorization id = administ ...
Local Database alias = TESTDB

C:\USERS\ADMINISTRATOR>DB2 Get db cfg|find "Cur_commit"
Currently implemented (cur_commit) = On

C:\users\administrator>

---session 1 delete records for id=1
C:\USERS\ADMINISTRATOR>DB2 "SELECT * from JAVAN.TAB2 where id=1"

ID NAME
----------- --------------------
1 1.25125888zjadolf

1 records have been selected.

C:\users\administrator>db2 +c "Delete from javan.tab2 where id=1"
Db20000i SQL Command completed successfully.

C:\users\administrator>
C:\users\administrator>db2pd-db Testdb-locks

Database Partition 0--Database TESTDB--Active--up 0 days 00:01:56

Locks:
Address tranhdl lockname Type Mode Sts Owner
Dur holdcount Att RELEASEFLG rriid
0x7fb70980 2 41414141415a425a7f4760b841 Internal P.. S G 2
1 0 0x00000000 0x40000000 0
0x7fb72900 2 02000800040000000000000052 Row. X G 2----plus x row-level lock
1 0 0x00000020 0x40000000 0
0x7fb71e00 2 02000800000000000000000054 Table. IX G 2
1 0 0x00002000 0x40000000 0

---Session 2 read the record that just deleted ID =2
C:\USERS\ADMINISTRATOR>DB2 +c "SELECT * from JAVAN.TAB2 where id=1"

ID NAME
----------- --------------------
1 1.25125888zjadolf

1 records have been selected.

Because the unread read is enabled, DB2 implements the undo-read functionality in Oracle (although the implementation principle is different).
Take a look at the description of this new feature in IBM's official documentation: "Currently implemented" (currently committed semantics, hereafter abbreviated CC) new features,
The salient feature of this new feature is that the presence of lock waits can be significantly reduced when the cursor stability (cursor stability, hereafter referred to as CS) isolation level, and the frequency of deadlocks occur.

Database configuration Parameters Cur_commit

The database configuration parameters are primarily used to control the behavior of the cursor stability Scan, with the default value on and the optional value:

(1) on: open;

For a newly created database, the default value is on, in which case when you try to read a row that is being modified by another application, the currently implemented version data for that row is returned directly (that is, the value before the first change).

(2) AVAILABLE: Available;

This value indicates that your app needs to explicitly request "currently committed behavior" to get the "currently implemented" result.

(3) DISABLED: Disable;

If the database is upgraded from a previous version, this parameter will be set to DISABLED, which is consistent with the behavior of the previous version. This parameter needs to be changed to on if you want to use the current implementation to control the behavior of the cursor stability scan.

It is important to note that registry variables db2_evaluncommitted, db2_skipdeleted, and db2_skipinserted are affected when the Cur_commit parameter is enabled. When binding (BIND) or precompilation (precompile), specify the use currently COMMITTED or WAIT for outcome on the Concurrentaccessresolution option, then the registry variable db2_ Evaluncommitted, db2_skipdeleted, and db2_skipinserted will be ignored.

--Appendix
Blocking conditions in the case of ORACLE Snapshot isolation Level
Workload read workload write workloads that appear after workload \ first appears
Read Workload No (no clogging) no (no clogging)
Write Workload no (no clogging) Yes (blocked)

DB2 blocking in the previous version of V9.7 using CS isolation Level
Workload read workload write workloads that appear after workload \ first appears
Read Workload No (no clogging) possible
Write workload is (blocked) Yes (blocked)

DB2 blocking in the case of "currently implemented" CS isolation level enabled in V9.7
Workload read workload write workloads that appear after workload \ first appears
Read Workload No (no clogging) no (no clogging)
Write Workload no (no clogging) Yes (blocked)

DB2 v9.7 new feature Cur_commit can implement uncommitted read new feature Cur_commit can implement uncommitted read