An application can fail while its database transaction still has unfinished work. Oracle must reverse the uncommitted changes, finish the transaction, and release held resources. An administrator establishes completion by following the database evidence and checking whether affected work can proceed.
Oracle DBA Lesson 14 Part 1 — What Remains After a Session Fails?
An interrupted update
Suppose a row initially contains a salary of 5000. A user changes it to 6000 and leaves the transaction uncommitted. The transaction holds a row lock. A second session attempting to update that same row waits for the first transaction to finish. Ordinary readers can obtain a read-consistent value using undo. The example's waiting session is another writer.
Now the application fails abnormally. Failure detection and cleanup have their own timing. A network interruption can leave the database unaware of the failure for a while. The case here assumes an ordinary local transaction whose update has not been committed, without application replay. A connection error during a commit requires investigating the actual commit outcome before deciding whether to retry.
Once Oracle detects the failure, it can recover the abandoned transaction. Seeing the application disappear is the beginning of the investigation. The administrator follows the remaining transaction and blocking evidence through completion.
Who handles the cleanup?
In Oracle Database 19c, the process monitor group has several responsibilities:
| Process | Role in this explanation |
|---|---|
PMON | Detects abnormal process termination and delegates cleanup. |
CLMN | The cleanup main process. It handles and coordinates cleanup work. |
CLnn | Cleanup helper processes that assist with the work. |
LREG | Registers instance and service information with the listener. This is a separate responsibility. |
The cleanup group recovers abandoned work and releases held resources, including locks and process and session resources. Cleanup can take time, particularly when there is substantial work or cleanup itself is blocked. The process allocation described here follows current 19c documentation. Older material may attribute listener registration to PMON. Oracle moved that responsibility to LREG beginning with 12c.
See process architecture.
How undo reverses the change
Undo contains earlier information that Oracle can use to reverse a transaction's changes. In the salary example, the relevant undo preserves the earlier value, 5000. Rollback applies that information and reverses the change from 6000 to 5000.
The database buffer cache, inside the SGA, holds working copies of database blocks, including data and undo blocks. Table data and permanent-object undo have physical storage in data files on disk. An undo tablespace contains undo data files. The video shows applying the earlier information to a working data block. The undo source remains available. It is a conceptual application of undo, rather than moving an entire physical file.
The storage diagram identifies locations. It does not imply that every modification reaches a data file immediately, or that rollback waits for all restored blocks to be written to disk. The database writer handles buffer writes separately.
As the transaction ends, its transaction locks are released. The waiting writer can then proceed, subject to any other current blockers. A transaction that changed many rows can have substantial rollback work. Reversal of one illustrated row makes the mechanism visible. Completion of the whole transaction is the operational condition being checked.
See transactions, memory architecture, and logical storage structures.
Connect a session to its transaction
Use a monitoring connection in the same database instance and container as the application session.
SELECT s.sid, s.serial#, s.username, s.status,
t.used_ublk, t.used_urec
FROM v$session s
LEFT JOIN v$transaction t ON t.addr = s.taddr
WHERE s.username = 'APP_USER'
ORDER BY s.sid;The query connects V$SESSION.TADDR, the session's transaction address, to V$TRANSACTION.ADDR, the transaction state object's address. V$TRANSACTION lists active transactions. The left join retains sessions even when it finds no matching transaction. Their selected undo columns are then null.
Interpret the columns together:
| Column | What it tells you |
|---|---|
SID and SERIAL# | The session identity. Record both, because a later session can reuse a SID. Keep the instance, container, and application context as well. |
USERNAME | The database account. Several sessions can share it. |
STATUS | Session state. KILLED means marked for termination. |
USED_UBLK | Number of undo blocks currently used by the transaction. |
USED_UREC | Number of undo records currently used by the transaction. |
Take repeated observations to understand changes in undo usage. The counters alone do not provide an exact rollback completion time. A transaction row can disappear between observations, and a session row can remain after its transaction has ended.
An empty join result needs context: perhaps the session has ended, the account filter is wrong, or the connection is observing a different container. Null undo columns describe this snapshot's missing transaction match. Correlate the intended session with the remaining checks.
See V$SESSION and V$TRANSACTION.
Check waiting sessions
Inspect sessions currently reporting the investigated SID as their blocker:
SELECT sid, serial#, event,
blocking_session_status,
blocking_instance, blocking_session
FROM v$session
WHERE blocking_session_status = 'VALID'
AND blocking_session = &holder_sid;Enter the current holder SID that you identified with its serial number and context. A VALID blocking status makes BLOCKING_INSTANCE and BLOCKING_SESSION meaningful. Other states include NO HOLDER, NOT IN WAIT, and UNKNOWN. The wait event helps explain the wait. Follow the current chain when another blocker appears. In RAC, use instance-aware evidence such as GV$SESSION and match the holder's instance too.
After rollback, check that the intended transaction has ended, that the original row value is restored, and that affected waiting work can proceed. A KILLED label by itself supplies only termination status. The practical conclusion comes from the transaction, the application data, and the blocking evidence together.
See V$SESSION and session termination.
Keep the failure boundary clear
This lesson follows one failed session while the instance remains running. When an entire instance fails, SMON coordinates instance recovery at startup. That recovery involves its own redo and undo sequence. Part 2 of Lesson 14 will address a transaction spanning databases whose coordinated outcome is unresolved.
See process architecture.
Observe transaction completion
Two SQL*Plus connections are sufficient: the owner and a monitor. Autocommit must be off.
-
In the owner connection, confirm the baseline:
SELECT employee_id, salary FROM cleanup_demo WHERE employee_id = 1; -
Update exactly the row, leaving it uncommitted:
UPDATE cleanup_demo SET salary = 6000 WHERE employee_id = 1;Confirm that one intended row was affected. Keep the owner connection open.
-
In the monitor, run the session and transaction query above. Identify the owner connection using SID, serial number, and known session context. Observe the undo columns. Several account sessions may appear.
-
In the owner, explicitly finish by reversing the change:
ROLLBACK; -
Recheck the owner row and the monitor query. The salary should be
5000, and the intended session should have no active transaction match, provided it has started no new modifying transaction.
DDL and a normal exit can commit pending work. This exercise observes the transactional evidence with explicit rollback.
Recap
An unfinished transaction can remain after the application fails. Oracle reverses the uncommitted changes with undo, finishes the transaction, and releases held locks and process and session resources. In Oracle Database 19c, PMON detects abnormal process termination and delegates cleanup. CLMN coordinates that work, and CLnn helpers assist. LREG registers the instance and its services with the listener. Oracle moved that responsibility from PMON to LREG beginning with 12c. Undo preserves the earlier value, and rollback applies it, so the salary example returns from 6000 to 5000. The buffer cache in the SGA holds the working blocks. Data files and the undo tablespace store table data and permanent undo. Rollback does not wait for every restored block to be written to disk. Join V$SESSION to V$TRANSACTION on the transaction address, and keep sessions that have no transaction match. Record SID with SERIAL#. Take repeated observations of USED_UBLK and USED_UREC. The counters alone do not provide an exact rollback completion time. KILLED only marks termination. Treat BLOCKING_SESSION as an identified blocker when BLOCKING_SESSION_STATUS is VALID, with the current identity and instance. This lesson follows one failed session while the instance stays up. If the instance fails, SMON coordinates recovery at startup.
No comments:
Post a Comment