Quickly Diagnose Oracle Lock Contention with AWK and ass109.awk

Background

In early Oracle versions, lock and deadlock problems were common due to immature technology. Traditional remedies were either restarting the database or performing detailed analysis ("考究"). Restarting is simple but ineffective for application bugs, large rollbacks, and often requires high‑level approval.

Traditional Analysis

Conventional method dumps the entire memory state to a trace file and manually inspects the interactions between processes, which can involve thousands of lines and is time‑consuming.

Using AWK for Fast Trace Analysis

Linux provides powerful text‑processing tools such as AWK and SED. The community created ass109.awk, an AWK script that parses Oracle trace files quickly.

Test Environment

Oracle 11gR2 (11.2.0.4) was used. Two sessions (SID 319 and 479) were made to delete the same row, causing a lock.

-- Session 1
create table t as select object_id from dba_objects;
select sid from v$mystat where rownum<2;
delete t where object_id=1000;

-- Session 2
select sid from v$mystat where rownum<2;
delete t where object_id=1000;  -- hangs

Collecting the Trace

The oradebug utility was used to dump the system state and locate the trace file:

oradebug setmypid
oradebug dump systemstate 10
oradebug tracefile_name

The trace file

/u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_8344.trc

and the ass109.awk script reside in the same directory.

Running the AWK Script

Executing the script produces a concise list of wait events and blockers. awk -f ass109.awk testdb_ora_8344.trc The output shows that process 27 is waiting for enq: TX - row lock contention while process 26 holds the enqueue TX-00060002-00000611.

Mapping to Sessions

Querying v$process and v$session reveals the corresponding sessions:

select * from v$process where pid in (26,27);
select sid, serial#, paddr, event from v$session where paddr in ('00000001339FDBD0','00000001339FEC88');

Session 319 (SID 319) is waiting on SQL*Net message from client, and session 479 (SID 479) is waiting on enq: TX - row lock contention, confirming the lock contention between the two sessions.

Conclusion

Database lock contention is common in production environments. Using the ass109.awk script together with AWK allows rapid identification of the blocking and waiting sessions, enabling faster problem resolution.