事务队列等待（Tx Enqueue）深入分析——外键约束
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
如果2个表之间建立了主外键关系，那么对它们的数据操作就会受到外键约束，以保证数据完整性：子表插入数据时，外键键值在主表中必须存在；主表删除时，子表中必须已经没有该键值的数据。如果进行这样的数据操作时，相应数据约束在另外事务里面已经满足（或正导致违反约束），但事务未完成，那么这样的数据操作的事务就会进入等待队列：
SQL代码
- HELLODBA.COM>create table tx_parent (p_id number, a varchar2(20));
- Table created.
- HELLODBA.COM>alter table tx_parent
- 2 add constraint tx_parent_pk primary key (p_id);
- Table altered.
- HELLODBA.COM>create table tx_child (c_id number, p_id number, b varchar2(20));
- Table created.
- HELLODBA.COM>alter table tx_child
- 2 add constraint tx_child_fk foreign key (p_id) references tx_parent (p_id);
- Table altered.
- HELLODBA.COM>create index tx_child_idx on tx_child (p_id);
- Index created.
- --session 1
- HELLODBA.COM>insert into tx_parent values (1, 'AAA');
- 1 row created.
- --session 2
- HELLODBA.COM>insert into tx_child values(1, 1, 'BBB');
等待队列分析
这种情况下的锁也是共享锁，等待事件为"enq: TX - row lock contention":
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
- --------------- --------------- ------------- --------------------------
- 304 300 Exclusive Share
- 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 =304;
- SID EVENT ROW_WAIT_OBJ# OBJECT_NAME
- ---------- -------------------------------- -------------- ----------------
- 300 enq: TX - row lock contention 53844 ERROR_INFO
这种等待也是因为应用引起的，同样，需要排除其它可能（过程可以参考唯一性约束等待分析过程）：
- 找到被阻塞的语句；
- 找到锁对象表；
- 找出对应表上的主外键约束、唯一约束和Bitmap索引。
如果仍然无法定位，则需要找到阻塞事务和被阻塞事务的逻辑代码进行分析。
死锁分析
对这一类等待导致的死锁的分析，也是要先确认锁的类型和等待事件类型：
SQL代码
- Deadlock graph:
- ---------Blocker(s)-------- ---------Waiter(s)---------
- Resource Name process session holds waits process session holds waits
- TX-00010025-00017965 29 304 X 13 300 S
- TX-00080019-000213b0 13 300 X 29 304 S
- ...
- last wait for 'enq: TX - row lock contention' blocking sess=0x1EDD0244 seq=167 wait_time=2999826 seconds since wait started=15
- name|mode=54580004, usn<<16 | slot=80019, sequence=213b0
可以知道这是由于应用引起的死锁，然后获取相关语句进行逻辑分析：
SQL代码
- Current SQL statement for this session:
- insert into tx_child values(2, 2, 'aaw')
- ...
- Current SQL Statement:
- delete from tx_parent where p_id=1
对于外键约束引起的锁和死锁问题，要注意的是，在外键是否有索引，不仅会影响约束检查的性能，而且会导致关联表上被加载TM锁，增加死锁风险。
此外，还需要注意的是，外键的DELETE CASCADE（SET NULL）属性，如果设置了该属性，主表数据删除时，会自动执行一条对子表对于键值数据的删除语句。
解决方法
如果锁或者死锁是由于外键约束TX锁引起的，则解决方法就是需要调整应用，消除死锁陷阱，而如果是TM锁引起的，则需要在外键上建立索引。