Oracle Row-Level Locking ↔ Distributed Transactions: Lock Release Timing

Answer

Yes – in an Oracle XA distributed transaction the row‑level lock (and the ITL slot that protects it) is **not** released after the phase‑one xa_prepare. The lock persists for the entire "prepared" (in‑doubt) interval and is released only when the transaction manager later issues the global xa_commit or xa_rollback to the local branch.

Confirmed facts (from Oracle documentation and testing)

• Locks remain held after xa_prepare; they survive instance crash and recovery, appearing in DBA_2PC_PENDING until the coordinator resolves the branch.
• SELECT … FOR UPDATE NOWAIT raises ORA-00054 whenever the lock cannot be obtained immediately, irrespective of whether the holder is a normal active transaction, an active XA branch, or a prepared XA branch. The error does not indicate the lock’s source.
• The duration of the prepared state is controlled by the external transaction manager (network latency, coordinator failure, heuristic decisions), so lock hold times can vary widely and are not tied to database‑internal activity.

Likely explanation for "unpredictable lock durations"

The window between xa_prepare and the final xa_commit/xa_rollback is dictated by the transaction manager’s coordination logic. If the coordinator is slow, unreachable, or decides heuristically, the lock may be held for seconds, minutes, or longer.

Steps to verify the lock‑release timing in your environment

1. Start an XA branch (via your TM or XA client), update a row, and issue xa_prepare.
2. From a second session run SELECT … FOR UPDATE NOWAIT on the same row; you should see ORA-00054.
3. While the branch is prepared, query the metadata to confirm the lock holder:

   SELECT * FROM DBA_2PC_PENDING WHERE LOCAL_TRAN_ID = :xid;

4. Join V$LOCK / V$TRANSACTION with V$SESSION to see the enqueue; note that the preparing branch will have no active session attached.
5. Issue the global xa_commit (or xa_rollback) from the transaction manager.
6. Re‑run the SELECT … FOR UPDATE NOWAIT; it should now succeed immediately, showing the lock was released at the global decision point.

What to check if you need to distinguish the blocker

Since NOWAIT does not differentiate lock sources, you must rely on the data dictionary views:

• DBA_2PC_PENDING – shows prepared/in‑doubt branches.
• V$LOCK with TYPE='TX' – identifies the transaction holding the row lock.
• V$SESSION – if a session is attached, it is a normal active transaction; absence of a session indicates a prepared XA branch.

If you observe a lock held by a branch with no attached session and an entry in DBA_2PC_PENDING, the blocker is a prepared distributed transaction.