分类目录归档:DB2

db2 内存监控

active databa 列表

[db2inst1@xifenfei ~]$ db2 list active databases

                           Active Databases

Database name                              = XIFENFEI
Applications connected currently           = 1
Database path                              = /home/db2inst1/db2inst1/NODE0000/SQL00003/

查看db2各个组件内存分配

[db2inst1@xifenfei ~]$ db2mtrk -i -p -v -d
Tracking Memory on: 2012/05/07 at 22:13:44

Memory for instance

   Other Memory is of size 10682368 bytes
   FCMBP Heap is of size 786432 bytes
   Database Monitor Heap is of size 327680 bytes
   Total: 11796480 bytes

Memory for database: XIFENFEI

   Backup/Restore/Util Heap is of size 65536 bytes
   Package Cache is of size 196608 bytes
   Other Memory is of size 131072 bytes
   Catalog Cache Heap is of size 65536 bytes
   Buffer Pool Heap (1) is of size 72482816 bytes
   Buffer Pool Heap (System 32k buffer pool) is of size 851968 bytes
   Buffer Pool Heap (System 16k buffer pool) is of size 589824 bytes
   Buffer Pool Heap (System 8k buffer pool) is of size 458752 bytes
   Buffer Pool Heap (System 4k buffer pool) is of size 393216 bytes
   Shared Sort Heap is of size 65536 bytes
   Lock Manager Heap is of size 10551296 bytes
   Database Heap is of size 13172736 bytes
   Application Heap (13) is of size 65536 bytes
   Application Heap (12) is of size 65536 bytes
   Application Heap (11) is of size 65536 bytes
   Application Heap (10) is of size 65536 bytes
   Application Heap (9) is of size 65536 bytes
   Applications Shared Heap is of size 196608 bytes
   Total: 99549184 bytes

Memory for agent 33

   Other Memory is of size 196608 bytes
   Total: 196608 bytes

Memory for agent 32

   Other Memory is of size 196608 bytes
   Total: 196608 bytes

Memory for agent 31

   Other Memory is of size 196608 bytes
   Total: 196608 bytes

Memory for agent 30

   Other Memory is of size 196608 bytes
   Total: 196608 bytes

Memory for agent 19

   Other Memory is of size 393216 bytes
   Total: 393216 bytes

查看内存统计(9.5及其以后版本)

[db2inst1@xifenfei ~]$ db2pd -dbptnmem -db xff

Database XFF not activated on database partition 0.

Option -dbptnmem is an instance scope option.  The database option has been ignored.

Database Partition 0 -- Active -- Up 0 days 00:05:30 -- Date 2012-05-07-22.16.43.375064

Database Partition Memory Controller Statistics

Controller Automatic: Y
Memory Limit:         775904 KB
Current usage:        306560 KB
HWM usage:            306816 KB
Cached memory:        78144 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-XIFENFEI            40000         40000       39488
DBMS-db2inst1            31936         31936        4992
FMP_RESOURCES            22528         22528           0
PRIVATE                   6272          6272           0
LCL-p8353                  128           128           0
LCL-p8353                  128           128           0
DB-XIFENFEI             205568        205568       33664

查看内存段粗略信息
可以通过-db database 指定具体数据库

[db2inst1@xifenfei ~]$ db2pd -memset

Database Partition 0 -- Active -- Up 0 days 00:18:39 -- Date 2012-05-07-22.29.52.410789

Memory Sets:
Name         Address    Id          Size(Kb)   Key         DBP    Type   Unrsv(Kb)  Used(Kb)   HWM(Kb)    Cmt(Kb)    Uncmt(Kb) 
DBMS         0x10000000 32769       31936      0xF5EDE61   0      0      4992       11648      11648      11648      20288     
FMP          0x11F30000 65538       22592      0x0         0      0      2          0          192        22592      0         
Trace        0x00000000 0           8510       0xF5EDE74   0      -1     0          8510       0          8510       0         

--指定数据库名称
[db2inst1@xifenfei ~]$ db2pd -memset -db xifenfei

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:26:18 -- Date 2012-05-07-23.56.47.325997

Memory Sets:
Name         Address    Id          Size(Kb)   Key         DBP    Type   Unrsv(Kb)  Used(Kb)   HWM(Kb)    Cmt(Kb)    Uncmt(Kb) 
XIFENFEI     0xA62E9000 1015815     205632     0x0         0      1      33664      96832      96832      96832      108800    
AppCtl       0xB2BB9000 983046      40064      0x0         0      12     0          576        832        832        39232     
App38        0x00000000 0           0          0x0         0      0      0          0          0          0          0         
App37        0x00000000 0           0          0x0         0      0      0          0          0          0          0         
App36        0x00000000 0           0          0x0         0      0      0          0          0          0          0         
App35        0x00000000 0           0          0x0         0      0      0          0          0          0          0         
App34        0x000E8005 950277      128        0x0         0      4      0          128        0          128        0   

查看内存段具体信息

[db2inst1@xifenfei ~]$ db2pd -mempool

Database Partition 0 -- Active -- Up 0 days 00:18:48 -- Date 2012-05-07-22.30.01.008074

Memory Pools:
Address    MemSet   PoolName   Id    Overhead   LogSz       LogUpBnd    LogHWM      PhySz       PhyUpBnd    PhyHWM      Bnd BlkCnt CfgParm   
0x10000AA4 DBMS     fcm        74    0          0           608414      0           0           655360      0           Ovf 0      n/a       
0x100009F0 DBMS     fcmsess    77    65440      845168      1118208     845168      983040      1179648     983040      Ovf 3      n/a       
0x1000093C DBMS     fcmchan    79    65440      159488      405504      159488      327680      458752      327680      Ovf 3      n/a       
0x10000888 DBMS     fcmbp      13    65440      590592      860160      590592      786432      917504      786432      Ovf 3      n/a       
0x100007D4 DBMS     fcmctl     73    186304     1176241     3118764     1176241     1376256     3145728     1376256     Ovf 11     n/a       
0x10000720 DBMS     monh       11    122592     144003      368640      144251      327680      393216      327680      Ovf 18     MON_HEAP_SZ
0x1000066C DBMS     resynch    62    26928      104080      1703936     104080      196608      1703936     196608      Ovf 2      n/a       
0x100005B8 DBMS     apmh       70    2672       459104      4325376     459636      524288      4325376     524288      Ovf 25     n/a       
0x10000504 DBMS     kerh       52    112        276828      3997696     276828      327680      3997696     327680      Ovf 65     n/a       
0x10000450 DBMS     bsuh       71    65408      2235556     8978432     2266560     2359296     8978432     2359296     Ovf 44     n/a       
0x1000039C DBMS     sqlch      50    0          1681833     1703936     1681833     1703936     1703936     1703936     Ovf 203    n/a       
0x100002E8 DBMS     krcbh      69    0          106248      65536       106352      131072      65536       131072      Ovf 14     n/a       
0x10000234 DBMS     eduah      72    1904       2816016     2816048     2816016     2818048     2818048     2818048     Ovf 1      n/a       
0x11F30234 FMP      undefh     59    8048       122900      22971520    122900      131072      23003136    131072      Phy 1      n/a       

--指定数据库名称
[db2inst1@xifenfei ~]$ db2pd -mempool -db xifenfei

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 00:05:29 -- Date 2012-05-07-22.35.58.149684

Memory Pools:
Address    MemSet   PoolName   Id    Overhead   LogSz       LogUpBnd    LogHWM      PhySz       PhyUpBnd    PhyHWM      Bnd BlkCnt CfgParm   
0xA62E9E28 XIFENFEI utilh      5     0          2120        24313856    2544        65536       24313856    65536       Ovf 10     UTIL_HEAP_SZ
0xA62E9CC0 XIFENFEI pckcacheh  7     29216      115799      Unlimited   117211      196608      Unlimited   196608      Ovf 4      PCKCACHESZ
0xA62E9C0C XIFENFEI xmlcacheh  93    50944      80008       20971520    80008       131072      20971520    131072      Ovf 1      n/a       
0xA62E9B58 XIFENFEI catcacheh  8     0          59488       Unlimited   59488       65536       Unlimited   65536       Ovf 9      CATALOGCACHE_SZ
0xA62E99F0 XIFENFEI bph        16    114464     72118000    Unlimited   72118000    72482816    Unlimited   72482816    Ovf 535    n/a       
0xA62E9888 XIFENFEI bph        16    32         782592      Unlimited   782592      851968      Unlimited   851968      Ovf 5      n/a       
0xA62E9720 XIFENFEI bph        16    32         520448      Unlimited   520448      589824      Unlimited   589824      Ovf 3      n/a       
0xA62E95B8 XIFENFEI bph        16    32         389376      Unlimited   389376      458752      Unlimited   458752      Ovf 2      n/a       
0xA62E9450 XIFENFEI bph        16    32         323840      Unlimited   323840      393216      Unlimited   393216      Ovf 2      n/a       
0xA62E939C XIFENFEI shsorth    18    0          8860        28770304    8860        65536       28770304    65536       Ovf 16     SHEAPTHRES_SHR
0xA62E92E8 XIFENFEI lockh      4     32         10487424    10616832    10487424    10551296    10616832    10551296    Ovf 1      LOCKLIST  
0xA62E9234 XIFENFEI dbh        2     419040     12439291    24903680    12441635    13172736    24903680    13172736    Ovf 739    DBHEAP    
0xB2BB966C AppCtl   apph       1     0          7452        1048576     7452        65536       1048576     65536       Phy 17     APPLHEAPSZ
0xB2BB95B8 AppCtl   apph       1     0          7452        1048576     7452        65536       1048576     65536       Phy 17     APPLHEAPSZ
0xB2BB9504 AppCtl   apph       1     0          7452        1048576     8864        65536       1048576     65536       Phy 17     APPLHEAPSZ
0xB2BB9450 AppCtl   apph       1     0          7452        1048576     7452        65536       1048576     65536       Phy 17     APPLHEAPSZ
0xB2BB92E8 AppCtl   apph       1     0          7726        1048576     18084       65536       1048576     65536       Phy 20     APPLHEAPSZ
0xB2BB9234 AppCtl   appshrh    20    2048       127088      20480000    144484      196608      20512768    196608      Phy 25     application shared
发表在 DB2 | 评论关闭

DB2 主要行级锁模拟

S行锁和X行锁模拟

--会话1
[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei(id int,name varchar(100))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c "insert into t_xifenfei values(1,'www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:07:02 -- Date 2012-04-29-01.46.48.462742

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3AE40 8          53514C4332473137315992A241 Internal P ..S  G   8          1   0          0x00 0x40000000 
0x99B3A690 8          02000700040080000000000052 Row        ..X  G   8          1   0          0x08 0x40000000 
0x99B3A6F0 8          02000700000000000000000054 Table      .IX  G   8          1   0          0x00 0x40000000 

--会话2
[db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei"
hang住

--会话3查询等待
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:08:40 -- Date 2012-04-29-01.48.26.676607

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3AE40 8          53514C4332473137315992A241 Internal P ..S  G   8          1   0          0x00 0x40000000 
0x99B3A540 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B3A690 8          02000700040080000000000052 Row        ..X  G   8          1   0          0x08 0x40000000 
0x99B40C60 7          02000700040080000000000052 Row        .NS  W   8          1   0          0x00 0x00000001 
0x99B3A420 7          02000000010000000100407056 Internal V ..S  G   7          1   0          0x00 0x40000000 
0x99B3A6F0 8          02000700000000000000000054 Table      .IX  G   8          1   0          0x00 0x40000000 
0x99B3A510 7          02000700000000000000000054 Table      .IS  G   7          1   0          0x00 0x00000001 
--这里可以发现Sts=W(STATUS=WAIT),会话的NS锁处于等待状态

--会话1
[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.

--会话2
[db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei"

ID          NAME                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 www.xifenfei.com                                                                                    

  1 record(s) selected.
--结果出现

--会话3
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:09:06 -- Date 2012-04-29-01.48.52.069878

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

U锁模拟

--会话1
[db2inst1@xifenfei ~]$ db2 +c "declare c1 cursor for select * from t_xifenfei for update"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c open c1
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c fetch c1

ID          NAME                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 WWW.XIFENFEI.COM                                                                                    

  1 record(s) selected.

--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:27 -- Date 2012-04-29-02.07.13.594441

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3A420 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B3A510 7          02000700040080000000000052 Row        ..U  G   7          1   0          0x00 0x00000001 
0x99B40C60 7          01000000010000000100807256 Internal V ..S  G   7          1   0          0x00 0x40000000 
0x99B3A4E0 7          02000700000000000000000054 Table      .IX  G   7          1   0          0x00 0x00000001 

--会话1
[db2inst1@xifenfei ~]$ db2 +c "update t_xifenfei set name='www.xifenfei.com'"
DB20000I  The SQL command completed successfully.

--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:44 -- Date 2012-04-29-02.07.30.684616

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3A420 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B3A510 7          02000700040080000000000052 Row        ..X  G   7          2   0          0x00 0x40000001 
0x99B40C60 7          01000000010000000100807256 Internal V ..S  G   7          1   0          0x00 0x40000000 
0x99B3A4E0 7          02000700000000000000000054 Table      .IX  G   7          2   0          0x00 0x40000001 
--行级锁由U升级到X

--会话1
[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.

--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:31:43 -- Date 2012-04-29-02.11.29.167659

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
发表在 DB2 | 评论关闭

DB2 主要表级锁模拟

模拟X锁

[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
T_03XFF                         DB2INST1        T     2012-04-11-21.33.12.479480

  3 record(s) selected.

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in exclusive mode"
DB20000I  The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:28:32 -- Date 2012-04-29-01.08.18.056347

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B43210 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B42F30 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          1   0          0x00 0x40000000 
0x99B43240 7          53514C4445464C5428DD630641 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B430F0 7          02000400000000000000000054 Table      ..X  G   7          255 0          0x00 0x40000000 
--还有db2内部P锁和CatCache锁

[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:28:44 -- Date 2012-04-29-01.08.30.152903

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟S锁

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in share mode"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:29:23 -- Date 2012-04-29-01.09.09.610865

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B43240 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B43030 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          1   0          0x00 0x40000000 
0x99B431B0 7          02000400000000000000000054 Table      ..S  G   7          255 0          0x00 0x40000000 
[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:29:41 -- Date 2012-04-29-01.09.27.402678

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟Z锁

[db2inst1@xifenfei ~]$ db2 +c drop table t_02xff
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:31:14 -- Date 2012-04-29-01.11.00.399066

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B41270 7          000006000E006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42870 7          00000600010071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42CF0 7          0100000000000000B0FA119F43 CatCache   ..X  G   7          255 0          0x00 0x40000000 
0x99B43240 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B418D0 7          00000600000070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B424B0 7          00000600060071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B415D0 7          00000600050070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42090 7          000006000B0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41AB0 7          0000060004006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B412D0 7          000006000A0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42930 7          00000600100071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42ED0 7          00000C0107004C030000000052 Row        ..S  G   7          2   0          0x10 0x40000000 
0x99B41A50 7          0000060003006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B425D0 7          0000060009006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B420C0 7          000006000F0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42750 7          0000060008006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42630 7          000006000E006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B425A0 7          00000600010072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41F30 7          000006000D006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41B70 7          00000600000071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40CF0 7          0200000000000000000000004F ObjTab     .IN  G   7          255 0          0x00 0x40000000 
0x99B41C60 7          00000600050071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41630 7          00000600040070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42390 7          000006000A0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42030 7          0000060003006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B416F0 7          00000600090070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41690 7          000006000F0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41990 7          0000060008006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B429F0 7          000006000E0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B426F0 7          0000060007006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41E10 7          000006000D006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B428D0 7          00000600000072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42E40 7          000006000C006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42B10 7          00001101100057120000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41CC0 7          00000600040071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B417B0 7          000006000F00CE1A0000000052 Row        ..X  G   7          1   0          0x00 0x40000000 
0x99B42960 7          00000600030070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B422D0 7          00000600090071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41570 7          000006000F0072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41B10 7          0000060002006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41510 7          00000600080070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B413F0 7          000006000E0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40E70 7          00000E006E003B010000000052 Row        ..S  G   7          1   0          0x10 0x40000000 
0x99B41150 7          0000060001006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41BD0 7          0000060007006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42540 7          000006000D0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41210 7          0000060006006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41E70 7          000006000C006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B43090 7          00000500076E0D00B0FA119F43 CatCache   ..X  G   7          255 0          0x00 0x40000000 
0x99B421B0 7          000006000B006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42C30 7          000013000A00C81A0000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B427B0 7          00000600030071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42D50 7          00000A00080063000000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41870 7          00000600020070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B423F0 7          00000600080071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42690 7          0000060001006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40F30 7          00000600070070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41DB0 7          000006000D0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B410F0 7          0000060000006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41F90 7          0000060006006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42A50 7          000006000C0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40F90 7          0000060005006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41ED0 7          000006000B006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41750 7          00000600110070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B414B0 7          000006000A006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B411B0 7          0000060010006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42450 7          000006000F006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40DB0 7          02000000000000000000000070 Pool       .IX  G   7          255 0          0x00 0x40000000 
0x99B42810 7          00000600020071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42F90 7          0000050007006E0D0000000052 Row        ..X  G   7          7   0          0x20 0x40000000 
0x99B41CF0 7          00000600010070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42330 7          00000600070071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41030 7          0000060000006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41D50 7          00000600060070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41810 7          000006000C0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41090 7          0000060005006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42270 7          000006000B0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B419F0 7          0000060004006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41330 7          000006000A006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42210 7          00000600100070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41450 7          0000060009006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42150 7          000006000F006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42C90 7          00001300000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B43210 7          00000C01000000000000000054 Table      .IS  G   7          2   0          0x10 0x40000000 
0x99B42BD0 7          00000E01000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B430F0 7          00000500000000000000000054 Table      .IX  G   7          7   0          0x00 0x40000000 
0x99B42AB0 7          00000600000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B42B70 7          00001101000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B42E70 7          00000800000000000000000054 Table      .IX  G   7          2   0          0x00 0x40000000 
0x99B42DE0 7          00000A00000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B40D50 7          02000500000000000000000054 Table      ..Z  G   7          255 0          0x00 0x40000000 
0x99B40E10 7          00000C00000000000000000054 Table      .IS  G   7          1   0          0x00 0x40000000 
0x99B40ED0 7          00000E00000000000000000054 Table      .IS  G   7          1   0          0x10 0x40000000 
0x99B413C0 7          00000801000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
--除了Z锁之外,因为DDL操作会修改系统表,因此还出现很多在系统表上表锁和行锁

[db2inst1@xifenfei ~]$ db2 rollback
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:31:41 -- Date 2012-04-29-01.11.27.194147

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟IX锁

[db2inst1@xifenfei ~]$ db2 +c "DELETE FROM T_02XFF where tabname LIKE 'T_%XFF'"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:43:47 -- Date 2012-04-29-01.23.33.163605

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3A510 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B40C30 7          0200050008006F010000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B3A4B0 7          02000500000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 

[db2inst1@xifenfei ~]$ db2 rollback
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:45:06 -- Date 2012-04-29-01.24.52.429166

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟SIX锁

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in share mode"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c "DELETE FROM T_01XFF"
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:39:47 -- Date 2012-04-29-01.19.33.620920

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B40E10 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B40C30 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          5   0          0x00 0x40000000 
0x99B40D50 7          02000400FFFF01000000000052 Row        ..S  G   7          1   0          0x10 0x00000001 
0x99B431B0 7          02000400000000000000000054 Table      SIX  G   7          255 0          0x10 0x40000001 

因为IS锁不太好模拟,在本实验中没有体现出来,其本质就是数据库在select查询数据库时给表加的一个表级锁.

发表在 DB2 | 评论关闭