Transactions (ACID)

What is a Transaction?

A transaction is a sequence of database operations that are treated as a single, indivisible unit of work. Either ALL operations complete successfully (commit), or NONE of them do (rollback). Transactions are fundamental to maintaining data integrity.

The Classic Example: Bank Transfer

// Transfer $100 from Account A to Account B
// This MUST be atomic - both operations or neither!

BEGIN TRANSACTION;

// Step 1: Debit from Account A
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';

// Step 2: Credit to Account B
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

// If both succeed
COMMIT;

// If anything fails
ROLLBACK;

// Without transactions, if step 1 succeeds but step 2 fails:
// - Account A loses $100
// - Account B doesn't gain $100
// - $100 just vanishes! BAD!

ACID Properties

ACID is the set of properties that guarantee transaction reliability.

A - Atomicity

// ATOMICITY: "All or Nothing"
// A transaction is an atomic unit - it either completes entirely or not at all.

┌───────────────────────────────────────────────────────────┐
│                     TRANSACTION                            │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐       │
│  │ Operation 1 │─▶│ Operation 2 │─▶│ Operation 3 │       │
│  └─────────────┘  └─────────────┘  └─────────────┘       │
│                                                           │
│  If ANY operation fails → ROLLBACK ALL                   │
│  If ALL operations succeed → COMMIT ALL                  │
└───────────────────────────────────────────────────────────┘

// Example: Creating an order
try {
    tx.begin();

    // 1. Create order
    em.persist(order);

    // 2. Create order items
    for (OrderItem item : items) {
        em.persist(item);
    }

    // 3. Update inventory
    for (OrderItem item : items) {
        Product p = em.find(Product.class, item.getProductId());
        p.setStock(p.getStock() - item.getQuantity());
    }

    // 4. Charge customer
    paymentService.charge(order.getCustomerId(), order.getTotal());

    tx.commit();  // All 4 steps complete together
} catch (Exception e) {
    tx.rollback();  // All 4 steps undone
}

C - Consistency

// CONSISTENCY: Database moves from one valid state to another
// Transactions must maintain all database rules and constraints.

// Example constraints that must be maintained:
// - Primary key uniqueness
// - Foreign key references
// - Check constraints (balance >= 0)
// - Unique constraints (email must be unique)

// Before transaction: Total money in system = $10,000
// After transaction: Total money in system = $10,000
// Money doesn't appear or disappear!

┌─────────────────┐                      ┌─────────────────┐
│  VALID STATE    │                      │  VALID STATE    │
│                 │                      │                 │
│  Account A: 500 │      Transaction     │  Account A: 400 │
│  Account B: 300 │  ─────────────────▶  │  Account B: 400 │
│  ─────────────  │                      │  ─────────────  │
│  Total:    800  │                      │  Total:    800  │
└─────────────────┘                      └─────────────────┘

I - Isolation

// ISOLATION: Concurrent transactions don't interfere with each other
// Each transaction sees a consistent snapshot of data.

// Problem without isolation (Lost Update):

Time    Transaction 1             Transaction 2
───────────────────────────────────────────────────
T1      Read balance = 100
T2                                Read balance = 100
T3      Balance = 100 + 50
T4      Write balance = 150
T5                                Balance = 100 + 30
T6                                Write balance = 130  ← T1's update LOST!

// With proper isolation:
Time    Transaction 1             Transaction 2
───────────────────────────────────────────────────
T1      Read balance = 100
T2                                Read balance = 100 (waits or sees 100)
T3      Balance = 100 + 50
T4      Write balance = 150
T5      COMMIT
T6                                Read balance = 150
T7                                Balance = 150 + 30
T8                                Write balance = 180  ← Both updates preserved!

D - Durability

// DURABILITY: Once committed, data survives any failure
// Power outage, crash, hardware failure - data persists!

// How databases achieve durability:

┌─────────────────────────────────────────────────────────────────┐
│                    COMMIT PROCESS                                │
│                                                                  │
│  1. Write changes to TRANSACTION LOG (Write-Ahead Logging)      │
│     └─▶ Log is on persistent storage (disk)                     │
│                                                                  │
│  2. Flush log to disk (fsync)                                   │
│     └─▶ Data guaranteed on stable storage                       │
│                                                                  │
│  3. Return "commit successful" to application                   │
│                                                                  │
│  4. Write actual data pages to disk (can be lazy)               │
│     └─▶ If crash before this, recover from log                  │
│                                                                  │
│  After commit: Even if server explodes, data is safe!           │
└─────────────────────────────────────────────────────────────────┘

Transaction Isolation Levels

Isolation levels control how much transactions are isolated from each other. Higher isolation = more safety but less performance.

Concurrency Problems

// 1. DIRTY READ: Reading uncommitted data from another transaction
Transaction 1                    Transaction 2
─────────────────────────────────────────────────
UPDATE balance SET val=150
                                 SELECT val FROM balance  → 150 (dirty!)
ROLLBACK
                                 Uses 150, but actual value is still 100!

// 2. NON-REPEATABLE READ: Same query returns different results
Transaction 1                    Transaction 2
─────────────────────────────────────────────────
SELECT val FROM balance → 100
                                 UPDATE balance SET val=150
                                 COMMIT
SELECT val FROM balance → 150   Different result!

// 3. PHANTOM READ: New rows appear in repeated query
Transaction 1                    Transaction 2
─────────────────────────────────────────────────
SELECT * WHERE age > 25  → 3 rows
                                 INSERT INTO users (age=30)
                                 COMMIT
SELECT * WHERE age > 25  → 4 rows  Phantom row!

Isolation Levels Comparison

Setting Isolation Levels

// JDBC
Connection conn = dataSource.getConnection();
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

// JPA/Hibernate
@PersistenceContext
EntityManager em;

Map<String, Object> props = new HashMap<>();
props.put("jakarta.persistence.lock.timeout", 3000);
em.find(Account.class, accountId, LockModeType.PESSIMISTIC_WRITE, props);

// Spring @Transactional
@Transactional(isolation = Isolation.SERIALIZABLE)
public void criticalOperation() {
    // Highest isolation
}

@Transactional(isolation = Isolation.READ_COMMITTED)  // Default for most DBs
public void normalOperation() {
    // Standard isolation
}

Transactions in Java

JDBC Transactions

public void transferFunds(long fromId, long toId, BigDecimal amount) {
    Connection conn = null;

    try {
        conn = dataSource.getConnection();

        // 1. Disable auto-commit (start transaction)
        conn.setAutoCommit(false);

        // 2. Debit source account
        try (PreparedStatement debitStmt = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?")) {
            debitStmt.setBigDecimal(1, amount);
            debitStmt.setLong(2, fromId);
            debitStmt.setBigDecimal(3, amount);

            if (debitStmt.executeUpdate() == 0) {
                throw new RuntimeException("Insufficient funds");
            }
        }

        // 3. Credit destination account
        try (PreparedStatement creditStmt = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
            creditStmt.setBigDecimal(1, amount);
            creditStmt.setLong(2, toId);
            creditStmt.executeUpdate();
        }

        // 4. Commit transaction
        conn.commit();

    } catch (Exception e) {
        // 5. Rollback on any error
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        throw new RuntimeException("Transfer failed", e);

    } finally {
        if (conn != null) {
            try {
                conn.setAutoCommit(true);  // Reset for connection pool
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

JPA/Hibernate Transactions

public void transferFunds(long fromId, long toId, BigDecimal amount) {
    EntityManager em = emf.createEntityManager();
    EntityTransaction tx = em.getTransaction();

    try {
        tx.begin();

        Account fromAccount = em.find(Account.class, fromId, LockModeType.PESSIMISTIC_WRITE);
        Account toAccount = em.find(Account.class, toId, LockModeType.PESSIMISTIC_WRITE);

        if (fromAccount.getBalance().compareTo(amount) < 0) {
            throw new RuntimeException("Insufficient funds");
        }

        fromAccount.setBalance(fromAccount.getBalance().subtract(amount));
        toAccount.setBalance(toAccount.getBalance().add(amount));

        tx.commit();

    } catch (Exception e) {
        if (tx.isActive()) {
            tx.rollback();
        }
        throw e;

    } finally {
        em.close();
    }
}

Spring @Transactional

@Service
public class AccountService {

    private final AccountRepository accountRepository;

    // Basic transaction
    @Transactional
    public void transferFunds(Long fromId, Long toId, BigDecimal amount) {
        Account from = accountRepository.findById(fromId)
            .orElseThrow(() -> new AccountNotFoundException(fromId));
        Account to = accountRepository.findById(toId)
            .orElseThrow(() -> new AccountNotFoundException(toId));

        if (from.getBalance().compareTo(amount) < 0) {
            throw new InsufficientFundsException();
        }

        from.withdraw(amount);
        to.deposit(amount);
        // Commit happens automatically if no exception
        // Rollback happens automatically on RuntimeException
    }

    // Read-only transaction (optimization)
    @Transactional(readOnly = true)
    public Account getAccount(Long id) {
        return accountRepository.findById(id).orElseThrow();
    }

    // Custom timeout
    @Transactional(timeout = 30)  // 30 seconds
    public void longRunningOperation() {
        // ...
    }

    // Specify which exceptions cause rollback
    @Transactional(
        rollbackFor = Exception.class,           // Rollback for all exceptions
        noRollbackFor = EmailException.class    // Except this one
    )
    public void createUserAndNotify(User user) {
        userRepository.save(user);
        emailService.sendWelcome(user);  // Email failure won't rollback user creation
    }
}

Transaction Propagation (Spring)

Propagation defines how transactions behave when one @Transactional method calls another.

// REQUIRED (default): Join existing or create new
@Transactional(propagation = Propagation.REQUIRED)
public void methodA() {
    // If called without transaction → creates new
    // If called within transaction → joins it
}

// REQUIRES_NEW: Always create new (suspends existing)
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void auditLog(String action) {
    // Always runs in its own transaction
    // Commits even if outer transaction rolls back
}

// MANDATORY: Must run within existing transaction
@Transactional(propagation = Propagation.MANDATORY)
public void mustBeInTransaction() {
    // Throws exception if no transaction exists
}

// SUPPORTS: Use transaction if exists, otherwise non-transactional
@Transactional(propagation = Propagation.SUPPORTS)
public Account findAccount(Long id) {
    // Works with or without transaction
}

// NOT_SUPPORTED: Always run non-transactionally
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public void nonTransactionalOperation() {
    // Suspends existing transaction
}

// NEVER: Must NOT run within transaction
@Transactional(propagation = Propagation.NEVER)
public void mustNotBeInTransaction() {
    // Throws exception if transaction exists
}

// NESTED: Nested transaction with savepoint
@Transactional(propagation = Propagation.NESTED)
public void nestedOperation() {
    // Creates savepoint, can rollback independently
}

Propagation Example

@Service
public class OrderService {

    private final PaymentService paymentService;
    private final AuditService auditService;

    @Transactional
    public void placeOrder(Order order) {
        // Save order (within this transaction)
        orderRepository.save(order);

        // Process payment (within this transaction - REQUIRED)
        paymentService.processPayment(order);  // Joins transaction

        // Audit log (separate transaction - REQUIRES_NEW)
        auditService.logOrderPlaced(order);  // Own transaction

        // If payment fails, order is rolled back
        // But audit log is already committed!
    }
}

@Service
public class PaymentService {

    @Transactional(propagation = Propagation.REQUIRED)  // Default
    public void processPayment(Order order) {
        // Part of outer transaction
    }
}

@Service
public class AuditService {

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logOrderPlaced(Order order) {
        // Always commits, even if outer rolls back
    }
}

Locking Strategies

Optimistic Locking

// Optimistic: Assume no conflicts, detect them at commit time
// Best for read-heavy workloads with rare conflicts

@Entity
public class Product {

    @Id
    private Long id;

    private String name;
    private Integer quantity;

    @Version  // Optimistic locking version field
    private Long version;
}

// How it works:
// 1. Read product (version = 1)
// 2. Modify product
// 3. UPDATE product SET ..., version = 2 WHERE id = ? AND version = 1
// 4. If 0 rows updated → OptimisticLockException (someone else modified!)

@Service
public class ProductService {

    @Transactional
    public void updateQuantity(Long productId, Integer newQuantity) {
        try {
            Product product = productRepository.findById(productId).orElseThrow();
            product.setQuantity(newQuantity);
            // On save, JPA checks version
        } catch (OptimisticLockException e) {
            // Another transaction modified this product
            throw new ConcurrentModificationException("Product was modified by another user");
        }
    }
}

Pessimistic Locking

// Pessimistic: Assume conflicts, prevent them with locks
// Best for high-contention scenarios

@Service
public class InventoryService {

    @PersistenceContext
    private EntityManager em;

    @Transactional
    public void decrementStock(Long productId, int quantity) {
        // Acquire exclusive lock (FOR UPDATE)
        Product product = em.find(
            Product.class,
            productId,
            LockModeType.PESSIMISTIC_WRITE  // SELECT ... FOR UPDATE
        );

        if (product.getStock() < quantity) {
            throw new InsufficientStockException();
        }

        product.setStock(product.getStock() - quantity);
        // Lock released on commit/rollback
    }
}

// Lock modes:
LockModeType.PESSIMISTIC_READ   // Shared lock (read allowed, write blocked)
LockModeType.PESSIMISTIC_WRITE  // Exclusive lock (all access blocked)
LockModeType.PESSIMISTIC_FORCE_INCREMENT  // Exclusive + increment version

// With JPQL
List<Product> products = em.createQuery(
    "SELECT p FROM Product p WHERE p.category = :cat", Product.class)
    .setParameter("cat", category)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE)
    .getResultList();

// Spring Data JPA
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT p FROM Product p WHERE p.id = :id")
    Optional<Product> findByIdForUpdate(@Param("id") Long id);
}

Optimistic vs Pessimistic

Common Transaction Pitfalls

@Service
public class OrderService {

    @Transactional
    public void placeOrder(Order order) {
        saveOrder(order);    // Calls method below
        processPayment(order);
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void saveOrder(Order order) {
        // THIS DOESN'T GET ITS OWN TRANSACTION!
        // Self-invocation bypasses the proxy!
    }
}

// Solution: Inject self or use separate service
@Service
public class OrderService {

    @Autowired
    private OrderPersistenceService persistenceService;

    @Transactional
    public void placeOrder(Order order) {
        persistenceService.saveOrder(order);  // Proxy is used!
        processPayment(order);
    }
}

// BAD: Swallowing exception prevents rollback
@Transactional
public void badMethod() {
    try {
        riskyOperation();
    } catch (Exception e) {
        log.error("Error", e);
        // Transaction commits! Data may be inconsistent!
    }
}

// GOOD: Re-throw or manually rollback
@Transactional
public void goodMethod() {
    try {
        riskyOperation();
    } catch (Exception e) {
        log.error("Error", e);
        throw e;  // Transaction rolls back
    }
}

// OR: Mark for rollback manually
@Autowired
TransactionStatus status;

try {
    riskyOperation();
} catch (Exception e) {
    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}

// BAD: Transaction held during external call
@Transactional
public void processOrder(Order order) {
    orderRepository.save(order);
    externalPaymentService.charge(order);  // HTTP call! 5 seconds!
    // Database connection held for entire time
}

// GOOD: External calls outside transaction
public void processOrder(Order order) {
    Order savedOrder = saveOrder(order);  // Transaction 1
    PaymentResult result = externalPaymentService.charge(order);  // No tx
    updateOrderStatus(savedOrder, result);  // Transaction 2
}

@Transactional
private Order saveOrder(Order order) {
    return orderRepository.save(order);
}

Summary