Transactions in DBMS: Mastering ACID, Isolation Levels, and Concurrency

-- io.thecodeforge: Atomic Bank Transfer Example
BEGIN;

-- Step 1: Deduct from source
UPDATE io_thecodeforge.accounts 
SET balance = balance - 500, updated_at = NOW() 
WHERE account_id = 'ACC-001' AND balance >= 500;

-- Step 2: Add to destination
UPDATE io_thecodeforge.accounts 
SET balance = balance + 500, updated_at = NOW() 
WHERE account_id = 'ACC-002';

-- If a network timeout or constraint violation occurs here,
-- the entire block is invalidated.
COMMIT;

-- PRO TIP: In Spring Boot, use @Transactional(propagation = Propagation.REQUIRED)
-- to wrap these calls in a single proxy-managed transaction.

-- io.thecodeforge: Demonstrating Read Phenomena

-- 1. READ COMMITTED (Default for Postgres)
-- Prevents Dirty Reads but allows Non-Repeatable Reads.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 2. REPEATABLE READ (Default for MySQL InnoDB)
-- Uses MVCC (Multi-Version Concurrency Control) to ensure you see a snapshot.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 3. SERIALIZABLE (Highest Safety)
-- Prevents Phantoms by placing range locks or using predicate locking.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Production Pattern: Use 'SELECT ... FOR UPDATE' to skip isolation logic
-- and force a row-level write lock immediately.
BEGIN;
SELECT balance FROM io_thecodeforge.accounts WHERE account_id = 'ACC-001' FOR UPDATE;
-- This row is now locked until we COMMIT.

-- io.thecodeforge: Deadlock Prevention via Deterministic Ordering

-- BAD: Locking in random order based on app logic
-- TX1: Update A, then B
-- TX2: Update B, then A  <-- High Deadlock Risk!

-- GOOD: Always sort IDs before locking
BEGIN;
-- By ordering our IDs, all transactions wait in the same 'queue'
SELECT * FROM io_thecodeforge.accounts 
WHERE account_id IN ('ACC-001', 'ACC-002') 
ORDER BY account_id ASC 
FOR UPDATE;

UPDATE io_thecodeforge.accounts SET balance = balance - 100 WHERE account_id = 'ACC-001';
UPDATE io_thecodeforge.accounts SET balance = balance + 100 WHERE account_id = 'ACC-002';
COMMIT;