ITPub博客

首页 > Linux操作系统 > Linux操作系统 > db2_dml锁分析

db2_dml锁分析

原创 Linux操作系统 作者:redhouser 时间:2011-09-09 17:25:19 0 删除 编辑

目的:
通过分析不同隔离级别下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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 效率与企业文化
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810281