DB2 three related to lock variable db2_evaluncommitted,db2_skipdeleted and db2_skipinserted use

This paper mainly explains the use of the three lock variables db2_evaluncommitted,db2_skipdeleted and db2_skipinserted in DB2

Lab Environment:

DB2 v9.7.0.6
AIX 6.1.0.0
using the default isolation level CS

DDL and initial contents of the student table
CREATE TABLE "e97q6c". " STUDENT "(
"Age" INTEGER,
"NAME" CHAR (8))

In "USERSPACE1";

$ DB2 "SELECT * FROM Student"
Age NAME
----------- --------
3 Xu
5 Gao
6 mu
6 mu
6 mu
4 Three
1 an
7 record (s) selected.

--Split Line--

when enabled db2_evaluncommitted , DB2 can predicate an uncommitted insert (insert) or updated (update) data.
If uncommitted data does not conform to the predicate criteria of the clause, DB2 will not lock uncommitted data, thus avoiding the need to
The lock wait state caused by uncommitted data lock increases the concurrency of application access.


Experiment 1
The test is as follows (this parameter conflicts with the database configuration parameter cur_commit, so the Cur_commit parameter needs to be set to disabled before testing.)
First, in Session 1, do an "uncommitted insert" operation.

Session 1
---------
$ DB2 +c "INSERT into student values (7, ' he ')"
db20000i the SQL command completed successfully.

Session 2
---------
$ DB2 "SELECT * FROM Student"
sql0911n the current transaction have been rolled back because of a deadlock
OrTimeout.  Reason code "68". sqlstate=40001
$ DB2 "SELECT * FROM student where Age = 3"
sql0911n the current transaction have been rolled back because of a deadlock
OrTimeout.  Reason code "68". sqlstate=40001

As you can see, the two statements in session 2 fail because of a lock timeout. The reason is if the user is changing (UPDATE), inserting (insert), or
When a row is deleted (delete), an exclusive lock is added to the line, and other users cannot read and write unless the UR isolation level is used.

Now enable the db2_evaluncommitted variable, need to restart the instance, then do the same insert in Session 1, Session 2 to do the same query
Operation:

Session 1

------------
$ db2set Db2_evaluncommitted=on
$ db2stop Force
$ db2start

$ DB2 Connect to Qsmiao
$ DB2 +c "INSERT into student values (7, ' he ')"
db20000i the SQL command completed successfully.

Session 2
------------
$ DB2 "SELECT * FROM Student"
sql0911n The current transaction have been rolled back because of a deadlock
or timeout.  Reason code "68". sqlstate=40001
$ DB2 "SELECT * FROM student where Age = 3"
Age NAME
----------- --------
3 Xu
1 record (s) selected.

this time you can see the 2nd query is successful, because there is an uncommitted insert in Session 1, Session 2 in the scan, the row was judged by the predicate, found that the predicate is not consistent with the hope of breaking conditions
That is, age = 3, Session 2 does not lock the row, and therefore does not cause the lock to time out.

db2_skipdeletedThe effect of the variable being enabled is that the deleted rows are skipped unconditionally during table access.

Lab 2:
By default, if the db2_evaluncommitted and db2_skipdeleted variables are not set, Session 1 will be timed out to query the entire table if it is deleted by the Commit method,
As follows:
Session 1
---------
$ db2set Db2_skipdeleted=off
$ db2set Db2_evaluncommitted=off
$ db2stop Force
$ db2start
$ DB2 Connect to Qsmiao
$ DB2 +c "Delete from student where age=6"
db20000i the SQL command completed successfully.


Session 2
---------
$ DB2 "SELECT * FROM Student"
sql0911n the current transaction have been rolled back because of a deadlock
or timeout.  Reason code "68". sqlstate=40001


As below, now modify the db2_skipdeleted to ON, then session 2 query, will unconditionally skip the deleted rows, and therefore will succeed.
Session 1
---------
$ DB2 Rollback
$ db2setDb2_skipdeleted=on
$ db2stop Force
$ db2start
$ DB2 Connect to Qsmiao
$ DB2 +c "Delete from student where age=6"
db20000i the SQL command completed successfully.


Session 2
---------
$ DB2 "SELECT * FROM Student"


Age NAME
----------- --------
3 Xu
5 Gao
4 Three
1 an


4 record (s) selected.


Similar to thedb2_skipinsertedthe function of a variable is to skip the inserted rows unconditionally, as if they had not been inserted.


Resources:
New "Advanced DB2 (2nd edition)"

DB2 three related to lock variable db2_evaluncommitted,db2_skipdeleted and db2_skipinserted use