In this article we will see how the locks are on formed
on the tables when we do not have an index on the foreign key colum
Example, we will see the following scenario.
1.
Let us create a primary table “P” with primary
key.
SQL> create table p (x int primary key);
Table created.
2.
Create a child table with column “x” referencing
to the parent table “P”
SQL> create table c (x references p);
Table created.
3.
Structure of the tables are as below.
SQL> desc p;
Name Null? Type
--------------------------------------
-------- --------------------------
X NOT NULL
NUMBER(38)
SQL> desc c;
Name Null? Type
--------------------------------------
-------- --------------------------
X
NUMBER(38)
4.
Let us insert records into the parent table
“P” and commit the changes.
SQL> insert into p select rownum from dual connect by
level < 10000;
9999 rows created.
SQL> commit;
Commit complete.
5.
Insert the same from table to “P” into table “C”
SQL> insert into c select * from p;
9999 rows created.
SQL> commit;
Commit complete.
6.
Now, update the child table “C” for a value of
row and pause the session without commiting.
This implies that the session is not ended.
SQL> update c set x=2 where x=1;
1 row updated.
SQL> pause
7.
In another session, update the child record of
another row and do not commit.
SQL> update c set x=2 where x=3;
1 row updated.
8.
Next, in same session (2nd session),
try to delete a row from parent table “P”
SQL> delete from p where x=10;
9.
Now, you can the session is not ending the
hanging.
10.
Let us see whether do we have any locks on the
tables .
select s1.username || '@' ||
s1.machine || ' ( SID=' || s1.sid || ' )
is blocking '
|| s2.username || '@' ||
s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1,
v$lock l2, v$session s2
where s1.sid=l1.sid and
s2.sid=l2.sid
and l1.BLOCK=1 and l2.request
> 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
2 3 4
5 6 7
BLOCKING_STATUS
------------------------------------------------------------------------------------------------------------------------------------------------------
BCTEST@vm1 ( SID=145 ) is blocking BCTEST@vm1 ( SID=145 )
BCTEST@vm1 ( SID=133 ) is blocking BCTEST@vm1 ( SID=145 )
SQL> select a.sid,a.serial#,c.object_name,c.object_type
from V$session a, V$locked_object b, dba_objects c
where a.sid=b.session_id
and b.object_id=c.object_id; 2 3
SID
SERIAL# OBJECT_NAME
OBJECT_TYPE
---------- ----------
--------------- -------------------
145
61 P TABLE
133 11 P TABLE
145 61 C TABLE
133 11 C TABLE
SQL> @pid.sql
Enter Oracle SID: 145
=====================================================================
SID/Serial : 145,61
Foreground : PID: 6794 - sqlplus@vm1 (TNS V1-V3)
Shadow : PID: 6797 - oracle@vm1 (TNS V1-V3)
Terminal : pts/2/ UNKNOWN
OS User : oracle on vm1
Ora User : BCTEST
Status Flags: ACTIVE DEDICATED
USER
Tran Active : 000000006D487318
Login Time : Fri 15:42:49
Last Call : Fri 15:52:26 - 6.2 min
Lock/ Latch : 000000006E39F108/
NONE
Latch Spin : NONE
Current SQL statement:
delete from p where
x=:"SYS_B_0"
Previous SQL statement:
update c set
x=:"SYS_B_0" where x=:"SYS_B_1"
Session Waits:
WAITING: enq: TM -
contention
Connect Info:
: Oracle Bequeath NT Protocol Adapter
for Linux: Version 11.2.0.3.0 - Production
: Oracle Advanced Security: authentication
service for Linux: Version 11.2.0.3.0 - Production
: Oracle Advanced Security: encryption
service for Linux: Version 11.2.0.3.0 - Production
: Oracle Advanced Security:
crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production
Locks:
TRANSAC ENQ H: X R: NONE -
RS+SLOT#983048 WRP#3823
DML/DATA ENQ H: RX R: RSX - C
DML/DATA ENQ H: RX R: NONE - P
TYPE=AE H: S R: NONE - ID1=100 ID2=0
=====================================================================
SQL> @pid.sql
Enter Oracle SID: 133
=====================================================================
SID/Serial : 133,11
Foreground : PID: 6880 - sqlplus@vm1 (TNS V1-V3)
Shadow : PID: 6881 - oracle@vm1 (TNS V1-V3)
Terminal : pts/1/ UNKNOWN
OS User : oracle on vm1
Ora User : BCTEST
Status Flags: INACTIVE DEDICATED
USER
Tran Active : 000000006D4C5558
Login Time : Fri 15:48:17
Last Call : Fri 15:51:50 - 7.0 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
Previous SQL statement:
update c set
x=:"SYS_B_0" where x=:"SYS_B_1"
Session Waits:
WAITING: SQL*Net message
from client
Connect Info:
: Oracle Bequeath NT Protocol Adapter
for Linux: Version 11.2.0.3.0 - Production
:
Oracle Advanced Security: authentication service for Linux: Version 11.2.0.3.0
- Production
: Oracle Advanced Security: encryption
service for Linux: Version 11.2.0.3.0 - Production
: Oracle Advanced Security:
crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production
Locks:
TYPE=AE H: S R: NONE - ID1=100 ID2=0
DML/DATA ENQ H: RX R: NONE - P
TRANSAC ENQ H: X R: NONE -
RS+SLOT#851992 WRP#3894
DML/DATA ENQ H: RX R: NONE - C
=====================================================================
SQL>
1. From
the above results, you can see the sessions are locked one another on both
table “P” and “C”.
2. Session
145 is blocked itself as it has both update and delete statement within itself
and not continuing due to references between the columns in both tables.
11.
Next, we will see what happens when we create
index an index on foreign key (in child table).
SQL> create index c_idx on c(x);
Index created.
12.
Let us try to update and pause it in 1 session.
SQL> update c set x=2 where
x=1;
1 row updated.
SQL> pause
In another session, let us update
another row of child table and try to delete from Parent table.
SQL> update c set x=2 where
x=3;
1 row updated.
SQL> delete from p where
x=10;
delete from p where x=10
*
ERROR at line 1:
ORA-02292: integrity constraint (BCTEST.SYS_C0011326) violated - child
record found
Here, we see that the lock is
disappeared and we get an integrity constraint error.
So, we need to first delete the
record from child table “C” and then from parent table “P”.
In case, if you want to delete
both the parent and child records when we execute a delete a record from parent
table, then we have to create foreign key as below.
alter table sample1
add foreign key (col1)
references sample (col2)
on delete cascade;
add foreign key (col1)
references sample (col2)
on delete cascade;
Another example is below.
1.
Create a table supplier and insert some records.
CREATE TABLE supplier
( supplier_id number(10) not
null,
supplier_name varchar2(50) not
null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY
KEY (supplier_id)
);
INSERT INTO supplier VALUES (1,
'Supplier 1', 'Contact 1');
INSERT INTO supplier VALUES (2,
'Supplier 2', 'Contact 2');
COMMIT;
2.
Create another table Product . Please note on delete cascade.
CREATE TABLE product
( product_id number(10) not
null,
product_name varchar2(50) not
null,
supplier_id number(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE );
INSERT INTO product VALUES (1,
'Product 1', 1);
INSERT INTO product VALUES (2,
'Product 2', 1);
INSERT INTO product VALUES (3,
'Product 3', 2);
COMMIT;
3.
Delete some records from supplier (parent table
) in Session 1
SQL> DELETE supplier WHERE
supplier_id = 1;
1 row deleted.
In another session, try to
delete another record from same parent table
SQL> DELETE supplier WHERE supplier_id = 2;
In 3rd session, try to
insert a record into same Parent table.
INSERT INTO supplier VALUES (5,
'Supplier 5', 'Contact 5');
4.
We can see, sessions 2 and 3 will hung and will have enq:TM-contention issues from
below query.
SQL> SELECT l.sid, s.blocking_session blocker,
s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;
2 3 4
5 6
Enter value for user: BCTEST
old 3: WHERE
UPPER(s.username) = UPPER('&User')
new 3: WHERE
UPPER(s.username) = UPPER('BCTEST')
SID BLOCKER EVENT TY LMODE
REQUEST OBJECT_NAME
OBJECT_TYPE
---------- ---------- ------------------------------ --
---------- ---------- --------------- -------------------
133 SQL*Net message from client AE
4 0 ORA$BASE EDITION
133 SQL*Net message from client TM
3 0 SUPPLIER TABLE
133 SQL*Net message from client TM
3 0 PRODUCT TABLE
133 SQL*Net message from client TX
6 0
145 133 enq: TM - contention AE 4
0 ORA$BASE EDITION
145 133 enq: TM - contention TM 3 0 C TABLE
145 133 enq: TM - contention TM 3
0 P TABLE
145 133 enq: TM - contention TM 3
0 SUPPLIER TABLE
145 133 enq: TM - contention TM 0
5 PRODUCT TABLE
145 133 enq: TM - contention TX 6
0
152 145 enq: TM - contention AE
4 0 ORA$BASE EDITION
152 145 enq: TM - contention TM 0
3 PRODUCT TABLE
152 145 enq: TM - contention TM 3
0 SUPPLIER TABLE
13 rows selected.
5.
Now, we will see which foreign keys are not
having indexes.
SQL> col table_name format
a15
SQL> col column_name format
a35
SQL> SELECT * FROM (
2 SELECT c.table_name,
cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R' and c.table_name='PRODUCT'
MINUS
SELECT i.table_name,
ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name,
column_position;
TABLE_NAME COLUMN_NAME COLUMN_POSITION
---------------
----------------------------------- ---------------
PRODUCT SUPPLIER_ID 1
6.
Now, create an index on foreign key in child
table.
SQL> CREATE INDEX fk_supplier
ON product (supplier_id);
Index created.
7.
Now, we can see all sessions are completing
their statements without any issues.
Session 1
SQL> DELETE supplier WHERE supplier_id
= 1;
1 row deleted
Session 2
SQL> DELETE supplier WHERE
supplier_id = 2;
1 row deleted.
Session 3
SQL> INSERT INTO supplier
VALUES (5, 'Supplier 5', 'Contact 5');
1 row created.
No comments:
Post a Comment