等待事件enq: TX - row lock contention

【效能優化】佇列等待之enq: TX - row lock contention

問題背景：

客戶反映某條sql DELETE SHAREINNERDOC WHERE SOURCEID=:B1<br/>這個執行時間太長

問題解決

1> 

檢視awr報告：

 

 

有佇列等待之enq: TX - row lock contention，對應的sql也是客戶提供的sql

enq: TX - row lock contention 通常是Application級別的問題。通常情況下，Oracle資料庫的等待事件enq: TX - row lock contention會在下列三種情況下會出現。

（一）第一種情況，是真正的業務邏輯上的行鎖衝突，如一條記錄被多個人同時修改。這種鎖對應的請求模式是6（Waits for TX in mode 6 ：A 會話持有row level lock，B會話等待這個lock釋放。）。不同的session更新或刪除同一個記錄。（This occurs when one application is updating or deleting a row that another session is also trying to update or delete. ）

（二）第二種情況，是唯一鍵衝突（In mode 4,唯一索引），如主鍵欄位相同的多條記錄同時插入。這種鎖對應的請求模式是4。這也是應用邏輯問題。表上存在唯一索引，A會話插入一個值（未提交），B會話隨後也插入同樣的值;A會話提交後，enq: TX - row lock contention消失。

（三）第三種情況，是bitmap索引的更新衝突（in mode 4 :bitmap），就是多個會話同時更新bitmap索引的同一個資料塊。源於bitmap的特性：點陣圖索引的一個鍵值，會指向多行記錄，所以更新一行就會把該鍵值指向的所有行鎖定。此時會話請求鎖的對應的請求模式是4。bitmap索引的物理結構和普通索引一樣，也是 B-tree 結構,但它儲存的資料記錄的邏輯結構為"key_value,start_rowid,end_rowid,bitmap"。

其內容類似這樣："‘8088’,00000000000,10000034441,1001000100001111000"

Bitmap是一個二進位制，表示 START_ROWID 到 END_ROWID 的記錄，1 表示等於 key_value即‘8088’的 ROWID 記錄， 0 則表示不是這個記錄。

在瞭解bitmap索引的結構之後，我們就能理解同時插入多條記錄到擁有bitmap索引的表時，就會同時更新bitmap索引中一個塊中的記錄，等於某一個記錄被同時更新，自然就會出現行鎖等待。插入併發量越大，等待越嚴重。

（四）其他原因

It could be a primary key problem;  a trigger firing attempting to insert, delete, or update a row; a problem with initrans; waiting for an index split to complete; problems with bitmap indexes;updating a row already updated by another session; or something else.

如果資料庫一出現enq: TX - row lock contention等待，可以去看v$session和v$session_wait等檢視。在v$session和v$session_wait中，如果看到的event列是enq: TX - row lock contention的，就表示這個會話正處於行鎖等待。該等待事件的請求模式可以從v$session和v$session_wait的p1列中得到。

select sid,

       chr(bitand(p1, -16777216) / 16777215) ||

       chr(bitand(p1, 16711680) / 65535) "Name",

       (bitand(p1, 65535)) "Mode"

  from v$session_wait

 where event like 'enq%'; 

通過這個SQL可以將p1轉換為易閱讀的文字。

2> sql是個delete語句，檢視一下執行計劃，是否有索引

SQL_ID 4ggjbjszghd7x

--------------------

DELETE SHAREINNERDOC WHERE SOURCEID=:B1

 

Plan hash value: 2749040791

----------------------------------------------------------------------------------------------------------------

| Id  | Operation                            | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT                     |                         |       |       |     9 (100)|          |

|   1 |  DELETE                              | SHAREINNERDOC           |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SHAREINNERDOC           |    11 |   506 |     9   (0)| 00:00:01 |

|   3 |    INDEX RANGE SCAN                  | DSHAREINDEXDOC_SOURCEID |    11 |       |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------------------

可以看到SQL走的是INDEX RANGE SCAN,索引名稱DSHAREINDEXDOC_SOURCEID

檢視索引的型別為FUNCTION-BASED NORMAL

函式索引（Function-Based Indexes，FBI），它基於對錶中列進行計算後的結果建立索引。函式索引在不修改應用程式的邏輯基礎上提高了查詢效能。如果沒有函式索引，那麼任何在列上執行了函式的查詢都不能使用這個列的索引。當在查詢中包含該函式時，資料庫才會使用該函式索引。函式索引可以是一個B-Tree索引或點陣圖索引。

用於生成索引的函式可以是算術表示式，也可以是一個包含SQL函式、使用者定義PL/SQL函式、包函式，或C呼叫的表示式。當資料庫處理INSERT和UPDATE語句時，它仍然必須計算函式才能完成對語句的處理。

對於函式索引的索引列的函式查詢可以通過檢視DBA_IND_EXPRESSIONS來實現，通過如下的SQL語句可以查詢所有的函式索引：

SELECT * FROM DBA_INDEXES D WHERE D.INDEX_TYPE LIKE 'FUNCTION-BASED%'; 

 

檢視這個索引是系統自動建立的，

而條件中引用的SOURCEID沒有合理的索引，只有一個組合索引，兩千萬的資料沒有合理的索引。。

為SOURCEID欄位建立索引並收集統計資訊，sql開始引用新的執行計劃，等待事件enq: TX - row lock contention也沒了

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69950231/viewspace-2661781/，如需轉載，請註明出處，否則將追究法律責任。