I lock my table before truncating it, but I still get ORA-00054: resource busy ... Why ?

The Oracle (tm) Users' Co-Operative FAQ

I lock my table before truncating it, but I still get ORA-00054: resource busy ... Why ?

Back to index of questions

The following quote can be found in the version 9.2 Concepts manual chapter 14: SQL, PL/SQL, and JAVA under the section titled Data Definition Language Statements:  “DDL statements implicitly commit the preceding [transaction] and start a new transaction.”

Truncate is a DDL statement and is contained in the list of example DDL immediately above the quote.  The implicit commit that precedes the truncate command terminates the transaction began by the lock table in exclusive mode command allowing DML statements issued by other sessions after the lock table command was issued and before the truncate command was issued to access the table. The sessions issuing DML statements obtain a lock or locks on the table preventing DDL alteration during the DML activity.  This behavior blocks the truncate command, which then because it is coded not to wait, immediately issues the ORA-00054 error.

            Example ran on Oracle version 9.2.0.4

Note - the marktest table has only 6 rows contained in one data block.
复制

 
复制

[step 1 from session one]
复制

 
复制

        12:45:31 SQL> lock table marktest in exclusive mode;
复制

 
复制

        Table(s) Locked.
复制

 
复制

        Elapsed: 00:00:00.11
复制

        
复制

 
复制

[Step 2 from a different session.  Note time of delete, elapsed time and time upon completion]
复制

 
复制

        12:45:56 SQL> delete from mpowel01.marktest where rownum = 1;
复制

 
复制

        [session hangs waiting on lock]
复制

        1 row deleted.
复制

 
复制

        Elapsed: 00:00:10.74   <= notice long elapsed time
复制

        12:46:17 SQL>          <= and termination time
复制

 
复制

[Step 3 back in session one, note time of truncate is after delete was issued]
复制

 
复制

        12:45:41 SQL>    [This enter was just to update the prompt time displayed]
复制

        12:46:13 SQL> truncate table marktest;
复制

        truncate table marktest
复制

                      *
复制

        ERROR at line 1:
复制

        ORA-00054: resource busy and acquire with NOWAIT specified
复制

 
复制

        Elapsed: 00:00:00.12
复制

        12:46:18 SQL>  [the truncate completes after the delete completes]
复制

 
复制

The delete goes into a lock wait when first issued.  Then when the truncate command is issued the delete executes, and because no commit follows the delete statement session two now has a lock on the table preventing the truncate from working.  Any small table will work for duplicating this test.

The behavior displayed above is a direct result of Oracle’s design decision to not require, indeed to not allowing DDL operations to be explicitly committed.  By including an implicit commit both before and after every DDL operation Oracle made every DDL statement a separate transaction.  This greatly reduces the likelihood of lock contention while accessing the Oracle base (dictionary) metadata tables.  However, behavior as demonstrated above is the result.

Note – commit and rollback statements can be issued after DDL statements are executed, but because of the implicit commit that follows all DDL statements the commit or rollback statements have no practical effect.  There is nothing to commit or rollback, hence in Oracle successful DDL statements cannot be rolled back.

Further reading:  SQL Manual entry for the truncate table command.

Back to index of questions