Why Oracle OOM Happens: Hidden Lock Leak and How to Diagnose It

What is OOM?

OOM stands for Out Of Memory and originates from the Java exception java.lang.OutOfMemoryError. It is thrown when the JVM cannot allocate an object because both the heap is exhausted and the garbage collector cannot free additional memory.

Case Analysis

Environment : Linux operating system with an Oracle database.

Symptoms : Gradual increase in memory usage, high CPU consumption due to frequent Full GC, and overall system slowdown despite the process not fully crashing.

Root Cause : A slow memory leak was traced to the Oracle JDBC driver JAR. A thread continuously executed an UPDATE statement that never completed because the underlying table was locked.

Explanation : The application used SELECT ... FOR UPDATE without committing, which placed a table‑level shared lock on the entire table. This prevented other sessions from acquiring the lock, causing their statements to wait indefinitely and eventually exhausting memory.

Conclusion : The OOM was caused by a lock‑induced memory leak. Key points:

Slow memory leak in the Oracle driver.

Lock held by a long‑running update SQL.

For‑update without commit caused a table‑level shared lock.

Oracle Lock Types and Diagnostic SQL

To inspect current locks you can run the following query (wrap the whole statement in a <code>...</code> block):

select s.SID,
       s.SERIAL#,
       s.MACHINE,
       s.TYPE,
       l.TYPE,
       l.CTIME,
       l.BLOCK,
       l.REQUEST,
       l.LMODE,
       decode(l.lmode,
              0,'None',
              1,'Null',
              2,'Row‑S (SS)',
              3,'Row‑X (SX)',
              4,'Share',
              5,'S/Row‑X (SSX)',
              6,'Exclusive') as "Locked Mode",
       DECODE(L.TYPE,'MR','File_ID:'||L.ID1,'TM',t.NAME,'TX','USN:'||to_char(TRUNC(L.ID1/65536))||'RWO:'||nvl(r.NAME,'None'),
              L.ID1) as LOCK_ID1,
       'alter system kill session '''||s.SID||','||s.SERIAL#||''' ;' as "Kill"
from v$process p
inner join v$session s on s.PADDR = p.ADDR
inner join v$lock l on l.SID = s.SID
left join sys.obj$ t on l.ID1 = t.obj#
left join sys.obj$ r on s.ROW_WAIT_OBJ# = r.obj#
where l.TYPE != 'MR'
order by s.SID;

The result includes a column with the generated ALTER SYSTEM KILL SESSION command, which can be copied and executed to release the offending lock.

Lock Types

Oracle uses several lock categories:

TX (Row‑level transaction lock) : Exclusive mode (6) required for data modifications; released on commit/rollback.

TM (Table‑level lock) : Acquired automatically for DML; shared mode for inserts/updates/deletes, exclusive for DDL.

ST (Space transaction lock) : One per database, exclusive for tablespace creation/deletion.

Lock Modes

None (0)

Row Share (RS)

Row Exclusive (RX)

Share (S)

Share Row Exclusive (SRX)

Exclusive (X)

Higher numeric values indicate stronger locks that affect more operations. For example, SELECT ... FOR UPDATE acquires a mode‑2 lock, while DML statements typically acquire mode‑3 locks.

Best‑Practice Recommendations

Avoid long‑running SELECT ... FOR UPDATE without timely commit.

Use proper transaction boundaries to release TX locks promptly.

Consider increasing INITRANS for tables that experience TX‑4 contention.

Prefer true temporary tablespaces to reduce ST lock contention.

When a lock problem is identified, use the generated ALTER SYSTEM KILL SESSION command rather than killing OS processes.

By understanding lock types, modes, and their impact on memory usage, developers and DBAs can prevent hidden memory leaks that lead to Out‑Of‑Memory errors.