Oracle Database 11.2.0.3.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?
Analysis
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