Careful with that append hint!!

Today a DBA issued an insert statement with an append hint. This resulted in a fairly time consuming insert of many rows. The DBA was puzzled when all other sessions were blocked from inserting into the table. This is actually fairly easily explained. An append hint instructs Oracle to take out a TM lock in exclusive mode 6 (“enq: TM – contention” wait event). This prevents any DML from occurring in the table in any other session. It’s as if you issued

alter table tablename lock;

It does this to prevent the possibility of any changes occurring while it is inserting data above the high water mark.

This is documented at the following URL:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2260

We can also prove this by running a test similar to what is below.

Start session one that inserts (with an append hint) a lot of rows that will take a while to process.

Start session two after session one and attempt a “regular” insert without an append hint.

Start session three that monitors locks in the v$lock view.

We first create our table, as well as a function that will insert 30 rows generated in a pipelined function that sleeps for one second between the generation of each row. This ensures the insert will run for 30 seconds and simulates a larger load.

SQL> create table foo(c number)
  2  /

Table created.

SQL> create or replace type array as table of number
  2  /

Type created.

SQL> create or replace function big_slow_insert return array pipelined as
  2  begin
  3    for i in 1..30 loop
  4      pipe row(i);
  5      dbms_lock.sleep(1);
  6    end loop;
  7  end;
  8  /

Function created.

SQL>

We then script a python program that will generate the three sessions we mentioned earlier. We will print progress to standard output every few seconds, so we we can see what is happening. The script is below…

#!/home/oracle/local/bin/python

import cx_Oracle, time, sys
from threading import Thread

running = True

#------------------------------------------------------------------------------------

class slowInsert(Thread):
  def __init__ (self):
    Thread.__init__(self)
  def run(self):
    global running
    running = True
    conn = cx_Oracle.connect("rep","rep")
    cursor = conn.cursor()
    print "Starting append insert, which should block other inserts..."
    cursor.execute("insert /*+ append */ into foo select * from table(big_slow_insert())")
    print "Transaction finished, sleeping 30 seconds to determine effect in v$Lock of an open transaction that hasn't been commited..."
    time.sleep(30)
    conn.commit()
    conn.close()

#------------------------------------------------------------------------------------

class regularInsert(Thread):
  def __init__ (self):
    Thread.__init__(self)
  def run(self):
    global running
    conn = cx_Oracle.connect("rep","rep")
    cursor = conn.cursor()
    print "Starting regular insert, sleeping 3 seconds to ensure other session has lock before we start..."
    time.sleep(3)
    cursor.execute("insert into foo values(1)")
    print "Regular insert successful..."
    conn.commit()
    conn.close()
    running = False

#------------------------------------------------------------------------------------

class monitorEnqueue(Thread):
  def __init__ (self):
    Thread.__init__(self)
  def run(self):
    global running
    conn = cx_Oracle.connect("rep","rep")
    while running == True:
      cursor = conn.cursor()
      cursor.execute("select count(*),lmode,request from v$Lock where type = 'TM' group by lmode,request")
      for row in cursor:
        print str(row[0]) + " sessions have a TM lock in mode " + str(row[1]) + ", with a request for it in mode " + str(row[2])
      time.sleep(5)
      cursor.close()
    conn.close()

#------------------------------------------------------------------------------------

threadList = []

current = slowInsert()
threadList.append(current)
current.start()
current = monitorEnqueue()
threadList.append(current)
current.start()
current = regularInsert()
threadList.append(current)
current.start()

for thisThread in threadList:
  thisThread.join()
print "Finished"

…with the output below when it is run. Keep in mind 6 is an exclusive table lock (our append hint), while 3 is a row exclusive lock (standard insert generates this). Notice our regular insert is blocked for the duration of the test until the first session commits.

Starting append insert, which should block other inserts...
1 sessions have a TM lock in mode 6, with a request for it in mode 0
Starting regular insert, sleeping 3 seconds to ensure other session has lock before we start...
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
Transaction finished, sleeping 30 seconds to determine effect in v$Lock of an open transaction that hasn't been commited...
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
1 sessions have a TM lock in mode 6, with a request for it in mode 0
1 sessions have a TM lock in mode 0, with a request for it in mode 3
Regular insert successful...
Finished