Friday, October 05, 2012

ORA-00054 and DDL_LOCK_TIMEOUT

I often get ORA-00054 errors when I am applying application software releases to databases. They happen when I fail to acquire a lock for a DDL statement. The DDL statement is often one of several pieces of SQL in a script and the ORA-00054 then causes some or all of the following SQL to fail. Sorting out the mess which follows can take time so I decided to see what I could do about it. My latest ORA-00054 occurred when I was trying to set up a foreign key constraint so I did the same for the example below, which was tested on Oracle 11.2. First I created a department table:

SQL> create table dept
  2  (dept_no varchar2(4),
  3   dept_name varchar2(10))
  4  /

Table created.

SQL> alter table dept
  2  add constraint pk_dept
  3  primary key(dept_no) using index
  4  /

Table altered.

SQL> insert into dept values('0001','IT')
  2  /

1 row created.

SQL>

Note that I did not COMMIT the INSERT at the end. Then, in another session (in red as opposed to blue), I created an employee table and tried to set up a foreign key constraint, which would check the department code against the department table:

SQL> create table emp
  2  (emp_no   varchar2(4),
  3   emp_name varchar2(10),
  4   dept_no  varchar2(4))
  5  /

Table created.

SQL> alter table emp
  2  add constraint check_dept
  3  foreign key(dept_no)
  4  references dept(dept_no)
  5  /
alter table emp
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT
specified or timeout expired

SQL>

It failed immediately with an ORA-00054 due to the uncommitted transaction in the department table. Oracle 11 has a new parameter called DDL_LOCK_TIMEOUT, which tells DDL to wait for the number of seconds specified if it cannot get a lock immediately. I decided to try it out in the 2nd session:

SQL> alter session set ddl_lock_timeout = 600;

Session altered.

SQL>

Then I tried to create the foreign key constraint again in the employee table:

SQL> alter table emp
  2  add constraint check_dept
  3  foreign key(dept_no)
  4  references dept(dept_no)
  5  /

This time the session waited instead of failing immediately. At this point, you could run some SQL to identify the locking session and take appropriate action. I will look at that in a future post. On this occasion I just committed the transaction in the department table:

SQL> insert into dept values('0001','IT')
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL>

... and when I returned to the other session, which was creating the foreign key constraint, it had finished:

SQL> alter table emp
  2  add constraint check_dept
  3  foreign key(dept_no)
  4  references dept(dept_no)
  5  /

Table altered.

SQL>

This option was not available in Oracle 10. The SQL below was tested on Oracle 10.2.0.3.0:

SQL> alter session set ddl_lock_timeout = 600;
alter session set ddl_lock_timeout = 600
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

SQL>

There is a lot more about ORA-00054 in the book advertised below:



However, on page 224 it says the following (as usual, click on the image to enlarge it and bring it into focus):

But when I tried this myself, I found that it worked in Oracle 11.1.0.6.0 too:

Solaris > sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 5 12:18:54 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set ddl_lock_timeout = 600;

Session altered.

SQL>

Still, if I ever know 1% of what Thomas Kyte does about Oracle, I'm sure I'll be quite happy!

No comments: