db2_dml鎖分析
目的:
透過分析不同隔離級別下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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL鎖分析MySql
- 死鎖分析
- SQLServer的死鎖分析（1）：頁鎖SQLServer
- For Update 加鎖分析
- 死鎖案例分析
- HashMap死鎖分析HashMap
- MySQL：一個死鎖分析 (未分析出來的死鎖)MySql
- 故障分析 | MySQL死鎖案例分析MySql
- MySQL鎖問題分析-全域性讀鎖MySql
- MySQL鎖等待與死鎖問題分析MySql
- MongoDB 中的鎖分析MongoDB
- MyRocks事務鎖分析
- Mysql鎖機制分析MySql
- oracle鎖阻塞的分析Oracle
- GreatSQL 死鎖案例分析SQL
- 從ReentrantLock加鎖解鎖角度分析AQSReentrantLockAQS
- MySQL死鎖系列-常見加鎖場景分析MySql
- InnoDB 事務加鎖分析
- MySQL 死鎖問題分析MySql
- 可重入鎖原始碼分析原始碼
- ORACLE 死鎖分析過程Oracle
- Sqlserver分析死鎖問題SQLServer
- 線上死鎖問題分析
- mysql鎖等待查詢分析MySql
- MySQL加鎖處理分析MySql
- MySQL 加鎖處理分析MySql
- MySQL 死鎖日誌分析MySql
- TX鎖（Transaction Lock）分析 (zt)
- 圖解Janusgraph系列-併發安全：鎖機制（本地鎖+分散式鎖）分析圖解分散式
- Lock的獨佔鎖和共享鎖的比較分析
- 故障分析 | 從 Insert 併發死鎖分析 Insert 加鎖原始碼邏輯原始碼
- JAVA物件分析之偏向鎖、輕量級鎖、重量級鎖升級過程Java物件
- MySQL批量更新死鎖案例分析MySql
- 深入分析CAS（樂觀鎖）
- Java 重入鎖 ReentrantLock 原理分析JavaReentrantLock
- Oracle死鎖原因產生分析Oracle
- 故障分析 | MySQL鎖等待超時一例分析MySql
- 併發程式設計 —— 原始碼分析公平鎖和非公平鎖程式設計原始碼