Database Transactions & ACID

---

Definition

---

Isolation Levels

---

Read Phenomena

---

Transaction Examples

-- BASIC TRANSACTION
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- ROLLBACK ON ERROR
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Error occurs
ROLLBACK;  -- Nothing changed

-- SAVEPOINTS (partial rollback)
BEGIN;
INSERT INTO orders (id, total) VALUES (1, 100);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id) VALUES (1, 'A');
-- Error with this item
ROLLBACK TO SAVEPOINT order_created;
-- Order still exists, item doesn't

INSERT INTO order_items (order_id, product_id) VALUES (1, 'B');
COMMIT;

-- SET ISOLATION LEVEL
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Now in serializable mode
COMMIT;

-- Or per session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- PESSIMISTIC LOCKING
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is locked until transaction ends

-- OPTIMISTIC LOCKING (application level)
UPDATE products
SET name = 'New Name', version = version + 1
WHERE id = 1 AND version = 5;  -- Check version
-- If 0 rows affected, someone else updated it

---

Locking Strategies

---

Distributed Transactions

// TWO-PHASE COMMIT (2PC)

// Phase 1: Prepare
// Coordinator asks all participants: "Can you commit?"
// Participants write to log, respond yes/no

// Phase 2: Commit/Abort
// If all said yes: Coordinator says "COMMIT"
// If any said no: Coordinator says "ABORT"

// Problem: Blocking if coordinator fails

// SAGA PATTERN (Alternative)
// Chain of local transactions with compensations

// Order Saga:
// 1. Create Order → Compensation: Cancel Order
// 2. Reserve Inventory → Compensation: Release Inventory
// 3. Process Payment → Compensation: Refund Payment
// 4. Ship Order → Compensation: Cancel Shipment

// If step 3 fails:
// - Compensate step 2: Release Inventory
// - Compensate step 1: Cancel Order

@Saga
class OrderSaga {
    @StartSaga
    void createOrder(Order order) {
        orderService.create(order);
        inventoryService.reserve(order.getItems());
    }

    @SagaStep(compensate = "releaseInventory")
    void reserveInventory(Order order) {
        inventoryService.reserve(order.getItems());
    }

    @Compensate
    void releaseInventory(Order order) {
        inventoryService.release(order.getItems());
    }

    @SagaStep(compensate = "refundPayment")
    void processPayment(Order order) {
        paymentService.charge(order);
    }

    @Compensate
    void refundPayment(Order order) {
        paymentService.refund(order);
    }
}

---

Tips & Tricks

---