Oracle中Select For UPDATE锁在连接异常、超时或宕机时是否自动释放？

1. Do SELECT FOR UPDATE locks release when connections drop, time out, or servers crash? #

Short answer: It depends on whether Oracle detects the dead session immediately.

Locks from SELECT FOR UPDATE are tied directly to the database session that ran the query. Here's the breakdown:

• If the session is closed cleanly (via COMMIT/ROLLBACK, or the app explicitly closes the connection), locks are released right away.
• If the connection drops abruptly (network failure, server kill, crash), Oracle doesn't instantly know the session is dead. By default, it waits for the session to send some activity before realizing it's gone—this can take minutes or hours.
• To fix this delay, Oracle has Dead Connection Detection (DCD). If enabled, it periodically checks if the client is still reachable and cleans up stale sessions quickly, releasing locks in the process.
• Note: Using SELECT FOR UPDATE WAIT <n> only affects how long the query waits to acquire the lock—not how long it holds the lock once acquired.

2. Why aren't locks releasing after force-terminating my EJB server (no commit/rollback)? #

This is a common pain point with app servers and connection pooling. Here are the most likely culprits:

Connection Pooling Leftovers #

EJB containers use connection pools to reuse database connections. When you force-terminate the server, the pool doesn't get a chance to cleanly close the underlying database sessions. From Oracle's perspective, those sessions are still "active"—so locks stay held until Oracle finally detects the session is dead (which could take a while without DCD).

Missing Dead Connection Detection (DCD) #

If your Oracle server isn't configured with DCD, it has no way to proactively check if the client (your EJB server) is still alive. Stale sessions can linger indefinitely, holding locks until you manually kill them or restart the database. To enable DCD, add SQLNET.EXPIRE_TIME = 10 (checks every 10 seconds) to your server's sqlnet.ora file.

Unresolved Distributed Transactions #

If your EJB was using JTA (distributed transactions), the transaction might be in an incomplete state. Oracle will hold locks until the transaction manager either resolves it (commit/rollback) or the transaction times out (check your EJB container's transaction timeout settings).

Troubleshooting Steps: #

• Find and kill the stuck session: Run this query to identify the session holding the lock:

  SELECT s.sid, s.serial#, l.lock_type, l.mode 
  FROM v$session s 
  JOIN v$lock l ON s.sid = l.sid 
  WHERE l.type = 'TX';
  

  Then kill it with:

  ALTER SYSTEM KILL SESSION '<sid, serial#>';
  

• Tweak connection pool settings: Ensure your EJB container's pool validates connections before reuse and has an idle connection timeout. This helps clean up stale connections faster.
• Add error handling in code: Wrap your EJB logic in try-catch blocks to ensure transactions are rolled back if the server crashes or an error occurs (use ctx.setRollbackOnly() when needed).

内容的提问来源于stack exchange，提问作者Ponmanikandan Boothalingam