A foreign bond inquiry

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Familiar with the Oracle people know, if the foreign key column does not create index, often causes the table locking problem. The following examples to explore the foreign key is how to influence the lock.
First look at the database version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Create table P_TAB (ID NUMBER) the main table, F_TAB (ID number) from the table, from the table does not exist on the index.
The main table insert
To insert a record from the table, not submitted

SQL> insert into f_tab values(1);

The 1 line has been created. 
Check the lock case:

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TX  X	    458775	 1494	       0
       243 TM  SX	     84541	    0	       0
To insert records from the table, will in the main table and the table while adding TM:SX lock.
Insert a record into the main table, uncommitted

SQL> insert into p_tab values(3);

The 1 line has been created. 
Check the lock case:

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 TX  X	    131098	 1686	       0
	 4 AE  S	       100	    0	       0
	 4 TM  SX	     84543	    0	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TX  X	    458775	 1494	       0
       243 TM  SX	     84541	    0	       0
Insert a record into the main table, will in the main table and the table to add the TM:SX lock, from table insert (update, delete) add operation does not block the main table.
The operation of all rollback, testing the main table delete.
In the insert a record in a table

SQL> insert into f_tab values(1);

The 1 line has been created. 
Check the lock case

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
       243 TO  SX	     68064	    1	       0
       243 TX  X	    655385	11621	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TM  SX	     84541	    0	       0
Delete a non-existent record in the primary table

SQL> delete from p_tab where id=5;
At this point the user process is blocked, Check the lock usage:

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
	 4 TM	    S	     84543	    0	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 TX  X	    655385	11621	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       1
       243 TM  SX	     84541	    0	       0

From the table after the submission

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0

Thus, the delete operation on the main table, will add TM:S from the table lock, so it will be blocked from the DML statement table, also during operation would block from the DML statement table, delete operation is completed, the main table is from table TM lock, waiting to commit or rollback. For the main table update operations, will also add TM:S from the table lock, and after the completion of the operation on the release of TM from the table lock, waiting for the rollback or submit.
The main table DML Before the operation from the table lock After the operation the table lock Insert tm:sx tm:sx delete tm:s update tm:s no no
The following look, if the table foreign key field contains the index case:
At first, insert a record from the table, not to commit

SQL> insert into f_tab values(1);

The 1 line has been created. 
The lock case

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
       243 TO  SX	     68064	    1	       0
       243 TX  X	    196619	 1657	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84541	    0	       0
       243 TM  SX	     84543	    0	       0
Insert a record in the primary table:

SQL> insert into p_tab values(3);
The lock case:

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
	 4 TX  X	    655371	11652	       0
	 4 TM  SX	     84541	    0	       0
	 4 TM  SX	     84543	    0	       0
       243 TO  SX	     68064	    1	       0
       243 TX  X	    196619	 1657	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84541	    0	       0
       243 TM  SX	     84543	    0	       0
The same situation and index did not create a situation.
In the insert a record from the table, and delete a non-existent record in the primary table

SQL> delete from p_tab where id=3;

The 0 row has been deleted. 
The main table is not blocked, viewing usage lock:

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 AE  S	       100	    0	       0
	 4 TM  SX	     84543	    0	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TX  X	    327710	 1664	       0
       243 TM  SX	     84541	    0	       0
In the main table, delete one in existence from the table, but not by the DML operating records

SQL> delete from p_tab where id=1;
delete from p_tab where id=1
*
The first line error:
ORA-02292: violated the integrity constraints (SCOTT.FTAB) - sub record has been found
In the main table, delete one in existence from the table, and is removed from the table inserted record
From the table:

SQL> insert into f_tab values(2);

The 1 line has been created. 

SQL> select * from f_tab;

	ID
----------
	 2
	 2
	 1
The main table:

SQL> delete from p_tab where id=2;
The lock case:

SQL> /

       SID TY HOLD WANT        ID1	  ID2	   BLOCK
---------- -- ---- ---- ---------- ---------- ----------
	 4 TO  SX	     68064	    1	       0
	 4 TX  X	    655388	11653	       0
	 4 AE  S	       100	    0	       0
	 4 TM  SX	     84543	    0	       0
	 4 TX	    S	    327710	 1664	       0
	 4 TM  SX	     84541	    0	       0
       243 TO  SX	     68064	    1	       0
       243 AE  S	       100	    0	       0
       243 TM  SX	     84543	    0	       0
       243 TX  X	    327710	 1664	       1
       243 TM  SX	     84541	    0	       0
From here we can see that, for from the foreign key index, when the main table delete operation, will add TM:SM from the table lock, if you remove the primary table with DML operation records the corresponding foreign key, it will because the main table need to obtain the TX:S lock, blocked. After submitting a from table DML, if not in violation of the foreign key constraint is normal operation, and in the TM:Sx from the table to maintain lock, if illegal foreign key constraints, the operation fails. The update operation with the like rules;.
Summarized as follows:
The main table DML Before the operation from the table lock After the operation the table lock Insert tm:sx tm:sx update tm:sx TX:S tm:sx delete tm:sx corresponds to the foreign key corresponding foreign key TX:S tm:sx
Here, I think you must have understood why you need to add the index on the foreign key.
Of course, we are just part of a test, in other cases, such as delete on cascade, interested students can study.
From this example can be seen from the table, the operation will add TM:SX lock in the main table, the DDL statement may therefore blocking the main table.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Miriam at December 01, 2013 - 5:14 PM