关于主外键关系DML父表和DML子表加锁方式

原创 转载请注明出处

当存在主外键关系时候不管是更新主表还是子表都会出现对应的LOCK，解释如下：

实验

SQL> create table test1
  2  (id int);

SQL> alter table TEST1
  2    add constraint TEST_PK primary key (ID);

 

SQL> insert into test1
  2  values(10);

1 row inserted

SQL> commit;

Commit complete

SQL> create table test2
  2  (id int);

Table created

SQL> alter table TEST2
  2    add constraint test_fk foreign key (ID)
  3    references test1 (ID);

Table altered

SQL> INSERT INTO TEST2
  2  VALUES(10);

1 row inserted

SQL> COMMIT;

Commit complete

然后开一个会话进行删除TEST2。出现LOCK模式如下
SQL> SELECT LOCK_TYPE,MODE_HELD,LOCK_ID1 FROM DBA_LOCKS WHERE SESSION_ID=150;

LOCK_TYPE                  MODE_HELD                                LOCK_ID1
-------------------------- ---------------------------------------- ----------------------------------------
Transaction                Exclusive                                327689
DML                        Row-X (SX)                               51297
DML                        Row-S (SS)                               51295

这里LOCK_ID1是对应的OBJECT_ID我的子表是51297父表是51295  ，子表加了表共享行独占，父表加了表共享行共享。

为了测试主表这样的问题，必须引入大量的数据

declare
  i number(10);
 begin
  for i in 11..10000000 loop
  insert into test1
  values( i);
  end loop;
 end ;

这样可以等待很长时间

锁的模式如下

SQL> select LOCK_TYPE,MODE_HELD,LOCK_ID1 from dba_locks where session_id=148;

LOCK_TYPE                  MODE_HELD                                LOCK_ID1
-------------------------- ---------------------------------------- ----------------------------------------
Transaction                Exclusive                                262169
DML                        Row-S (SS)                               51297
DML                        Row-X (SX)                               51295
51295父表 51297子表

也许要问SS 和SX的区别区别在于行的独占和共享，如

DML                        Row-S (SS)                               51297
DML                        Row-X (SX)                               51295

这样的模式我们队51295进行FOR UPDATE是不行的行级X和S是不兼容的

但是对51297进行FOR update是可以因为S和S兼容。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-667534/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-667534/