ExamFinch.com My Study

Study guide

10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

Key Takeaways Explain This Topic AI

A database transaction in Oracle begins implicitly with the first executable DML statement and ends explicitly with COMMIT or ROLLBACK, or implicitly via DDL/DCL execution or session termination.

Any DDL command (such as CREATE, ALTER, DROP, TRUNCATE) automatically executes an implicit COMMIT both immediately before and immediately after its execution.

The SAVEPOINT statement establishes a named marker within a transaction, allowing partial rollback via ROLLBACK TO SAVEPOINT without ending the transaction or releasing locks acquired before the savepoint.

Oracle's Multi-Version Concurrency Control (MVCC) ensures statement-level read consistency using System Change Numbers (SCN) and Undo segments, guaranteeing that readers never block writers and writers never block readers.

The SELECT ... FOR UPDATE statement explicitly acquires exclusive row-level locks on queried records, holding them until transaction termination (COMMIT or ROLLBACK) to enforce pessimistic concurrency control.

Last updated: August 2026

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:

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!

Any DCL Statement is Executed: Statements like GRANT or REVOKE issue an implicit commit.

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:

Abnormal Session Disconnection: Network dropouts, killing the terminal window, or client application crashes.

Session Killed by Administrator: DBA executing ALTER SYSTEM KILL SESSION .

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:

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.

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!

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 .

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.