Oracle外键要建立索引的原理和实验(3)

对于TX这行记录，确实能够按照如下计算得到当前事务的回滚段编号、槽位号以及序列号。

SQL> select trunc(589843/65536) from dual; TRUNC(589843/65536) 9 SQL> select mod(589843,65536) from dual; MOD(589843,65536) 19 SQL> select XIDUSN,XIDSLOT,XIDSQN from V$TRANSACTION where XIDSQN=715; XIDUSN XIDSLOT XIDSQN 9 19 715

进一步，我们从处于hang的session 2，即执行删除主表操作的10046事件中还可以发现一些细节：

PARSING IN CURSOR #11135800 len=25 dep=0 uid=90 oct=7 lid=90 tim=1458405691531128 hv=2708121416 ad='526137f8' sqlid='9kjcfz6hqp9u8' delete from t1 where id=2 WAIT #11135800: nam='enq: TM - contention' ela= 4202458 name|mode=1414332420 object #=76830 table/partition=0 obj#=76830 tim=1458405695733683 PARSING IN CURSOR #11130048 len=68 dep=1 uid=0 oct=3 lid=0 tim=1458405695736681 hv=4142137182 ad='57d9d9f0' sqlid='cpbvjc7vf7zuy' select /*+ all_rows */ count(1) from "TEST"."T2" where "T1_ID" = :1 END OF STMT PARSE #11130048:c=2000,e=2221,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1458405695736680 STAT #11128324 id=2 cnt=1 pid=1 pos=1 obj=76830 op='TABLE ACCESS FULL T2 (cr=7 pr=0 pw=0 time=111 us cost=2 size=0 card=82)'

从这块可以得出两个结论：

(1) 此时删除主表记录时，会出现一个TM锁争用，需要说明的是，trc文件中这条信息是在session 1执行commit或rollback释放子表之后才写入的，我们看到session 1的执行，导致session 2执行需要请求T2表的TM锁时出现了等待。

(2) 删除主表记录的过程中，会根据外键字段检索子表记录，select /+ all_rows / count(1) from “TEST”.”T2” where “T1_ID” = :1，这就意味着，如果外键没有索引，则这条SQL语句会执行全表扫描，从后面的TABLE ACCESS FULL T2也证明了。

实验3

和实验2操作过程相同，只是session 2从delete操作换为了update主键操作：update t1 set id=3 where id=2

效果和实验2相同，session 2处于hang，且从10046事件看，和实验2还是相同：

PARSING IN CURSOR #11123668 len=29 dep=0 uid=90 oct=6 lid=90 tim=1458407400035758 hv=1728227981 ad='5278e0fc' sqlid='43bqtdxmh5and' 97 update t1 set id=3 where id=2 PARSING IN CURSOR #11109876 len=68 dep=1 uid=0 oct=3 lid=0 tim=1458407409907499 hv=4142137182 ad='57d9d9f0' sqlid='cpbvjc7vf7zuy' 105 select /*+ all_rows */ count(1) from "TEST"."T2" where "T1_ID" = :1

实验4

session 1删除子表id=1的记录：

SQL> delete from t2 where id = 1; 1 row deleted.

session 2更新主表id=2记录的非主键字段：

SQL> update t1 set name = 'c' where id = 2; 1 row updated.

没有出现hang，可以操作。

实验5

session 1插入子表新记录：

SQL> insert into t2 values(2, 1, 'c'); 1 row created.

session 2插入主表新记录：

SQL> insert into t1 values(3, 'c'); 1 row created.

没有出现hang，可以操作。

实验6

创建外键索引，看下效果，

SQL> create index idx_t2_id on t2(t1_id); Index created.

session 1删除子表id=1的记录：

SQL> delete from t2 where id = 1; 1 row deleted.

session 2删除主表id=2的记录：

SQL> delete from t1 where id = 2;

此时没有hang，

确实没有锁：

查看10046事件：

PARSING IN CURSOR #12183444 len=27 dep=0 uid=90 oct=7 lid=90 tim=1458479427981508 hv=3481522657 ad='57ded014' sqlid='87pqrfv7s7ng1' delete from t1 where id = 2 END OF STMT PARSE #12183444:c=1999,e=2469,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1636297587,tim=1458479427981505 EXEC #12183444:c=0,e=365,p=0,cr=1,cu=6,mis=0,r=1,dep=0,og=1,plh=1636297587,tim=1458479427982059 STAT #12183444 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE T1 (cr=1 pr=0 pw=0 time=298 us)' STAT #12183444 id=2 cnt=1 pid=1 pos=1 obj=76829 op='INDEX UNIQUE SCAN PK_T1 (cr=1 pr=0 pw=0 time=26 us cost=0 size=3 card=1)' WAIT #12183444: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1458479427983901

确实没有产生锁争用的现象。

总结：