Oracle COMMIT Statement: Behind the Scenes

When you execute COMMIT;, Oracle makes all your DML changes permanent. This seemingly simple statement triggers a complex series of operations involving redo logs, SCN allocation, lock releases, and checkpoint coordination. Understanding COMMIT is fundamental for database administrators and developers.

In this guide, we’ll explore what happens when you commit a transaction in Oracle Database.

The Complete COMMIT Execution Flow

┌─────────────────────────────────────────────────────────────────┐
│                    USER EXECUTES: COMMIT;                       │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 1: GENERATE COMMIT SCN                   │
│  • Allocate unique System Change Number                         │
│  • SCN marks point of transaction consistency                   │
│  • Incrementing sequence number                                 │
│  • Used for recovery and read consistency                       │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 2: WRITE REDO TO DISK                    │
│  • Force Redo Log Buffer flush to disk                          │
│  • LGWR (Log Writer) writes redo entries                        │
│  • Write COMMIT record to redo log files                        │
│  • This is the COMMIT POINT - point of no return                │
│  • Wait for disk write confirmation                             │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 3: MARK TRANSACTION COMPLETE             │
│  • Update transaction table in undo segment                     │
│  • Record commit SCN                                            │
│  • Mark transaction as committed                                │
│  • Free transaction slot                                        │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 4: RELEASE ALL LOCKS                     │
│  • Release TX (transaction) locks on rows                       │
│  • Release TM (table) locks                                     │
│  • Wake up waiting sessions                                     │
│  • Other users can now modify same rows                         │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 5: MARK UNDO INACTIVE                    │
│  • Undo segment marked as "inactive"                            │
│  • Space available for overwrite                                │
│  • Retained based on undo_retention                             │
│  • Used for read consistency and flashback                      │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 6: MAKE CHANGES VISIBLE                  │
│  • Changes visible to ALL sessions                              │
│  • Other users see committed data                               │
│  • Read consistency maintained via SCN                          │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 7: RETURN SUCCESS                        │
│  • Return "Commit complete" message                             │
│  • Transaction officially closed                                │
│  • Changes are PERMANENT                                        │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│            BACKGROUND PROCESS (ASYNCHRONOUS)                    │
│  • DBWR eventually writes dirty blocks to datafiles             │
│  • Happens independently of COMMIT                              │
│  • Changes already recoverable via redo logs                    │
└─────────────────────────────────────────────────────────────────┘

Key Concept: What COMMIT Does and Doesn’t Do

What COMMIT DOES:

✓ Writes redo log buffer to disk (LGWR)
✓ Generates and assigns commit SCN
✓ Releases all locks
✓ Makes changes visible to other sessions
✓ Marks transaction as complete
✓ Makes changes permanent and recoverable

What COMMIT DOES NOT DO:

✗ Does NOT write data blocks to datafiles immediately
✗ Does NOT write undo to disk immediately
✗ Does NOT clear buffer cache
✗ Does NOT wait for DBWR (Database Writer)
✗ Does NOT physically update table files right away

Critical Understanding:

┌────────────────────────────────────────────────────────┐
│              COMMIT GUARANTEES                         │
├────────────────────────────────────────────────────────┤
│                                                        │
│  After COMMIT returns successfully:                    │
│                                                        │
│  1. Changes are PERMANENT                              │
│  2. Changes are RECOVERABLE (via redo)                 │
│  3. Changes are VISIBLE to all users                   │
│                                                        │
│  Even if:                                              │
│  • Database crashes immediately after                  │
│  • Power failure occurs                                │
│  • Dirty blocks not yet written to disk                │
│                                                        │
│  Redo logs guarantee recovery!                         │
│                                                        │
└────────────────────────────────────────────────────────┘

Detailed Step-by-Step Breakdown

Step 1: Generate Commit SCN

Oracle assigns a unique System Change Number to mark the transaction’s commit point.

SCN (System Change Number):

What is SCN?
┌────────────────────────────────────────┐
│  • Ever-increasing sequence number     │
│  • Unique identifier for every change  │
│  • Used for:                           │
│    - Transaction ordering              │
│    - Read consistency                  │
│    - Recovery operations               │
│    - Flashback queries                 │
│    - Replication                       │
└────────────────────────────────────────┘

Example SCN Timeline:
SCN: 1234567 → INSERT executed
SCN: 1234568 → UPDATE executed
SCN: 1234569 → DELETE executed
SCN: 1234570 → COMMIT (all changes at this SCN)

View Current SCN:

-- Get current database SCN
SELECT CURRENT_SCN FROM V$DATABASE;

-- Get SCN from timestamp
SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;

-- Get timestamp from SCN
SELECT SCN_TO_TIMESTAMP(1234567) FROM DUAL;

Transaction Example:

-- Session 1
INSERT INTO employees VALUES (101, 'John', 'Doe', 50000);
UPDATE employees SET salary = 55000 WHERE employee_id = 100;
DELETE FROM employees WHERE employee_id = 99;

-- Before COMMIT: Changes at SCN (not committed)
SELECT CURRENT_SCN FROM V$DATABASE;
-- Result: 1234569 (example)

COMMIT;

-- After COMMIT: All changes permanently at SCN 1234570
SELECT CURRENT_SCN FROM V$DATABASE;
-- Result: 1234570

Step 2: Write Redo to Disk (THE CRITICAL STEP)

This is the actual COMMIT point – once redo is on disk, changes are permanent.

Redo Log Buffer to Redo Log File:

BEFORE COMMIT:
┌────────────────────────────────────┐
│    Redo Log Buffer (Memory)        │
├────────────────────────────────────┤
│ • INSERT redo entry                │
│ • UPDATE redo entry                │
│ • DELETE redo entry                │
│ • NOT YET ON DISK                  │
└────────────────────────────────────┘
            │
            │ COMMIT issued
            ▼
┌────────────────────────────────────┐
│    LGWR Process Activated          │
│  • Writes all redo to disk         │
│  • Writes COMMIT record            │
│  • Synchronous operation           │
│  • MUST complete before returning  │
└────────────────────────────────────┘
            │
            ▼
┌────────────────────────────────────┐
│    Redo Log Files (Disk)           │
├────────────────────────────────────┤
│ • All transaction redo entries     │
│ • COMMIT record written            │
│ • Changes now RECOVERABLE          │
│ • COMMIT POINT reached             │
└────────────────────────────────────┘

LGWR (Log Writer) Process:

-- View LGWR statistics
SELECT name, value
FROM v$sysstat
WHERE name IN (
    'redo writes',
    'redo write time',
    'redo blocks written',
    'user commits'
)
ORDER BY name;

Redo Log Files:

SET LINESIZE 180
COLUMN member FORMAT A60

-- View redo log files
SELECT l.group#,
       l.thread#,
       l.sequence#,
       l.bytes/1024/1024 size_mb,
       l.status,
       l.archived,
       lf.member
FROM v$log l
JOIN v$logfile lf ON l.group# = lf.group#
ORDER BY l.group#;

-- Check current redo log
SELECT group#, 
       sequence#,
       bytes/1024/1024 size_mb,
       status
FROM v$log
WHERE status = 'CURRENT';

Why Redo Write is Critical:

-- Scenario: Crash immediately after COMMIT

-- Session executes:
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
COMMIT;
-- "Commit complete" returned to user

-- Database crashes 1 second later
-- Dirty blocks NOT yet written to datafiles

-- On restart:
-- Oracle uses redo logs to replay the UPDATE
-- Data is recovered - change is permanent!

-- This is possible because:
-- 1. Redo was written to disk BEFORE COMMIT returned
-- 2. Redo contains all necessary information
-- 3. Oracle replays redo during crash recovery

Step 3: Mark Transaction Complete

Transaction table in undo segment header is updated.

-- View active transactions
SELECT s.username,
       s.sid,
       s.serial#,
       t.start_time,
       t.status,
       t.used_ublk,
       t.log_io,
       t.phy_io
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr;

-- Before COMMIT: Transaction appears in v$transaction
-- After COMMIT: Transaction disappears from v$transaction

Step 4: Release All Locks

All locks held by the transaction are immediately released.

Lock Release Process:

BEFORE COMMIT:
┌────────────────────────────────────┐
│     Locked Resources               │
├────────────────────────────────────┤
│ • Row lock on employee_id=101      │
│ • Row lock on employee_id=102      │
│ • Table lock (RX mode)             │
│ • Other sessions WAITING           │
└────────────────────────────────────┘

COMMIT EXECUTED:
┌────────────────────────────────────┐
│     Lock Release                   │
├────────────────────────────────────┤
│ • All row locks released           │
│ • Table locks released             │
│ • Waiting sessions awakened        │
│ • Resources available immediately  │
└────────────────────────────────────┘

Example – Lock Release:

-- Session 1
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- Lock acquired, not committed yet

-- Session 2 (waiting)
UPDATE employees SET department_id = 20 WHERE employee_id = 101;
-- WAITS for Session 1

-- Session 1
COMMIT;
-- Lock released immediately

-- Session 2
-- Immediately proceeds with UPDATE
-- No longer waiting

Monitor Lock Releases:

SET LINESIZE 180
COLUMN username FORMAT A15
COLUMN object_name FORMAT A30

-- View locks BEFORE commit
SELECT s.username,
       s.sid,
       o.object_name,
       DECODE(l.lmode,
              0, 'None',
              1, 'Null',
              2, 'Row Share',
              3, 'Row Exclusive',
              4, 'Share',
              5, 'Share Row Excl',
              6, 'Exclusive') lock_mode
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE s.username IS NOT NULL;

-- After COMMIT: Locks disappear

Step 5: Mark Undo Inactive

Undo segments are marked as inactive but retained for read consistency.

Undo Lifecycle:

ACTIVE TRANSACTION:
┌────────────────────────────────────┐
│     Undo Segment                   │
│  Status: ACTIVE                    │
│  • Contains before images          │
│  • Used for ROLLBACK               │
│  • Cannot be overwritten           │
└────────────────────────────────────┘
            │
            │ COMMIT
            ▼
┌────────────────────────────────────┐
│     Undo Segment                   │
│  Status: INACTIVE                  │
│  • Still contains data             │
│  • Used for read consistency       │
│  • Used for flashback              │
│  • Can be overwritten after        │
│    undo_retention period           │
└────────────────────────────────────┘

Undo Retention:

-- Check undo retention setting
SHOW PARAMETER undo_retention;
-- Typically 900 seconds (15 minutes)

-- View undo usage
SELECT tablespace_name,
       status,
       ROUND(SUM(bytes)/1024/1024, 2) mb
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;

-- Active: Current transactions
-- Unexpired: Within undo_retention, available for read consistency
-- Expired: Beyond undo_retention, can be overwritten

Step 6: Make Changes Visible

After COMMIT, all other sessions can see the changes.

Read Consistency Example:

-- Session 1 (Time: 10:00:00)
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- Not committed, SCN = 1234567

-- Session 2 (Time: 10:00:05)
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 50000 (old value, Session 1 not committed)

-- Session 1 (Time: 10:00:10)
COMMIT;
-- Commit SCN = 1234570

-- Session 2 (Time: 10:00:15)
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 60000 (new value, now visible after commit)

SCN-based Read Consistency:

-- Query as of specific SCN (before commit)
SELECT salary 
FROM employees AS OF SCN 1234567
WHERE employee_id = 101;
-- Result: 50000 (before update)

-- Query current data (after commit)
SELECT salary 
FROM employees 
WHERE employee_id = 101;
-- Result: 60000 (after update and commit)

-- Query as of timestamp
SELECT salary
FROM employees AS OF TIMESTAMP (SYSDATE - 1/24)
WHERE employee_id = 101;
-- Result: Old value from 1 hour ago

Step 7: Return Success & Background Write

COMMIT returns success immediately after redo write. Data blocks written later.

Timeline:

Time 0ms:  User executes COMMIT
Time 5ms:  LGWR writes redo to disk
Time 6ms:  "Commit complete" returned to user
           ↑ COMMIT FINISHES HERE
           
Time 100ms: DBWR writes dirty blocks to datafiles (background)
Time 500ms: More dirty blocks written
Time 2s:    Checkpoint occurs
           
Note: User doesn't wait for DBWR!

Why COMMIT is Fast:

-- COMMIT only waits for redo write (small, sequential)
-- Does NOT wait for data block writes (large, random I/O)

-- Typical COMMIT times:
-- • Redo write: 1-10 milliseconds
-- • Data block write: 100-1000 milliseconds (but async!)

-- This is why COMMIT is fast even for large transactions

COMMIT Variations

1. Explicit COMMIT

-- Manual commit
INSERT INTO employees VALUES (101, 'John', 'Doe', 50000);
UPDATE employees SET salary = 55000 WHERE employee_id = 100;
COMMIT;
-- Explicit, controlled by user

2. Auto-COMMIT (DDL Statements)

-- DDL auto-commits
CREATE TABLE test_table (id NUMBER);
-- Automatically commits
-- Any pending DML is also committed!

-- Example:
INSERT INTO employees VALUES (101, 'John', 'Doe', 50000);
-- Not committed yet

CREATE INDEX emp_idx ON employees(last_name);
-- DDL auto-commits
-- INSERT is now also committed!

DDL Statements that Auto-COMMIT:

CREATE TABLE ...    -- Auto-commits
ALTER TABLE ...     -- Auto-commits
DROP TABLE ...      -- Auto-commits
TRUNCATE TABLE ...  -- Auto-commits
CREATE INDEX ...    -- Auto-commits
GRANT ...           -- Auto-commits

3. COMMIT WRITE Options

-- Default COMMIT (wait for redo write)
COMMIT;

-- COMMIT IMMEDIATE (wait for redo write) - default
COMMIT WRITE IMMEDIATE;

-- COMMIT BATCH (group redo writes for performance)
COMMIT WRITE BATCH;

-- COMMIT NOWAIT (don't wait for redo write confirmation)
-- Dangerous! Not recommended
COMMIT WRITE NOWAIT;

-- COMMIT WAIT (wait for redo write) - safest, default
COMMIT WRITE WAIT;

Performance Tuning:

-- For high-volume OLTP with acceptable risk:
ALTER SESSION SET COMMIT_WRITE = 'BATCH,NOWAIT';
-- Groups commits, doesn't wait for disk confirmation
-- Faster but risk of data loss on crash

-- For critical transactions:
ALTER SESSION SET COMMIT_WRITE = 'IMMEDIATE,WAIT';
-- Default, safest option

4. COMMIT FORCE (Distributed Transactions)

-- For distributed transactions in doubt
COMMIT FORCE 'transaction_id';
-- Used in distributed database scenarios
-- Requires DBA privileges

COMMIT Best Practices

1. OLTP: Frequent Commits

-- Web application: Commit after each user action
BEGIN
    INSERT INTO orders VALUES (...);
    INSERT INTO order_items VALUES (...);
    UPDATE inventory SET quantity = quantity - 1;
    COMMIT;  -- Commit each order
END;
/

2. Batch Processing: Periodic Commits

-- Batch job: Commit every N rows
BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO staging VALUES (...);
        
        IF MOD(i, 10000) = 0 THEN
            COMMIT;  -- Commit every 10,000 rows
        END IF;
    END LOOP;
    COMMIT;  -- Final commit
END;
/

3. Never COMMIT Inside Loops (Usually)

-- Bad: Commit every row
FOR emp IN (SELECT * FROM employees) LOOP
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp.employee_id;
    COMMIT;  -- Too many commits! Slow!
END LOOP;

-- Good: Batch commits
DECLARE
    v_count NUMBER := 0;
BEGIN
    FOR emp IN (SELECT * FROM employees) LOOP
        UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp.employee_id;
        v_count := v_count + 1;
        
        IF MOD(v_count, 1000) = 0 THEN
            COMMIT;  -- Commit every 1000 rows
        END IF;
    END LOOP;
    COMMIT;
END;
/

4. Consider Transaction Size

-- Too small: Excessive commits (overhead)
UPDATE employees SET salary = salary + 100;
COMMIT;
UPDATE employees SET bonus = 1000;
COMMIT;
UPDATE employees SET commission = 500;
COMMIT;

-- Better: Group related changes
UPDATE employees SET salary = salary + 100;
UPDATE employees SET bonus = 1000;
UPDATE employees SET commission = 500;
COMMIT;  -- Single commit for all

-- Too large: Huge undo, long locks
UPDATE large_table SET status = 'PROCESSED';  -- 10 million rows
COMMIT;

-- Better: Batch
LOOP
    UPDATE large_table SET status = 'PROCESSED' WHERE ROWNUM <= 100000;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
END LOOP;

5. Always COMMIT or ROLLBACK

-- Bad: Session ends without commit
BEGIN
    UPDATE employees SET salary = 60000;
    -- Session disconnects here
    -- Implicit ROLLBACK occurs!
END;

-- Good: Explicit commit
BEGIN
    UPDATE employees SET salary = 60000;
    COMMIT;  -- Changes saved
END;

Monitoring COMMIT Performance

COMMIT Statistics

SET LINESIZE 150
COLUMN name FORMAT A40
COLUMN value FORMAT 999,999,999

-- View commit statistics
SELECT name, value
FROM v$sysstat
WHERE name IN (
    'user commits',
    'transaction rollbacks',
    'redo writes',
    'redo write time',
    'redo sync time'
)
ORDER BY name;

-- Calculate average redo write time per commit
SELECT ROUND(redo_write_time.value / redo_writes.value, 2) avg_write_time_ms
FROM (SELECT value FROM v$sysstat WHERE name = 'redo write time') redo_write_time,
     (SELECT value FROM v$sysstat WHERE name = 'redo writes') redo_writes;

Wait Events Related to COMMIT

SET LINESIZE 180
COLUMN event FORMAT A40
COLUMN total_waits FORMAT 999,999,999

SELECT event,
       total_waits,
       ROUND(time_waited/100, 2) time_waited_sec,
       ROUND(average_wait/100, 4) avg_wait_sec
FROM v$system_event
WHERE event LIKE '%log%'
   OR event LIKE '%commit%'
ORDER BY time_waited DESC;

-- Key events:
-- log file sync: Wait for LGWR to write redo (COMMIT wait)
-- log file parallel write: LGWR writing to disk
-- log buffer space: Waiting for log buffer space

Session COMMIT Activity

SET LINESIZE 180
COLUMN username FORMAT A15
COLUMN commits FORMAT 999,999

SELECT s.username,
       s.sid,
       s.serial#,
       st.value commits
FROM v$session s
JOIN v$sesstat st ON s.sid = st.sid
JOIN v$statname sn ON st.statistic# = sn.statistic#
WHERE sn.name = 'user commits'
  AND s.username IS NOT NULL
ORDER BY commits DESC;

Common COMMIT Scenarios

Scenario 1: Multi-Table Transaction

-- All or nothing: COMMIT makes all permanent
BEGIN
    INSERT INTO orders VALUES (1001, SYSDATE, 'Customer A');
    INSERT INTO order_items VALUES (1, 1001, 'Product X', 10);
    INSERT INTO order_items VALUES (2, 1001, 'Product Y', 5);
    UPDATE inventory SET quantity = quantity - 10 WHERE product = 'Product X';
    UPDATE inventory SET quantity = quantity - 5 WHERE product = 'Product Y';
    
    COMMIT;  -- All changes permanent together
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- All changes discarded together
END;
/

Scenario 2: Long-Running Job

-- Batch processing with periodic commits
DECLARE
    v_batch_size NUMBER := 10000;
    v_total NUMBER := 0;
BEGIN
    FOR rec IN (SELECT * FROM source_table) LOOP
        INSERT INTO target_table VALUES (rec.col1, rec.col2, rec.col3);
        v_total := v_total + 1;
        
        IF MOD(v_total, v_batch_size) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('Committed ' || v_total || ' rows');
        END IF;
    END LOOP;
    
    COMMIT;  -- Final commit for remaining rows
    DBMS_OUTPUT.PUT_LINE('Total rows: ' || v_total);
END;
/

Scenario 3: Error Handling

-- Proper error handling with COMMIT/ROLLBACK
BEGIN
    -- Start transaction
    UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
    UPDATE accounts SET balance = balance + 1000 WHERE account_id = 102;
    
    -- Validation
    IF (SELECT balance FROM accounts WHERE account_id = 101) < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
    END IF;
    
    COMMIT;  -- Success
    DBMS_OUTPUT.PUT_LINE('Transfer successful');
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- Failure
        DBMS_OUTPUT.PUT_LINE('Transfer failed: ' || SQLERRM);
        RAISE;
END;
/

COMMIT vs ROLLBACK Comparison

Quick Reference

Basic COMMIT Syntax

-- Simple commit
COMMIT;

-- Commit with comment (for distributed transactions)
COMMIT COMMENT 'Order processing completed';

-- Commit with write options
COMMIT WRITE IMMEDIATE;
COMMIT WRITE BATCH;
COMMIT WRITE WAIT;
COMMIT WRITE NOWAIT;

Key COMMIT Queries

-- Check if uncommitted changes exist
SELECT COUNT(*) FROM v$transaction;
-- If > 0, uncommitted transactions exist

-- View current SCN
SELECT CURRENT_SCN FROM V$DATABASE;

-- View redo log status
SELECT group#, status, archived FROM v$log;

-- View commit performance
SELECT name, value FROM v$sysstat 
WHERE name LIKE '%commit%' OR name LIKE '%redo%';

Conclusion

COMMIT is the most critical statement in transaction management. It guarantees durability by writing redo to disk, releases locks to improve concurrency, and makes changes visible to all users.

Key Takeaways:

1. COMMIT writes redo, not data – Data blocks written asynchronously
2. Redo on disk = changes permanent – Even if crash occurs immediately
3. SCN marks consistency point – Used for recovery and read consistency
4. Locks released immediately – Improves concurrency
5. Fast operation – Only waits for redo write (sequential I/O)
6. Undo marked inactive – Retained for read consistency
7. DDL auto-commits – Be careful with pending DML
8. Batch commits for performance – Balance between safety and efficiency

Understanding COMMIT execution is essential for:

• Transaction management
• Performance tuning
• Concurrency optimization
• Recovery planning
• Database design

Remember: Once COMMIT completes successfully, your changes are permanent and recoverable, even if the database crashes immediately afterward. This is the power of Oracle’s write-ahead logging!