This is a write-up of an issue I recently posted to the OTN discussion forum (https://community.oracle.com/message/12798407). I thought the associated test case was useful in demonstrating the issue, so is captured here for future reference. There were some useful replies to the OTN post, confirming my suspicions.
The test was performed using Oracle Enterprise Edition 220.127.116.11.0 on Linux.
A parallel delete blocks insert into dependent table, and shows up as waits on ‘enq: TM – contention’. The scenario is as follows.
- Session S1 is performing a parallel delete from table T1.
- Session S2 is performing a single-row insert into table T2.
- Table T2 has a foreign key referencing table T1. The foreign key column is indexed.
- Neither table is partitioned.
- The row being inserted into T2 does not depend on any of the rows being deleted from T1.
- Session S2 is blocked by session S1 until the parallel delete is committed, with an ‘enq: TM – contention’ wait event.
Due to the presence of a foreign key, it is necessary for S2 to ensure there is a valid parent record in T1 before permitting the insert into T2.
- In the case of a serial delete from S1, the process locks the affected rows in T1. Consequently, providing S2 is inserting records which are not affected by the delete, it is not blocked.
- In the case of a parallel delete from S1, the process locks the entire table with an exclusive (mode 6) lock. Consequently, S2 cannot insert any records into T2 and is blocked until S1 commits the transaction.
This is demonstrated in the following test case.
-- Set up tables and data CREATE TABLE t1 ( id NUMBER , data VARCHAR2(4000) , CONSTRAINT pk_t1 PRIMARY KEY (id) ); CREATE TABLE t2 ( id NUMBER , t1_id NUMBER , CONSTRAINT pk_t2 PRIMARY KEY (id) , CONSTRAINT fk_t2_t1 FOREIGN KEY (t1_id) REFERENCES t1 ); CREATE INDEX ix_t2_fk ON t2 (t1_id); -- Insert 1,000 rows into T1 INSERT INTO t1 SELECT level , RPAD('X',4000,'X') FROM dual CONNECT BY level <= 1000; -- Insert 10 rows into T2 INSERT INTO t2 SELECT level , level FROM dual CONNECT BY level <= 10; EXEC dbms_stats.gather_table_stats(user,'T1') EXEC dbms_stats.gather_table_stats(user,'T2') COMMIT;
Firstly, insert into a related table whilst performing a serial delete.
-- Session 1 - serial delete ALTER SESSION DISABLE PARALLEL DML; DELETE FROM t1 WHERE id BETWEEN 901 AND 1000; -- No commit -- Session 2 INSERT INTO t2 VALUES (11,11); -- No wait event -- Check locks SELECT session_id , lock_type , mode_held , mode_requested , lock_id1 , lock_id2 , blocking_others , object_type ||' ' || object_name AS locked_object FROM dba_locks LEFT JOIN user_objects ON lock_id1 = object_id WHERE lock_type IN ('DML','Transaction') AND session_id IN (&1,&2.) ORDER BY 1,2,5; /* 1 DML Row-X (SX) None 75618 0 Not Blocking TABLE T1 1 DML Row-X (SX) None 75620 0 Not Blocking TABLE T2 1 Transaction Exclusive None 262158 669 Not Blocking 2 DML Row-X (SX) None 75618 0 Not Blocking TABLE T1 2 DML Row-X (SX) None 75620 0 Not Blocking TABLE T2 2 Transaction Exclusive None 327680 830 Not Blocking */ -- Session 2 COMMIT;
Session 2 is able to insert a record into the dependent table T2 without encountering contention from Session 1. The serial delete in session 1 takes a share lock on T1 (SX), so session S2 is able to also take a share lock on T1 to validate the foreign key constraint.
Now see what happens when parallel DML is used to delete records in Session 1.
-- Session 1 - parallel delete COMMIT; ALTER SESSION ENABLE PARALLEL DML; DELETE /*+ PARALLEL */ FROM t1 WHERE id BETWEEN 801 AND 900; -- No commit -- Session 2 INSERT INTO t2 VALUES (12,12); -- Lock-wait -- Check locks again /* 1 DML Exclusive None 75618 0 Blocking TABLE T1 <-- this is the TM-X (mode 6) blocking lock 1 DML Row-X (SX) None 75620 0 Not Blocking TABLE T2 1 Transaction Exclusive None 458759 650 Not Blocking 2 DML None Row-X (SX) 75618 0 Not Blocking TABLE T1 <-- this is the blocked session */ -- Check wait events SELECT event FROM v$session WHERE sid = &2.; -- event = enq: TM - contention
Session 2 cannot insert into table T2 because it is blocked by session 1 deleting from table T1. The parallel delete has taken a table exclusive lock (X) on the table, so no other process can get a lock, even the SX share lock required to validate the foreign key. The insert from session 2 is blocked until session 1 commits.
-- Session 1 COMMIT; -- Session 2 -- Insert completes successfully
This test case has shown that parallel DML can create contention, not only for the table being written to, but also to other tables related by foreign keys. This should be borne in mind when deciding whether to use parallel DML to speed-up data maintenance processes.