14 Replies Latest reply on Jan 19, 2015 7:12 PM by GS613

    enq: TM - contention due to parallel DML and foreign keys

    Dan Jankowski

      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
      
        • 1. Re: enq: TM - contention due to parallel DML and foreign keys
          ddf_dba

          I suspect that Oracle is erring on the side of caution, since parallel DML slaves could be accessing any row in that table.  That being the case Oracle locks the table and prevents any additional DML until the parallel DML has completed.  You proved this in your example by first executing the delete as a single process, where the insert from the second session completed even though the delete had not yet been committed.

           

           

          David Fitzjarrell

          1 person found this helpful
          • 2. Re: enq: TM - contention due to parallel DML and foreign keys
            JuanM

            Dan Jankowski wrote:

             

            Oracle Database 11.2.0.3.0 on Linux

            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?

             

             

            Try reading the MOS Doc:

             

            I don't know if will resolve the issue but with luck it will help you to understand a little bit the locks you are getting.

             

            Regards,

            Juan M

            1 person found this helpful
            • 3. Re: enq: TM - contention due to parallel DML and foreign keys
              Mark D Powell

              Dan, with parallel DML when you see significant wait time associated with the process one option to consider is to reduce the degree of parallelism.  Since the performance improvement from adding more processes is not linear there is a point of diminishing returns and if you are past it then reducing the degree will help.

              - -

              HTH -- Mark D Powell --

              • 4. Re: enq: TM - contention due to parallel DML and foreign keys
                Jonathan Lewis

                Nothing you can do about it, that's just the way it is.

                In your test you won't even be able to select from t1 after the parallel delete until you've committed or rolled back - and essentially a select is what referential integrity has to do.

                 

                You only option is to engineer a completely different strategy - several hours to do a parallel delete sounds like an engineering failure, find out what's taking all the time and see what that suggests as an alternative.

                 

                 

                Regards

                Jonathan Lewis

                1 person found this helpful
                • 5. Re: enq: TM - contention due to parallel DML and foreign keys
                  Dan Jankowski

                  Jonathan - thanks for confirming my fears.

                   

                  Re your comment,

                  In your test you won't even be able to select from t1 after the parallel delete until you've committed or rolled back - and essentially a select is what referential integrity has to do.

                   

                  you can select from table T1 in another session (S2) after the delete has completed (and not committed). You obviously see the pre-deletion state of the table, but it can be queried. You just can't query it from the same session (S1) after performing a parallel delete and no commit - ORA-12838 is returned. Therefore the insert in session S2 is doing more than just querying T1 - it has to get a TM-03 lock on T1, and is blocked by the TM-06 lock being held by the parallel DML transaction.

                   

                  Most worryingly, in the production system, there are around 10 tables which reference T1, which means they are all effectively locked whilst the delete is taking place. One of the downfalls of parallel DML I guess.

                   

                  Regards,

                   

                  Dan

                  • 6. Re: enq: TM - contention due to parallel DML and foreign keys
                    GS613
                    One of the downfalls of parallel DML I guess.

                    I don't think the blame falls on parallel DML but more on your applications usage of the feature (design!).

                     

                    Parallel DML uses direct path load and will acquire a lock on the table. That is how it is able to achieve its performance gains as opposed to conventional load. That is how it works.

                    • 7. Re: enq: TM - contention due to parallel DML and foreign keys
                      Jonathan Lewis

                      you can select from table T1 in another session (S2) after the delete has completed (and not committed)


                      You're right, of course - it's not the select that's important it's the normal mode-3 RI lock. Can't think how I forgot that.

                       

                      All the locking that goes on around parallel DML is intended to avoid various deadlock possibilities (according to a MoS note I read a few years ago), but I've never worked out exactly what scenario it's protecting from.

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: enq: TM - contention due to parallel DML and foreign keys
                        Mark D Powell

                        Dan, question: do you really need to run the delete using parallelism?  Is there really a need for the delete to be fast?  I would think it would be more important that the delete be a lightweight process that has no performance impact on the system.  If true, then perhaps a process that deletes a few million rows, commits, repeat can be used.  The commits are based on the percentage of undo this task would take verse how much is available and how other processes would be affected by this process consuming the undo.

                        - -

                        HTH -- Mark D Powell --


                        • 9. Re: enq: TM - contention due to parallel DML and foreign keys
                          Dan Jankowski

                          Mark,

                           

                          Good point re. slower deletes in the background. Unfortunately the number of records to be deleted can be quite large (10s of millions per week), so it has to be done efficiently to keep up.

                           

                          Dan

                          • 10. Re: enq: TM - contention due to parallel DML and foreign keys
                            GS613

                            Would you consider partitioning?

                            • 11. Re: enq: TM - contention due to parallel DML and foreign keys
                              Dan Jankowski

                              I probably should consider partitioning, given the size of the data. There's no obvious partitioning keys, so the data would probably have to be hash partitioned. I'm not sure this would eliminate the contention - if parallel deletes are taking place across all partitions, then I'll still experience TM contention when inserting into related tables.

                              • 12. Re: enq: TM - contention due to parallel DML and foreign keys
                                GS613

                                How about replacing parallel DML with a multi-threaded solution. Multiple targeted deletes that run in parallel but delete conventionally. That way the inserts would be affected by locking.

                                • 13. Re: enq: TM - contention due to parallel DML and foreign keys
                                  Dan Jankowski

                                  Gary - originally I developed this approach in Standard Edition (i.e. without native parallel execution). I looked to using DBMS_PARALLEL_EXECUTE to multi-thread the process, and found an improvement over single-threaded processing. However, upon moving to Enterprise Edition, there were a number of reasons to use native parallel execution (PDML) :

                                   

                                  1. PDML is a convenient built-in feature, so was preferable to a DIY approach.
                                  2. DBMS_PARALLEL_EXECUTE requires access to DBMS_SCHEDULER, something which the DBAs were reluctant to grant.
                                  3. The index maintenance in a parallel delete should be more efficient than in a serial delete.
                                  4. A parallel full table scan ensures that each block is only read once (I believe). If you carve up the work by some arbitrary key (e.g. a range of IDs) and use DBMS_PARALLEL_EXECUTE, then individual blocks will likely be read by multiple threads.

                                   

                                  With DBMS_PARALLEL_EXECUTE, I suppose it should be possible to chunk-up the data by ranges of ROWIDs and ensure the resulting plan uses a "TABLE ACCESS BY ROWID RANGE", or alternatively partition the table, chunk the data by the partition, and ensure the plan uses partition pruning.

                                  • 14. Re: enq: TM - contention due to parallel DML and foreign keys
                                    GS613

                                    What OS are you using?

                                    I was thinking more along the lines of a *nix shell script to spawn parallel threads.

                                     

                                    Hash partition the table into N parts. Spawn N parallel threads. Design it so that the deletes use the same hash algorithm (ora_hash function) to target each separate partition in order to avoid delete contention.

                                     

                                    Insert as normal.

                                     

                                    This way you get the benefit of parallel processing, simple design (no use of DBA "owned" features), no (or minimal) contentioN on insert.

                                     

                                    i'm just throwing some ideas out there based on methods I've used in the past hoping it sparks some ideas !