10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

In relational database management systems, a transaction is a logical, atomic unit of work comprising one or more SQL statements that must either all succeed or all fail together. Transaction management ensures data integrity, consistency, and recoverability in multi-user database environments.

Oracle Database provides robust transaction control mechanisms grounded in the principles of ACID (Atomicity, Consistency, Isolation, Durability) and Multi-Version Concurrency Control (MVCC). Understanding transaction life cycles, explicit and implicit commit boundaries, SAVEPOINT mechanics, read consistency, and pessimistic row locking with SELECT ... FOR UPDATE is critical for building enterprise-grade applications and excelling on the 1Z0-071 examination.

---

ACID Properties in Oracle Database

Every transaction executed in Oracle Database adheres to the fundamental ACID properties:

+-----------------------------------------------------------------------------------+
|                              THE ACID PARADIGM                                    |
|                                                                                   |
|  [A] ATOMICITY    : "All or Nothing." All DML statements in a transaction are    |
|                     committed permanently or completely rolled back.              |
|                                                                                   |
|  [C] CONSISTENCY  : Transitions the database from one valid state to another,     |
|                     enforcing all schema integrity constraints and business rules.|
|                                                                                   |
|  [I] ISOLATION    : Uncommitted changes made by one session are invisible to other|
|                     concurrent sessions (preventing dirty reads).                 |
|                                                                                   |
|  [D] DURABILITY   : Once committed, changes are permanently recorded in redo log  |
|                     files and survive power failures or instance crashes.         |
+-----------------------------------------------------------------------------------+

---

Transaction Life Cycle and Boundaries

In Oracle SQL, transactions do not require an explicit BEGIN TRANSACTION statement. Instead, transactions start and end based on strict boundary rules.

+-----------------------------------------------------------------------------------+
|                            TRANSACTION LIFE CYCLE FLOW                            |
|                                                                                   |
|  START: First Executable DML Statement                                            |
|         (INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE)                 |
|                                |                                                  |
|                                v                                                  |
|         [Active Transaction State: DML Changes in SGA / Undo Blocks]              |
|         [Row-level exclusive locks held; changes private to session]              |
|                                |                                                  |
|        +-----------------------+-----------------------+                          |
|        |                                               |                          |
|        v                                               v                          |
|  SUCCESSFUL CONCLUSION:                          ABNORMAL CONCLUSION:             |
|  1. Explicit COMMIT;                             1. Explicit ROLLBACK;            |
|  2. Implicit DDL (CREATE, ALTER, TRUNCATE...)    2. Session Crash / Network Loss  |
|  3. Implicit DCL (GRANT, REVOKE)                 3. Instance / Server Failure     |
|  4. Normal Client Exit (DISCONNECT, EXIT)        4. Session Killed by DBA         |
|        |                                               |                          |
|        v                                               v                          |
|  [Changes Permanent; Locks Released]             [Changes Undone; Locks Released] |
+-----------------------------------------------------------------------------------+

1. How a Transaction Begins

A transaction begins automatically when the session executes its first executable DML statement (INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE).

2. Explicit Transaction Termination

• COMMIT [WORK];: Makes all pending data changes made during the transaction permanent, writes redo buffer entries to disk (LGWR), releases all row and table locks, and erases all savepoints.
• ROLLBACK [WORK];: Discards all pending data changes made during the transaction using undo segment data, restores original values, releases all row and table locks, and erases all savepoints.

(Note: The keyword WORK is optional in both COMMIT WORK and ROLLBACK WORK and has no syntactic effect).

3. Implicit Transaction Termination (Auto-Commit vs Auto-Rollback)

Implicit COMMIT Occurs When:

1. Any DDL Statement is Executed: Statements like CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, or RENAME issue an implicit COMMIT immediately before and immediately after execution. Even if the DDL statement fails with an error, the preceding implicit commit has already committed all prior DML work!
2. Any DCL Statement is Executed: Statements like GRANT or REVOKE issue an implicit commit.
3. Normal / Graceful Session Termination: Disconnecting cleanly from SQL*Plus, SQL Developer, or SQLcl (e.g., typing EXIT, QUIT, or closing a connection cleanly) issues an automatic COMMIT.

Implicit ROLLBACK Occurs When:

1. Abnormal Session Disconnection: Network dropouts, killing the terminal window, or client application crashes.
2. Session Killed by Administrator: DBA executing ALTER SYSTEM KILL SESSION.
3. Database Instance Failure: Server power outage or OS crash (uncommitted transactions are rolled back during crash recovery by SMON).

4. Statement-Level Rollback vs. Transaction Rollback

If a single DML statement within an active transaction fails during execution (for example, violating a UNIQUE constraint ORA-00001 or CHECK constraint ORA-02290), Oracle executes an automatic statement-level rollback that undoes only the effects of that specific failed statement. All prior successfully executed DML statements in the transaction remain active and uncommitted.

---

The SAVEPOINT Statement & Partial Rollback

The SAVEPOINT command marks an intermediate savepoint marker within the context of an active transaction. This enables partial rollback, allowing an application to undo errors or optional logic without rolling back the entire transaction.

+-----------------------------------------------------------------------------------+
|                             SAVEPOINT EXECUTION TIMELINE                          |
|                                                                                   |
|  Time 0: UPDATE employees SET salary = 5000 WHERE employee_id = 101;              |
|  Time 1: SAVEPOINT sp_step1;                                                      |
|  Time 2: UPDATE employees SET salary = 7000 WHERE employee_id = 102;              |
|  Time 3: SAVEPOINT sp_step2;                                                      |
|  Time 4: DELETE FROM employees WHERE department_id = 50;                          |
|  Time 5: ROLLBACK TO SAVEPOINT sp_step1;                                          |
|                                                                                   |
|  STATE AT TIME 5:                                                                 |
|  - Time 4 DELETE is completely UNDONE.                                            |
|  - Time 2 UPDATE on emp 102 is completely UNDONE.                                 |
|  - Savepoint sp_step2 is ERASED from memory.                                      |
|  - Time 0 UPDATE on emp 101 REMAINS ACTIVE and UNCOMMITTED!                       |
|  - The transaction is STILL OPEN (Locks on emp 101 are STILL HELD).               |
+-----------------------------------------------------------------------------------+

-- Concrete SAVEPOINT and Partial Rollback Demonstration
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;

SAVEPOINT transfer_step1;

UPDATE accounts SET balance = balance + 500 WHERE account_id = 9999; -- Invalid Account ID!

-- Rolling back only the failed credit operation
ROLLBACK TO SAVEPOINT transfer_step1;

-- Rerouting transfer to correct account
UPDATE accounts SET balance = balance + 500 WHERE account_id = 1002;

-- Finalizing all changes permanently
COMMIT;

Critical SAVEPOINT Rules Tested on 1Z0-071:

1. Does NOT End the Transaction: Rolling back to a savepoint (ROLLBACK TO SAVEPOINT sp_name;) does NOT end the transaction. The transaction remains open until an explicit COMMIT, full ROLLBACK, or DDL statement occurs.
2. Lock Retention: Rolling back to a savepoint releases locks acquired by statements executed after that savepoint, but retains all row and table locks acquired by statements executed before that savepoint!
3. Erasing Subsequent Savepoints: When you roll back to an earlier savepoint, all savepoints created after that savepoint are automatically invalidated and deleted from memory. Attempting to roll back to sp_step2 after rolling back to sp_step1 raises ORA-01086: savepoint 'SP_STEP2' never established in this session or is invalid.
4. Reusing Savepoint Names: If you declare a savepoint with an existing name (e.g., defining SAVEPOINT mark1; twice in the same transaction), Oracle moves the savepoint marker to the new position without raising an error.

---

Multi-Version Concurrency Control (MVCC) and Read Consistency

Oracle Database utilizes Multi-Version Concurrency Control (MVCC) to manage multi-user data access without concurrency bottlenecks.

+-----------------------------------------------------------------------------------+
|                         STATEMENT-LEVEL READ CONSISTENCY                          |
|                                                                                   |
|  Time T1 (SCN 1000): Session A starts: SELECT SUM(salary) FROM employees;         |
|  Time T2 (SCN 1005): Session B executes: UPDATE employees SET salary = 99999 ...  |
|  Time T3 (SCN 1006): Session B executes: COMMIT;                                  |
|  Time T4 (SCN 1010): Session A query is still reading table blocks...             |
|                                                                                   |
|  ORACLE BEHAVIOR:                                                                 |
|  - Session A query was initiated at SCN 1000.                                     |
|  - When Session A encounters blocks modified at SCN 1005, it reads the original   |
|    pre-image of those rows from UNDO SEGMENTS.                                    |
|  - Session A sees data AS IT EXISTED AT SCN 1000 (Guaranteed Read Consistency).   |
+-----------------------------------------------------------------------------------+

The Fundamental Concurrency Rules of Oracle SQL:

• Statement-Level Read Consistency: A single SELECT query always sees a completely consistent snapshot of data as it existed at the exact System Change Number (SCN) when the query started.
• Readers Never Block Writers: A session executing a SELECT query does not lock data blocks and never prevents concurrent sessions from updating or deleting those rows.
• Writers Never Block Readers: A session executing DML modifications (UPDATE, DELETE, INSERT) modifies data blocks and acquires exclusive row locks, but concurrent SELECT queries continue reading the pre-change image from Undo segments without waiting.
• Writers Only Block Writers: A transaction modifying a specific row blocks only other concurrent transactions that attempt to update or delete that exact same row.

---

Pessimistic Locking with SELECT ... FOR UPDATE

While Oracle defaults to optimistic non-blocking reads, enterprise applications often require pessimistic locking—locking rows before modifying them to guarantee that no other session can alter them during business calculations.

+-----------------------------------------------------------------------------------+
|                             SELECT ... FOR UPDATE SYNTAX                          |
|                                                                                   |
|  SELECT columns FROM tables [WHERE condition]                                     |
|  FOR UPDATE [OF [table.]column] [NOWAIT | WAIT integer | SKIP LOCKED];            |
+-----------------------------------------------------------------------------------+

Syntax Options and Execution Behaviors:

-- 1. Default FOR UPDATE (Indefinite Wait): Blocks and waits until locked rows are freed
SELECT employee_id, salary FROM employees WHERE department_id = 60 FOR UPDATE;

-- 2. NOWAIT: Immediately raises ORA-00054 if any row is locked by another session
SELECT employee_id, salary FROM employees WHERE department_id = 60 FOR UPDATE NOWAIT;

-- 3. WAIT n: Waits up to 'n' seconds before raising ORA-00054
SELECT employee_id, salary FROM employees WHERE department_id = 60 FOR UPDATE WAIT 10;

-- 4. SKIP LOCKED: Ignores locked rows and returns/locks only available rows (Queue Processing)
SELECT task_id, payload FROM task_queue WHERE status = 'READY' FOR UPDATE SKIP LOCKED;

-- 5. OF Clause (Multi-Table Joins): Restricts exclusive locking to specific tables
SELECT e.employee_id, d.department_name
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
WHERE e.department_id = 80
FOR UPDATE OF e.salary; -- Locks rows ONLY in the EMPLOYEES table, NOT DEPARTMENTS!

Locking Scope in Joins (OF Clause)

When a SELECT ... FOR UPDATE statement joins multiple tables without an OF clause, Oracle acquires row locks on all matching rows in all joined tables in the FROM list. Specifying FOR UPDATE OF table_name.column_name restricts the exclusive row locks strictly to the table containing that column.

Lock Duration

All row-level locks acquired by SELECT ... FOR UPDATE are held until the transaction terminates via an explicit COMMIT or ROLLBACK. Individual locks cannot be released independently.