IBM Support

Consideration to use DB2_SKIPDELETED and DB2_EVALUNCOMMITTED

Question & Answer


Question

What kind of issues may happen when using DB2_SKIPDELETED and DB2_EVALUNCOMMITTED to improve data concurrency?

Cause

To mitigate lock-conflict issues such as deadlock Db2 registry variables such as  'DB2_SKIPDELETED and DB2_EVALUNCOMMITTED' are often considered to improve data concurrency. That may trigger a data consistency problem depending on how the applications and business logic.  
This document introduces the possible scenarios.

Answer

The definition of the Db2 registry variables is as below.
 
DB2_SKIPDELETED:
Allows index scans to skip uncommitted pseudo deleted keys in an index (these would be from an uncommitted delete or update) and allows table scans to skip uncommitted deleted rows in a table.
DB2_EVALUNCOMMITTED:
Allows evaluation of predicates on uncommitted data. Predicates can be evaluated without locking a row and if the predicate does not qualify, the row can be skipped even though the value of the row is uncommitted. If the predicate does qualify, the scan (for a query or for an update/delete operation) will then attempt to lock the row if needed.
Note that, 1) We are trying to explain for DB2_SKIPDELETED and DB2_EVALUNCOMMITTED as DB2_SD and DB2_EU.
2) We have set up the "LOCKTIMEOUT" parameter as '-1' to shows you lock-waiting for test purposes. (the meaning of '-1' is that waiting for lock until acquiring.)
*Cases 1 and 2 are explaining What kind of issues may happen when DB2_SD and DB2_EU have been set up.*
Case1, No index
 
$ db2level
DB21085I  This instance or install (instance name, where applicable: "sjcsh")
uses "64" bits and DB2 code release "SQL1005B" with level identifier
"060C010E".
Informational tokens are "DB2 v10.5.0.11", "s200205", "IP24076", and Fix Pack
"11".
Product is installed at "/home/hotellnx90/sjcsh/sqllib".
$ db2set -all
[i] DB2_USE_IOCP=OFF
[i] DB2FCMCOMM=TCPIP4
[i] DB2_EVALUNCOMMITTED=YES
[i] DB2_FMP_COMM_HEAPSZ=5000
[i] DB2_DISABLE_FLUSH_LOG=ON
[i] DB2TCPCONNMGRS=8
[i] DB2LDAPCACHE=NO
[i] DB2_SKIPDELETED=ON
[i] DB2_HASH_JOIN=ON
[i] DB2COMM=TCPIP
[i] DB2CODEPAGE=970
[i] DB2_PARALLEL_IO=*
[i] DB2AUTOSTART=NO
$ db2 get db cfg for sample | grep -i cur
Currently Committed                        (CUR_COMMIT) = ON
$ db2 get db cfg for sample | grep -i lock
Lock timeout (sec)                        (LOCKTIMEOUT) = -1
[A session]
$ db2 -tvf test
create table t1(v1 int, v2 int, v3 int)
DB20000I  The SQL command completed successfully.
insert into t1 values (1,1,1), (2,2,3), (3,2,2),(3,2,4),(3,2,5)
DB20000I  The SQL command completed successfully.
(sjcsh@hotellnx90) /home/hotellnx90/sjcsh/test/TS003248412
$ db2 "select * from t1"
V1          V2          V3
----------- ----------- -----------
          1           1           1
          2           2           3
          3           2           2
          3           2           4
          3           2           5
  5 record(s) selected.

$ db2 +c "delete from t1 where v2=2 and v3=3"
DB20000I  The SQL command completed successfully.

$ db2 +c "update t1 set v3=3 where v2=2 and v3=2"
DB20000I  The SQL command completed successfully.

$ db2 +c "select * from t1"
V1          V2          V3
----------- ----------- -----------
          1           1           1
          3           2           3
          3           2           4
          3           2           5
  4 record(s) selected.

In this case, Row 2(2,2,3) is "uncommitted deleted row"
                    Row 3(3,2,3) is "uncommitted row"
                    other rows are "currently committed row"
                   
[B session]

$ db2 +c "delete from t1 where V2=2 and V3=3"
...waiting...

The committed Row1 (1,1,1) and evaluates the predicates without locking because DB2_EU is ON.   The row does not qualify, therefore, it is skipped.
At Row2 which is deleted but uncommitted.DB2_SD is ON, so Row2 is skipped regardless of whether the original (cur_commit) value of the row is qualified or not. At Row3 which has value (3,2,3) but is uncommitted. DB2_EU is on so it evaluates the predicate without locking.  The row as is DOES qualify so session B requests the lock on Row3.

Case 1-1: Rollback in session A

[A session]
$ db2pd -d sample -wlock
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:11:39 -- Date 2020-04-23-20.56.41.940602
Locks being waited on :
AppHandl [nod-index] TranHdl    Lockn
ame                   Type       Mode Conv Sts CoorEDU    AppName  AuthID   AppID
7        [000-00007] 3          03000500060000000000000052 RowLock    ..X       G   18         db2bp    SJCSH    *LOCAL.sjcsh.200424004502
38       [000-00038] 26         03000500060000000000000052 RowLock    ..U       W   70         db2bp    SJCSH    *LOCAL.sjcsh.200424005130

$ db2 rollback
DB20000I  The SQL command completed successfully.
B sesion got "SQL0100W"

 
[B session]
 
$ db2 +c "delete from t1 where V2=2 and V3=3"
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table.  SQLSTATE=02000


The table again has the 5 original committed rows with values (1,1,1), (2,2,3), (3,2,2), (3,2,4), (3,2,5)
 Session B session gets a lock on Row3 and processes it. The value of Row3 is (3,2,2) again. the predicates are evaluated again and do not qualify, so it is skipped. Note that session B does not go back and re-evaluate Row2 since it finished with that row already.
After waiting for the lock on Row3, session B processes Row3 appropriately and moves on to Row4. at Row4 and Row5 and evaluates the predicates without locking and they do not qualify so are skipped.
Case 1-2: commit in session A.
[A session]
$ db2pd -d sample -wlocks
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:01:48 -- Date 2020-04-23-21.46.50.819184
Locks being waited on :
AppHandl [nod-index] TranHdl    Lockname                   Type       Mode Conv Sts CoorEDU    AppName  AuthID   AppID
7        [000-00007] 3          03000500060000000000000052 RowLock    ..X       G   18         db2bp    SJCSH    *LOCAL.sjcsh.200424004502
38       [000-00038] 26         03000500060000000000000052 RowLock    ..U       W   70         db2bp    SJCSH    *LOCAL.sjcsh.200424005130

$ db2 commit
DB20000I  The SQL command completed successfully.
[B session]
 
$ db2 +c "delete from t1 where V2=2 and V3=3"
DB20000I  The SQL command completed successfully.
If session A commits,  the table now has committed rows Row1, committed deleted Row2, Row3 (3,2,3), Row4, Row5
Session B session gets the lock on Row3 and processes it. 
It evaluates the predicates again sees the value (3,2,3) qualifies and deletes it.  
Session B then looks at Row4 and Row5 and evaluates the predicates without locking and those do not qualify so are skipped.
There are no more rows to look at.
#######################
Case2, Index exists
 
$ db2set -all
[i] DB2_USE_IOCP=OFF
[i] DB2FCMCOMM=TCPIP4
[i] DB2_EVALUNCOMMITTED=YES
[i] DB2_FMP_COMM_HEAPSZ=5000
[i] DB2_DISABLE_FLUSH_LOG=ON
[i] DB2TCPCONNMGRS=8
[i] DB2LDAPCACHE=NO
[i] DB2_SKIPDELETED=ON
[i] DB2_HASH_JOIN=ON
[i] DB2COMM=TCPIP
[i] DB2CODEPAGE=970
[i] DB2_PARALLEL_IO=*
[i] DB2AUTOSTART=NO
$ db2 get db cfg for sample | grep -i cur
Currently Committed                        (CUR_COMMIT) = ON
$ db2 get db cfg for sample | grep -i lock
Lock timeout (sec)                        (LOCKTIMEOUT) = -1
[A session]
$ db2 -tvf test1
create table t2(v1 int, v2 int, v3 int)
DB20000I  The SQL command completed successfully.
create index Ix2 on t1 (v2)
DB20000I  The SQL command completed successfully.
insert into t2 values (1,1,1), (2,2,3), (3,2,2),(3,2,4),(3,2,5)
DB20000I  The SQL command completed successfully.

$ db2 "select * from t2"
V1          V2          V3
----------- ----------- -----------
          1           1           1
          2           2           3
          3           2           2
          3           2           4
          3           2           5
  5 record(s) selected.
 

$ db2 +c "delete from t2 where v2=2 and v3=3"
DB20000I  The SQL command completed successfully.

$ db2 +c "update t2 set v3=3 where v2=2 and v3=2"
DB20000I  The SQL command completed successfully.

$ db2 +c "select * from t2"
V1          V2          V3
----------- ----------- -----------
          1           1           1
          3           2           3
          3           2           4
          3           2           5
  4 record(s) selected.

Row 2(2) is "uncommitted delete state".
Row 3(3) is " uncommitted."
    
[B session]
$  db2 +c "delete from t2 where V2=2 and V3=3 /*<OPTGUIDELINES> <IXSCAN TABLE='T2'  INDEX='IX2'/> </OPTGUIDELINES> */"
..lock waiting...

By DB2_EU, Row3 which has value (3,2,3) is fetched and the predicate on V3 is applied without locking.
However, this time the value of V3=3 does qualify, and the lock is requested.
Session B waits for the lock on Row3 held by session A until it either commits or rolls back.
Case 2-1:Rollback in session A.  
[A session]
$ db2pd -db sample -wlocks
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:01:51 -- Date 2020-04-23-22.22.57.310838
Locks being waited on :
AppHandl [nod-index] TranHdl    Lockname                   Type       Mode Conv Sts CoorEDU    AppName  AuthID   AppID
7        [000-00007] 3          03000800060000000000000052 RowLock    ..X       G   18         db2bp    SJCSH    *LOCAL.sjcsh.200424022106
31       [000-00031] 26         03000800060000000000000052 RowLock    ..U       W   69         db2bp    SJCSH    *LOCAL.sjcsh.200424022237

$ db2 rollback
DB20000I  The SQL command completed successfully.
$ db2 "select * from t2"
V1          V2          V3
----------- ----------- -----------
          1           1           1
          2           2           3
          3           2           2
          3           2           4
          3           2           5
  5 record(s) selected.

 
If session A is going to work rollback, the table again has the 5 original committed rows with values (1,1,1), (2,2,3), (3,2,2), (3,2,4), (3,2,5)
[B session]
 
$ db2 +c "delete from t2 where V2=2 and V3=3 /*<OPTGUIDELINES> <IXSCAN TABLE='T2'  INDEX='IX2'/> </OPTGUIDELINES> */"
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table.  SQLSTATE=02000

Session B gets a lock on Row3 and processes it. The value of Row3 is (3,2,2) again and The predicates are evaluated again and do not satisfy.
Therefore,  "SQL0100W " happens.

Case 2-2: Commit in Session A
[A session]
 
$ db2 commit
DB20000I  The SQL command completed successfully.

$ db2 "select * from t2"
V1          V2          V3
----------- ----------- -----------
          1           1           1
          3           2           3
          3           2           4
          3           2           5
  4 record(s) selected.

When doing a commit, both "uncommitted deleted row" and "uncommitted row" are going to become "commit"
[B session]
 
$ db2 +c "delete from t2 where V2=2 and V3=3 /*<OPTGUIDELINES> <IXSCAN TABLE='T2'  INDEX='IX2'/> </OPTGUIDELINES> */"
DB20000I  The SQL command completed successfully.

The delete completes because there are existing values that are satisfied with where clause's condition.
 
#######################
As a result, the Appropriate index can be a way to avoid unexpected lock-conflict and it doesn't need to apply DB2_SD and DB2_EU.
So, case3 is one example that shows you that there is no lock-conflict even though there are no DB2_SD and DB2_EU if it has set up an Appropriate index.
Case3, Appropriate index scenario
#######################
[A session]
 
$ db2set DB2_EVALUNCOMMITTED=

$ db2set  DB2_SKIPDELETED=

$ db2stop force
SQL1064N  DB2STOP processing was successful.

$ db2start
SQL5043N  Support for one or more communications protocols specified in the DB2COMM environment variable failed to start successfully. However, core database manager functionality started successfully.

$ db2 connect to sample
   Database Connection Information
Database server        = DB2/LINUXX8664 10.5.11
SQL authorization ID   = SJCSH
Local database alias   = SAMPLE
$ db2 -tvf test4
create table t5(v1 int, v2 int, v3 int)
DB20000I  The SQL command completed successfully.
create index IX5 on t5 (v2,v3)
DB20000I  The SQL command completed successfully.
insert into t5 values (1,1,1), (2,2,3), (3,2,2),(3,2,4),(3,2,5),(0,3,4)
DB20000I  The SQL command completed successfully.

$ db2 "select * from t5"
V1          V2          V3
----------- ----------- -----------
          1           1           1
          2           2           3
          3           2           2
          3           2           4
          3           2           5
          0           3           4
  6 record(s) selected.

Index has keys ( Row 1 [1,1]), (Row 2 [2,3]),Row 3 [2,2]),Row 4 [2,4]),Row 5 [2,5], Row 6[3,4]) that allow a row to be fetched based on a key found during an index scan.
 
$ db2 +c "delete from t5 where v2=2 and v3=3"
DB20000I  The SQL command completed successfully.

$  db2 +c "update t5 set v1=6 where v2=3 and v3=4"
DB20000I  The SQL command completed successfully.

$ db2 +c "select * from t5"
V1          V2          V3
----------- ----------- -----------
          1           1           1
          3           2           2
          3           2           4
          3           2           5
          6           3           4
  5 record(s) selected.
 Row 2(2,3) is "uncommitted delete state".
 Row 6(3,4) is " uncommitted."
    
[B session]
 
$ db2 +c "delete from t5 where V2=2 and V3>3 /*<OPTGUIDELINES> <IXSCAN TABLE='T5'  INDEX='IX5'/> </OPTGUIDELINES> */"
DB20000I  The SQL command completed successfully.
The delete uses an index scan on index IX3 with index range start key>(2,3), stopkey=(2).
The index scan looks up start key v2,v3 > 2,3 in the index and finds key (2,4 Row4).  It qualifies the start/stop key and can be locked so it is deleted.
The index scan moves to the next key (2,5) and sees that it satisfies the stop key and can be locked so it is deleted.
The index scan moves to the next key (3,4 Row6). This does not satisfy the stop key so the delete is done.
In short, the rows that were updated by Session A are never looked at so there was is no lock wait even if DB2_EU and DB2_SD are not set.
#######################

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m0z000000cwHGAAY","label":"lock"}],"ARM Case Number":"TS003248412","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
11 October 2021

UID

ibm16495847