事务队列等待（Tx Enqueue）深入分析——唯一性约束
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
唯一性约束是为了保证某个字段或者某一组字段的每一条记录的数据在表中只村子唯一一条的约束。当向表中插入一条记录（或修改记录）时，如果存在唯一性约束，就需要先检查表中是否已经存在该数据，如果不存在，数据才允许插入。而这一检查过程实际上就是对已有数据的一次查询，因此，每一个唯一性约束都会在表中建立一个对应的唯一索引（Unique Index）。而如果一个事务在插入（或修改）一条数据时，新数据已经在另外一个事务中被插入、或者已有数据在另外一个事务中被删除，相应索引数据记录被加上共享锁，但事务并未结束，此时当前事务就需要进入队列等待另外事务结束，并记录"enq: TX - Row lock contention"事件。
SQL代码
- HELLODBA.COM>alter table TX_TEST_UNIQUE
- 2 add constraint TX_TEST_UNIQUE_PK primary key (A);
- Table altered.
- --Session 1
- HELLODBA.COM>insert into TX_TEST_UNIQUE(a,b) values(3, 'CCC');
- 1 row created.
- --Session 2
- HELLODBA.COM>var v_id number
- HELLODBA.COM>exec :v_id := 3;
- PL/SQL procedure successfully completed.
- HELLODBA.COM>insert into TX_TEST_UNIQUE(a,b) values(:v_id, 'CCC');
- Session 2进入等待队列。
等待队列分析
查看锁的请求模式：
SQL代码
- HELLODBA.COM>select WAITING_SESSION, HOLDING_SESSION, MODE_HELD, MODE_REQUESTED from dba_waiters where lock_type='Transaction';
- WAITING_SESSION HOLDING_SESSION MODE_HELD MODE_REQUESTED
- --------------- --------------- ------------- --------------------------
- 307 310 Exclusive Share
可以看到请求模式为共享。但是，有多种情况在请求共享锁会进入等待队列，需要确定是哪种情况还需要更多信息。10g中，先看被阻塞会话记录的是哪一种等待事件：
SQL代码
- HELLODBA.COM>select s.sid, s.event, s.row_wait_obj#, o.object_name
- 2 from v$session s, v$enqueue_lock l, dba_objects o
- 3 where l.sid = s.sid
- 4 and s.row_wait_obj# = o.object_id(+)
- 5 and s.sid =307;
- SID EVENT ROW_WAIT_OBJ# OBJECT_NAME
- ---------- -------------------------------- -------------- ----------------
- 307 enq: TX - row lock contention -1
其事件为“enq: TX - row lock contention”。在10g中，共享请求发出这一事件的情形有三种：
- 唯一性约束；
- BITMAP索引；
- 外键约束。
要确定是哪种情形造成的等待，我们要找到被阻塞的语句是什么：
SQL代码
- HELLODBA.COM>select s.sid, s.event, q.sql_text
- 2 from v$session s, v$sqlarea q
- 3 where s.sql_address = q.address(+)
- 4 and s.sql_hash_value = q.hash_value(+)
- 5 and s.sid = 307;
- SID EVENT SQL_TEXT
- ---------- ------------------------------ -------------------------------------------------
- 307 enq: TX - row lock contention insert into TX_TEST_UNIQUE(a,b) values(:v_id, 'CCC')
其请求的操作是向表TX_TEST_UNIQUE插入数据（DELETE操作是不会等待唯一性约束的锁的）。同时，看到阻塞会话中加锁的对象：
SQL代码
- HELLODBA.COM>select o.owner, o.object_name from V$LOCKED_OBJECT l, dba_objects o where l.object_id=o.object_id(+) and l.session_id = 310;
- OWNER OBJECT_NAME
- ---------- ----------------
- DEMO TX_TEST_UNIQUE
可以查询该表的信息做一些排除。
是否存在主外键约束：
SQL代码
- HELLODBA.COM>select 'Refer to' as type, p.owner, p.table_name, c.constraint_name, c.status, c.deferrable, c.deferred from dba_constraints c, dba_constraints p
- 2 where c.owner = 'DEMO' and c.table_name = 'TX_TEST_UNIQUE' and c.constraint_type = 'R' and c.r_owner=p.owner and c.r_constraint_name=p.constraint_name
- 3 union
- 4 select 'Be refered' as type, c.owner, c.table_name, c.constraint_name, c.status, c.deferrable, c.deferred from dba_constraints c, dba_constraints p
- 5 where p.owner = 'DEMO' and p.table_name = 'TX_TEST_UNIQUE' and c.constraint_type = 'R' and c.r_owner=p.owner and c.r_constraint_name=p.constraint_name;
- no rows selected
是否存在BITMAP索引：
SQL代码
- HELLODBA.COM>select index_name, status from dba_indexes where owner = 'DEMO' and table_name = 'TX_TEST_UNIQUE' and index_type='BITMAP';
- no rows selected
是否存在唯一索引：
SQL代码
- HELLODBA.COM>select table_name, constraint_name, status, constraint_type from dba_constraints where constraint_type in ('U', 'P') and owner = 'DEMO' and table_name = 'TX_TEST_UNIQUE';
- TABLE_NAME CONSTRAINT_NAME STATUS C
- ------------------------------ ------------------------------ -------- -
- TX_TEST_UNIQUE TX_TEST_UNIQUE_PK ENABLED P
获取到这些索引、约束信息后，再回头看被阻塞的语句，其修改的数据字段是否是在相关索引、键中，可以继续排除一些可能。
经过以上排除后，如果还存在多种可能性。则需要通过代码逻辑分析找出锁的原因。找到可能导致等待的语句：
SQL代码
- HELLODBA.COM>select w.holding_session,
- 2 s1.username holding_user,
- 3 q1.sql_text holding_sql
- 4 from dba_waiters w,
- 5 v$session s1,
- 6 v$open_cursor q1,
- 7 v$locked_object l1,
- 8 dba_objects o1
- 9 where w.holding_session = s1.sid
- 10 and s1.sid = q1.sid(+)
- 11 and l1.session_id = s1.sid
- 12 and l1.object_id = o1.object_id
- 13 and (upper(q1.sql_text) like '%DELETE%' or upper(q1.sql_text) like '%UPDATE%' or upper(q1.sql_text) like '%INSERT%')
- 14 and upper(q1.sql_text) like '%'||o1.object_name||'%'
- 15 and s1.sid = 310;
- HOLDING_SESSION HOLDING_USER HOLDING_SQL
- --------------- ------------- -----------------------------------------------
- 310 DEMO insert into TX_TEST_UNIQUE(a,b) values(3, 'CCC')
要注意的是，语句也可能会已经被从library cache中清除了，上述查询的结果可能不是我们需要的，此时，如果需要找回这些语句，就需要借助log miner了。对redo log挖掘后，查询事务中执行的语句：
SQL代码
- HELLODBA.COM>begin
- 2 dbms_logmnr.add_logfile(LogFileName=>'C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\ONLINELOG\O1_MF_3_1R3145T0_.LOG', Options=>dbms_logmnr.addfile);
- 3 dbms_logmnr.start_logmnr();
- 4 end;
- 5 /
- PL/SQL procedure successfully completed.
- HELLODBA.COM>select c.scn, o.object_name, c.operation, c.sql_redo
- 2 from v$logmnr_contents c, v$transaction t, v$session s, dba_objects o
- 3 where c.scn >= 2979920105 and c.session#=310
- 4 and t.addr = s.taddr
- 5 and c.data_obj#=o.object_id(+)
- 6 and t.xidusn=c.xidusn
- 7 and t.xidslot=c.xidslt
- 8 and t.xidsqn=c.xidsqn;
- SCN OBJECT_NAME OPERATION SQL_REDO
- ---------- -------------- --------- ------------------------------------------------------
- 2979920105 TX_TEST_UNIQUE INSERT insert into "UNKNOWN"."OBJ# 198349"("COL 1","COL 2") values (HEXTORAW('c104'),HEXTORAW('434343'));
- 2979920105 0 START set transaction read write;
通过对持锁事务和等待事务语句的分析，可以确定导致等待的原因，以及导致等待的对象。
死锁分析
首先确认死锁请求锁的类型：
SQL代码
- Deadlock graph:
- ---------Blocker(s)-------- ---------Waiter(s)---------
- Resource Name process session holds waits process session holds waits
- TX-000a001c-00019c08 21 307 X 18 310 S
- TX-00020007-0001fdd2 18 310 X 21 307 S
可见是请求共享锁。然后看请求会话的等待事件：
SQL代码
- last wait for 'enq: TX - row lock contention' blocking sess=0x1EDDBE14 seq=1786 wait_time=3000022 seconds since wait started=9
- name|mode=54580004, usn<<16 | slot=20007, sequence=1fdd2
是索引或约束引起的，说明需要进行应用分析。找到等待语句：
SQL代码
- Current SQL statement for this session:
- insert into TX_TEST_UNIQUE(a,b) values(:v_id, 'CCC')
- ...
- Current SQL Statement:
- insert into TX_TEST_UNIQUE(a,b) values(6, 'CCC')
找到语句后，再结合对关联对象上的索引、约束分析，不难定位到造成死锁的逻辑。
解决方法
与row lock contention相关的等待都是由于应用引起的，其解决方法就是调整应用、避免交替约束的出现。
--- Fuyuncat TBC ---