作者：tacy lee
在應(yīng)用中，我們經(jīng)常會(huì)碰到sql執(zhí)行很慢，但是數(shù)據(jù)庫(kù)cpu和內(nèi)存使用率又不高的情況，類似的問(wèn)題基本上由于鎖，排序等原因造成，本文主要描述如何去定位鎖等待問(wèn)題，誰(shuí)在鎖等待？等待誰(shuí)持有的鎖？鎖在那個(gè)表？
一、測(cè)試準(zhǔn)備
1、先在session1執(zhí)行如下操作，創(chuàng)建測(cè)試表
#db2 connect to eos
#export DB2OPTIONS=+C
#db2 "create table tacy_test (a int not null primary key,b varchar(10))"
#db2 "insert into tacy_test values(1,'a')"
#db2 "insert into tacy_test values(2,'a')"
#db2 "insert into tacy_test values(3,'a')"
#db2 "insert into tacy_test values(4,'a')"
#db2 commit
2、在session2執(zhí)行如下操作
#db2 connect to eos
#export DB2OPTIONS=+C
二、產(chǎn)生一個(gè)lock wait
在session1做一個(gè)表更新：
#db2 "update tacy_test set b='b' where a=4"
sql執(zhí)行成功
在session2做同樣更新操作：
#db2 "update tacy_test set b='c' where a=4"
進(jìn)程被掛起等待
三、定位鎖等待
1、先來(lái)看看應(yīng)用的情況：
#db2pd -db eos -applications
Database Partition 0 -- Database EOS -- Active -- Up 0 days 07:37:37
Applications:
Address AppHandl [nod-index] NumAgents CoorPid Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid
0x10140040 8 [000-00008] 1 8425 Lock-wait 80 2 66 1 *LOCAL.db2inst1.071124043739
0x100CE540 7 [000-00007] 1 8358 UOW-Waiting 0 0 80 2 *LOCAL.db2inst1.071124043708
可以看到有一個(gè)應(yīng)用的狀態(tài)處于Lock-wait
2、現(xiàn)在我們來(lái)看看應(yīng)用在等什么
#db2pd -db eos -locks showlock wait
Database Partition 0 -- Database EOS -- Active -- Up 0 days 07:42:56
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att Rlse
0x2C8E0760 3 02001806078066020000000052 Row ..X W 2 1 0 0 0x0 TbspaceID 2 TableID 1560 RecordID 0x2668007
鎖的類型為Row（行鎖），X鎖（排他鎖），下面是我們最關(guān)心的鎖的位置
TbspaceID 2 TableID 1560 RecordID 0x2668007
其中TbspaceID為表空間ID，TableID為表的ID，RecordID代表具體位置，全部應(yīng)該是0x0266807,其中前面三個(gè)字節(jié)為page number，為0x02668，后面一個(gè)字節(jié)代表solt identifier，為0x07
3、找到相應(yīng)的表
#db2 "select tbspace,tabschema,tabname,tableid,tbspaceid from syscat.tables where tbspaceid=2 and tableid=1560"
TBSPACE TABSCHEMA TABNAME TABLEID TBSPACEID
------------ ----------- ---------- ------- ---------
USERSPACE1 DB2INST1 TACY_TEST 1560 2
1 record(s) selected.
4、根據(jù)RecordID找到鎖在哪行
db2提供了一個(gè)強(qiáng)大的數(shù)據(jù)分析工具db2dart，可以dump出相應(yīng)的page數(shù)據(jù)
#db2dart eos /dd /tsi 2 /oi 1560 /ps 157312p /np 1 /v y
Warning: The database state is not consistent.
Warning: Reorg rows MAY be due to the inconsistent state of the database.
DB2DART Processing completed with warning(s)!
Complete DB2DART report found in:
/home/db2inst1/sqllib/db2dump/DART0000/EOS.RPT其中tsi為表空間id(2)，oi為表id(1560)，ps為page number(0x0266807)，需要轉(zhuǎn)換為十進(jìn)制，在結(jié)尾必須加p，np代表你要獲取的頁(yè)數(shù)，v為是否詳細(xì)輸出
現(xiàn)在我們來(lái)看看EOS.RPT
______________________________________________________________________________
_______ DART _______
D a t a b a s e A n a l y s i s a n d R e p o r t i n g T o o l
IBM DB2 6000
______________________________________________________________________________
DART (V8.1.0) Report:
2007-11-24-20.59.51.355893
Database Name: EOS
Report name: EOS.RPT
Old report back-up: EOS.BAK
Database Subdirectory: /opt/db2/db2inst1/NODE0000/SQL00001
Operational Mode: Database Inspection Only (INSPECT)
______________________________________________________________________________
------------------------------------------------------------------------------
Action option: DD
Table-object-ID: 1560; Tablespace-ID: 2; First-page: 157312p; Number-pages: 1; Verbose: y
Warning: The database state is not consistent.
Warning: Reorg rows MAY be due to the inconsistent state of the database.
Connecting to Buffer Pool Services...
Table object report phase start.
Dump format is verbose.
______________________________________
Page 0 of object 1560 from table space 2.
BPS Page Header:
Page Data Offset = 48
Page Data Length = 4048
Page LSN = 0000 AE97 AE41
Object Page Number = 0
Pool Page Number = 157312
Object ID = 1560
Object Type = Data Object
Data Page Header:
Slot Count = 8
Total Free Space = 2784
Total Reserve Space = 0
Youngest Reserve Space = n/a
Youngest TID = n/a
Free Space Offset = 2799
Maximum Record Size = 23
Data Records:
Slot 0:
Offset Location = 3996 (xF9C)
Record Length = 32 (x20)
Record Type = Data Object Header Control Record
Page count = 1
Object Creation LSN = 0000 AE97 800C
Object State = x0000
UDI Since Runstats = 0
DART Field = x00000000
Slot 1:
Offset Location = 2992 (xBB0)
Record Length = 1004 (x3EC)
Record Type = Free Space Control Record
Free space entries:
0: 2884 (x0B44), 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC)
4: 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC)
8: 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC)
省略。。。
492: 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC)
496: 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC), 4028 (x0FBC)
Slot 2:
Offset Location = 2916 (xB64)
Record Length = 76 (x4C)
Record Type = Table Directory Record
MetaIndex Root Page = 157377
Index Type = 2
Table Descriptor Pointer -- Page 157312 Slot 3
Max Insert Search = 0
Flags = x02000200
bit representation = 00000010 00000000 00000010 00000000
Check pending info:
Constraint status = x00
Constraint RID = Page 0 Slot 0
last BID = x00000000
Slot 3:
Offset Location = 2892 (xB4C)
Record Length = 24 (x18)
Record Type = Table Description Record
Number of Columns = 2
Column 1:
Type is Long Integer
Length = 4
Prohibits NULLs
Prohibits Default
Fixed offset: 0
Column 2:
Type is Fixed Length Character String
Length = 10
Allows NULLs
Prohibits Default
Fixed offset: 4
Slot 4:
Offset Location = 2869 (xB35)
Record Length = 23 (x17)
Record Type = Table Data Record (FIXEDVAR)
Fixed part length value = 15
Column 1:
Fixed offset: 0
Type is Long Integer
Value = 1
Column 2:
Fixed offset: 4
Type is Fixed Length Character String
61202020 20202020 2020 a
Slot 5:
Offset Location = 2846 (xB1E)
Record Length = 23 (x17)
Record Type = Table Data Record (FIXEDVAR)
Fixed part length value = 15
Column 1:
Fixed offset: 0
Type is Long Integer
Value = 2
Column 2:
Fixed offset: 4
Type is Fixed Length Character String
61202020 20202020 2020 a
Slot 6:
Offset Location = 2823 (xB07)
Record Length = 23 (x17)
Record Type = Table Data Record (FIXEDVAR)
Fixed part length value = 15
Column 1:
Fixed offset: 0
Type is Long Integer
Value = 3
Column 2:
Fixed offset: 4
Type is Fixed Length Character String
61202020 20202020 2020 a
Slot 7:
Offset Location = 2800 (xAF0)
Record Length = 23 (x17)
Record Type = Table Data Record (FIXEDVAR)
Fixed part length value = 15
Column 1:
Fixed offset: 0
Type is Long Integer
Value = 4
Column 2:
Fixed offset: 4
Type is Fixed Length Character String
61202020 20202020 2020 a
Slots Summary: Total=8, In-use=8, Deleted=0.
Table object report phase end.
______________________________________
DB2DART Processing completed with warning(s)!
Warning(s) detected during processing.
______________________________________
Complete DB2DART report found in:
/home/db2inst1/sqllib/db2dump/DART0000/EOS.RPT
_______ D A R T P R O C E S S I N G C O M P L E T E _______找到Solt 7 (0x07)，ok，你現(xiàn)在可以清楚的知道應(yīng)用等待的Row為(4,a)
總結(jié)
通過(guò)上面的方法，我們簡(jiǎn)單描述了一個(gè)db2鎖問(wèn)題的定位方法，希望能給大家在分析和定位應(yīng)用性能問(wèn)題的時(shí)候起到一定的幫助