ORA-00054: resource busy and acquire with NOWAIT specified
A statement that won't wait for locks (NOWAIT, DDL, or a lock with a timeout) found the table or row locked by another session's uncommitted work.
- Error code
ORA-00054- Category
- Transactions and locking
What it means
ORA-00054 means a lock was requested without waiting, or with a timeout that expired, and another session holds it. DDL such as ALTER TABLE, DROP TABLE and TRUNCATE needs an exclusive lock and fails immediately by default if any session has uncommitted changes on the table. The full 19c message is resource busy and acquire with NOWAIT specified or timeout expired.
Newer releases describe the lock and who holds it: Failed to acquire a lock (Type: ..., Name: ..., Description: ...) because it is currently held by another session.
Common causes
- Running DDL on a table while another session has uncommitted DML on it, often a forgotten session in a GUI tool.
SELECT ... FOR UPDATE NOWAITon rows locked by another transaction.LOCK TABLE ... NOWAIT.
How to fix it
-
Find the session holding the lock.
SELECT s.sid, s.serial#, s.username, s.program, o.object_name FROM v$locked_object l JOIN dba_objects o ON o.object_id = l.object_id JOIN v$session s ON s.sid = l.session_id WHERE o.object_name = 'ORDERS';
-
Have that session commit or roll back. As a last resort, an administrator can end it.
ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;
-
Let DDL wait for locks instead of failing at once.
ALTER SESSION SET ddl_lock_timeout = 60;
Related errors
ORA-00060deadlock detected while waiting for resourceORA-04021timeout waiting to lock object1222SQL Server: Lock request time out period exceeded55P03PostgreSQL: lock not available1205MySQL: Lock wait timeout exceededSQLITE_BUSYSQLite: database is locked
More transactions and locking errors
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
Work on Oracle in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.