[20180423]关于闪回表与主外键约束.txt

最新推荐文章于 2025-09-13 19:07:06 发布

转载最新推荐文章于 2025-09-13 19:07:06 发布 · 66 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/605668

文章标签：

#数据库

本文通过多个测试案例详细探讨了在Oracle数据库中闪回表操作对主外键约束的影响，特别是在删除表和恢复过程中主外键约束的状态变化。

[20180423]关于闪回表与主外键约束.txt

--//上个星期做的测试,可以发现删除表在恢复主外键约束丢失.
--//链接:http://blog.itpub.net/267265/viewspace-2153199/
--//不过这个可以模拟一个另外的测试.

1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table p ( x int primary key );
create table c ( x references p );
insert into p values ( 1 );
insert into p values ( 2 );
commit;

SCOTT@book> insert into c values ( 3 );
insert into c values ( 3 )
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0011835) violated - parent key not found

2.测试1:

--//session 1:
SCOTT@book> insert into p values ( 3 );
1 row created.

--//session 2:
SCOTT@book> drop table c purge;
drop table c purge
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SCOTT@book> drop table c ;
drop table c
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
--//因为P表存在insert操作,而这个要锁定c表获取TM锁,这样删除是无法操作的:

SCOTT@book> @ &r/viewlock

   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90716      0          SCOTT TABLE      C                    No
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction Exclusive None       655387     21831                                             No
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90714      0          SCOTT TABLE      P                    No

3.测试2:
--//session 1:
SCOTT@book> commit ;
Commit complete.

--//注意不提交.
SCOTT@book> update p set x=x where x=2;
1 row updated.

--//session 2:
SCOTT@book> @ &r/viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction Exclusive None       655370     21844                                             No
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90714      0          SCOTT TABLE      P                    No

--//有点奇怪这个时候并没有锁C表.

SCOTT@book> insert into c values (2);
1 row created.

SCOTT@book> commit ;
Commit complete.

--//c表可以进行正常插入操作.

--//session 1:
SCOTT@book> commit ;
Commit complete.

SCOTT@book> delete from c ;
1 row deleted.

SCOTT@book> commit ;
Commit complete.

4.测试3:
--//session 1:
--//注意不提交.
SCOTT@book> @ &r/s
SCOTT@book(274,9)>

SCOTT@book> update p set x=x+2 where x=2;
1 row updated.

--//session 2:
SCOTT@book> @ &r/s
SCOTT@book(106,69)>
SCOTT@book> insert into c values (2);

--//挂起!!

SCOTT@book> @ &r/viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
   106         69 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90716      0          SCOTT TABLE      C                    No    0000000084C36170
   106         69 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90714      0          SCOTT TABLE      P                    No    0000000084C36170
   106         69 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction Exclusive None       589841     2823                                              No    0000000084C36170
   106         69 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction None       Share      655384     21828                                             No    0000000084C36170
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction Exclusive None       655384     21828                                             Yes
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90714      0          SCOTT TABLE      P                    No
6 rows selected.

--//偏题了,不再做主外键测试.

5.测试5:
--//session 1:
SCOTT@book(274,9)> select * from p;
         X
----------
         1
         2
         3

SCOTT@book(274,9)> select * from c;
no rows selected

SCOTT@book(274,9)> update p set x=x where x=2;
1 row updated.

--//session 2:
SCOTT@book(106,69)> @ &r/viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction Exclusive None       655377     21680                                             No
   274          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90714      0          SCOTT TABLE      P                    No

SCOTT@book(106,69)> drop table c purge;
drop table c purge
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

--//不能删除表C.
SCOTT@book(106,69)> drop table c ;
Table dropped.

--//这样实际上并没有真正删除,而是进入回收站.

SCOTT@book(106,69)> flashback table c to before drop ;
Flashback complete.

SCOTT@book(106,69)> insert into c values (1111);
1 row created.

SCOTT@book(106,69)> commit ;
Commit complete.

--//恢复后主外键已经破坏,可以插入异常数据.
--//可以看出一种特殊的情况就是drop table c ;时实际上主外键约束就已经删除了.