db2_dml锁分析

最新推荐文章于 2021-03-23 09:33:18 发布
最新推荐文章于 2021-03-23 09:33:18 发布
阅读量256 收藏
点赞数
文章标签： 数据库

目的:
通过分析不同隔离级别下dml(包括select)操作所持有的锁,了解DB2加锁的特点,以避免死锁,增加应用程序的并发性.

版本：
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1997, 2008
Windows XP


1,测试方法:
session 1(db2cmd):
db2 "values application_id()"
*LOCAL.DB2.110909005828


session 2(db2cmd db2 -t):
select agent_id,appl_id from sysibmadm.applications where appl_id='*LOCAL.DB2.110909055532';
AGENT_ID                  APPL_ID
     481  *LOCAL.DB2.110909055532

--...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
select lpad(lock_object_type,12)||'|'||
         lpad(lock_mode,6)||'|'||
         lock_status||'|'||
         lpad(row_number() over(partition by lock_mode),3) ||'|'||
         lock_name||'|'||
         lpad(nvl(tabname,' '),12)||'|'|| 
         lpad(tab_file_id,3)||'|'||
         lock_escalation 
from sysibmadm.locks_held where agent_id=481
order by lock_object_type;


测试用表：
*表结构：
db2 => describe table employee
                                数据类型                      列
列名                             模式       数据类型名称      长     小数位 NULL
------------------------------- --------- ------------------- ---------- ----------
EMPNO                           SYSIBM    CHARACTER                    6     0否
FIRSTNME                        SYSIBM    VARCHAR                     12     0否
MIDINIT                         SYSIBM    CHARACTER                    1     0是
LASTNAME                        SYSIBM    VARCHAR                     15     0否
WORKDEPT                        SYSIBM    CHARACTER                    3     0是
PHONENO                         SYSIBM    CHARACTER                    4     0是
HIREDATE                        SYSIBM    DATE                         4     0是
JOB                             SYSIBM    CHARACTER                    8     0是
EDLEVEL                         SYSIBM    SMALLINT                     2     0否
SEX                             SYSIBM    CHARACTER                    1     0是
BIRTHDATE                       SYSIBM    DATE                         4     0是
SALARY                          SYSIBM    DECIMAL                      9     2是
BONUS                           SYSIBM    DECIMAL                      9     2是
COMM                            SYSIBM    DECIMAL                      9     2是

*记录个数
db2 => select count(*) from employee
         42

*该表上共有2个索引
db2 => select indname,index_objectid,tbspaceid from syscat.indexes where tabname
='EMPLOYEE'
INDNAME          INDEX_OBJECTID TBSPACEID
PK_EMPLOYEE                 6           2
XEMP2                       6           2

*每个索引对应的列
db2 => select indname,colname,colseq from syscat.indexcoluse where indname='PK_E
MPLOYEE' order by colseq
INDNAME       COLNAME     COLSEQ
PK_EMPLOYEE     EMPNO          1

db2 => select indname,colname,colseq from syscat.indexcoluse where indname='XEMP2' order by colseq
INDNAME       COLNAME     COLSEQ
XEMP2        WORKDEPT          1

2,select for read only 操作:
2.1 job列没有索引
2.1.1 with ur Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for read only with ur
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0

2.1.2 with cs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for read only with cs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0


2.1.3 with rs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for read only with rs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|    NS|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

2.1.4 with rr Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for read only with rr
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
  TABLE_LOCK|     S|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' for read only with rr" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  #Columns = 2
|  May participate in Scan Sharing structures
|  Fast scan, for purposes of scan sharing management
|  Scan can be throttled in scan sharing management
|  Relation Scan
|  |  Prefetch: Eligible
|  Isolation Level: Repeatable Read   --RR
|  Lock Intents
|  |  Table: Share                --table,share
|  |  Row  : None
|  Sargable Predicate(s)
|  |  #Predicates = 1
|  |  Return Data to Application
|  |  |  #Columns = 3
Return Data Completion
End of section

2.2 with index on job
db2 create index job on mahong.employee(job)
db2 runstats on table mahong.employee and indexes all

2.2.1 with ur Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for read only with ur
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0

2.2.2 with cs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for read only with cs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0


分析查询过程中加锁情况：
db2 +c declare c1 cursor for select empno,job,salary from employee where job='CLERK' for read only with cs
db2 +c open c1
加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  2|0x53514C43324832307F4760B841|            |  0|0
INTERNALV_LO|     S|GRNT|  1|0x03000000010000000100401256|            |  0|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

db2 +c fetch c1
一直到没有记录，加锁情况不变：
D:\>db2 +c fetch c1

EMPNO  JOB      SALARY
------ -------- ---------

  0 条记录已选择。

 

2.2.3 with rs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for read only with rs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|    NS|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' for read only with rs" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 2
|  Skip Inserted Rows
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Isolation Level: Read Stability   --RS
|  Lock Intents
|  |  Table: Intent Share            --table，IS
|  |  Row  : Next Key Share          --row，NS
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 3
Return Data Completion
End of section

分析查询过程中加锁情况：
db2 +c declare c1 cursor for select empno,job,salary from employee where job='CLERK' for read only with rs
db2 +c open c1
加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  2|0x53514C43324832307F4760B841|            |  0|0
INTERNALV_LO|     S|GRNT|  1|0x03000000010000000100007D56|            |  0|0
    ROW_LOCK|    NS|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

db2 +c fetch c1
一直到没有记录，加锁情况不变：
D:\>db2 +c fetch c1

EMPNO  JOB      SALARY
------ -------- ---------

  0 条记录已选择。

2.2.4 with rr Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for read only with rr
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT| 10|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     S|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  2|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  3|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  4|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  5|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  6|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  7|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  8|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  9|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' for read only with rr" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 2
|  #Key Columns = 1
|  |  Start Key: Inclusive Value   --index
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Isolation Level: Repeatable Read  --RR
|  Lock Intents
|  |  Table: Intent Share            --table,IS
|  |  Row  : Share                   --row,S
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 3
Return Data Completion
End of section

分析查询过程中加锁情况：
db2 +c declare c1 cursor for select empno,job,salary from employee where job='CLERK' for read only with rr
db2 +c open c1
加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT| 11|0x53514C43324832307F4760B841|            |  0|0
INTERNALV_LO|     S|GRNT| 10|0x02000000010000000100A00956|            |  0|0
    ROW_LOCK|     S|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  2|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  3|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  4|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  5|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  6|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  7|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  8|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  9|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

db2 +c fetch c1
一直到没有记录，加锁情况不变：
D:\>db2 +c fetch c1

EMPNO  JOB      SALARY
------ -------- ---------

  0 条记录已选择。


3,select 操作,与2完全相同:
3.1 job列没有索引
3.1.1 with ur Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' with ur
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0

3.1.2 with cs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' with cs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0


3.1.3 with rs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' with rs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|    NS|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

3.1.4 with rr Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' with rr
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
  TABLE_LOCK|     S|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' with rr" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  #Columns = 2
|  May participate in Scan Sharing structures
|  Fast scan, for purposes of scan sharing management
|  Scan can be throttled in scan sharing management
|  Relation Scan
|  |  Prefetch: Eligible
|  Isolation Level: Repeatable Read   --RR
|  Lock Intents
|  |  Table: Share                --table,share
|  |  Row  : None
|  Sargable Predicate(s)
|  |  #Predicates = 1
|  |  Return Data to Application
|  |  |  #Columns = 3
Return Data Completion
End of section

3.2 with index on job
db2 create index job on mahong.employee(job)
db2 runstats on table mahong.employee and indexes all

3.2.1 with ur Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' with ur
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0

3.2.2 with cs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' with cs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0

分析查询过程中加锁情况：
db2 +c declare c1 cursor for select empno,job,salary from employee where job='CLERK' with cs
db2 +c open c1
加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  2|0x53514C43324832307F4760B841|            |  0|0
INTERNALV_LO|     S|GRNT|  1|0x03000000010000000100401256|            |  0|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

db2 +c fetch c1
一直到没有记录，加锁情况不变：
D:\>db2 +c fetch c1

EMPNO  JOB      SALARY
------ -------- ---------

  0 条记录已选择。

3.2.3 with rs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' with rs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|    NS|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' with rs" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 2
|  Skip Inserted Rows
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Isolation Level: Read Stability   --RS
|  Lock Intents
|  |  Table: Intent Share            --table，IS
|  |  Row  : Next Key Share          --row，NS
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 3
Return Data Completion
End of section

分析查询过程中加锁情况：
db2 +c declare c1 cursor for select empno,job,salary from employee where job='CLERK' with rs
db2 +c open c1
加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  2|0x53514C43324832307F4760B841|            |  0|0
INTERNALV_LO|     S|GRNT|  1|0x03000000010000000100606156|            |  0|0
    ROW_LOCK|    NS|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|    NS|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

db2 +c fetch c1
一直到没有记录，加锁情况不变：
D:\>db2 +c fetch c1

EMPNO  JOB      SALARY
------ -------- ---------

  0 条记录已选择。

 

3.2.4 with rr Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' with rr
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT| 10|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     S|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  2|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  3|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  4|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  5|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  6|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  7|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  8|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  9|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' with rr" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 2
|  #Key Columns = 1
|  |  Start Key: Inclusive Value   --index
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Isolation Level: Repeatable Read  --RR
|  Lock Intents
|  |  Table: Intent Share            --table,IS
|  |  Row  : Share                   --row,S
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 3
Return Data Completion
End of section

分析查询过程中加锁情况：
db2 +c declare c1 cursor for select empno,job,salary from employee where job='CLERK' with rr
db2 +c open c1
加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT| 11|0x53514C43324832307F4760B841|            |  0|0
INTERNALV_LO|     S|GRNT| 10|0x03000000010000000100C01556|            |  0|0
    ROW_LOCK|     S|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  2|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  3|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  4|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  5|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  6|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  7|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  8|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  9|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

db2 +c fetch c1
一直到没有记录，加锁情况不变：
D:\>db2 +c fetch c1

EMPNO  JOB      SALARY
------ -------- ---------

  0 条记录已选择。

4,select for update操作:
4.1 job列没有索引
4.1.1 with ur Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for update with ur
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0

4.1.2 with cs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for update with cs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0


4.1.3 with rs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for update with rs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     U|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

4.1.4 with rr Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for update with rr
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
  TABLE_LOCK|     U|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' for update with rr" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  #Columns = 2
|  Relation Scan
|  |  Prefetch: Eligible
|  Isolation Level: Repeatable Read
|  Lock Intents
|  |  Table: Update
|  |  Row  : None
|  Sargable Predicate(s)
|  |  #Predicates = 1
Return Data to Application
|  #Columns = 3
End of section

4.2 with index on job
db2 create index job on mahong.employee(job)
db2 runstats on table mahong.employee and indexes all

4.2.1 with ur Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for update with ur
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0

4.2.2 with cs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for update with cs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0


4.2.3 with rs Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for update with rs
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     U|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     U|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' for update with rs" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 2
|  Skip Inserted Rows
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Isolation Level: Read Stability
|  Lock Intents
|  |  Table: Intent Exclusive
|  |  Row  : Update
Return Data to Application
|  #Columns = 3
End of section


4.2.4 with rr Isolation Level
db2 +c select empno,job,salary from employee where job='CLERK' for update with rr
EMPNO  JOB      SALARY
------ -------- -----------
000120 CLERK       49250.00
000230 CLERK       42180.00
000240 CLERK       48760.00
000250 CLERK       49180.00
000260 CLERK       47250.00
000270 CLERK       37380.00
200120 CLERK       39250.00
200240 CLERK       37760.00
  8 条记录已选择。

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT| 10|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     S|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  2|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  3|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  4|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  5|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  6|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  7|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  8|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     S|GRNT|  9|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

计划解释：
dynexpln -d sample -q "select empno,job,salary from employee where job='CLERK' for update with rr" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 2
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Isolation Level: Repeatable Read
|  Lock Intents
|  |  Table: Intent Exclusive
|  |  Row  : Share
Return Data to Application
|  #Columns = 3
End of section

5,insert操作:
5.1 with ur Isolation Level
db2 +c insert into employee values('300340','F',null,'L','E21','234',null,'DESIGNER',15,null,null,10,100.0,1000) with ur

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x020012000B0000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x020006002E0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

5.2 with cs Isolation Level
db2 +c insert into employee values('300340','F',null,'L','E21','234',null,'DESIGNER',15,null,null,10,100.0,1000) with cs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x020012000B0000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x020006002E0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

5.3 with rs Isolation Level
db2 +c insert into employee values('300340','F',null,'L','E21','234',null,'DESIGNER',15,null,null,10,100.0,1000) with rs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x020012000B0000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x020006002E0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

5.4 with rr Isolation Level
db2 +c insert into employee values('300340','F',null,'L','E21','234',null,'DESIGNER',15,null,null,10,100.0,1000) with rr

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x020012000B0000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x020006002E0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

6,update操作 with cs:
6.1无job索引
6.1.1 with ur Isolation Level
db2 +c update employee set salary=1 where job='CLERK' with ur

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

6.1.2 with cs Isolation Level
db2 +c update employee set salary=1 where job='CLERK' with cs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

6.1.3 with rs Isolation Level
db2 +c update employee set salary=1 where job='CLERK' with rs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

6.1.4 with rr  Isolation Level
db2 +c update employee set salary=1 where job='CLERK' with rr

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

6.2有job索引
db2 create index job on mahong.employee(job)
db2 runstats on table mahong.employee and indexes all

6.2.1 with ur Isolation Level
db2 +c update employee set salary=1 where job='CLERK' with ur

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  3|0x53514C43324832307F4760B841|            |  0|0
INTERNALV_LO|     S|GRNT|  2|0x03000000010000000100C01556|            |  0|0
    ROW_LOCK|     S|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

dynexpln -d sample -q "update employee set salary=1 where job='CLERK' with ur" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 0
|  Skip Inserted Rows
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Exclusive
|  |  Row  : Exclusive
Update:  Table Name = MAHONG.EMPLOYEE  ID = 2,6
End of section

6.2.2 with cs Isolation Level
db2 +c update employee set salary=1 where job='CLERK'

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  3|0x53514C43324832307F4760B841|            |  0|0
INTERNALV_LO|     S|GRNT|  2|0x03000000010000000100C01556|            |  0|0
    ROW_LOCK|     S|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

dynexpln -d sample -q "update employee set salary=1 where job='CLERK'" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 0
|  Skip Inserted Rows
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Exclusive
|  |  Row  : Exclusive
Update:  Table Name = MAHONG.EMPLOYEE  ID = 2,6
End of section

6.2.3 with rs Isolation Level
db2 +c update employee set salary=1 where job='CLERK' with rs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0


dynexpln -d sample -q "update employee set salary=1 where job='CLERK' with rs" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 0
|  Skip Inserted Rows
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Isolation Level: Read Stability
|  Lock Intents
|  |  Table: Intent Exclusive
|  |  Row  : Exclusive
Update:  Table Name = MAHONG.EMPLOYEE  ID = 2,6
End of section

6.2.4 with rr Isolation Level
db2 +c update employee set salary=1 where job='CLERK' with rr

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     U|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  1|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  2|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  3|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0

dynexpln -d sample -q "update employee set salary=1 where job='CLERK' with rr" -t
Access Table Name = MAHONG.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = MAHONG.JOB  ID = 3
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: JOB (Ascending)
|  #Columns = 0
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 'CLERK   '
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Isolation Level: Repeatable Read
|  Lock Intents
|  |  Table: Intent Exclusive
|  |  Row  : Exclusive
Update:  Table Name = MAHONG.EMPLOYEE  ID = 2,6
End of section

7,delete操作 with cs:
7.1无job索引
7.1.1 with ur Isolation Level
db2 +c delete employee where job='CLERK' with ur

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02001200080000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x02001200040000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  3|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  9|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT| 10|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000700000000000000000054|   EMP_PHOTO|  7|0
  TABLE_LOCK|    IS|GRNT|  2|0x02000800000000000000000054|  EMP_RESUME|  8|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000500000000000000000054|  DEPARTMENT|  5|0
  TABLE_LOCK|    IX|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

7.1.2 with cs Isolation Level
db2 +c delete employee where job='CLERK' with cs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02001200080000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x02001200040000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  3|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  9|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT| 10|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000700000000000000000054|   EMP_PHOTO|  7|0
  TABLE_LOCK|    IS|GRNT|  2|0x02000800000000000000000054|  EMP_RESUME|  8|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000500000000000000000054|  DEPARTMENT|  5|0
  TABLE_LOCK|    IX|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

7.1.3 with rs Isolation Level
db2 +c delete employee where job='CLERK' with rs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02001200080000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x02001200040000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  3|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  9|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT| 10|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000700000000000000000054|   EMP_PHOTO|  7|0
  TABLE_LOCK|    IS|GRNT|  2|0x02000800000000000000000054|  EMP_RESUME|  8|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000500000000000000000054|  DEPARTMENT|  5|0
  TABLE_LOCK|    IX|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

7.1.4 with rr Isolation Level
db2 +c delete employee where job='CLERK' with rr

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02001200080000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x02001200040000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  3|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  9|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT| 10|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000700000000000000000054|   EMP_PHOTO|  7|0
  TABLE_LOCK|    IS|GRNT|  2|0x02000800000000000000000054|  EMP_RESUME|  8|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000500000000000000000054|  DEPARTMENT|  5|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  2|0x02001200000000000000000054|     ADEFUSR| 18|0

7.2有job索引
db2 create index job on mahong.employee(job)
db2 runstats on table mahong.employee and indexes all

7.2.1 with ur Isolation Level
db2 +c delete employee where job='CLERK' with ur

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02001200080000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x02001200040000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  3|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  9|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT| 10|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000700000000000000000054|   EMP_PHOTO|  7|0
  TABLE_LOCK|    IS|GRNT|  2|0x02000800000000000000000054|  EMP_RESUME|  8|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000500000000000000000054|  DEPARTMENT|  5|0
  TABLE_LOCK|    IX|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

7.2.2 with cs Isolation Level
db2 +c delete employee where job='CLERK' with cs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02001200080000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x02001200040000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  3|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  9|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT| 10|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000700000000000000000054|   EMP_PHOTO|  7|0
  TABLE_LOCK|    IS|GRNT|  2|0x02000800000000000000000054|  EMP_RESUME|  8|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000500000000000000000054|  DEPARTMENT|  5|0
  TABLE_LOCK|    IX|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

7.2.3 with rs Isolation Level
db2 +c delete employee where job='CLERK' with rs

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     X|GRNT|  1|0x02001200080000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x02001200040000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  3|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  9|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT| 10|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000700000000000000000054|   EMP_PHOTO|  7|0
  TABLE_LOCK|    IS|GRNT|  2|0x02000800000000000000000054|  EMP_RESUME|  8|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000500000000000000000054|  DEPARTMENT|  5|0
  TABLE_LOCK|    IX|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

7.2.4 with rr Isolation Level
db2 +c delete employee where job='CLERK' with rr

加锁情况：
...lock_type|l_mode|stat|.rn|...................lock_name|....tab_name|fid|es
INTERNALP_LO|     S|GRNT|  1|0x53514C43324832307F4760B841|            |  0|0
    ROW_LOCK|     U|GRNT|  1|0x02000600100000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  1|0x02001200080000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  2|0x02001200040000000000000052|     ADEFUSR| 18|0
    ROW_LOCK|     X|GRNT|  3|0x02000600290000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  4|0x02000600250000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  5|0x020006001C0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  6|0x020006001B0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  7|0x020006001A0000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  8|0x02000600190000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT|  9|0x02000600180000000000000052|    EMPLOYEE|  6|0
    ROW_LOCK|     X|GRNT| 10|0x020006000D0000000000000052|    EMPLOYEE|  6|0
  TABLE_LOCK|    IS|GRNT|  1|0x02000700000000000000000054|   EMP_PHOTO|  7|0
  TABLE_LOCK|    IS|GRNT|  2|0x02000800000000000000000054|  EMP_RESUME|  8|0
  TABLE_LOCK|    IX|GRNT|  1|0x02000500000000000000000054|  DEPARTMENT|  5|0
  TABLE_LOCK|    IX|GRNT|  2|0x02000600000000000000000054|    EMPLOYEE|  6|0
  TABLE_LOCK|   SIX|GRNT|  1|0x02001200000000000000000054|     ADEFUSR| 18|0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-707048/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-707048/

cuian7084
关注
万字长文深入探究Oracle DML锁机制
Enmotech的博客
08-04 273
点击上方"蓝字"关注我们，享更多干货！1.1. 锁的基本概念锁的定义：锁（lock）机制用于管理对共享资源的并发访问，用于多用户的环境下，可以保证数据库的完整性和一致性。锁...
db2高级锁
路好远
05-16 2786
备注： db2 +c "select * from test for update with rs" 会在行上加U锁， 其它普通 select * from test  可以查， 但 select * from test for update with rs 语句不能查(不能在U锁上试图再加U锁)。 ================ 我们在进行客户支持时遇到最多的话题之一就是锁。“为...
DB2锁机制
路好远
05-20 561
DB2锁机制     相比较Oracle来说，DB2的锁机制麻烦了很多，而且这个麻烦带来的不是性能的上升而是下降，不过如果细致了解的话，只能感慨不愧是数据库理论诞生的公司，在实现数据库理论上比Oracle全面得多。    Oracle没有实现一般数据库理论里的锁机制，带来的并发性与性能上的提升以及相关的问题上文已经介绍了，现在来说说几乎完全实现一般数据库理论锁机制的DB2数据库这方面的实现。  ...
DB2的锁lock
王春雨
03-17 5621
--------------------------------------锁的分类： (1)读锁:即共享锁(S) (2)写锁:即排他锁(X) S锁可以与S锁兼容，不可与X锁兼容。 X锁与S锁和X锁都不可以兼容。 举例： 一条记录加了共享锁(S)，那么其他事务也可以对该条记录加共享锁(S); 但是不能加排他锁，需要等到该共享锁释放之后再加排他锁。 一条记录加了排他锁(S), 那么其他...
db2_查询锁方法
08-03 425
1,获取锁方法：1.1管理视图:SELECT * FROM SYSIBMADM.SNAPLOCKselect * from SYSIBMADM.LOCKS_HELD 1.2 表函数:SELECT * FROM TABLE(SN...
S2JDBC DB2DialectでのforUpdate()を調べてみた
weixin_30378311的博客
09-29 186
FOR UPDATEの自動生成 DB2Dialectを指定してS2JDBCのSQL自動生成でforUpdate()を使うと、「 for update with rs」が付加されたSQLが発行されます。 select T1_.KEY as C1_, T1_.VALUE as C2_ from ID_GENERATOR T1_ where T1_.KEY = 'EMPLOYEE_ID' for u...
DB2查看表锁定情况与解锁
热门推荐
hyfstyle的博客
03-19 1万+
当数据库中的表查询异常慢时，考虑可能是表锁定的原因 连接数据库服务器，查看锁定情况 用命令：db2pd -db DB_NAME -locks show detail 用命令db2 get snapshot for locks on DB_NAME 用SQL：select * from sysibmadm.locks_held with ur查看表锁定情况 发现锁定，找到AGENT_ID，...
db2_sql入门教程
06-15
DB2是由IBM开发的关系型数据库系统，广泛应用于企业级数据存储和管理，支持复杂的事务处理和大数据分析。SQL（Structured Query Language），即结构化查询语言，是用于管理和操作数据库的标准语言。 在本教程中，你...
DB2 V9.7 锁事件监控.pdf
01-01
### DB2 V9.7 锁事件监控知识点详解 #### 一、锁机制与锁定事件监控的重要性 在大型DB2数据库环境中，多个应用程序之间经常会出现数据资源的竞争情况。为了确保数据的一致性和完整性，DB2引入了锁机制来保护用户在...
DB2培训PPT
09-22
了解如何通过索引、分区、统计信息收集来提升查询性能，同时掌握数据库的监控和调优工具，如db2top、db2pd等，以分析和改进数据库性能。理解锁机制和并发控制对性能的影响也是关键。 六、DB2高可用性和灾难恢复 DB2...
DB2数据库锁超时问题的分析与排查
summer_huan的博客
01-18 8199
前言DB2的锁管理机制一直为DB2应用开发人员和DBA所诟病。对其锁机制不理解的直接后果就是导致锁超时和死锁的发生。所以监控并分析锁超时和死锁，应是每个DB2 DBA的居家必备生存技能。本文记录了公司生产环境数据库发生锁超时后的分析、处理过程，如有表述不当之处，欢迎指正。数据库环境DB2 pureScale V10.5 FP5。问题背景我司生产环境某数据库每天下午15:40~15:50会报锁超时。
DB2迁移Oracle SQL语句改动点
foolish''
03-23 806
1. 去掉数据库访问中的“with ur”和 “for read only” 关键字，Oracle不支持 --DB2 select * from userInfo with ur --Oracle select * from userInfo 2. current date改为current_date，current timestamp改为current_timestamp --DB2 insert into coreframe.userInfo (name, createorid, lastmod
Select For update语句浅析
ciqu9915的博客
05-04 5791
Select … for update语句是我们经常使用手工加锁语句。通常情况下，select语句是不会对数据加锁，妨碍影响其他的DML和DDL操作。同时，在多版本一致读机制的支持下，select语句也不会被其他类型语句所阻碍。...
for update的使用
记忆_2012_有你 的专栏
07-23 1549
对for update的使用   在日常中，我们对for update的使用还是比较普遍的，特别是在如pl/sql developer中手工修改数据。此时只是觉得方便，而对for update真正的含义缺乏理解。   For update是Oracle提供的手工提高锁级别和范围的特例语句。Oracle的锁机制是目前各类型数据库锁机制中比较优秀的。所以，Oracle认为一般不
DB2数据库、DML数据操纵语言、DDL数据定义语言
810364804
08-08 543
DML(Data Manipulation Language commands)数据操纵语言；DDL(Data Definition Language commands)数据定义语言；TCC(Transaction Control commands)事务控制语言；SCC(System Control commands )系统控制语言 DB2数据库使用 DML(Data Manipulat...
DB2隔离级别之RR/RS/CS/UR
weixin_30830327的博客
08-21 1149
1.RR隔离级别：在此隔离级别下。 DB2会锁住全部相关的纪录。 在一个SQL语句运行期间， 全部运行此语句扫描过的纪录都会被加上对应的锁。在一个SQL语句运行期间，全部运行此语句扫描过的纪录都会被加上对应的锁。 详细的锁的类型还是由操作的类型来决定， 假设是读取。则加共享锁； 假设是更新。 则加独占锁。详细的锁的类型还是由操作的类型来决定。假设是读取，则加共享锁；假设是更新，则加...
db2查看表空间和系统锁状态以及索引
HouYing
06-04 3876
使用db2inst1用户登陆数据库，
Oracle DML锁机制详解：TM锁与TX锁
主要讨论的是DML锁，包括表级锁（TM锁）和事务锁（TX锁）。TM锁用于保护表结构，防止用户非法修改，而TX锁则是行级锁，用于在事务中锁定具体的数据行，防止并发冲突。DML锁有多种模式，如SS、SX、S、X等，对应不同的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值