關於主外來鍵關係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相容。