foreign key 造成 TM contention 案例

Oracle 版本: 12.1.0.2.0

OS 版本: AIX 7.1

問題描述:

對 Table 進行 delete 動作時卡住無法成功執行。

SQL> SQL> delete regions where region_id=7;  (毫無反應)

問題分析:

從 v$session 可以發現等待事件為 enq: TM – contention 且被 sid 為 80 的 session 咬住 :

不過 sid 為 80 的 session 當前狀態為 SQL*Net message from client ，是一個 idle 的事件，無法確定 sid 80 是執行了什麼導致 TM – contention ，進一步使用 hanganalyze 分析 :

SQL> oradebug setospid 5436 Oracle pid: 52, Unix process pid: 5436, image: oracle@orcl (TNS V1-V3) SQL> oradebug hanganalyze 3

由 trace 當中發現等待的是 mode 4 的 TM lock :

mode 4 的 TM lock 一般與 foreign key 有關，當具有 primary key 的 table 進行 update / delete 操作時，需要先確認此筆資料是否存在於 child table ，此時若 child table 上的 foreign key 欄位沒有建立 index ，則會對 child table 進行 Share mode 的 DML lock ，也就是 mode 4 lock ，由 trace 顯示無法完成 mode 4 lock ，所以整個語法無法完成，有可能此時 child table 進行了尚未 commit 的 DML 操作所以發生了 TM – contention 。

檢查 reference 到 REGIONS 的所有 table :

檢查 COUNTRIES 與 PEO 的 foreign key 欄位是否缺少 index :

CONTRIES 與 PEO 作為 foreign key 欄位的 REGION_ID 都沒有建立 index 。

解決方法:

將所有 foreign key 欄位建立 index 即可 :

SQL> create index ix_countries on countries(region_id); SQL> create index ix_peo on peo(region_id);