DB2 中的鎖定的發(fā)生過程和原因是最不為人知的特性。本文介紹了一個常見的鎖定場景并說明哪些鎖被持有以及相應(yīng)的原因。
我們在進行客戶支持時遇到最多的話題之一就是鎖定?！盀槭裁?DB2 鎖住了這個表、行或者對象？” “這個鎖會阻塞多長時間及為什么？” “為什么出現(xiàn)了死鎖？” “我的鎖請求在等待什么？”更仔細(xì)地分析一些常見的鎖定示例可以說明 DB2 鎖定策略背后的原則。
下面是關(guān)于 DB2 如何訪問和鎖定行的一個常見誤解。當(dāng)兩位用戶插入同一個表，然后第一位用戶試圖選擇他剛插入的行時，就會出現(xiàn)問題。他會得到一個鎖等待。這是預(yù)料之外的 —— 或者是用戶在等待他自己的鎖，而這是不可能的，或者是他在等待不相關(guān)的插入，而這沒有意義。不管是什么原因，這個鎖等待是一個意外，用戶不知道如何發(fā)現(xiàn)到底發(fā)生了什么事情。
讓我們看看在這種情況下如何分析鎖定。
  |
|
假定在名為 LOCKTEST 的數(shù)據(jù)庫中有以下兩個表：
CREATE TABLE MAIN_TABLE( MAIN_JOIN_COLUMN VARCHAR(10) NOT NULL , MAIN_DATA_COLUMN VARCHAR(20) NOT NULL ) IN USERSPACE1 ; ALTER TABLE MAIN_TABLE ADD CONSTRAINT MAINPKEY PRIMARY KEY (MAIN_JOIN_COLUMN, MAIN_DATA_COLUMN); CREATE TABLE CHILD_TABLE ( CHILD_JOIN_COLUMN VARCHAR(10) NOT NULL , CHILD_DATA_COLUMN VARCHAR(20) NOT NULL ) IN USERSPACE1 ; ALTER TABLE CHILD_TABLE ADD CONSTRAINT CHILDPKEY PRIMARY KEY (CHILD_JOIN_COLUMN); |
并且用以下數(shù)據(jù)初始化這些表：
MAIN_TABLE: MAIN_JOIN_COLUMN MAIN_DATA_COLUMN ------------------ -------------------- 1 some existing data 1 2 some existing data 2 3 some existing data 3 4 some existing data 4 5 some existing data 5 CHILD_TABLE: CHILD_JOIN_COLUMN CHILD_DATA_COLUMN ----------------- -------------------- 1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE |
下面的一系列操作生成了一個好象是意外的鎖等待：
insert into main_table values（'1'，'deadlock 1'） insert into main_table values（'1'，'deadlock 2'） select main_table.main_join_column, child_table.child_data_column
from main_table, child_table
where main_table.main_join_column = child_table.child_join_column
and main_table.main_data_column = 'deadlock 1' 這個 select 不會完成，它看來是在等待一個鎖。因此一定是等待第二個會話的插入提交。但是并沒有選擇那一行。到底發(fā)生了什么？
由于在 main_table 的兩列上有一個主鍵，并且選擇在第一列上進行聯(lián)接，并有一個針對第二列的謂詞，您可能會認(rèn)為 DB2 會從 main_table 中提取滿足條件的一行，因而不會被第二個會話中的鎖所阻塞。但是 DB2 并沒有這樣工作。
 |
|
為了理解發(fā)生的情況，讓我們分析這個查詢和存取計劃（access plan）。要找出發(fā)生鎖等待時持有哪個鎖，我們使用 db2pd 實用程序。為了閱讀方便，對下面的 db2pd 輸出進行了修改，刪除除了鎖定的行以外的所有行。
/home/lorysa $db2pd -db locktest -locks show detail Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att Rlse 0x402C07E0 3 000200020000000A0000000052 Row ..X G 3 1 0 8 0x40 TbspaceID 2 TableID 2 RecordID 0xA 0x402C02E0 2 000200020000000A0000000052 Row .NS W 3 1 0 0 0x0 TbspaceID 2 TableID 2 RecordID 0xA 0x402C03A8 2 00020002000000090000000052 Row ..X G 2 1 0 8 0x40 TbspaceID 2 TableID 2 RecordID 0x9 |
可以看到 DB2 持有 tbspaceid 2 表空間中 tableid 2 表上的鎖?，F(xiàn)在找出這是哪個表：
/home/lorysa $db2 "select substr(tabschema,1,9) as tabschema, substr(tabname,1,12) as tabname, tableid, tbspaceid from syscat.tables where tbspaceid = 2 and tableid = 2" TABSCHEMA TABNAME TABLEID TBSPACEID --------- ------------ ------- --------- LORYSA MAIN_TABLE 2 2 1 record(s) selected. |
上面的 db2pd 輸出提供了被鎖定的行的記錄標(biāo)識（RID）。值 0xA 實際上表示 0x0000000A，RID 是由一個三字符頁號（這里是 0）和一個單字符 slot 標(biāo)識（這里是 0xA，即 10）組成的四字符字段。它告訴我們所關(guān)注的這一行是在表的第 0 頁的 slot 10 中。每個數(shù)據(jù)頁有最多 255 個 “slot”，它包含給定行在頁中的偏移量。RID 通常描述為（頁號；slot 數(shù)），即十進制記數(shù)的（0；10）、二進制記數(shù)的（0；A）。RID 惟一地標(biāo)識了表中的一行。
輸出表明等待的是 db2pd 輸出中的行（0；A）上的鎖，因為提供鎖請求狀態(tài)的 ‘Sts’ 列顯示 W，表示等待。其他鎖的狀態(tài)為 G，表示授予（granted），因此它們被持有。
因此，總結(jié)有關(guān)的鎖：
運行 select 的代理等待的行是值為 ('1', 'deadlock 2') 的行，而不是它剛插入的行?？梢酝茢嘤捎诓迦氲男猩嫌?X 鎖 —— 新插入的行獨占性地鎖定，直到插入被提交。同時，一個代理不能等待它自己，如果一個代理對于它已經(jīng)擁有獨占（X）鎖的行請求一個共享（NS）鎖，那么這個請求會被授予，因為已經(jīng)擁有了一個具有足夠或者更高模式的鎖。因此問題是：如果選擇具有值 deadlock 1 的行，為什么會等待新插入的、值為 deadlock 2 的行？要理解其中的原因，必須分析 select 的存取計劃。
select main_table.main_join_column, child_table.child_data_column from main_table, child_table where main_table.main_join_column = child_table.child_join_column and main_table.main_data_column = 'deadlock 1' |
存取計劃基本上就是 DB2 訪問滿足查詢的數(shù)據(jù)的路線圖?？梢杂?DB2 的 explain 工具程序生成存取計劃。用下面的命令創(chuàng)建包含 explain 結(jié)果的表：
db2 -tvf EXPLAIN.DDL
在實例主目錄的 sqllib 目錄中的 EXPLAIN.DDL 文件包含創(chuàng)建 explain 表所需要的 DDL 語句。然后可以用下面的命令解釋語句：
db2 explain all for select statement
db2exfmt 工具就會生成存取計劃。
關(guān)于 explain 和 db2exfmt 的細(xì)節(jié)的更多信息，請參閱 DB2 Command Reference 和 DB2 SQL Reference，第 1 卷。
Total Cost: 43.712 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 3.44 HSJOIN ( 2) 43.712 2 /-----+----- 86 3.44 TBSCAN TBSCAN ( 3) ( 4) 20.7463 22.2771 1 1 | | 86 86 TABLE: LORYSA TABLE: LORYSA CHILD_TABLE MAIN_TABLE |
啊哈。在缺少當(dāng)前統(tǒng)計的情況下，優(yōu)化器選擇對參與聯(lián)接的兩個表進行表掃描。因此要找到滿足 select 語句中謂詞的行，這個優(yōu)化器必須將主表中的所有行上鎖、讀取它們的值并與謂詞中提供的值進行比較。表掃描總是讀取（并上鎖）表中所有行。在 explain（db2exfmt）輸出中，可以看到對計劃中第 4 號表進行掃描的 sargable 謂詞：
Predicates: ---------- 2) Sargable Predicate Relational Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.04 Predicate Text: -------------- (Q2.MAIN_DATA_COLUMN = 'deadlock 1') |
因此主表中所有 7 行都被讀?。ㄏ壬湘i），data_column 中的值與 deadlock 1 比較，匹配的行參與聯(lián)接。
現(xiàn)在讓我們對兩個表進行 runstats 以了解它對計劃的影響。情況在這里有了有意思的變化。仍然會有鎖等待，但是稍有不同：
在這里，發(fā)生鎖等待時，被持有的鎖是：
/home/lorysa $db2pd -db locktest -locks show detail Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att Rlse 0x402C07E0 3 000200020000000A0000000052 Row ..X G 3 1 0 8 0x40 TbspaceID 2 TableID 2 RecordID 0xA 0x402C02E0 2 000200020000000A0000000052 Row .NS W 3 1 0 0 0x0 TbspaceID 2 TableID 2 RecordID 0xA 0x402C06A0 2 00020003000000040000000052 Row .NS G 2 1 0 0 0x0 TbspaceID 2 TableID 3 RecordID 0x4 0x402C03A8 2 00020002000000090000000052 Row ..X G 2 1 0 8 0x40 TbspaceID 2 TableID 2 RecordID 0x9 |
像前面一樣總結(jié)：
這里的存取計劃是：
Total Cost: 17.0258 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 1 NLJOIN ( 2) 17.0258 1 /------+------ 1 1 IXSCAN FETCH ( 3) ( 4) 2.05312 14.9727 0 1 | /---+--- 5 1 5 INDEX: LORYSA IXSCAN TABLE: LORYSA MAINPKEY ( 5) CHILD_TABLE 1.64992 0 | 5 INDEX: LORYSA CHILDPKEY |
在這里我們使用了兩個索引，并且有同樣的謂詞，這一次針對計劃中編號為 3 的索引掃描：
Predicates: ---------- 2) Sargable Predicate Relational Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.2 Predicate Text: -------------- (Q2.MAIN_DATA_COLUMN = 'deadlock 1') |
注意完全相同的謂詞。這是鍵。這是一個 sargable 謂詞，而不是一個索引 sargable 謂詞（即，它不能作為索引掃描的開始/停止鍵）。這是因為它針對索引的第二列。
索引 sarg（即開始/停止鍵）會限制由索引掃描返回的行數(shù)。這意味著：在值 X 處開始掃描，在值 Y 處停止，只返回值在 X 與 Y 之間的鍵。沒有索引 sarg 時，索引掃描將使用索引訪問數(shù)據(jù)，但是會返回索引中的所有鍵。
為了展示這一點，將它與計劃中第 5 號索引掃描上使用的謂詞相比較，它針對子表中惟一的索引列：
Predicates: ---------- 3) Start Key Predicate Relational Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.2 Predicate Text: -------------- (Q2.MAIN_JOIN_COLUMN = Q1.CHILD_JOIN_COLUMN) 3) Stop Key Predicate Relational Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.2 Predicate Text: -------------- (Q2.MAIN_JOIN_COLUMN = Q1.CHILD_JOIN_COLUMN) |
因為主表上的謂詞不能用于索引，所以我們必須讀取表中所有行，使用索引，然后在將滿足條件的行傳給聯(lián)接之前使用謂詞。與以前一樣，在判斷謂詞之前必須鎖住這些行。
|
|
我們知道了所發(fā)生的事情，可以采取以下步驟改進并發(fā)性：
|
|
在完成這個例子的過程中，您看到了不同的工具（db2pd、SQL、explain、db2exfmt）是如何幫助揭示真實情況的。這些工具所收集的信息使我們可以理解為什么鎖會被持有，這又可幫助我們確定避免不必要的鎖定的策略和技術(shù)。有了這些信息，任何需要了解鎖定的人都可以將這里使用的技術(shù)和原則應(yīng)用到自己的場景中，并得出類似的分析和建議。
| ||
| | | Lorysa Bond 是 DB2 UDB Advanced Support 的高級分析師，專長是鎖定和并發(fā)問題。在重新加入客戶支持部門之前，Lorysa 是 DB2 UDB 引擎工作組的開發(fā)人員。她在客戶問題診斷和解決方案方面具有豐富的經(jīng)驗。 |
