Oracle Transaction Management & Read Consistency

Oracle’s transaction management and read consistency mechanisms are fundamental to maintaining data integrity in multi-user environments. Understanding how Oracle handles concurrent transactions, prevents dirty reads, and ensures data consistency is crucial for database administrators and developers.

In this guide, we’ll explore Oracle’s ACID properties, multi-version concurrency control (MVCC), and read consistency implementation.

What is a Transaction?

A transaction is a logical unit of work containing one or more SQL statements that must succeed or fail as a single unit.

┌────────────────────────────────────────┐
│         TRANSACTION EXAMPLE            │
├────────────────────────────────────────┤
│                                        │
│  BEGIN TRANSACTION (implicit)          │
│    ↓                                   │
│  UPDATE accounts                       │
│  SET balance = balance - 1000          │
│  WHERE account_id = 101;               │
│    ↓                                   │
│  UPDATE accounts                       │
│  SET balance = balance + 1000          │
│  WHERE account_id = 102;               │
│    ↓                                   │
│  COMMIT or ROLLBACK                    │
│                                        │
│  All or Nothing!                       │
└────────────────────────────────────────┘

ACID Properties in Oracle

Oracle guarantees ACID properties for every transaction.

1. Atomicity (All or Nothing)

-- Money transfer example
BEGIN
    UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
    UPDATE accounts SET balance = balance + 1000 WHERE account_id = 102;
    COMMIT;  -- Both updates succeed
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- Both updates fail
        -- Account 101 is NOT debited if account 102 credit fails
END;
/

How Oracle Ensures Atomicity:

• Undo segments store before images
• ROLLBACK restores original state
• No partial transactions visible

2. Consistency (Valid State to Valid State)

-- Constraint ensures consistency
CREATE TABLE accounts (
    account_id NUMBER PRIMARY KEY,
    balance NUMBER CHECK (balance >= 0)  -- Business rule
);

-- This will fail and rollback
UPDATE accounts SET balance = -500 WHERE account_id = 101;
-- ORA-02290: check constraint violated
-- Database remains consistent (no negative balances)

How Oracle Ensures Consistency:

• Constraint validation
• Trigger execution
• Referential integrity checks
• Automatic rollback on violation

3. Isolation (Transactions Don’t Interfere)

-- Session 1
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- Not committed

-- Session 2
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 50000 (old value)
-- Session 2 doesn't see Session 1's uncommitted change

-- Session 1
COMMIT;

-- Session 2
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 60000 (now sees committed change)

How Oracle Ensures Isolation:

• Row-level locking
• Read consistency via undo
• Multi-version concurrency control (MVCC)
• No dirty reads

4. Durability (Permanent After COMMIT)

-- Transaction commits
INSERT INTO orders VALUES (1001, SYSDATE, 'Customer A');
COMMIT;
-- "Commit complete"

-- Database crashes 1 second later
-- Power failure, system crash, etc.

-- After restart: Order 1001 still exists
-- Redo logs ensure durability

How Oracle Ensures Durability:

• Redo logs written before COMMIT returns
• Write-ahead logging (WAL)
• Crash recovery replays redo
• Changes permanent once committed

Transaction Control Statements

Basic Transaction Control

-- Start transaction (implicit with first DML)
INSERT INTO employees VALUES (101, 'John', 50000);

-- Set transaction properties
SET TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION NAME 'Monthly_Payroll';

-- Create savepoint
SAVEPOINT before_update;

-- Partial rollback
ROLLBACK TO SAVEPOINT before_update;

-- Complete transaction
COMMIT;  -- or ROLLBACK;

Transaction Naming

-- Name transaction for monitoring
SET TRANSACTION NAME 'order_processing_batch';

INSERT INTO orders VALUES (1001, SYSDATE);
INSERT INTO order_items VALUES (1, 1001, 'Product', 10);
COMMIT;

-- View named transactions
SELECT name, start_time, used_ublk
FROM v$transaction;

Read-Only Transactions

-- Ensure no modifications
SET TRANSACTION READ ONLY;

-- These work
SELECT * FROM employees;
SELECT COUNT(*) FROM orders;

-- These fail
UPDATE employees SET salary = 60000;
-- ORA-01456: may not perform insert/delete/update operation inside a read-only transaction

COMMIT;  -- End read-only transaction

Oracle’s Read Consistency Model

Oracle uses Statement-Level Read Consistency by default.

Statement-Level Read Consistency

-- Long-running query starts at 10:00:00 AM
SELECT * FROM large_table WHERE status = 'ACTIVE';
-- Query takes 5 minutes

-- At 10:02:00 AM, another session:
UPDATE large_table SET status = 'INACTIVE' WHERE id = 12345;
COMMIT;

-- Original query completes at 10:05:00 AM
-- Results show data as of 10:00:00 AM (query start time)
-- Row 12345 still shows 'ACTIVE' in query results
-- No dirty reads, phantom reads, or non-repeatable reads

How It Works:

Query Start (SCN: 1234567):
┌────────────────────────────────────────┐
│  Oracle records query start SCN        │
│  Reads data blocks from buffer cache   │
│  If block changed after SCN 1234567:   │
│    → Reads undo to reconstruct         │
│    → Returns data as of SCN 1234567    │
└────────────────────────────────────────┘

Result:
- Consistent view of data
- No blocking of other transactions
- No locks on read data

Transaction-Level Read Consistency

-- Set transaction isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Query 1 at 10:00:00 (SCN: 1234567)
SELECT COUNT(*) FROM orders WHERE status = 'PENDING';
-- Result: 100 orders

-- Another session at 10:01:00:
INSERT INTO orders VALUES (1001, SYSDATE, 'PENDING');
COMMIT;

-- Query 2 at 10:02:00 (same transaction)
SELECT COUNT(*) FROM orders WHERE status = 'PENDING';
-- Result: 100 orders (still!)
-- Sees data as of transaction start (SCN: 1234567)

COMMIT;  -- End transaction

-- New query
SELECT COUNT(*) FROM orders WHERE status = 'PENDING';
-- Result: 101 orders (now sees new insert)

Multi-Version Concurrency Control (MVCC)

Oracle’s MVCC allows readers and writers to work without blocking each other.

How MVCC Works

Current Data Block (Buffer Cache):
┌────────────────────────────────────────┐
│  Block SCN: 1234570                    │
│  Row 1: EmpID=101, Salary=60000       │
│  (Modified by Session 1, not committed)│
└────────────────────────────────────────┘

Undo Segment:
┌────────────────────────────────────────┐
│  SCN: 1234567                          │
│  Row 1: EmpID=101, Salary=50000        │
│  (Before image)                        │
└────────────────────────────────────────┘

Session 2 Query (started at SCN 1234568):
1. Reads current block (SCN: 1234570)
2. Detects SCN is too new
3. Reads undo segment
4. Reconstructs row as of SCN 1234568
5. Returns: Salary=50000

Benefits:
✓ Readers don't block writers
✓ Writers don't block readers
✓ No dirty reads
✓ Consistent results

MVCC Example

-- Session 1: Update but don't commit
UPDATE employees SET salary = 70000 WHERE employee_id = 101;
-- Row locked, change in buffer cache

-- Session 2: Read same row
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 60000 (old value from undo)
-- NO WAITING, returns immediately

-- Session 3: Try to update same row
UPDATE employees SET department_id = 20 WHERE employee_id = 101;
-- WAITS (row is locked)

-- Session 1: Commit
COMMIT;

-- Session 2: Read again
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 70000 (new value, now committed)

-- Session 3: Now proceeds
-- UPDATE completes

SCN (System Change Number)

SCN is Oracle’s logical clock for ordering transactions.

Understanding SCN

-- Get current SCN
SELECT CURRENT_SCN FROM V$DATABASE;
-- Result: 1234567

-- Perform transaction
UPDATE employees SET salary = 60000;
COMMIT;  -- Assigned SCN: 1234568

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

SELECT salary 
FROM employees AS OF SCN 1234568
WHERE employee_id = 101;
-- Result: 60000 (after update)

SCN in Action

-- Timestamp to SCN
SELECT TIMESTAMP_TO_SCN(SYSDATE - 1) FROM DUAL;
-- Returns SCN from 24 hours ago

-- SCN to Timestamp
SELECT SCN_TO_TIMESTAMP(1234567) FROM DUAL;
-- Returns time when SCN was generated

-- Query historical data
SELECT * FROM employees
AS OF TIMESTAMP (SYSDATE - 1/24)  -- 1 hour ago
WHERE department_id = 10;

Isolation Levels in Oracle

Oracle supports two isolation levels.

1. READ COMMITTED (Default)

-- Default isolation level
-- Each query sees committed data as of query start

-- Session 1
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- Not committed

-- Session 2
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 50000 (doesn't see uncommitted change)

-- Session 1
COMMIT;

-- Session 2 (new query)
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 60000 (sees newly committed change)

Characteristics:

• Statement-level read consistency
• Sees changes committed by other transactions
• No dirty reads
• Possible non-repeatable reads within transaction

2. SERIALIZABLE

-- Transaction-level read consistency
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- All queries see data as of transaction start
SELECT COUNT(*) FROM orders;
-- Result: 100

-- Another session inserts and commits
-- INSERT INTO orders...
-- COMMIT;

-- This transaction still sees old count
SELECT COUNT(*) FROM orders;
-- Result: 100 (unchanged within transaction)

COMMIT;

-- New transaction sees updated count
SELECT COUNT(*) FROM orders;
-- Result: 101

Characteristics:

• Transaction-level read consistency
• Phantom reads prevented
• May get serialization errors
• Higher overhead

Comparison

Locking Mechanisms

Row-Level Locking

-- Automatic row locking on DML
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- Only row 101 is locked

-- Other sessions can modify other rows
UPDATE employees SET salary = 55000 WHERE employee_id = 102;
-- No waiting, different row

Manual Locking

-- SELECT FOR UPDATE (explicit lock)
SELECT * FROM employees 
WHERE employee_id = 101
FOR UPDATE;
-- Row locked until COMMIT or ROLLBACK

-- Other session tries to update
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- WAITS until first session commits

-- Lock with NOWAIT (fail immediately if locked)
SELECT * FROM employees 
WHERE employee_id = 101
FOR UPDATE NOWAIT;
-- ORA-00054: resource busy and acquire with NOWAIT specified

-- Lock with timeout
SELECT * FROM employees 
WHERE employee_id = 101
FOR UPDATE WAIT 5;
-- Waits max 5 seconds, then fails

Lock Types

-- Table-level lock (rare)
LOCK TABLE employees IN EXCLUSIVE MODE;
-- Entire table locked

-- Row share lock (for foreign key checks)
LOCK TABLE employees IN ROW SHARE MODE;

-- Share lock (allow reads, prevent writes)
LOCK TABLE employees IN SHARE MODE;

Deadlock Detection

Oracle automatically detects and resolves deadlocks.

-- Session 1
UPDATE accounts SET balance = 1000 WHERE account_id = 101;

-- Session 2
UPDATE accounts SET balance = 2000 WHERE account_id = 102;

-- Session 1 (tries to lock Session 2's row)
UPDATE accounts SET balance = 3000 WHERE account_id = 102;
-- WAITS

-- Session 2 (tries to lock Session 1's row)
UPDATE accounts SET balance = 4000 WHERE account_id = 101;
-- DEADLOCK DETECTED!
-- ORA-00060: deadlock detected while waiting for resource
-- Session 2's statement is rolled back automatically

-- Session 1 now proceeds
-- UPDATE completes

Deadlock Resolution:

• Oracle detects deadlock immediately
• Chooses victim (least work to rollback)
• Victim’s statement rolled back
• Other session proceeds
• Application should retry

Undo and Redo in Transaction Management

Undo Segment Usage

-- View undo usage
SELECT s.username,
       s.sid,
       t.used_ublk * 8192/1024/1024 undo_mb,
       t.start_time
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY undo_mb DESC;

-- Check undo retention
SHOW PARAMETER undo_retention;

-- View undo tablespace
SELECT tablespace_name,
       status,
       COUNT(*) extents,
       SUM(bytes)/1024/1024 size_mb
FROM dba_undo_extents
GROUP BY tablespace_name, status;

Redo Log Usage

-- View redo generation
SELECT name, value
FROM v$sysstat
WHERE name IN ('redo size', 'redo writes', 'user commits');

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

-- View redo log switch frequency
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') hour,
       COUNT(*) switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Flashback Query (Point-in-Time Queries)

Oracle’s read consistency extends to querying historical data.

Flashback Query Syntax

-- Query as of timestamp
SELECT * FROM employees
AS OF TIMESTAMP (SYSDATE - 1/24)  -- 1 hour ago
WHERE department_id = 10;

-- Query as of SCN
SELECT * FROM employees
AS OF SCN 1234567
WHERE department_id = 10;

-- View changes between two points
SELECT * FROM employees
VERSIONS BETWEEN TIMESTAMP 
    (SYSDATE - 1/24) AND SYSDATE
WHERE employee_id = 101;

Flashback Example

-- Current data
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 70000

-- Update and commit
UPDATE employees SET salary = 80000 WHERE employee_id = 101;
COMMIT;

-- View current
SELECT salary FROM employees WHERE employee_id = 101;
-- Result: 80000

-- View 5 minutes ago
SELECT salary FROM employees
AS OF TIMESTAMP (SYSDATE - 5/1440)
WHERE employee_id = 101;
-- Result: 70000 (old value)

-- View all versions in last hour
SELECT versions_starttime,
       versions_endtime,
       versions_operation,
       salary
FROM employees
VERSIONS BETWEEN TIMESTAMP 
    (SYSDATE - 1/24) AND SYSDATE
WHERE employee_id = 101;

Best Practices

1. Keep Transactions Short

-- Bad: Long transaction
BEGIN
    FOR emp IN (SELECT * FROM employees) LOOP
        -- Complex processing
        DBMS_LOCK.SLEEP(1);  -- Simulating long work
        UPDATE employees SET processed = 'Y' WHERE employee_id = emp.employee_id;
    END LOOP;
    COMMIT;  -- Hours later!
END;

-- Good: Batch commits
BEGIN
    FOR emp IN (SELECT * FROM employees) LOOP
        UPDATE employees SET processed = 'Y' WHERE employee_id = emp.employee_id;
        IF MOD(SQL%ROWCOUNT, 1000) = 0 THEN
            COMMIT;  -- Commit every 1000 rows
        END IF;
    END LOOP;
    COMMIT;
END;

2. Use Appropriate Isolation Level

-- OLTP: Use default READ COMMITTED
-- Fast, low overhead

-- Reports: Use SERIALIZABLE for consistency
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Financial reports
-- Audit reports
-- Data exports

3. Handle Deadlocks

-- Retry logic for deadlocks
DECLARE
    v_attempts NUMBER := 0;
    v_max_attempts CONSTANT NUMBER := 3;
    deadlock_detected EXCEPTION;
    PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
    LOOP
        BEGIN
            -- Transaction logic
            UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
            UPDATE accounts SET balance = balance + 1000 WHERE account_id = 102;
            COMMIT;
            EXIT;  -- Success
        EXCEPTION
            WHEN deadlock_detected THEN
                v_attempts := v_attempts + 1;
                IF v_attempts >= v_max_attempts THEN
                    RAISE;
                END IF;
                DBMS_LOCK.SLEEP(1);  -- Wait before retry
        END;
    END LOOP;
END;

4. Monitor Transaction Activity

-- Active transactions
SELECT s.username,
       s.sid,
       s.serial#,
       t.start_time,
       t.used_ublk,
       sq.sql_text
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
ORDER BY t.start_time;

-- Long-running transactions
SELECT s.username,
       s.sid,
       ROUND((SYSDATE - t.start_date) * 24 * 60) duration_min,
       t.used_ublk
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE (SYSDATE - t.start_date) * 24 * 60 > 10  -- > 10 minutes
ORDER BY duration_min DESC;

Monitoring Queries

-- Transaction statistics
SELECT name, value
FROM v$sysstat
WHERE name IN (
    'user commits',
    'user rollbacks',
    'transaction rollbacks',
    'active txn count during cleanout'
);

-- Lock waits
SELECT event,
       total_waits,
       time_waited/100 time_waited_sec,
       average_wait/100 avg_wait_sec
FROM v$system_event
WHERE event LIKE '%enq%'
   OR event LIKE '%lock%'
ORDER BY time_waited DESC;

-- Undo generation rate
SELECT (value/1024/1024) undo_mb_per_sec
FROM v$sysstat
WHERE name = 'undo change vector size';

-- Read consistency statistics
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%consistent%'
   OR name LIKE '%CR%';

Conclusion

Oracle’s transaction management and read consistency model provides robust data integrity while maintaining high concurrency. The combination of ACID properties, MVCC, and statement-level read consistency ensures reliable operations in multi-user environments.

Key Takeaways:

1. ACID properties – Guaranteed for all transactions
2. MVCC – Readers don’t block writers, writers don’t block readers
3. SCN-based consistency – Ensures consistent reads
4. No dirty reads – Always see committed data
5. Statement-level consistency – Default, best for OLTP
6. Serializable isolation – Transaction-level consistency for reports
7. Undo enables consistency – Critical for read consistency
8. Automatic deadlock detection – Oracle handles conflicts

Understanding these concepts is essential for:

• Designing concurrent applications
• Troubleshooting locking issues
• Optimizing transaction performance
• Ensuring data integrity
• DBA interviews and certifications

Remember: Oracle’s architecture allows thousands of concurrent users to read and modify data safely without compromising consistency or performance!