ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML", Description: "Synchronizes accesses to an object") because it is currently held by another session. The resource being locked can be identified by 73492 ("Table") and 0 ("operation")

Another session holds a lock on the table or row, and your statement did not wait for it.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-12

ORA-00054 means your statement needed a lock that another session is holding, and Oracle gave up instead of waiting. Older releases say resource busy and acquire with NOWAIT specified or timeout expired. 23ai, used on this page, prints the lock type: TM (a table lock, as with DDL) or TX (a row lock held by a transaction).

In every case below, session A has changed data and not committed yet. The fix is either to let your statement wait, to skip the locked rows, or to find the blocking session and have it COMMIT or ROLLBACK.

Causes and fixes

  1. ALTER TABLE while another session has uncommitted changes
  2. SELECT ... FOR UPDATE NOWAIT on a row someone else is changing
  3. CREATE INDEX on a table with uncommitted DML

1ALTER TABLE while another session has uncommitted changes

Session A runs UPDATE orders ... WHERE order_id = 1 and does not commit. Session B runs ALTER TABLE orders MODIFY (status VARCHAR2(20)) and fails at once with a TM lock error. DDL does not wait by default.

With ALTER SESSION SET ddl_lock_timeout = 60, the same ALTER waits instead of failing. While it waited, v$session showed session B (SID 50) with BLOCKING_SESSION 56 and event enq: TM - contention. As soon as session A ran COMMIT, the ALTER finished with Table altered.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, status VARCHAR2(10), amount NUMBER);
INSERT INTO orders SELECT LEVEL, 'NEW', LEVEL * 100 FROM dual CONNECT BY LEVEL <= 5;
COMMIT;
OUTPUT
Table created.


5 rows created.


Commit complete.
Reproduce2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a
UPDATE orders SET status = 'PAID' WHERE order_id = 1;
OUTPUT
1 row updated.
Session Brun as lab_a
ALTER TABLE orders MODIFY (status VARCHAR2(20));
OUTPUT
ALTER TABLE orders MODIFY (status VARCHAR2(20))
*
ERROR at line 1:
ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML", Description: "Synchronizes accesses to an object") because it is currently held by another session. The resource being locked can be
identified by 73492 ("Table") and 0 ("operation")
Help: https://docs.oracle.com/error-help/db/ora-00054/

ddl_lock_timeout is in seconds. It only helps if the blocking session ends its transaction within that time, so find the blocker with v$session.blocking_session as well.

After the fix3 sessions open at the same time (A, B, S), run in this order
Session Arun as lab_a
UPDATE orders SET status = 'PAID' WHERE order_id = 1;
OUTPUT
1 row updated.
Session Brun as lab_a
ALTER SESSION SET ddl_lock_timeout = 60;
OUTPUT
Session altered.
Session Brun as lab_a⏳ Waiting for the lock…
ALTER TABLE orders MODIFY (status VARCHAR2(20));
Session S · Checkrun as system
COLUMN event FORMAT a30
SELECT s.sid, s.blocking_session, s.event, o.object_name
  FROM v$session s
  JOIN v$locked_object lo ON lo.session_id = s.sid
  JOIN dba_objects o ON o.object_id = lo.object_id
 WHERE o.owner = 'LAB_A'
 ORDER BY s.sid;
OUTPUT
       SID BLOCKING_SESSION EVENT                          OBJECT_NAME
---------- ---------------- ------------------------------ ------------------------------
        50               56 enq: TM - contention           ORDERS
        56                  SQL*Net message from client    ORDERS

2 rows selected.
Session A · Fixrun as lab_a
COMMIT;
OUTPUT
Commit complete.
Session B: the waiting statement finishesrun as lab_a
OUTPUT
Table altered.

2SELECT ... FOR UPDATE NOWAIT on a row someone else is changing

Session A has updated order 1 and not committed. Session B runs SELECT ... WHERE order_id = 1 FOR UPDATE NOWAIT and gets ORA-00054 with a TX lock, followed by ORA-40097: Failure to acquire row lock, currently held by transaction ID ....

If the job is "take the next free rows", use FOR UPDATE SKIP LOCKED. Session B got orders 2 to 5 right away, and the row locked by session A was simply left out.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, status VARCHAR2(10), amount NUMBER);
INSERT INTO orders SELECT LEVEL, 'NEW', LEVEL * 100 FROM dual CONNECT BY LEVEL <= 5;
COMMIT;
OUTPUT
Table created.


5 rows created.


Commit complete.
Reproduce2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a
UPDATE orders SET status = 'PAID' WHERE order_id = 1;
OUTPUT
1 row updated.
Session Brun as lab_a
SELECT order_id, status FROM orders WHERE order_id = 1 FOR UPDATE NOWAIT;
OUTPUT
SELECT order_id, status FROM orders WHERE order_id = 1 FOR UPDATE NOWAIT
                             *
ERROR at line 1:
ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another
session. The resource being locked can be identified by 262163 ("usn<<16 | slot") and 592 ("sequence")
ORA-40097: Failure to acquire row lock, currently held by transaction ID 4.19.592
Help: https://docs.oracle.com/error-help/db/ora-00054/

SKIP LOCKED is for queue-style processing. It does not give you the locked row; if you need that exact row, it has to wait for session A to commit or roll back.

After the fix2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a
UPDATE orders SET status = 'PAID' WHERE order_id = 1;
OUTPUT
1 row updated.
Session B · Fixrun as lab_a
SELECT order_id, status FROM orders
 WHERE status = 'NEW'
 ORDER BY order_id
 FOR UPDATE SKIP LOCKED;
OUTPUT
  ORDER_ID STATUS
---------- ------------------------------
         2 NEW
         3 NEW
         4 NEW
         5 NEW

4 rows selected.

3CREATE INDEX on a table with uncommitted DML

Session A inserts a row and does not commit. A plain CREATE INDEX orders_status_ix ON orders (status) in session B fails at once with a TM lock error.

CREATE INDEX ... ONLINE does not fail. It waited while session A's insert was open, and printed Index created. right after session A committed.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, status VARCHAR2(10), amount NUMBER);
INSERT INTO orders SELECT LEVEL, 'NEW', LEVEL * 100 FROM dual CONNECT BY LEVEL <= 5;
COMMIT;
OUTPUT
Table created.


5 rows created.


Commit complete.
Reproduce2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a
INSERT INTO orders VALUES (6, 'NEW', 600);
OUTPUT
1 row created.
Session Brun as lab_a
CREATE INDEX orders_status_ix ON orders (status);
OUTPUT
CREATE INDEX orders_status_ix ON orders (status)
*
ERROR at line 1:
ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML", Description: "Synchronizes accesses to an object") because it is currently held by another session. The resource being locked can be
identified by 73496 ("Table") and 0 ("operation")
Help: https://docs.oracle.com/error-help/db/ora-00054/

ONLINE still waits for transactions that are already open, so on a busy table it can take a while to finish.

After the fix2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a
INSERT INTO orders VALUES (6, 'NEW', 600);
OUTPUT
1 row created.
Session B · Fixrun as lab_a⏳ Waiting for the lock…
CREATE INDEX orders_status_ix ON orders (status) ONLINE;
Session Arun as lab_a
COMMIT;
OUTPUT
Commit complete.
Session B: the waiting statement finishesrun as lab_a
OUTPUT
Index created.

FAQ

How do I find which session is blocking my ALTER TABLE?

While your statement waits, query v$session: the waiting session has BLOCKING_SESSION set to the SID of the blocker. Joining v$locked_object with dba_objects shows which table each session has locked.

Can I make DDL wait instead of failing with ORA-00054?

Yes. ALTER SESSION SET ddl_lock_timeout = 60 makes DDL in that session wait up to 60 seconds for the lock. On this page the ALTER waited and finished right after the other session committed.

What is the difference between TM and TX in the 23ai message?

TM is a lock on a table, which DDL like ALTER TABLE or CREATE INDEX needs. TX is a transaction lock on rows, which you hit with FOR UPDATE NOWAIT on a row someone else changed.

How do I process rows that are not locked by other sessions?

Use SELECT ... FOR UPDATE SKIP LOCKED. Locked rows are skipped instead of raising ORA-00054.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors