最近发生的几起 enq: TX - row lock contention 等待事件很怪，通过 blocking session id 查看，不是语句是 select，就是会话是 inactive 的。

实验

准备工作

进入 hr 用户，同时查看会话 id，下面会称为 会话 38

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

创建测试表

create table emp_bak as select * from employees

创建被锁会话，同时查看会话 id，下面会称为 会话 28

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

测试

会话 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

会话 28，为了区分操作语句，此处我们执行 delete 操作，此时会出现 hang，暂且不去管它

SQL> delete from emp_bak
  2   where employee_id = 166

此时，我们新启会话查一下锁情况

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



那么此时，我们在会话 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



我们此时可以再关联 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;

结论

blocking session id 记录的是谁锁的自己

sqltext 记录的是当前执行的语句，而并非是被哪句锁住了

inactive 仅表示处于此状态的会话没有正在执行，但由于之前执行的语句，依然会产生锁

v$transaction 可以获取事务的状态以及进度，重复查询 USED_UBLK、USED_UREC 这两个值，可以看到变化，可以估计事务的进度，尤其是长时间的回滚操作，当这两个值为0，回滚也就完成了。