Oracle Database 184.108.40.206.0 on Linux
A parallel delete is blocking an insert on a dependent table, showing 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.
My question is - what can be done to eliminate the contention? At present, the delete from T1 is taking several hours, and consequently, no inserts can be made into T2 during this period. I know that parallel DML restricts what can be done to a table concurrently, but is there any way to prevent this from blocking inserts into related tables?
Due to the presence of a foreign key, it is necessary for S2 to ensure there is a valid 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; -- 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 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 1 COMMIT; -- Session 2 -- Insert completes successfully