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
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
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;Table created.
5 rows created.
Commit complete.UPDATE orders SET status = 'PAID' WHERE order_id = 1;1 row updated.ALTER TABLE orders MODIFY (status VARCHAR2(20));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.
UPDATE orders SET status = 'PAID' WHERE order_id = 1;1 row updated.ALTER SESSION SET ddl_lock_timeout = 60;Session altered.ALTER TABLE orders MODIFY (status VARCHAR2(20));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; SID BLOCKING_SESSION EVENT OBJECT_NAME
---------- ---------------- ------------------------------ ------------------------------
50 56 enq: TM - contention ORDERS
56 SQL*Net message from client ORDERS
2 rows selected.COMMIT;Commit complete.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
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;Table created.
5 rows created.
Commit complete.UPDATE orders SET status = 'PAID' WHERE order_id = 1;1 row updated.SELECT order_id, status FROM orders WHERE order_id = 1 FOR UPDATE NOWAIT;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.
UPDATE orders SET status = 'PAID' WHERE order_id = 1;1 row updated.SELECT order_id, status FROM orders
WHERE status = 'NEW'
ORDER BY order_id
FOR UPDATE SKIP LOCKED; 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
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;Table created.
5 rows created.
Commit complete.INSERT INTO orders VALUES (6, 'NEW', 600);1 row created.CREATE INDEX orders_status_ix ON orders (status);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.
INSERT INTO orders VALUES (6, 'NEW', 600);1 row created.CREATE INDEX orders_status_ix ON orders (status) ONLINE;COMMIT;Commit complete.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