Addressing TM Enqueue Contention – What is Wrong with this Quote?

June 19, 2011

Currently, the most viewed article in the past 90 days on this blog demonstrates a deadlock situation that is present in Oracle Database 11.1.0.6 and above that is not present in Oracle Database 10.2.0.1 through 10.2.0.5 (someone else might be able to check the earlier releases).  This particular deadlock was the result of a missing foreign key index, which resulted in TM enqueues and an eventual deadlock when the first of three sessions issued a COMMIT.  From this bit of information, hopefully it is clear that foreign key columns should usually be indexed (especially if the primary/unique column in in the parent table is subject to UPDATE statements).

While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, I noticed a test case in Recipe 5-15 that is intended to demonstrate how an index on a foreign key’s column(s) helps to resolve the most common cause of TM enqueues.  While the test case included the DDL to create a parent and child table, it did not include the DML to create test data.  The DDL for the two tables looks something like the following:

CREATE TABLE STORES (
  STORE_ID NUMBER(10) NOT NULL,
  SUPPLIER_NAME VARCHAR2(40) NOT NULL,
  CONSTRAINT STORES_PK PRIMARY KEY (STORE_ID));

CREATE TABLE PRODUCTS (
  PRODUCT_ID NUMBER(10) NOT NULL,
  PRODUCT_NAME VARCHAR2(30) NOT NULL,
  SUPPLIER_ID NUMBER(10) NOT NULL,
  STORE_ID NUMBER(10) NOT NULL,
  CONSTRAINT FK_STORES FOREIGN KEY (STORE_ID) REFERENCES STORES(STORE_ID) ON DELETE CASCADE); 

Immediately after the above DDL statements appears the following claim is made by the alpha copy of the book:

“If you now delete any rows in the STORES table, you’ll notice waits due to locking.”

Interesting observation… I hope that the final copy of the book includes the DML to populate those tables.  In the mean time, let’s create our own test data to see if the claim made in the quote is correct:

INSERT INTO
  STORES
SELECT
  ROWNUM STORE_ID,
  'SUPPLIER NAME '||TO_CHAR(ROWNUM) SUPPLIER_NAME
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO
  PRODUCTS
SELECT
  ROWNUM PRODUCT_ID,
  'PRODUCT NAME '||TO_CHAR(ROWNUM) PRODUCT_NAME,
  TRUNC((ROWNUM-1)/10)+100 SUPPLIER_ID,
  TRUNC((ROWNUM-1)/10)+1 STORE_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

COMMIT; 

We now have 10 rows in the parent table and 100 rows in the child table (side note: is there something strange about the names of the columns in the tables?).  Let’s connect to the database using two sessions.  In Session 1, execute the following to delete a row from the STORES table:

DELETE FROM
  STORES
WHERE
  STORE_ID=1; 

Here is the challenge, what happens if we attempt to execute the following script in Session 2 (the values to be inserted should not conflict with existing rows in the tables):

INSERT INTO
  STORES
SELECT
  ROWNUM+10 STORE_ID,
  'SUPPLIER NAME '||TO_CHAR(ROWNUM+10) SUPPLIER_NAME
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO
  PRODUCTS
SELECT
  ROWNUM PRODUCT_ID,
  'PRODUCT NAME '||TO_CHAR(ROWNUM+100) PRODUCT_NAME,
  TRUNC((ROWNUM+100-1)/10)+100 SUPPLIER_ID,
  TRUNC((ROWNUM+100-1)/10)+1 STORE_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=100; 

Which of the following will happen?

a. Session 2 will be blocked by session 1, with session 2 waiting in the “enq: TM – contention” wait event when it attempts to execute the first of the two SQL statements.

b. Session 2 will be blocked by session 1, with session 2 waiting in the “enq: TM – contention” wait event when it attempts to execute the second of the two SQL statements.

c. Session 2 will receive the following error message “ERROR at line 1: ORA-02291: integrity constraint (TESTUSER.FK_STORES) violated – parent key not found”.

d. Session 2 will corrupt the database due to the missed TM enqueue.

e. Session 2 will be waiting in the “SQL*Net message from client” wait event after it attempts to execute the second of the two SQL statements.

—–

Think about the above for a moment before scrolling down.  Does the Oracle release version make a difference?

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

Now that you have thought about the question for a minute or two, take a look at the output I received on Oracle Database 11.2.0.2:

SQL> INSERT INTO
  2    STORES
  3  SELECT
  4    ROWNUM+10 STORE_ID,
  5    'SUPPLIER NAME '||TO_CHAR(ROWNUM+10) SUPPLIER_NAME
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;

10 rows created.

SQL> INSERT INTO
  2    PRODUCTS
  3  SELECT
  4    ROWNUM PRODUCT_ID,
  5    'PRODUCT NAME '||TO_CHAR(ROWNUM+100) PRODUCT_NAME,
  6    TRUNC((ROWNUM+100-1)/10)+100 SUPPLIER_ID,
  7    TRUNC((ROWNUM+100-1)/10)+1 STORE_ID
  8  FROM
  9    DUAL
 10  CONNECT BY
 11    LEVEL<=100;

100 rows created. 

So, the answer to the multiple choice question is…

Wait… we are not done yet.  Back in Session 2 execute the following which will delete a previously committed row (Session 1 previously deleted the row with STORE_ID=1, but has yet to COMMIT):

DELETE FROM
  STORES
WHERE
  STORE_ID=10; 

The output I received on Oracle Database 11.2.0.2:

SQL> DELETE FROM
  2    STORES
  3  WHERE
  4    STORE_ID=10;

(session is hung - reporting wait event "enq: TM - contention") 

So, the answer to the multiple choice question is…  Wait, does the Oracle Database release version matter?

—

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.