Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Lock table followed by DDL?

Posted by Riyaj Shamsudeen on January 8, 2009

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.

14 Responses to “Lock table followed by DDL?”

  1. Interesting.

    Because I learned a new feature of Oracle 11, and because I always thought that DDL do a commit AFTER they execution, from your example seams the opposite.

    Or am I wrong?

  2. Hello
    DDL implicitly commits at the start of operation and then at the end of that operation.

    Cheers
    Riyaj

  3. Bunditj said

    Hi Riyaj,

    Thanks for sharing this.

    On second script, it may cause infinite loop should there is no column too even if no any lock from other sessions.

    Regards

  4. Hi Bunditj
    Thanks for reading my blog.
    True. Another reason why exception handling with others is such a bad idea.
    Fortunately, it is not part of any application and printed here to make a point.
    Cheers
    Riyaj

  5. Robert Klemme said

    Another worrying observation that would make a good explanation for “the code block did not do what we wanted”:

    dbms_output.put_line (‘**** Table locked. Adding column ****’ ) ;
    — If we lock it, drop the column
    execute immediate ‘alter table t1 drop column n5 ‘;

    The output talks about adding but the DDL is actually a DROP COLUMN… Btw, this also seems to be a bit inconsistent in your article. :-)

    Another thing that made me wonder: why did you do the 10 second sleep? If the application is OLTP and the table is heavily used, I’d rather loop without sleep because IMHO you do not increase your likeliness of obtaining the lock by sleeping (OLTP => high frequency, short transactions).

    One could add a max counter to avoid endless looping. But then again, you can as well use CTRL-C to interrupt or kill the session.

    Cheers

  6. Hello Robert
    Thanks for reading my blog.
    Original script was supposed to drop and add few columns. I used ‘drop’ part of the script to make my point. Obviously didn’t catch that comment discrepancy. I have updated that.
    About ‘sleep’, yeah, there are more than one way to accomplish that goal :-)

    Cheers
    Riyaj

  7. hrishy said

    Hi

    Coincidence i was asked this question in a interview and the same exact sceanario where the database is very busy.I mentioned my choice as dbms_redefination wundering why you didnt opt for the dbms_redefination way

    • Hi Hrishy
      Thank you for reading my blog. Of course, there are more than one way to do that operation and that entry was related DDL and implicit commit.
      Hope you got that job.

      Cheers
      Riyaj

  8. Mark Bobak said

    Hi Riyaj,

    Well, ok, better late than never.

    I remember that talk we had some time ago, and the investigation.

    Once other solution, if 11g is not yet an option, is available, but would require modification of application code. If that’s an option, you can code DBMS_LOCK to take a user defined enqueue in shared mode before doing DML on the table, and call it from normal application operation. When you want to add column, drop column, whatever, you can call DBMS_LOCK on your user defined enqueue with exclusive mode. This will queue behind the shared mode locks held by the application. Eventually, the app will release those locks, your session will acquire exclusive lock, and then you can proceed w/ drop column, add column, whatever. As long as all DML on the table is happening from the application where code always takes the user defined enqueue first, this should work. When you’re done w/ the add/drop column, release the enqueue, and the application will start running again.

    Hope that helps,

    -Mark

  9. Mark Bobak said

    Note: In the comments above, where I said DDL, I meant DML.

  10. Saravanan said

    hai friend,
    i tried the following PL/SQL when droping a column in lock mode.but it show the ERROR.
    SQL> ed
    Wrote file afiedt.buf

    1 declare
    2 begin
    3 dbms_output.put_line (‘1′) ;
    4 — lock table in exclusive mode and wait for 5 minutes
    5 execute immediate ‘lock table sample in exclusive mode wait 60′;
    6 dbms_output.put_line (‘2′) ;
    7 dbms_output.put_line (‘**** Table locked. Dropping column ****’ ) ;
    8 dbms_output.put_line (‘3′) ;
    9 — If we lock it, drop the column
    10 execute immediate ‘alter table sample add address varchar2(20)';
    11 exception
    12 when others then
    13 dbms_output.put_line(‘ No luck ‘);
    14 dbms_output.put_line(‘ SQL code’||sqlcode||’,’|| ‘error ‘||sqlerrm );
    15* end;
    SQL> /
    1
    No luck
    SQL code-933,error ORA-00933: SQL command not properly ended

    PL/SQL procedure successfully completed.

    • Hi Saravanan
      Thanks for reading my blog. I had a mistake in that part of the script, Sorry. I was testing something else and accidentally must have introduced ‘wait 300′ in the ddl statement. I have corrected it and your sql need to be modified as below:
      (BTW, sample is a keyword and it is probably not a good idea to use for table name).
      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;

      Cheers
      Riyaj

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 210 other followers

%d bloggers like this: