關於enq: TX - row lock contention行鎖的總結

常用分析sql語句：
 --子查詢中 request>0 即是存在鎖請求的會話，透過 id1, id2, type 關聯 gv$lock，即可將阻塞者和被阻塞者全部列出；
select /* +rule*/
t.INST_ID,
decode(request,0,'holder','waiter') role,
t.SID,
t.TYPE,
t.REQUEST,t.LMODE,t.BLOCK,t.CTIME,t.ID1,t.ID2
 from  gv$lock t
 where (t.ID1,t.ID2,t.TYPE)
 in (select t.ID1,t.ID2,t.TYPE from gv$lock where t.REQUEST>0)
 order by ctime desc,role;
--wrh$sql_stat 觀察過去幾小時 SQL 的執行頻率、執行計劃、單次響應時間、邏輯讀等執行統計資訊 發現 PLAN_HASH_VALUE 未改變意味著執行計劃未曾變化，但是 SQL 單次響應時間發生了數量級的變化，從百分秒上升到了秒級，邏輯讀也有上升，時間在 CPUtime 上花費較少，較多發生到了 Application 型別等待和 Cluster 型別等待上。
-- 另外 ROWS_PROC 即處理行數，逐步呈上升趨勢，看起來是應用端發生了堵塞，每次update的行數變大了
select a.snap_id,a.instance_number inst,to_char(b.end_interval_time,'yyyymmdd hh24:mi:ss') end_time,
a.version_count v_cnt,a.plan_hash_value plan_hash,
a.executions_delta exec,
round(a.elapsed_time_delta/decode(a.elapsed_time_delta,0,1,a.elapsed_time_delta)/1000000,6) elap,
round(a.cpu_time_delta/decode(a.executions_delta,0,1,a.elapsed_time_delta)/1000000,6) cput,
round(a.buffer_gets_delta/decode(a.executions_delta,0,1,a.executions_delta)) bget,
round(a.rows_processed_delta/decode(a.executions_delta,0,1,a.executions_delta),3)rows_proc,
round(a.apwait_delta/decode(a.executions_delta,0,1,a.executions_delta)) apwait,
round(a.clwait_delta/decode(a.executions_delta,0,1,a.executions_delta)) clwait,
round(a.ccwait_delta/decode(a.executions_delta,0,1,a.executions_delta)) ccwait
 from sys.wrh$_sqlstat a,sys.wrm$_snapshot b
 where a.dbid=b.dbid
 and a.snap_id=b.snap_id
 and a.instance_number=b.instance_number
 and a.dbid=(select dbid from v$database)
 and a.sql_id='2qrkr5gkgd37m'
 order by 4,1,3 desc;
 

在一些事務頻繁，併發較高的環境下，為了儘可能減少 TX - row lock contention 等待事件的發生，應當從應用設計到資料庫多個層面進行考慮。

應用層面：

1、約束通常是為了保證資料完整性，在併發場景下，應充分考慮事務進行的邏輯順序，避免多個會話事務交叉進行，觸發約束衝突在事務級別發生競爭；

2、要提高併發效率，應當儘可能拆分大事務為小事務，提高 tx enqueue 鎖的獲取釋放速度；

3、如果要使用悲觀鎖（for update），應儘可能減少鎖定的行範圍；

資料庫層面：

1、在 dml 頻繁的表上建立適當的索引，提高 SQL 執行的效率，減少 tx enqueue 鎖持有的時間；避免全表掃描這種，容易造成 IO 開銷巨大，熱塊競爭，會話堆積的訪問方式。

2、在 dml 頻繁的表上不應使用點陣圖索引；

3、對於 dml 頻繁的表，不應使用 IOT 表，物化檢視等；

4、RAC 環境下，對於批次的 dml 操作，儘可能固定在單一節點，儘量降低網路開銷、叢集競爭、一致性塊獲取和日誌刷盤等帶來的影響。

Eygle 最後補充兩點：

1. 易欣在分享的內容中用到了 AWR 對比報告，這個報告非常實用，大家如果沒有用過，可以認真研究一下。用 $ORACLE_HOME/rdbms/admin/awrddrpt.sql 可以生成。

2. 在 RAC 環境下，易欣講到的第3個案例，由於鎖競爭帶來的複雜性會極具放大。Cache Fusion 的機制更復雜。我把一個非常精彩的文件分享給大家吧，讀懂這個文件，大家對於 RAC 和 LOCK 的理解一定會更上一層樓。易欣引用的一個圖就是來自這個文件。

Understanding Oracle RAC Internals - The Cache Fusion Edition，這個文件非常精彩，Markus 是 RAC 的產品經理，大家一定有時間認真讀一下：

killdb關於 enq: TX - row lock contention的總結

1. 其原因一般有如下幾種:
1) 表上存在主鍵或唯一性約束,多個會話操作同一條記錄

2) 表存在主外來鍵讀情況,主表不提交,子表那麼必須進行等待.

3) 表上存在點陣圖Index,這跟uniqeue index中存在重複值是一樣的道理,其中一個會話操作，其他會話必須等待.

4) 表進行自我外來鍵關聯,前面的事務不提交，那麼會導致後面的會話一直等待.

2. 對於網上說的enq: TX – row lock contention也有可能是在等待index block分裂的情況，我沒有進行測試，   從理論上來講，如果是在等待index block分裂，那麼應該還伴有enq: TX – index contention等待事件產生.

3. 對於enq: TX – row lock contention,透過v$session檢視查詢時，等待會話帶lock mode通常為4，而blocker   會話帶lock mode通常為6，並且一般查詢blocker會話的sql_id都為空。這是正常現象，v$session顯示是當前狀態,   而非歷史資料.

參考文獻：
http://mp.weixin.qq.com/s?__biz=MjM5MzExMTU2OQ==&mid=2650603515&idx=1&sn=275956ad38d26168e44027336644e5a0&scene=23&srcid=0711qxhIykeqO278x7VZFx5k#rd
%e5%85%b3%e4%ba%8eenq-tx-row-lock-contention%e7%9a%84%e6%b5%8b%e8%af%95%e5%92%8c%e6%a1%88%e4%be%8b%e5%88%86%e6%9e%90.html

http://blog.csdn.net/songyang_oracle/article/details/6433753