联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户和我说:他在含主外键的表中实验发现,在主表数据未提交,然后在外键表插入该数据数据时,出现外键表hang住现象.我开始以为是不同的会话,根据oracle数据库的一致性原则,应该新会话在外键表中不能知道这个记录的存在,直接报错.
可是我实验结果证明:外键表会被阻塞.分析原因如下:
模拟环境
SQL> create table t_p(id number primary key,name varchar2(100)); Table created. SQL> create table t_f(fid number primary key,pid number, foreign key(pid) references t_p(id)); Table created. --会话1 SQL> insert into t_p values(1,'xifenfei'); 1 row created. SQL> commit; Commit complete. --会话2 SQL> insert into t_f values(1,1); 1 row created. SQL> commit; --会话1 SQL> insert into t_p values(2,'XIFENFEI'); 1 row created. --会话2 SQL> insert into t_f values(2,2); --hang住
通过实验发现,当主键数据没有提交,然后在外键表中插入该数据外键数据时,该条记录会处于hang住状态(等待),那是什么原因导致了这个等待呢?对会话2做一个10046的trace,发现如下
*** 2012-05-17 17:25:41.757 WAIT #3065187488: nam='enq: TX - row lock contention' ela= 27002895 name|mode=1415053316 usn<<16 | slot=262151 sequence=588 obj#=-1 tim=1337246741756917 EXEC #3065187488:c=4000,e=27004456,p=0,cr=2,cu=14,mis=0,r=0,dep=0,og=1, plh=0,tim=1337246741757690 ERROR #3065187488:err=1013 tim=1337246741757751 STAT #3065187488 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=0 pr=0 pw=0 time=12 us)' WAIT #3065187488: nam='SQL*Net break/reset to client' ela= 581 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1337246741782587 WAIT #3065187488: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1337246741782668
通过这个trace发现,是因为TX锁导致了外键表上的插入操作被阻塞.出现该问题的原因
有两种可能:1.两次插入(主键表和外键表分别插入)在主键表上有不兼容锁;2.外键表上有不兼容性锁.
使用oradebug跟踪会话
oradebug setmypid --EVENT 10704跟踪锁的使用情况 oradebug EVENT 10704 trace name context forever,level 10 --插入数据操作 oradebug EVENT 10704 trace name context off oradebug TRACEFILE_NAME
跟踪主键表插入数据
*** 2012-05-17 19:05:52.410 ksqgtl *** TM-00012892-00000000 mode=3 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x343c0e54, ktcdix=2147483647, topxcb=0x343c0e54 ktcipt(topxcb)=0x0 *** 2012-05-17 19:05:52.411 ksucti: init txn DID from session DID ksqgtl: ksqlkdid: 0001-0013-0000000F *** 2012-05-17 19:05:52.429 *** ksudidTrace: ksqgtl ktcmydid(): 0001-0013-0000000F ksusesdi: 0000-0000-00000000 ksusetxn: 0001-0013-0000000F ksqgtl: RETURNS 0 *** 2012-05-17 19:05:52.430 ksqgtl *** TM-00012894-00000000 mode=3 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x343c0e54, ktcdix=2147483647, topxcb=0x343c0e54 ktcipt(topxcb)=0x0 *** 2012-05-17 19:05:52.430 ksucti: init session DID from txn DID: ksqgtl: ksqlkdid: 0001-0013-0000000F *** 2012-05-17 19:05:52.430 *** ksudidTrace: ksqgtl ktcmydid(): 0001-0013-0000000F ksusesdi: 0000-0000-00000000 ksusetxn: 0001-0013-0000000F ksqgtl: RETURNS 0 *** 2012-05-17 19:05:52.431 ksqgtl *** TX-00050019-00000307 mode=6 flags=0x401 timeout=0 *** ksqgtl: xcb=0x343c0e54, ktcdix=2147483647, topxcb=0x343c0e54 ktcipt(topxcb)=0x0 *** 2012-05-17 19:05:52.431 ksucti: init session DID from txn DID: ksqgtl: ksqlkdid: 0001-0013-0000000F *** 2012-05-17 19:05:52.431 *** ksudidTrace: ksqgtl ktcmydid(): 0001-0013-0000000F ksusesdi: 0000-0000-00000000 ksusetxn: 0001-0013-0000000F ksqgtl: RETURNS 0 SQL> SELECT TO_NUMBER(12892,'xxxxxxx') from dual; TO_NUMBER(12892,'XXXXXXX') -------------------------- 75922 SQL> SELECT TO_NUMBER(12894,'xxxxxxx') from dual; TO_NUMBER(12894,'XXXXXXX') -------------------------- 75924 SQL> select object_name from dba_objects where object_id in(75922,75924); OBJECT_NAM ---------- T_P T_F
通过锁使用情况跟踪可以知道,在主键表插入一条记录时,先在主键表获得TM锁,然后外键表获得TM锁,最后主键表获得TX MODE=6的锁。
跟踪外键表插入数据
*** 2012-05-17 19:49:24.912 ksqgtl *** TM-00012892-00000000 mode=3 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x343a45e8, ktcdix=2147483647, topxcb=0x343a45e8 ktcipt(topxcb)=0x0 *** 2012-05-17 19:49:24.912 ksucti: init txn DID from session DID ksqgtl: ksqlkdid: 0001-0015-00000064 *** 2012-05-17 19:49:24.913 *** ksudidTrace: ksqgtl ktcmydid(): 0001-0015-00000064 ksusesdi: 0000-0000-00000000 ksusetxn: 0001-0015-00000064 ksqgtl: RETURNS 0 *** 2012-05-17 19:49:24.913 ksqgtl *** TM-00012894-00000000 mode=3 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x343a45e8, ktcdix=2147483647, topxcb=0x343a45e8 ktcipt(topxcb)=0x0 *** 2012-05-17 19:49:24.913 ksucti: init session DID from txn DID: ksqgtl: ksqlkdid: 0001-0015-00000064 *** 2012-05-17 19:49:24.913 *** ksudidTrace: ksqgtl ktcmydid(): 0001-0015-00000064 ksusesdi: 0000-0000-00000000 ksusetxn: 0001-0015-00000064 ksqgtl: RETURNS 0 *** 2012-05-17 19:49:24.913 ksqgtl *** TX-0002001f-0000034a mode=6 flags=0x401 timeout=0 *** ksqgtl: xcb=0x343a45e8, ktcdix=2147483647, topxcb=0x343a45e8 ktcipt(topxcb)=0x0 *** 2012-05-17 19:49:24.913 ksucti: init session DID from txn DID: ksqgtl: ksqlkdid: 0001-0015-00000064 *** 2012-05-17 19:49:24.914 *** ksudidTrace: ksqgtl ktcmydid(): 0001-0015-00000064 ksusesdi: 0000-0000-00000000 ksusetxn: 0001-0015-00000064 ksqgtl: RETURNS 0 *** 2012-05-17 19:49:24.914 ksqgtl *** TX-00050019-00000307 mode=4 flags=0x10021 timeout=21474836 *** ksqgtl: xcb=0x343a45e8, ktcdix=2147483647, topxcb=0x343a45e8 ktcipt(topxcb)=0x0 *** 2012-05-17 19:49:24.914 ksucti: init session DID from txn DID: ksqgtl: ksqlkdid: 0001-0015-00000064 *** 2012-05-17 19:49:24.914 *** ksudidTrace: ksqgtl ktcmydid(): 0001-0015-00000064 ksusesdi: 0000-0000-00000000 ksusetxn: 0001-0015-00000064 *** 2012-05-17 19:49:24.914 ksqcmi: TX,50019,307 mode=4 timeout=21474836
从这里可以发现:先在主键表和外键表上加上TM锁,然后外键表获得TX MODE=6的锁(这边成功,因为该表上未有其他级别不兼容锁),再需要在主键表上获得TX MODE=4(表结构共享锁+所有记录共享锁),但是这个时候,发现该锁上已经在主键表插入数据未提交的时候,已经含有了TX MODE=6的锁,从而使得TX MODE=4无法获得,从而使得外键表插入数据处于阻塞状态.