I have been a database performance enthusiast for a long time, and I have spent a lot of time looking at Oracle database trace files. Depending on the level of database activity, this can become a time-intensive operation. These days, things might be easier, provided you are authorised to use AI tools for troubleshooting, which this post is all about.

A customer asked me to provide additional details about and ORA-54 error (failed to acquire lock) in very specific circumstances.

I reproduced the problem on my local playground environment featuring Oracle AI Database 26ai Ffree. The trace command is inspired by Tanel Poder and its use has been demonstrated by Jonathan Lewis.

ALTER SESSION SET EVENTS 
'54 [sql: 88kd1c3rgxnyz] trace name errorstack level 1; name systemstate level 2, lifetime 1';

Please do me a favour and don’t blindly run this command! Raise a service request with Oracle Support to get its blessing first.

Reproducing the error and forcing trace file generation

Now let’s see if we can reproduce the error and generate a trace file.

Session 1 starts by selecting a row from a table in update mode.

select
  object_id 
from
  t1
where
  object_id = 140
for update nowait;

I know that this SQL command, issued by session 2, will trigger an ORA-54 error. If I want to catch the details, I have to enable tracing first, before executing the select … for update:

alter session set events '54 [sql: 88kd1c3rgxnyz] trace name errorstack level 1; name systemstate level 2, lifetime 1';
select
  object_id 
from
  t1
where
  object_id = 140
for update nowait;

As expected, I get the ORA-54:

*
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 524315 ("usn<<16 | slot") and 1825 ("sequence")
ORA-40097: Failure to acquire row lock, currently held by transaction ID
8.27.1825
Help: https://docs.oracle.com/error-help/db/ora-00054/

Now let’s see if codex can help me analyse the tracefile:

SQL> select value from v$diag_info where name like 'Def%';
VALUE
--------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_176344.trc

I copied this trace to codex and had it analyse it. That’s not a problem for me, after all, it’s a synthetic workload without any reference to real world data. You on the other hand must ensure that you have your security department’s (or whoever else makes the call in your organisation) blessing to use AI tools on trace files. Never ever run AI tools on sensitive data like traces without approvals and proper procedures like anonymisation, redaction, etc.

Codex Analysis

Here’s what the codex session revealed, it’s rather remarkable. It started with my prompt:

You are an oracle tuning and performance expert. You know everything about traces, in particular system state dumps. Review the trace file in this directory and tell me what lead to the ORA-54 error. I want to know SQL_ID, SQL text, information about blocker and blocked session and more details about the lock held on the object. Plus details about the object, too

Codex came back with this. It was not allowed to use an MCP server to connect to the database, the entire analysis was performed offline. The following is verbatim codex output. The skills codex mentions are available free for anyone to use. See here for the repository, and here for installation instructions.

I’ll inspect the trace for the ORA-00054, then correlate the sessions, SQL, lock resources, and object details recorded in the dump. I’m using the Oracle Database skill for the analysis.

• Explored · 1 failed
  └ Read SKILL.md (db skill)
...
• Explored
  └ Read FREE_ora_176344.trc

The failing SQL is SELECT ... FOR UPDATE NOWAIT. Session 216 holds the transaction lock that caused session 217 to fail, and the blocker is idle at the SQL*Plus prompt with its transaction still open. I’m checking the object and row details next; both sessions also hold compatible table locks, so those are not the cause of this error.

Session 217, serial 21572, received ORA-00054 because session 216, serial 7991, already held a row lock on the row where T1.OBJECT_ID = 140. Both sessions executed the same SELECT ... FOR UPDATE NOWAIT. Session 216 acquired the lock and left its transaction open; session 217’s NOWAIT request failed immediately.

The error occurred on 29 October 2026 at 15:19:21.957 UTC, in FREEPDB1, container ID 3, instance FREE. The database is Oracle AI Database 26ai Free, version 23.26.3.0.0.

SQL involved

The failing SQL_ID is 88kd1c3rgxnyz:

select object_id from t1 where object_id = 140 for update nowait

The error stack explicitly identifies:

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", ...)
... 524315 ("usn<<16 | slot") and 1825 ("sequence")
ORA-40097: Failure to acquire row lock,
currently held by transaction ID 8.27.1825

Evidence: error stack and failing SQL (FREE_ora_176344.trc:38).

Crucially, the blocker’s transaction dump also explicitly says “Transaction started by SQLID (88kd1c3rgxnyz)”, and reproduces the same SQL. This establishes the lock-acquiring statement directly, rather than merely inferring it from the blocker’s most recent SQL. See blocker transaction (FREE_ora_176344.trc:13184).

Blocker and affected session

Sources: blocker session (FREE_ora_176344.trc:12533), affected session (FREE_ora_176344.trc:14780), server process mapping (FREE_ora_176344.trc:6623).

The blocker was waiting on SQL*Net message from client, with the current idle wait lasting approximately 2 minutes 4 seconds. Its open transaction began at 14:41:41 UTC, approximately 37 minutes 41 seconds before the dump. That is the transaction’s age; it is not a separately measured row-lock acquisition timestamp.

This is an idle session retaining a transaction, rather than a SQL statement that must finish executing before the row becomes available.

The locks and how they correlate

The blocker owns this transaction enqueue:

TX-0008001B-00000721-05059BEB-00000000
mode: X
own: 0x85a55738
sess: 0x85a55738

Its identifiers decode exactly to the resource reported in ORA-00054:

Evidence: held TX enqueue (FREE_ora_176344.trc:13213).

The row lock is represented in the data block; the TX enqueue provides the transaction resource against which another session would wait. Mode X here does not mean the blocker exclusively locked the entire table.

Both sessions also successfully hold:

TM-00013E50-00000000-05059BEB-00000000
mode: SX

This is a row-exclusive table lock, mode 3, on object 81488. These two SX table locks are compatible. The failure is the conflicting row lock associated with transaction 8.27.1825, not a conflict between the table locks.

Evidence: blocker TM lock (FREE_ora_176344.trc:13340), affected session TM lock (FREE_ora_176344.trc:15094). Oracle documents the table lock modes in V$LOCKED_OBJECT.

Object and physical row details

The blocker’s private redo contains:

KDO Op code: LKR
bdba: 0x06005653
itli: 2
tabn: 0 slot: 0 to: 2

Its undo record identifies:

objn: 81488 objd: 81488 tsn: 6

This is strong evidence of an actual row-lock operation (LKR), consistent with SELECT FOR UPDATE; an UPDATE is not required to explain this incident. See redo and undo records (FREE_ora_176344.trc:13272).

The affected session also has a dumped consistent-read copy of that same block. It contains ten rows; row slot 0 contains the Oracle NUMBER bytes c2 02 29, which decode to 140. Its CR metadata references transaction 0x8.1b.721, matching the blocker.

Because this is a CR copy, its cleared ITL entries and row lock bytes do not contradict the active lock shown in the blocker’s transaction and redo. See buffer metadata and row dump (FREE_ora_176344.trc:15981).

The likely qualified name is EMILY.T1, but the trace does not provide an explicit dictionary mapping confirming the owner of object 81488. Nor does it establish the tablespace name, datafile path, full table definition, or indexes. Those require a dictionary lookup in FREEPDB1, for example:

select owner, object_name, subobject_name, object_type,
       object_id, data_object_id
from dba_objects
where object_id = 81488;

What resolves it

Session 216,7991 must end its locking transaction with the appropriate COMMIT or ROLLBACK. Session 217 can then retry.

If immediate failure is undesirable, the application can use FOR UPDATE WAIT n; SKIP LOCKED is appropriate only when skipping this row is acceptable. DDL_LOCK_TIMEOUT does not address this SELECT FOR UPDATE NOWAIT failure.

There is no sustained TX wait to measure for session 217: NOWAIT caused immediate rejection, which also explains why it is not shown waiting on enq: TX - row lock contention in the snapshot. Oracle’s ORA-00054 documentation confirms the error identifies a lock resource already held by another session.

This analysis is based on the trace snapshot; I have not queried or changed the database.

Summary

The codex output is rather complete … and it took very little time to generate. So for me, as a DBA or performance analyst, this is a great time saver. Not only does it provide a comprehensive analysis providing enough evidence for further investigation, it also points you to the line in the trace file where it deduced its findings from. If you use the GUI app or the VSCode plugin (for example), you’ll be able to click on hyperlinked line numbers and jump straight into the trace file.

Honestly, if this isn’t a time saver and productivity gain, what is 😀

… If it weren’t for the approvals you need to get, before passing sensitive information into an LLM via a coding agent like codex. Always make sure you operate within the limits set by the authoritative party in your organsation.