db2診斷系列之---定位鎖等待問題? 作者：tacy lee
? 在應用中，我們經常會碰到sql執行很慢，但是數據庫cpu和內存使用率又不高的情況，類似的問題基本上由于鎖，排序等原因造成，本文主要描述如何去定位鎖等待問題，誰在鎖等待？等待誰持有的鎖？鎖在那個表？
? 一、測試準備
? 1、先在session1執行如下操作，創建測試表
? #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執行如下操作
? #db2 connect to eos #export DB2OPTIONS=+C 二、產生一個lock wait
? 在session1做一個表更新：
? #db2 "update tacy_test set b='b' where a=4" sql執行成功 在session2做同樣更新操作： #db2 "update tacy_test set b='c' where a=4" 進程被掛起等待
? 三、定位鎖等待
? 1、先來看看應用的情況：
? #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 ? ?
? 可以看到有一個應用的狀態處于Lock-wait
? 2、現在我們來看看應用在等什么
? #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鎖（排他鎖），下面是我們最關心的鎖的位置
? TbspaceID 2 TableID 1560 RecordID 0x2668007
? 其中TbspaceID為表空間ID，TableID為表的ID，RecordID代表具體位置，全部應該是0x0266807,其中前面三個字節為page number，為0x026680，后面一個字節代表solt identifier，為0x07? 3、找到相應的表
? #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、根據RecordID找到鎖在哪行
? db2提供了一個強大的數據分析工具db2dart，可以dump出相應的page數據
? #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)，需要轉換為十進制，在結尾必須加p，np代表你要獲取的頁數，v為是否詳細輸出
? 現在我們來看看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，你現在可以清楚的知道應用等待的Row為(4,a)
?
? 總結
? 通過上面的方法，我們簡單描述了一個db2鎖問題的定位方法，希望能給大家在分析和定位應用性能問題的時候起到一定的幫助 根據rid能查到該row數據 ========================== 在程序出現問題的時候，鎖等待往往得不到釋放，就是那個application執行完畢都還hold住表，在這種情況下，我們需要盡可能多的提供信息給開發人員，幫助他們定位問題所在，確實這個很useful。
? 1，db2pd -d mall -locks show detail 查看到鎖等待row所在的page和slot 2，根據數據庫ROWID的組成，pages為640和slot為4，如果是 large tablespace，則rowid轉換為整數為:640*65536+4 （補充：一般說來，RID在v8里面是4字節，在v9里面的large tablespace（默認）是6字節。4字節的版本是3字節page id加上1字節的slot。6字節版本是4字節page id加上2字節的slot ）
3，select * from ORDERS where rid(ORDERS)= 54758604808 with ur ? 根據rid能查到該row數據 ------ 這塊為什么要乘以65536
大家可能聽說過RID，也很困惑RID是什么咚咚（只知道是指向某一個record的，但是不知道具體是怎么指的）。
一般說來，RID在v8里面是4字節，在v9里面的large tablespace（默認）是6字節。4字節的版本是3字節page id加上1字節的slot。
6字節版本是4字節page id加上2字節的slot

這塊是v9 ,large tablespace ,所以2的16次方=65536 ,也就是一頁有65536個slot, 故當前的row對應的rowid為640*65536+4

轉載于:https://blog.51cto.com/freebile/919418

總結

以上是生活随笔為你收集整理的db2诊断系列之---定位锁等待问题的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。