Database Management SystemsTransaction States

Transaction States

LevelIntermediate

Duration75 mins

TopicTransaction States

3 / 5

Committed

The Point of Permanence

There comes a moment in every successful transaction's life when its changes become irrevocable. No crash can undo them. No failure can erase them. No rollback can reverse them. This moment is when a transaction enters the Committed state.

The Committed state is not merely a bookkeeping designation—it represents a fundamental guarantee that the database makes to you as a user or application. It is the fulfillment of the Durability promise in ACID: once committed, your transaction's effects will survive any subsequent failure of the system.

But what does 'committed' actually mean at a technical level? How does the database guarantee permanence? What happens after a transaction commits? These questions are essential for understanding database reliability and designing robust applications.

What You Will Learn

By the end of this page, you will understand the formal definition of the Committed state, comprehend the guarantees that committed transactions receive, learn what post-commit activities occur, understand the relationship between commit and recovery, and appreciate the implications of commit for application design.

Formal Definition of the Committed State

Let's establish a precise understanding of what it means for a transaction to be Committed.

Formal Definition:

A transaction T is in the Committed state if and only if:

T was in the Partially Committed state
The commit record for T has been successfully written to stable storage
T's changes are guaranteed to survive any future failure

Using formal notation:

T ∈ Committed ⟺ 
    (previous_state(T) = PartiallyCommitted) ∧
    (commit_record_durable(T) = true) ∧
    (durability_guaranteed(T) = true)

The Committed State is Terminal:

Unlike Active or Partially Committed, the Committed state is a terminal state. Once a transaction enters Committed, it never transitions to any other state. The transaction's lifecycle is complete.

Committed State Properties
Property	Value	Implication
State Type	Terminal (final)	No more transitions possible
Operations Allowed	None	Transaction has completed all work
Data Changes	Permanent	Changes survive any failure
Locks	Released	Other transactions can access data
Rollback Possible	No	Only compensating transactions can undo effects
Visible to Others	Yes	Changes are visible per isolation rules

Commit vs Committed

Important distinction: 'Commit' is the action (the COMMIT command). 'Committed' is the state. A transaction can attempt to commit but fail (transitioning to Failed instead of Committed). Only when the commit is fully successful does the transaction enter the Committed state.

The Complete Transaction State Diagram:

With the Committed state, we can now show a significant portion of the transaction state machine:

Converting Mermaid diagram...

The Durability Guarantee

The defining characteristic of the Committed state is the durability guarantee. Let's examine what this means in rigorous terms.

What Durability Guarantees:

When a transaction is in the Committed state, the database guarantees that:

Persistence — The transaction's changes will be retained indefinitely (or until explicitly modified by a future transaction)
Crash Survival — If the system crashes immediately after commit, recovery will restore the committed changes
Media Survival (with proper backup) — The changes can be recovered even from catastrophic storage failure if proper backup/recovery procedures are followed
Correctness — The committed state represents a consistent state of the database (all constraints are satisfied)

What Durability Does NOT Guarantee:

Durability Limitations

•Physical storage permanence — Hardware can fail; durability relies on proper redundancy (RAID, replication)
•Protection against application errors — If you commit incorrect data, durability ensures the incorrect data persists
•Protection against malicious modification — Durability doesn't prevent authorized users from deleting data
•Instant visibility everywhere — In distributed systems, propagation delays exist even after commit
•Rollback capability — Once committed, you cannot roll back; you can only create compensating transactions

The Technical Basis of Durability:

Durability is achieved through a combination of mechanisms:

1. Write-Ahead Logging (WAL)

All changes are logged before being applied to data files. The commit record in the log is the authoritative marker of a committed transaction.

2. Stable Storage

Logs are written to storage that survives power loss and crashes. This typically means:

Synchronized writes (fsync/fdatasync)
Redundant storage (RAID, mirroring)
Battery-backed write caches

3. Recovery Algorithms

After a crash, the recovery process:

Scans the log to find committed transactions
Redoes any committed changes not yet in data files
Ensures the database reflects all committed transactions

durability-verification.sql
PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Demonstrating durability: data survives restart
 
-- Step 1: Insert data and commit
BEGIN;
INSERT INTO audit_log (event_type, message, created_at)
VALUES ('TEST', 'Testing durability', NOW());
 
-- Verify data exists in transaction
SELECT * FROM audit_log WHERE event_type = 'TEST';
 
-- Commit - transaction enters Committed state
COMMIT;
 
-- Step 2: Force a checkpoint to ensure data is written
-- (In practice, the log force at commit is sufficient for durability)
CHECKPOINT;
 
-- Step 3: Simulate or perform a database restart
-- The data will still be there after restart because:
-- 1. The commit record was forced to WAL at commit time
-- 2. On restart, recovery replays any needed WAL records
 
-- Step 4: After restart, verify data persists
SELECT * FROM audit_log WHERE event_type = 'TEST';
-- Result: Data is present, demonstrating durability
 
-- View WAL history to understand the durability mechanism
SELECT * FROM pg_stat_wal;
 
-- Check transaction status tracking (pg_xact)
-- This is updated atomically with the commit

Testing Durability

To truly test durability, perform a 'crash' immediately after commit—kill the database process, cut power, or trigger an immediate shutdown. On restart, verify your committed data is present. This is how database vendors validate their durability claims. Never assume durability; verify it in your specific environment.

Post-Commit Activities

Once a transaction enters the Committed state, several post-commit activities occur. These activities clean up resources and finalize the transaction's effects.

1. Lock Release:

When a transaction commits, all locks it held are released. This is critical for system concurrency:

Shared locks — Other transactions can now write to data this transaction was reading
Exclusive locks — Other transactions can now read or write to data this transaction modified
Intent locks — Hierarchical locks are cleared

Lock release is typically immediate upon commit, though some systems may batch this for efficiency.

lock-release-observation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Session 1: Acquire lock
BEGIN;
SELECT * FROM accounts WHERE id = 100 FOR UPDATE;
-- Exclusive lock now held on row id=100
 
-- Session 2 (before Session 1 commits): This blocks!
BEGIN;
UPDATE accounts SET balance = 1000 WHERE id = 100;
-- Waiting...
 
-- Session 1: Commit (entering Committed state)
COMMIT;
-- Lock released immediately
 
-- Session 2: Now proceeds!
-- The UPDATE completes because lock was released
 
-- Verify no locks remain from committed transaction
-- (PostgreSQL)
SELECT * FROM pg_locks WHERE pid = <session1_pid>;
-- Returns: Empty - all locks released

2. Transaction Descriptor Cleanup:

The internal data structures that tracked the transaction are deallocated:

Transaction descriptor memory is freed
Entry in active transaction table is removed
Transaction ID is marked as complete

3. Log Space Management:

Once a transaction commits (and its effects are checkpointed to data files), its log records become candidates for recycling:

Committed transactions don't need undo information anymore
Redo information is needed until data pages are flushed
After checkpoint, log space can be reclaimed

4. MVCC Version Management:

In MVCC systems, commit affects version visibility:

The transaction's new versions become potentially visible to other transactions
The commit timestamp (or transaction ID) is recorded for visibility checks
Old versions may eventually become eligible for garbage collection (vacuum)

Post-Commit Resource Lifecycle
Resource	During Transaction	After Commit	Eventually
Locks	Held for isolation	Released immediately	N/A
Transaction Descriptor	Allocated in memory	Deallocated	N/A
Undo Log Records	Needed for rollback	Not needed for this txn	Reclaimed after checkpoint
Redo Log Records	Accumulated	May still be needed	Reclaimed after checkpoint
MVCC Old Versions	Still visible to some txns	Visibility decreases	Vacuumed when no longer needed
Transaction ID	In active list	Removed from active list	May be reused (wrap-around)

Commit and Rollback Are Not Symmetric

Commit is 'lighter' than rollback in many ways. Commit releases locks and cleans up, but doesn't need to undo any changes. Rollback, by contrast, must actively undo all modifications. This is one reason why successful commit is more efficient than abort (and why you should aim for transactions to commit, not abort, under normal circumstances).

Commit and Recovery

Understanding the Committed state is incomplete without understanding how commit interacts with database recovery. This relationship is at the heart of durability.

The Recovery Scenario:

Consider what happens when a system crashes:

The database was running with many transactions
Some transactions were Committed before the crash
Some transactions were Active (in progress)
Some transactions were Partially Committed (commit in progress)
Power fails; everything in RAM is lost

Recovery Must:

Preserve all Committed transactions — Their effects must be present after recovery
Abort all Active transactions — They never completed; must be rolled back
Correctly handle Partially Committed — Check if commit record reached disk

Converting Mermaid diagram...

The Recovery Algorithm (ARIES-style simplified):

Phase 1: Analysis

Scan log from last checkpoint
Identify all transactions that were active at crash time
Determine which transactions committed (have COMMIT record)
Build list of transactions to redo and undo

Phase 2: Redo

Replay all log records forward from checkpoint
Apply changes for ALL transactions (committed and uncommitted)
Restore database to crash-time state

Phase 3: Undo

For each uncommitted transaction, apply undo log records backward
Roll back all changes from transactions without COMMIT record
Write compensation log records (CLRs) for recovery idempotence

recovery-logic-pseudocode.pseudo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
// Simplified recovery algorithm demonstrating commit status importance
 
function recover_database() {
    // Phase 1: Analysis - identify transaction states
    committed_txns = Set()
    active_txns = Set()
    
    for each log_record in log from checkpoint:
        if log_record.type == BEGIN:
            active_txns.add(log_record.transaction_id)
        elif log_record.type == COMMIT:
            // THIS IS THE KEY: presence of COMMIT record
            committed_txns.add(log_record.transaction_id)
            active_txns.remove(log_record.transaction_id)
        elif log_record.type == ABORT:
            active_txns.remove(log_record.transaction_id)
    
    // After scanning: committed_txns has committed transactions
    // active_txns has transactions that were in progress (never committed)
    
    // Phase 2: Redo - apply all logged changes
    for each log_record in log from checkpoint:
        if log_record is a data modification:
            page = read_page(log_record.page_id)
            if page.lsn < log_record.lsn:
                // Page doesn't have this change - apply it
                apply_redo(log_record, page)
    
    // Phase 3: Undo - roll back uncommitted transactions
    for txn_id in active_txns:
        // These transactions never reached Committed state
        // Their changes must be undone
        rollback_transaction(txn_id)
    
    // Database is now consistent:
    // - All Committed transactions' effects are present
    // - All Active (uncommitted) transactions' effects are removed
}

The Commit Record is Everything

In recovery, the PRESENCE or ABSENCE of the commit record in the durable log is the sole determinant of whether a transaction's effects are preserved. There is no gray area. No commit record = abort. Has commit record = committed. This binary distinction is why the Partially Committed state's log force operation is so critical.

Commit, Visibility, and Isolation Levels

When a transaction commits, its changes become potentially visible to other transactions. However, the exact visibility rules depend on the isolation level configuration.

The Principle of Visibility:

Once a transaction enters the Committed state:

Its changes are durably stored
Its changes are eligible to be seen by other transactions
The exact visibility depends on when other transactions started and their isolation level

Visibility by Isolation Level:

When Committed Changes Become Visible
Isolation Level	Visibility of Just-Committed Data	Explanation
READ UNCOMMITTED	Immediately visible	Can see data even before commit (not relevant here)
READ COMMITTED	Visible on next statement	Each statement sees latest committed data
REPEATABLE READ	Visible only if txn started after commit	Transaction sees snapshot from its start
SERIALIZABLE	Depends on implementation	May use locking or snapshot isolation

visibility-example.sql
PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Demonstrating commit visibility at different isolation levels
 
-- === Setup ===
CREATE TABLE products (id int PRIMARY KEY, price decimal(10,2));
INSERT INTO products VALUES (1, 100.00);
 
-- === READ COMMITTED Example ===
-- Session 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT price FROM products WHERE id = 1;  -- Returns: 100.00
 
    -- Session 2: (concurrent)
    BEGIN;
    UPDATE products SET price = 150.00 WHERE id = 1;
    COMMIT;  -- Now committed
 
-- Session 1: Next read sees the committed change
SELECT price FROM products WHERE id = 1;  -- Returns: 150.00 (!)
COMMIT;
 
-- === REPEATABLE READ Example ===
-- Session 1:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT price FROM products WHERE id = 1;  -- Returns: 150.00
 
    -- Session 2: (concurrent)
    BEGIN;
    UPDATE products SET price = 200.00 WHERE id = 1;
    COMMIT;  -- Now committed
 
-- Session 1: STILL sees original value (snapshot isolation)
SELECT price FROM products WHERE id = 1;  -- Returns: 150.00 (unchanged!)
COMMIT;
 
-- Only AFTER Session 1 commits and starts new transaction:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT price FROM products WHERE id = 1;  -- NOW Returns: 200.00

MVCC and Commit Visibility:

In MVCC-based systems (PostgreSQL, Oracle, MySQL InnoDB), commit visibility is determined by comparing transaction IDs or timestamps:

Each transaction gets a snapshot of 'which transactions are visible'
A committed transaction is visible if:
- It committed before the reader's snapshot was taken
- The reader allows seeing newer commits (READ COMMITTED)
The commit timestamp (or transaction ID) is recorded when a transaction commits
This timestamp is used by readers to determine visibility

Commit != Instant Global Visibility

A common misconception is that committed data is immediately visible everywhere. In reality: (1) MVCC isolation levels may hide committed data from transactions that started earlier, (2) In distributed systems, replication delays mean reads from replicas may not see recent commits, (3) Application caches may serve stale data even after database commit. Design your applications with these visibility semantics in mind.

Application Design Implications

Understanding the Committed state has profound implications for application design. Here are key considerations for building robust applications.

1. Once Committed, You Can't Rollback:

Unlike Active transactions where ROLLBACK reverses changes, committed changes are permanent. To 'undo' a committed transaction, you must create a compensating transaction—a new transaction that logically reverses the effects.

compensating-transaction.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Original transaction (now committed)
BEGIN;
INSERT INTO orders (id, customer_id, amount) VALUES (1001, 42, 500.00);
UPDATE accounts SET balance = balance - 500.00 WHERE customer_id = 42;
COMMIT;  -- This is now permanent
 
-- Oops! The order was an error. Cannot ROLLBACK - already committed.
-- Must create a compensating transaction:
 
BEGIN;
-- Compensate the balance deduction
UPDATE accounts SET balance = balance + 500.00 WHERE customer_id = 42;
 
-- Mark the order as cancelled (or delete, depending on requirements)
UPDATE orders SET status = 'CANCELLED' WHERE id = 1001;
 
-- Record the compensation for audit
INSERT INTO order_audit (order_id, action, reason, timestamp)
VALUES (1001, 'CANCEL', 'Order entered in error', NOW());
 
COMMIT;  -- Compensation is also committed

2. Commit Confirmation Matters:

Until you receive confirmation that a commit succeeded, you cannot assume your data is durable. This is especially important for:

Network-connected databases (connection could drop during commit)
Distributed transactions (two-phase commit can fail)
Replication scenarios (what does 'committed' mean with async replication?)

3. Designing for Durability Failures:

In rare cases, even 'committed' transactions might be lost if durability infrastructure fails (e.g., both disks in a mirror fail simultaneously). Critical applications should:

Use proper redundancy (RAID, replication across data centers)
Consider synchronous replication for critical data
Implement application-level acknowledgments (two-phase commit at app level)
Have disaster recovery procedures

Best Practices for Handling Commit

•Always check commit success — Don't assume COMMIT worked; check for errors and handle connection failures
•Design idempotent operations — If you're unsure whether a commit succeeded, retrying an idempotent operation is safe
•Use unique identifiers — Assign unique IDs to transactions/operations to detect duplicates if retry occurs
•Log before commit for critical ops — For irreversible operations, log the intent before committing (e.g., audit logs)
•Consider two-phase commit for distributed data — When data spans multiple systems, coordinate commits properly
•Have compensating transaction designs — Know how you'll 'undo' critical operations if business requirements change

The Irony of Commitment

There's an irony in database commit: we work hard to make data permanent, then often need to change it later. Good database design anticipates change: use soft deletes, maintain history tables, design flexible schemas. The permanence of commit should be viewed as 'permanent until intentionally modified,' not 'permanent forever.'

Observing Committed Transactions

How can you confirm that a transaction has committed? How do you view commit history? Different database systems provide various tools for observing committed transactions.

PostgreSQL: Transaction Status and History:

observing-committed.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Check if a specific transaction ID committed
-- (Requires track_commit_timestamp = on)
SELECT pg_xact_commit_timestamp('12345'::xid) AS commit_time;
-- Returns timestamp if committed, NULL if not
 
-- View recent prepared transactions (2PC)
SELECT * FROM pg_prepared_xacts;
 
-- See commit status in pg_xact (internal, not directly queryable)
-- But you can check via: 
SELECT txid_status('12345');  -- 'committed', 'aborted', 'in progress', or NULL
 
-- View current snapshot information
SELECT txid_current_snapshot();
-- Returns: xmin:xmax:xip_list
-- Transactions below xmin are committed and visible
 
-- Check if specific data change has committed (indirect)
SELECT xmin, xmax, * FROM my_table WHERE id = 100;
-- xmin shows the transaction that created/modified the row
-- If xmin < current_xmin in snapshot, it's committed and visible

Audit Logging for Commit Tracking:

For applications that need to track commit history beyond what the database provides, implement audit logging:

audit-logging.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Create audit log table
CREATE TABLE transaction_audit (
    audit_id BIGSERIAL PRIMARY KEY,
    transaction_id VARCHAR(100) NOT NULL,
    operation_type VARCHAR(50) NOT NULL,
    table_affected VARCHAR(100) NOT NULL,
    record_id BIGINT,
    old_values JSONB,
    new_values JSONB,
    committed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    committed_by VARCHAR(100) DEFAULT CURRENT_USER
);
 
-- Use a trigger to log changes
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO transaction_audit 
        (transaction_id, operation_type, table_affected, record_id, old_values, new_values)
    VALUES (
        txid_current()::text,
        TG_OP,
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        CASE WHEN TG_OP = 'DELETE' OR TG_OP = 'UPDATE' 
             THEN row_to_json(OLD) ELSE NULL END,
        CASE WHEN TG_OP = 'INSERT' OR TG_OP = 'UPDATE' 
             THEN row_to_json(NEW) ELSE NULL END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
-- Apply trigger to tables you want to audit
CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION log_changes();
 
-- Now all committed changes to 'orders' are logged

Summary: The Committed State

We've thoroughly explored the Committed state—the successful terminal state that represents the achievement of durability. Let's consolidate our understanding:

Key Takeaways

•Terminal success state — Committed is a final state; no further transitions occur. The transaction's lifecycle is complete with success.
•Durability guarantee — The defining characteristic of Committed is that changes will survive any subsequent failure. This is the 'D' in ACID.
•Based on log presence — A transaction is considered Committed if and only if its commit record is durably stored in the transaction log.
•Post-commit cleanup — After commit, locks are released, transaction descriptors are deallocated, and resources become available for other transactions.
•Recovery respects commit — During crash recovery, committed transactions' effects are preserved (redone if necessary), while uncommitted transactions are rolled back.
•Visibility depends on isolation — Committed changes are eligible to be seen by other transactions, but exact visibility depends on isolation level and timing.
•No rollback possible — Once committed, you cannot ROLLBACK. To reverse a committed transaction's effects, you must create a compensating transaction.
•Application design matters — Applications should verify commit success, design idempotent operations, and have strategies for compensating transactions.

What's Next:

We've now covered the 'happy path' of transaction states: Active → Partially Committed → Committed. But what happens when things go wrong? The next page explores the Failed state—the state a transaction enters when an error or failure prevents successful completion.

Page Complete

You now have a comprehensive understanding of the Committed state—the successful endpoint of a transaction's lifecycle. You understand the durability guarantee, how recovery preserves committed transactions, visibility rules, and application design considerations. This completes your understanding of the successful transaction path.

3 / 5

Loading learning content...

Database Management SystemsTransaction States

Transaction States

LevelIntermediate

Duration75 mins

TopicTransaction States

3 / 5

Committed

The Point of Permanence

What You Will Learn

Formal Definition of the Committed State

Let's establish a precise understanding of what it means for a transaction to be Committed.

Formal Definition:

A transaction T is in the Committed state if and only if:

T was in the Partially Committed state
The commit record for T has been successfully written to stable storage
T's changes are guaranteed to survive any future failure

Using formal notation:

T ∈ Committed ⟺ 
    (previous_state(T) = PartiallyCommitted) ∧
    (commit_record_durable(T) = true) ∧
    (durability_guaranteed(T) = true)

The Committed State is Terminal:

Committed State Properties
Property	Value	Implication
State Type	Terminal (final)	No more transitions possible
Operations Allowed	None	Transaction has completed all work
Data Changes	Permanent	Changes survive any failure
Locks	Released	Other transactions can access data
Rollback Possible	No	Only compensating transactions can undo effects
Visible to Others	Yes	Changes are visible per isolation rules

Commit vs Committed

The Complete Transaction State Diagram:

With the Committed state, we can now show a significant portion of the transaction state machine:

Converting Mermaid diagram...

The Durability Guarantee

The defining characteristic of the Committed state is the durability guarantee. Let's examine what this means in rigorous terms.

What Durability Guarantees:

When a transaction is in the Committed state, the database guarantees that:

Persistence — The transaction's changes will be retained indefinitely (or until explicitly modified by a future transaction)
Crash Survival — If the system crashes immediately after commit, recovery will restore the committed changes
Media Survival (with proper backup) — The changes can be recovered even from catastrophic storage failure if proper backup/recovery procedures are followed
Correctness — The committed state represents a consistent state of the database (all constraints are satisfied)

What Durability Does NOT Guarantee:

Durability Limitations

•Physical storage permanence — Hardware can fail; durability relies on proper redundancy (RAID, replication)
•Protection against application errors — If you commit incorrect data, durability ensures the incorrect data persists
•Protection against malicious modification — Durability doesn't prevent authorized users from deleting data
•Instant visibility everywhere — In distributed systems, propagation delays exist even after commit
•Rollback capability — Once committed, you cannot roll back; you can only create compensating transactions

The Technical Basis of Durability:

Durability is achieved through a combination of mechanisms:

1. Write-Ahead Logging (WAL)

All changes are logged before being applied to data files. The commit record in the log is the authoritative marker of a committed transaction.

2. Stable Storage

Logs are written to storage that survives power loss and crashes. This typically means:

Synchronized writes (fsync/fdatasync)
Redundant storage (RAID, mirroring)
Battery-backed write caches

3. Recovery Algorithms

After a crash, the recovery process:

Scans the log to find committed transactions
Redoes any committed changes not yet in data files
Ensures the database reflects all committed transactions

durability-verification.sql
PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Demonstrating durability: data survives restart
 
-- Step 1: Insert data and commit
BEGIN;
INSERT INTO audit_log (event_type, message, created_at)
VALUES ('TEST', 'Testing durability', NOW());
 
-- Verify data exists in transaction
SELECT * FROM audit_log WHERE event_type = 'TEST';
 
-- Commit - transaction enters Committed state
COMMIT;
 
-- Step 2: Force a checkpoint to ensure data is written
-- (In practice, the log force at commit is sufficient for durability)
CHECKPOINT;
 
-- Step 3: Simulate or perform a database restart
-- The data will still be there after restart because:
-- 1. The commit record was forced to WAL at commit time
-- 2. On restart, recovery replays any needed WAL records
 
-- Step 4: After restart, verify data persists
SELECT * FROM audit_log WHERE event_type = 'TEST';
-- Result: Data is present, demonstrating durability
 
-- View WAL history to understand the durability mechanism
SELECT * FROM pg_stat_wal;
 
-- Check transaction status tracking (pg_xact)
-- This is updated atomically with the commit

Testing Durability

Post-Commit Activities

Once a transaction enters the Committed state, several post-commit activities occur. These activities clean up resources and finalize the transaction's effects.

1. Lock Release:

When a transaction commits, all locks it held are released. This is critical for system concurrency:

Shared locks — Other transactions can now write to data this transaction was reading
Exclusive locks — Other transactions can now read or write to data this transaction modified
Intent locks — Hierarchical locks are cleared

Lock release is typically immediate upon commit, though some systems may batch this for efficiency.

lock-release-observation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Session 1: Acquire lock
BEGIN;
SELECT * FROM accounts WHERE id = 100 FOR UPDATE;
-- Exclusive lock now held on row id=100
 
-- Session 2 (before Session 1 commits): This blocks!
BEGIN;
UPDATE accounts SET balance = 1000 WHERE id = 100;
-- Waiting...
 
-- Session 1: Commit (entering Committed state)
COMMIT;
-- Lock released immediately
 
-- Session 2: Now proceeds!
-- The UPDATE completes because lock was released
 
-- Verify no locks remain from committed transaction
-- (PostgreSQL)
SELECT * FROM pg_locks WHERE pid = <session1_pid>;
-- Returns: Empty - all locks released

2. Transaction Descriptor Cleanup:

The internal data structures that tracked the transaction are deallocated:

Transaction descriptor memory is freed
Entry in active transaction table is removed
Transaction ID is marked as complete

3. Log Space Management:

Once a transaction commits (and its effects are checkpointed to data files), its log records become candidates for recycling:

Committed transactions don't need undo information anymore
Redo information is needed until data pages are flushed
After checkpoint, log space can be reclaimed

4. MVCC Version Management:

In MVCC systems, commit affects version visibility:

The transaction's new versions become potentially visible to other transactions
The commit timestamp (or transaction ID) is recorded for visibility checks
Old versions may eventually become eligible for garbage collection (vacuum)

Post-Commit Resource Lifecycle
Resource	During Transaction	After Commit	Eventually
Locks	Held for isolation	Released immediately	N/A
Transaction Descriptor	Allocated in memory	Deallocated	N/A
Undo Log Records	Needed for rollback	Not needed for this txn	Reclaimed after checkpoint
Redo Log Records	Accumulated	May still be needed	Reclaimed after checkpoint
MVCC Old Versions	Still visible to some txns	Visibility decreases	Vacuumed when no longer needed
Transaction ID	In active list	Removed from active list	May be reused (wrap-around)

Commit and Rollback Are Not Symmetric

Commit and Recovery

Understanding the Committed state is incomplete without understanding how commit interacts with database recovery. This relationship is at the heart of durability.

The Recovery Scenario:

Consider what happens when a system crashes:

The database was running with many transactions
Some transactions were Committed before the crash
Some transactions were Active (in progress)
Some transactions were Partially Committed (commit in progress)
Power fails; everything in RAM is lost

Recovery Must:

Preserve all Committed transactions — Their effects must be present after recovery
Abort all Active transactions — They never completed; must be rolled back
Correctly handle Partially Committed — Check if commit record reached disk

Converting Mermaid diagram...

The Recovery Algorithm (ARIES-style simplified):

Phase 1: Analysis

Scan log from last checkpoint
Identify all transactions that were active at crash time
Determine which transactions committed (have COMMIT record)
Build list of transactions to redo and undo

Phase 2: Redo

Replay all log records forward from checkpoint
Apply changes for ALL transactions (committed and uncommitted)
Restore database to crash-time state

Phase 3: Undo

For each uncommitted transaction, apply undo log records backward
Roll back all changes from transactions without COMMIT record
Write compensation log records (CLRs) for recovery idempotence

recovery-logic-pseudocode.pseudo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
// Simplified recovery algorithm demonstrating commit status importance
 
function recover_database() {
    // Phase 1: Analysis - identify transaction states
    committed_txns = Set()
    active_txns = Set()
    
    for each log_record in log from checkpoint:
        if log_record.type == BEGIN:
            active_txns.add(log_record.transaction_id)
        elif log_record.type == COMMIT:
            // THIS IS THE KEY: presence of COMMIT record
            committed_txns.add(log_record.transaction_id)
            active_txns.remove(log_record.transaction_id)
        elif log_record.type == ABORT:
            active_txns.remove(log_record.transaction_id)
    
    // After scanning: committed_txns has committed transactions
    // active_txns has transactions that were in progress (never committed)
    
    // Phase 2: Redo - apply all logged changes
    for each log_record in log from checkpoint:
        if log_record is a data modification:
            page = read_page(log_record.page_id)
            if page.lsn < log_record.lsn:
                // Page doesn't have this change - apply it
                apply_redo(log_record, page)
    
    // Phase 3: Undo - roll back uncommitted transactions
    for txn_id in active_txns:
        // These transactions never reached Committed state
        // Their changes must be undone
        rollback_transaction(txn_id)
    
    // Database is now consistent:
    // - All Committed transactions' effects are present
    // - All Active (uncommitted) transactions' effects are removed
}

The Commit Record is Everything

Commit, Visibility, and Isolation Levels

When a transaction commits, its changes become potentially visible to other transactions. However, the exact visibility rules depend on the isolation level configuration.

The Principle of Visibility:

Once a transaction enters the Committed state:

Its changes are durably stored
Its changes are eligible to be seen by other transactions
The exact visibility depends on when other transactions started and their isolation level

Visibility by Isolation Level:

When Committed Changes Become Visible
Isolation Level	Visibility of Just-Committed Data	Explanation
READ UNCOMMITTED	Immediately visible	Can see data even before commit (not relevant here)
READ COMMITTED	Visible on next statement	Each statement sees latest committed data
REPEATABLE READ	Visible only if txn started after commit	Transaction sees snapshot from its start
SERIALIZABLE	Depends on implementation	May use locking or snapshot isolation

visibility-example.sql
PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Demonstrating commit visibility at different isolation levels
 
-- === Setup ===
CREATE TABLE products (id int PRIMARY KEY, price decimal(10,2));
INSERT INTO products VALUES (1, 100.00);
 
-- === READ COMMITTED Example ===
-- Session 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT price FROM products WHERE id = 1;  -- Returns: 100.00
 
    -- Session 2: (concurrent)
    BEGIN;
    UPDATE products SET price = 150.00 WHERE id = 1;
    COMMIT;  -- Now committed
 
-- Session 1: Next read sees the committed change
SELECT price FROM products WHERE id = 1;  -- Returns: 150.00 (!)
COMMIT;
 
-- === REPEATABLE READ Example ===
-- Session 1:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT price FROM products WHERE id = 1;  -- Returns: 150.00
 
    -- Session 2: (concurrent)
    BEGIN;
    UPDATE products SET price = 200.00 WHERE id = 1;
    COMMIT;  -- Now committed
 
-- Session 1: STILL sees original value (snapshot isolation)
SELECT price FROM products WHERE id = 1;  -- Returns: 150.00 (unchanged!)
COMMIT;
 
-- Only AFTER Session 1 commits and starts new transaction:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT price FROM products WHERE id = 1;  -- NOW Returns: 200.00

MVCC and Commit Visibility:

In MVCC-based systems (PostgreSQL, Oracle, MySQL InnoDB), commit visibility is determined by comparing transaction IDs or timestamps:

Each transaction gets a snapshot of 'which transactions are visible'
A committed transaction is visible if:
- It committed before the reader's snapshot was taken
- The reader allows seeing newer commits (READ COMMITTED)
The commit timestamp (or transaction ID) is recorded when a transaction commits
This timestamp is used by readers to determine visibility

Commit != Instant Global Visibility

Application Design Implications

Understanding the Committed state has profound implications for application design. Here are key considerations for building robust applications.

1. Once Committed, You Can't Rollback:

compensating-transaction.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Original transaction (now committed)
BEGIN;
INSERT INTO orders (id, customer_id, amount) VALUES (1001, 42, 500.00);
UPDATE accounts SET balance = balance - 500.00 WHERE customer_id = 42;
COMMIT;  -- This is now permanent
 
-- Oops! The order was an error. Cannot ROLLBACK - already committed.
-- Must create a compensating transaction:
 
BEGIN;
-- Compensate the balance deduction
UPDATE accounts SET balance = balance + 500.00 WHERE customer_id = 42;
 
-- Mark the order as cancelled (or delete, depending on requirements)
UPDATE orders SET status = 'CANCELLED' WHERE id = 1001;
 
-- Record the compensation for audit
INSERT INTO order_audit (order_id, action, reason, timestamp)
VALUES (1001, 'CANCEL', 'Order entered in error', NOW());
 
COMMIT;  -- Compensation is also committed

2. Commit Confirmation Matters:

Until you receive confirmation that a commit succeeded, you cannot assume your data is durable. This is especially important for:

Network-connected databases (connection could drop during commit)
Distributed transactions (two-phase commit can fail)
Replication scenarios (what does 'committed' mean with async replication?)

3. Designing for Durability Failures:

In rare cases, even 'committed' transactions might be lost if durability infrastructure fails (e.g., both disks in a mirror fail simultaneously). Critical applications should:

Use proper redundancy (RAID, replication across data centers)
Consider synchronous replication for critical data
Implement application-level acknowledgments (two-phase commit at app level)
Have disaster recovery procedures

Best Practices for Handling Commit

•Always check commit success — Don't assume COMMIT worked; check for errors and handle connection failures
•Design idempotent operations — If you're unsure whether a commit succeeded, retrying an idempotent operation is safe
•Use unique identifiers — Assign unique IDs to transactions/operations to detect duplicates if retry occurs
•Log before commit for critical ops — For irreversible operations, log the intent before committing (e.g., audit logs)
•Consider two-phase commit for distributed data — When data spans multiple systems, coordinate commits properly
•Have compensating transaction designs — Know how you'll 'undo' critical operations if business requirements change

The Irony of Commitment

Observing Committed Transactions

How can you confirm that a transaction has committed? How do you view commit history? Different database systems provide various tools for observing committed transactions.

PostgreSQL: Transaction Status and History:

observing-committed.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Check if a specific transaction ID committed
-- (Requires track_commit_timestamp = on)
SELECT pg_xact_commit_timestamp('12345'::xid) AS commit_time;
-- Returns timestamp if committed, NULL if not
 
-- View recent prepared transactions (2PC)
SELECT * FROM pg_prepared_xacts;
 
-- See commit status in pg_xact (internal, not directly queryable)
-- But you can check via: 
SELECT txid_status('12345');  -- 'committed', 'aborted', 'in progress', or NULL
 
-- View current snapshot information
SELECT txid_current_snapshot();
-- Returns: xmin:xmax:xip_list
-- Transactions below xmin are committed and visible
 
-- Check if specific data change has committed (indirect)
SELECT xmin, xmax, * FROM my_table WHERE id = 100;
-- xmin shows the transaction that created/modified the row
-- If xmin < current_xmin in snapshot, it's committed and visible

Audit Logging for Commit Tracking:

For applications that need to track commit history beyond what the database provides, implement audit logging:

audit-logging.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Create audit log table
CREATE TABLE transaction_audit (
    audit_id BIGSERIAL PRIMARY KEY,
    transaction_id VARCHAR(100) NOT NULL,
    operation_type VARCHAR(50) NOT NULL,
    table_affected VARCHAR(100) NOT NULL,
    record_id BIGINT,
    old_values JSONB,
    new_values JSONB,
    committed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    committed_by VARCHAR(100) DEFAULT CURRENT_USER
);
 
-- Use a trigger to log changes
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO transaction_audit 
        (transaction_id, operation_type, table_affected, record_id, old_values, new_values)
    VALUES (
        txid_current()::text,
        TG_OP,
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        CASE WHEN TG_OP = 'DELETE' OR TG_OP = 'UPDATE' 
             THEN row_to_json(OLD) ELSE NULL END,
        CASE WHEN TG_OP = 'INSERT' OR TG_OP = 'UPDATE' 
             THEN row_to_json(NEW) ELSE NULL END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
-- Apply trigger to tables you want to audit
CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION log_changes();
 
-- Now all committed changes to 'orders' are logged

Summary: The Committed State

We've thoroughly explored the Committed state—the successful terminal state that represents the achievement of durability. Let's consolidate our understanding:

Key Takeaways

•Terminal success state — Committed is a final state; no further transitions occur. The transaction's lifecycle is complete with success.
•Durability guarantee — The defining characteristic of Committed is that changes will survive any subsequent failure. This is the 'D' in ACID.
•Based on log presence — A transaction is considered Committed if and only if its commit record is durably stored in the transaction log.
•Post-commit cleanup — After commit, locks are released, transaction descriptors are deallocated, and resources become available for other transactions.
•Recovery respects commit — During crash recovery, committed transactions' effects are preserved (redone if necessary), while uncommitted transactions are rolled back.
•Visibility depends on isolation — Committed changes are eligible to be seen by other transactions, but exact visibility depends on isolation level and timing.
•No rollback possible — Once committed, you cannot ROLLBACK. To reverse a committed transaction's effects, you must create a compensating transaction.
•Application design matters — Applications should verify commit success, design idempotent operations, and have strategies for compensating transactions.

What's Next:

Page Complete

3 / 5