Transactions in DBMS Explained — ACID, Isolation Levels & Real-World Pitfalls

-- Scenario: Transfer $200 from Alice's account to Bob's account.
-- This is the canonical ACID demonstration.

-- Start an explicit transaction block
BEGIN;

-- Step 1: Deduct from sender. We use SELECT FOR UPDATE to lock
-- Alice's row so no concurrent transaction can read a stale balance.
SELECT balance FROM accounts WHERE account_id = 'alice' FOR UPDATE;
-- Assume the query returns: balance = 500

-- Step 2: Apply the deduction. CHECK constraint on the table
-- prevents balance going negative — Consistency enforced by DB.
UPDATE accounts
   SET balance = balance - 200
 WHERE account_id = 'alice';

-- Step 3: Credit the receiver.
UPDATE accounts
   SET balance = balance + 200
 WHERE account_id = 'bob';

-- Step 4: Record the transfer for audit purposes.
INSERT INTO transfer_log (from_account, to_account, amount, transferred_at)
VALUES ('alice', 'bob', 200, NOW());

-- Step 5: Commit. Only at this point are ALL changes made permanent
-- and visible to other transactions. The WAL is flushed to disk here.
COMMIT;

-- If ANY step above had failed (e.g., alice had insufficient balance
-- and the CHECK constraint fired), the database would execute:
-- ROLLBACK;
-- ...and every change above would be undone. Alice keeps her $500.
-- Bob gets nothing. The log stays empty. Database is consistent.

-- ============================================================
-- DEMONSTRATING A NON-REPEATABLE READ at READ COMMITTED level
-- Run these two sessions simultaneously to observe the anomaly.
-- ============================================================

-- ---- SESSION A (start first) ----
BEGIN; -- Session A starts a transaction

-- First read: product price is 100
SELECT price FROM products WHERE product_id = 42;
-- Returns: price = 100

-- (Pause here — now run Session B below and let it commit)

-- Second read of the SAME row in the SAME transaction
SELECT price FROM products WHERE product_id = 42;
-- At READ COMMITTED: Returns price = 150 (non-repeatable read!)
-- At REPEATABLE READ: Still returns 100 (anomaly prevented)

COMMIT;


-- ---- SESSION B (run while Session A is paused) ----
BEGIN;

-- A pricing engine updates the product mid-flight
UPDATE products
   SET price = 150
 WHERE product_id = 42;

COMMIT; -- Session A can now see this committed change


-- ============================================================
-- FIX: Upgrade isolation level for Session A
-- ============================================================

-- Tell PostgreSQL this transaction needs stronger isolation
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT price FROM products WHERE product_id = 42;
-- Returns: 100

-- Even after Session B commits its UPDATE, this transaction
-- sees a consistent snapshot of data as of the moment BEGIN ran.
SELECT price FROM products WHERE product_id = 42;
-- Still returns: 100 — anomaly eliminated

COMMIT;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

public class TransactionWithRetryAndSavepoint {

    // PostgreSQL error code for serialization failure / deadlock
    private static final String DEADLOCK_ERROR_CODE = "40P01";
    private static final String SERIALIZATION_ERROR_CODE = "40001";
    private static final int MAX_RETRY_ATTEMPTS = 3;

    public static void main(String[] args) throws Exception {
        String jdbcUrl = "jdbc:postgresql://localhost:5432/forge_bank";
        String username = "forge_user";
        String password = "forge_pass";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
            processBatchOrdersWithRetry(conn);
        }
    }

    /**
     * Processes a batch of orders. Uses savepoints so a single bad
     * order doesn't abort the entire batch. Retries on deadlock.
     */
    static void processBatchOrdersWithRetry(Connection conn) throws SQLException {
        int attemptNumber = 0;
        boolean transactionSucceeded = false;

        while (!transactionSucceeded && attemptNumber < MAX_RETRY_ATTEMPTS) {
            attemptNumber++;
            try {
                // Turn off auto-commit so we control the transaction boundary
                conn.setAutoCommit(false);

                // Set isolation level — Repeatable Read for consistent inventory reads
                conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

                processBatchWithSavepoints(conn);

                // All orders processed — make every change permanent
                conn.commit();
                System.out.println("Batch committed successfully on attempt " + attemptNumber);
                transactionSucceeded = true;

            } catch (SQLException sqlEx) {
                String sqlState = sqlEx.getSQLState();

                if (DEADLOCK_ERROR_CODE.equals(sqlState) || SERIALIZATION_ERROR_CODE.equals(sqlState)) {
                    // Deadlock or serialization failure — safe to retry
                    System.out.printf("Attempt %d failed with %s (%s). Retrying...%n",
                            attemptNumber, sqlState, sqlEx.getMessage());
                    conn.rollback(); // Undo everything before retrying

                    try {
                        // Brief back-off to reduce contention before retry
                        Thread.sleep(50L * attemptNumber);
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                    }
                } else {
                    // Non-retryable error (e.g., constraint violation) — give up
                    conn.rollback();
                    throw new RuntimeException("Non-retryable DB error: " + sqlEx.getMessage(), sqlEx);
                }
            } finally {
                // Always restore auto-commit so connection pool gets a clean connection
                conn.setAutoCommit(true);
            }
        }

        if (!transactionSucceeded) {
            throw new RuntimeException("Batch failed after " + MAX_RETRY_ATTEMPTS + " attempts.");
        }
    }

    /**
     * Inserts three orders using savepoints. A failure on order 2
     * rolls back only order 2, keeping orders 1 and 3.
     */
    static void processBatchWithSavepoints(Connection conn) throws SQLException {
        String insertOrderSql = "INSERT INTO orders (customer_id, product_id, quantity) VALUES (?, ?, ?)";

        // Simulate three orders from the batch
        int[][] orderBatch = {
            {101, 42, 2},  // customer 101 wants 2 of product 42
            {102, 99, 1},  // customer 102 wants 1 of product 99 (will simulate failure)
            {103, 17, 5}   // customer 103 wants 5 of product 17
        };

        for (int[] order : orderBatch) {
            int customerId = order[0];
            int productId  = order[1];
            int quantity   = order[2];

            // Create a savepoint BEFORE each order attempt
            Savepoint beforeOrder = conn.setSavepoint("before_order_" + customerId);

            try (PreparedStatement insertStmt = conn.prepareStatement(insertOrderSql)) {
                insertStmt.setInt(1, customerId);
                insertStmt.setInt(2, productId);
                insertStmt.setInt(3, quantity);
                insertStmt.executeUpdate();

                System.out.printf("Order inserted: customer=%d product=%d qty=%d%n",
                        customerId, productId, quantity);

            } catch (SQLException orderEx) {
                // Roll back ONLY this order, not the whole batch
                System.out.printf("Order for customer %d failed: %s — rolling back to savepoint%n",
                        customerId, orderEx.getMessage());
                conn.rollback(beforeOrder); // The savepoint is our safety net
                // Release the savepoint to free resources
                conn.releaseSavepoint(beforeOrder);
            }
        }
    }
}