Lock table followed by DDL?

My client DBA wrote a small script: To drop a column to a busy table. This application is mostly an OLTP application and table is modified heavily. It is hard to get a lock on the table, even though DDL to drop the column will be quick completing within sub-seconds. Following script was tried in the middle of night so as to minimize production impact.

set serveroutput on size 100000
declare
begin
      -- lock table in exclusive mode 
      execute immediate 'lock table t1 in exclusive mode';
      dbms_output.put_line ('**** Table locked. Dropping column ****' ) ;
      -- If we lock it, drop the column
      execute immediate 'alter table t1 drop column n5 ';
exception
  when others then
     dbms_output.put_line(' No luck ');
     dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm );
end;
/

Logic of the above script is a) wait for 5 minutes to lock the table in exclusive mode, before timing out. b) If we acquire exclusive lock on that table, then add a column.

Do you see any issues with this script?

So, did we add that column ?

To his surprise, script was consistently failing with the output printed below.

**** Table locked. Dropping column ****  ..........................................(#1)
No luck
SQL code-54,error ORA-00054: resource busy and acquire with NOWAIT specified or ...(#2)
timeout expired

PL/SQL procedure successfully completed.

What is wrong with the output printed above? Code locked the table as the line (#1) indicates.But, drop column DDL statement failed with an error message (line #2), indicating that the DDL statement couldn’t acquire exclusive table level lock. Isn’t it surprising, first statement got an exclusive lock on the table and then subsequent DDL statement fails to lock the table, even though executed from the same session ?

I think NOT

Fortunately, Mark Bobak and I have investigated this issue few years ago and I was able to quickly understand the problem with this script. If you have guessed implicit commit, right on!

Enter implicit commit.

DDL statement will execute an implicit commit as a first step. So, sequence of steps from the script is:

1. Lock table statement acquires an exclusive lock on the table.
2. Implicit commit from next DDL ( alter table) statement released the exclusive lock acquired above, as commit will release all locks acquired.
3. And alter table DDL statement fails immediately with ORA-54 error since the lock is not available immediately at that second.

How to resolve this issue? Tough luck. Only brutal force method available until 11g. Slightly rewritten script printed below tries to drop the column in a loop and sleeping for 10 seconds between consecutive tries. Quits if successful.

declare
   success boolean := false;
begin
  while ( not success) 
   loop
     begin
       execute immediate 'alter table t1 drop column n5 ';
       success := true;
     exception
       when others then
         null;
         dbms_lock.sleep(10);
      end;
    end loop;
end;
/

There are few issues with this method:

1. Session checks to see if table can be locked in exclusive mode, if not, quickly fails. session does not enqueue or wait for the lock. Session might never get a lock since there are always outstanding quick transactions on this table.
2. This could potentially be an infinite loop.

We can perform this activity much more elegantly in Oracle version 11g.

Introducing ddl_lock_timeout

11g introduces the notion of DDL statements waiting for lock(s). Prior to 11g, only DML can wait for a lock using ‘wait nnn’ syntax. 11g introduces a parameter ddl_lock_timeout. This parameter controls how many seconds,DDL statement must wait before timing out. Rewritten script for 11g printed below.

declare
begin
      -- We really want to drop that column and so wait for 1800 seconds.
      execute immediate 'alter session set ddl_lock_timeout=1800';
      execute immediate 'alter table t1 drop column n5 ';
exception
  when others then
     dbms_output.put_line(' No luck ');
     dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm );
end;
/

Essentially, code sets up maximum wait time to be 30 minutes using ddl_lock_timeout parameter. So, ‘Alter table’ DDL statement will wait for 30 minutes before timing out with ORA-54 error, if it is unable to acquire the exclusive lock on that table. This DDL statement enqueues and hence gets serviced in FIFO basis. This is very different compared to earlier versions, since DDL statement will never enqueue prior to 11g.

This solution is more elegant and quickly will drop the columns enqueuing behind existing transactions

Surprise! Add column doesn’t need exclusive table locks in 11g.

It is also worth mentioning about this new feature in this entry. Prior to 11g, any DDL on a table will require an exclusive table level lock (type TM). This means that if there are any uncommitted transactions on the table, then DDL statement will fail. But, in 11g, few DDL statements do not acquire TM locks at all. For example, adding a column doesn’t need to acquire a TM table level lock.

In the test case below, session #1 has an uncommitted transaction and session #2 adds a column even though there is an uncommitted transaction on that table. I don’t see any consistency issues either.

REM Session #1: No commits
insert into t1 values(1,1,1,1,1);
1 row created.

REM Session #2:
alter table t1 add ( n6 number);

Table altered.

Alter table DDL statement in the test case above acquires SX level (Row exclusive) lock on the table. There is also a new enqueue type OD introduced to support this new feature. One of my presentation 11g new features covers this OD type in detail.