Oracle：select 或 inactive 会话语句产生锁？

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

实验

准备工作

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

   1 2 3 4

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

2. 创建测试表

   1	create table emp_bak as select * from employees

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

   1 2 3 4

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

测试

1. 会话 38 产生锁操作，注意，此处不进行提交操作，且操作完不进行 exit 操作

   1 2 3 4 5 6 7 8 9 10

   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，暂且不去管它

   1 2	SQL> delete from emp_bak   2   where employee_id = 166

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

   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 语句，查询的状态是怎样的呢？

   1 2 3 4 5 6

   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，来查看具体信息

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

   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，回滚也就完成了。