最近發生的幾起 enq: TX - row lock contention 等待事件很怪，透過 blocking session id 檢視，不是語句是 select，就是會話是 inactive 的。

實驗

準備工作

1. 進入 hr 使用者，同時檢視會話 id，下面會稱為 會話 38
   

   SQL> select userenv('sid') from dual;
   USERENV('SID')
   --------------
   	    3

2. 建立測試表

   create table emp_bak as select * from employees

3. 建立被鎖會話，同時檢視會話 id，下面會稱為 會話 28

   SQL> select userenv('sid') from dual;
   USERENV('SID')
   --------------
   	    28

測試

1. 會話 38 產生鎖操作，注意，此處不進行提交操作，且操作完不進行 exit 操作

   SQL> SELECT employee_id, first_name, last_name, salary
     2    FROM emp_bak
     3   WHERE employee_id = 166;
   EMPLOYEE_ID FIRST_NAME				     LAST_NAME						    SALARY
   ----------- ---------------------------------------- -------------------------------------------------- ----------
   	166 Sundar				     Ande						      6400
   SQL> update emp_bak
     2     set salary = salary + 100
     3   where employee_id = 166;
   1 row updated

2. 會話 28，為了區分操作語句，此處我們執行 delete 操作，此時會出現 hang，暫且不去管它

   SQL> delete from emp_bak
     2   where employee_id = 166

3. 此時，我們新啟會話查一下鎖情況

   col event for a30
   col username for a8
   col process for a7
   col machine for a7
   col program for a30
   col sql for a80
   SELECT a.sid,
          b.status,
          b.event,
          b.USERNAME,
          b.PROCESS,
          b.MACHINE,
          b.program,
          CASE
            WHEN rawtohex(b.SQL_ADDRESS) <> '00' THEN
             'CURR'
            ELSE
             'PREV'
          END STAT,
          c.sql_text "SQL"
     FROM v$lock a, v$session b, v$sql c
    WHERE (a.id1, a.id2) IN (SELECT ID1, ID2
                               FROM gv$lock
                              WHERE TYPE = 'TX'
                                AND request > 0)
      AND a.sid = b.sid
      AND CASE
            WHEN rawtohex(b.SQL_ADDRESS) <> '00' THEN
             b.SQL_ADDRESS
            ELSE
             b.PREV_SQL_ADDR
          END = c.address
      AND CASE
            WHEN b.SQL_HASH_VALUE > 0 THEN
             b.SQL_HASH_VALUE
            ELSE
             b.PREV_HASH_VALUE
   END = c.hash_value;

   由於長時間未對資料庫進行操作，所以會話狀態為 INACTIVE 狀態，鎖的語句為 update

   

4. 那麼此時，我們在會話 38 上執行 select 語句，查詢的狀態是怎樣的呢？

   SQL> SELECT employee_id, first_name, last_name, salary
     2    FROM emp_bak
     3   WHERE employee_id = 166;
   EMPLOYEE_ID FIRST_NAME				     LAST_NAME						    SALARY
   ----------- ---------------------------------------- -------------------------------------------------- ----------
   	166 Sundar				     Ande						      6500

   
   

5. 我們此時可以再關聯 v$transaction，來檢視具體資訊
   

   SELECT a.sid,
          b.status,
          b.event,
          b.USERNAME,
          b.PROCESS,
          b.MACHINE,
          b.program,
          CASE
            WHEN rawtohex(b.SQL_ADDRESS) <> '00' THEN
             'CURR'
            ELSE
             'PREV'
          END STAT,
          c.sql_text "SQL",
          d.start_time,
          d.status,
          d.xid,
          d.USED_UBLK,
          d.USED_UREC
     FROM v$lock a, v$session b, v$sql c, v$transaction d
    WHERE (a.id1, a.id2) IN (SELECT ID1, ID2
                               FROM gv$lock
                              WHERE TYPE = 'TX'
                                AND request > 0)
      AND a.sid = b.sid
      AND CASE
            WHEN rawtohex(b.SQL_ADDRESS) <> '00' THEN
             b.SQL_ADDRESS
            ELSE
             b.PREV_SQL_ADDR
          END = c.address
      AND CASE
            WHEN b.SQL_HASH_VALUE > 0 THEN
             b.SQL_HASH_VALUE
            ELSE
             b.PREV_HASH_VALUE
          END = c.hash_value
      AND rawtohex(d.addr(+)) = b.taddr;

   

結論

1. blocking session id 記錄的是誰鎖的自己

2. sqltext 記錄的是當前執行的語句，而並非是被哪句鎖住了

3. inactive 僅表示處於此狀態的會話沒有正在執行，但由於之前執行的語句，依然會產生鎖

4. v$transaction 可以獲取事務的狀態以及進度，重複查詢 USED_UBLK、USED_UREC 這兩個值，可以看到變化，可以估計事務的進度，尤其是長時間的回滾操作，當這兩個值為0，回滾也就完成了。