Three lock variables in DB2: DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED

Three lock variables in DB2: DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED

This article mainly explains how to use 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
Use the default CS isolation level

DDL and initial content 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
7 record (s) selected.

-- Split line --

When enabledDB2_EVALUNCOMMITTEDDB2 can perform predicate judgment on uncommitted INSERT or UPDATE data.
If the uncommitted data does not meet the condition for determining the predicate of the statement, DB2 will not lock the uncommitted data to avoid
The lock wait status caused by uncommitted Data Locking improves the concurrency of application access.


Lab 1
The test is as follows (this parameter conflicts with the Database Configuration Parameter CUR_COMMIT. Therefore, before testing, you must set the CUR_COMMIT parameter to disabled .)
First, perform an "uncommitted INSERT operation" in session 1"

Session 1
---------
$ Db2 + c "insert into student values (7, 'hes ')"
DB20000I The SQL command completed successfully.

Session 2
---------
$ Db2 "select * from student"
SQL0911N The current transaction has been rolled back because of a deadlock
Or timeout. Reason code "68". SQLSTATE = 40001
$ Db2 "select * from student where age = 3"
SQL0911N The current transaction has been rolled back because of a deadlock
Or timeout. Reason code "68". SQLSTATE = 40001

We can see that the two statements in session 2 fail due to lock timeout. The reason is that if the user changes (UPDATE), inserts (INSERT) or
When you DELETE a row, the exclusive lock will be applied to this row. Other users cannot read or write the row unless the UR isolation level is used.

Now that the DB2_EVALUNCOMMITTED variable is enabled, restart the instance, perform the same insert operation in session 1, and perform the same query in session 2.
Operation:

Session 1

------------
$ Db2set DB2_EVALUNCOMMITTED = ON
$ Db2stop force
$ Db2start

$ Db2 connect to qsmiao
$ Db2 + c "insert into student values (7, 'hes ')"
DB20000I The SQL command completed successfully.

Session 2
------------
$ Db2 "select * from student"
SQL0911N The current transaction has 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 2nd query statements are successful because session 1 has uncommitted insert operations, and session 2 performs a predicate judgment on this row during scanning, it is found that the condition does not meet the predicate condition.
That is, if the age is 3, session 2 will not lock the row, so it will not cause lock timeout.

DB2_SKIPDELETEDWhen a table is accessed, the deleted row is skipped unconditionally.

Lab 2:
By default, when the variables DB2_EVALUNCOMMITTED and DB2_SKIPDELETED are not set, if session 1 deletes the table in the submitted way, Session 2 times out to query the entire table,
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 has been rolled back because of a deadlock
Or timeout. Reason code "68". SQLSTATE = 40001


As shown in the following figure, if DB2_SKIPDELETED is modified to ON, the deleted row will be skipped unconditionally during session 2 query.
Session 1
---------
$ Db2 rollback
$ Db2set DB2_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


4 record (s) selected.


Similarly,DB2_SKIPINSERTEDVariables are used to unconditionally skip inserted rows, just as they are not inserted.


References:
Niuxinzhuang advanced DB2 (2nd)

Db2 I want to assign null values to a variable that can be null. What should I do?

This is not acceptable. The correct usage is as follows:
Insert into test values (123, null)
Or
Insert into test (a) values (123)
If B does not have a default value, the values of B after the preceding two statements are null.

How to declare variables in db2

Put all variable declarations at the beginning, such:

Create procedure "AML". "CZJY053"
(IN "DATADATE" VARCHAR (8)
)

BEGIN
Declare period varchar (20); -- Review Cycle
Declare times varchar (20); -- number of occurrences in a cycle
Declare sum_crat varchar (20); -- total amount within the cycle
Set period = (select value from CONTEXT_VARIABLE where type = 'czjy053' and key = 'period ');

Set times = (select value from CONTEXT_VARIABLE where type = 'czjy053' and key = 'times ');

Set sum_crat = (select value from CONTEXT_VARIABLE where type = 'czjy053 'and key = 'sum _ crat ');
Delete from T_FXQNBS WHERE REMARK1 = 'czjy053 ';
Insert into T_FXQNBS select * FROM FXQTRADE_CC TRANS, (select csnm, tcac from FXQTRADE_CC where tstm> to_char (to_date (DATADATE, 'yyyymmdd')-period days, 'yyyymmdd ') AND opcustomtype in (select key_value from FXQPARAM where key = 'oppcustomtype _ CC') group by csnm, tcac having count (*)> = 9 and sum (CRAT) >=1800000)
Where trans. TSTM = DATADATE
And trans. CSNM = A. CSNM
And trans. TCAC = A. TCAC;

END;