ORA-00054 means a statement needed a lock that another session holds, and was told not to wait for it, or waited and gave up. It appears with SELECT FOR UPDATE NOWAIT, with LOCK TABLE ... NOWAIT, and with DDL such as TRUNCATE or ALTER TABLE on a table that has uncommitted changes. In Oracle AI Database 26ai the message is longer: it names the lock type and, for row locks, adds ORA-40097 with the holding transaction.
Reproduce the Error
The error needs two sessions. First, a table with two seats:
Example:
create table demo_seats (seat varchar2(4) primary key, passenger varchar2(30));
insert into demo_seats values ('12A', null);
insert into demo_seats values ('12B', null);
commit;Session 1 updates seat 12A and keeps its transaction open for a minute:
Example:
-- session 1: lock seat 12A and keep the transaction open update demo_seats set passenger = 'Ana' where seat = '12A'; exec dbms_session.sleep(60) rollback;
Meanwhile, session 2 tries to lock the same row, and the table:
Example:
-- session 2, while session 1 holds the lock select * from demo_seats where seat = '12A' for update nowait; truncate table demo_seats; lock table demo_seats in exclusive mode nowait;
Output:
Error starting at line : 2
In command -
select * from demo_seats where seat = '12A' for update nowait
Error at Command Line : 2 Column : 15
Error report -
SQL Error: 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 1179649 ("usn<<16 | slot") and 1665 ("sequence")
ORA-40097: Failure to acquire row lock, currently held by transaction ID
18.1.1665
Error starting at line : 3
In command -
truncate table demo_seats
Error report -
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 98513 ("Table") and 0
("operation")
Error starting at line : 4
In command -
lock table demo_seats in exclusive mode nowait
Error report -
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 98513 ("Table") and 0
("operation")- FOR UPDATE NOWAIT fails on the row lock (type TX) held by session 1.
- TRUNCATE and LOCK TABLE need the table lock (type TM), which session 1's open DML blocks.
Fix: Wait, or Skip Locked Rows
Example:
-- session 2: wait up to 3 seconds instead of failing at once select * from demo_seats where seat = '12A' for update wait 3; -- or lock only the rows that are free select seat from demo_seats for update skip locked; rollback;
Output:
Error starting at line : 2
In command -
select * from demo_seats where seat = '12A' for update wait 3
Error at Command Line : 2 Column : 15
Error report -
SQL Error: 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 1179649 ("usn<<16 | slot") and 1665 ("sequence")
ORA-40097: Failure to acquire row lock, currently held by transaction ID
18.1.1665
SEAT
_______
12B
Rollback complete.- WAIT 3 waits up to three seconds for the lock. Here session 1 kept it longer, so the error came after the wait; a shorter transaction would have released it in time.
- SKIP LOCKED locks only the free rows and returns 12B, which suits queue processing, where each worker takes rows nobody else holds.
Fix the Cause
- Find the session that holds the lock in V$LOCKED_OBJECT and V$SESSION, and commit or roll back that transaction; an open transaction in an idle tool is a common cause.
- Run DDL in a maintenance window, or let it wait with ALTER SESSION SET DDL_LOCK_TIMEOUT = 30.
- Keep transactions short: commit when the work is done, not later.
Related Guides
Conclusion
ORA-00054 means another session holds the lock the statement needs. Use WAIT or SKIP LOCKED where waiting or skipping is acceptable, set DDL_LOCK_TIMEOUT for DDL, and find and end the transaction that holds the lock.
