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.

--cutting line--

when enabled db2_evaluncommitted , DB2 is able to predicate the uncommitted inserts (insert) or updated (update) data.

Assuming that uncommitted data does not conform to the predicate inference condition of the statement, DB2 does not correctly lock the uncommitted data. This avoids the need to
The lock wait state caused by uncommitted data lock increases the concurrency of application access.


Experiment 1
Tests such as the following (this is in conflict with the database configuration parameter cur_commit, so.) The cur_commit must 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

Can see that the two statements in session 2 failed because of a lock timeout. The reason is to assume that the user is changing (UPDATE), inserting (insert), or
When a row is deleted (delete), an exclusive lock is added to this line. Other users cannot read or write. Unless you use the UR isolation level.

Now enable the db2_evaluncommitted variable, you 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 we can see that the 2nd query statement is successful. The reason is that there is an uncommitted insert operation in Session 1, Session 2, when scanning, predicate inference to the line, found that does not conform to the predicate waiting condition
That is, age = 3, Session 2 does not lock the row, and therefore does not cause the lock to time out.

db2_skipdeletedThe effect that a variable is enabled is: during a table visit. The deleted rows are skipped unconditionally.

Lab 2:
In the default case. That is, if the db2_evaluncommitted and db2_skipdeleted variables are not set, the session 1 will be timed out to query the entire table when the session is deleted by the commit method.
For example, the following:
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


For example, now that the change db2_skipdeleted is on, the session 2 query will unconditionally skip the deleted rows and will therefore 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.db2_skipinsertedthe function of a variable is to skip the inserted line unconditionally. As if they had not yet been inserted.

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

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