问题
很多时候,分析DB2锁问题的时候,如果是行锁,需要知道具体是加在了哪一行上,这一行的具体内容是什么。
分析过程
<如果嫌这一部分麻烦,可以直接跳过,只看结论>以一个具体的表T1为例子,通过视图syscat.tables看到TABLEID为6, 十六进制形式为0600;TBSPACEID为3,十六进制形式为0300:
inst105@db2a:~$ db2 "select TABLEID,TBSPACEID,hex(TABLEID) as TABLEIDHEX, hex(TBSPACEID) as TBSPACEIDHEX from syscat.tables where tabname='T1'"
TABLEID TBSPACEID TABLEIDHEX TBSPACEIDHEX
------- --------- ---------- ------------
6 3 0600 0300
1 record(s) selected.
DB2中每一行都有一个标识,叫做rid(row id),可以使用函数rid()来获取。下面的SQL语句显示了查询结果中每一行的RID以及RID的16进制形式:
inst105@db2a:~$ db2 +c "select id, rid(T1) as rid, hex(rid(T1)) as ridHex from T1 where id in (1, 10, 1000, 1111, 2000) with RS"
ID RID RIDHEX
----------- -------------------- ----------------
1 4 0400000000000000
10 13 0D00000000000000
1000 196858 FA00030000000000
1111 262243 6300040000000000
2000 458954 CA00070000000000
5 record(s) selected.
inst105@db2a:~$ db2pd -db sample -locks
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:06:28 -- Date 2017-09-05-20.48.10.384462
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x00007FC7CFA86100 3 03000600040000000000000052 RowLock .NS G 3 1 0 0x00000000 0x00000001 0
0x00007FC7CFA85380 3 03000600630004000000000052 RowLock .NS G 3 1 0 0x00000000 0x00000001 0
0x00007FC7CFA85E80 3 4141414141664164FE8BC714C1 PlanLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007FC7CFA8BD00 3 03000600FA0003000000000052 RowLock .NS G 3 1 0 0x00000000 0x00000001 0
0x00007FC7CFA8C680 3 03000600CA0007000000000052 RowLock .NS G 3 1 0 0x00000000 0x00000001 0
0x00007FC7CFA8C280 3 030006000D0000000000000052 RowLock .NS G 3 1 0 0x00000000 0x00000001 0
0x00007FC7CFA8C580 3 03000600000000000000000054 TableLock .IS G 3 1 0 0x00002000 0x00000001 0
再来对比一下RID的十六进制形式和这5个lockname:
RIDHEX
----------------
0400000000000000
0D00000000000000
FA00030000000000
6300040000000000
CA00070000000000
Lockname
0300| 0600| 0400000000000000|52
0300| 0600| 6300040000000000|52
0300| 0600| FA00030000000000|52
0300| 0600| CA00070000000000|52
0300| 0600| 0D00000000000000|52
就会发现,第一列 0300是表所在表空间ID的十六进制形式,第二列是表ID的十六进制形式,第三列是表的RID的十六进制形式。
结论:
所以,根据lockname找到对应的行分两步,先找到对应的表,再找到对应的行,这里以上面例子中的lockname为 0300 0600 CA0007000000000052的锁来说明:1. 找到表名:
inst105@db2a:~$ db2 "select substr(TABSCHEMA,1,30) as TABSCHEMA, substr(TABNAME,1,30) as TABNAME from syscat.tables where hex(TBSPACEID)='0300' and hex(TABLEID)='0600'"
TABSCHEMA TABNAME
------------------------------ ------------------------------
INST105 T1
1 record(s) selected.
2. 根据表名和RID找到对应的行:
inst105@db2a:~$ db2 "select * from INST105.T1 where hex(rid(T1))='CA00070000000000'"
ID NAME
----------- ---------------
2000 mqs2000
1 record(s) selected.