SQL: Transaction Processing

-- Start a transaction
BEGIN;  -- or START TRANSACTION

-- Execute a series of operations
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- All succeeded, commit
COMMIT;

-- Something went wrong, roll back
ROLLBACK;

BEGIN;

INSERT INTO orders VALUES (1020, 101, 1, '2026-06-28', 500.00, 'pending');
SAVEPOINT sp1;

INSERT INTO order_items VALUES (1020, 1, 2, 100.00);
SAVEPOINT sp2;

-- If the second step has an error, roll back only to sp1
ROLLBACK TO sp1;

-- Retain the first step's operations
COMMIT;

-- View current isolation level
SELECT @@transaction_isolation;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Transaction A: Locks Table 1 → Waits for Table 2
Transaction B: Locks Table 2 → Waits for Table 1
→ Waiting forever, can never complete

-- Start a transaction
BEGIN;  -- MySQL, PostgreSQL
START TRANSACTION;  -- Also supported by MySQL

-- Commit the transaction (all operations take permanent effect)
COMMIT;

-- Roll back the transaction (undo all operations)
ROLLBACK;

-- Savepoints
SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
RELEASE SAVEPOINT savepoint_name;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL isolation_level;
-- Isolation levels: READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

-- Set global isolation level (affects all new connections)
SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level;

-- Autocommit switch (MySQL defaults to ON)
SET autocommit = 0;  -- Disable autocommit, requires manual COMMIT
SET autocommit = 1;  -- Enable autocommit (default)

-- Scenario: A customer places an order for a product; need to create the order and deduct inventory simultaneously
BEGIN;

-- Step 1: Create the order
INSERT INTO orders (order_id, customer_id, employee_id, order_date, total_amount, status)
VALUES (1020, 101, 1, '2026-06-28', 6999.00, 'pending');

-- Step 2: Deduct inventory
UPDATE products SET stock = stock - 1 WHERE product_id = 1;

-- Step 3: Check if inventory is sufficient (roll back if stock < 0)
-- In practice, this is usually handled by application logic or triggers

-- All succeeded, commit
COMMIT;

BEGIN;

INSERT INTO orders (order_id, customer_id, employee_id, order_date, total_amount, status)
VALUES (1021, 102, 3, '2026-06-28', 129.00, 'pending');

UPDATE products SET stock = stock - 1 WHERE product_id = 2;

-- Suppose we discover insufficient inventory, roll back the entire transaction
ROLLBACK;
-- Both the order and inventory are restored to their pre-transaction state

BEGIN;

-- First order: successful
INSERT INTO orders VALUES (1022, 103, 4, '2026-06-28', 3500.00, 'pending');
UPDATE products SET stock = stock - 1 WHERE product_id = 4;
SAVEPOINT after_first_order;

-- Second order: error
INSERT INTO orders VALUES (1023, 104, 4, '2026-06-28', 2200.00, 'pending');
UPDATE products SET stock = stock - 1 WHERE product_id = 5;
SAVEPOINT after_second_order;

-- Discovered an issue with the second order, roll back to after the first
ROLLBACK TO after_first_order;

-- The first order is still valid, commit
COMMIT;
-- Result: Order 1022 is retained, Order 1023 is rolled back

SELECT order_id, total_amount, status FROM orders WHERE order_id >= 1022;

order_id | total_amount | status
---------+-------------+--------
    1022 |     3500.00 | pending

BEGIN;

-- Give Sales department a 10% raise
UPDATE employees 
SET salary = salary * 1.10 
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

SAVEPOINT after_sales_raise;

-- Give Tech department an 8% raise
UPDATE employees 
SET salary = salary * 1.08 
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Tech');

SAVEPOINT after_tech_raise;

-- Suppose budget approval didn't pass, only undo the Tech department raise
ROLLBACK TO after_sales_raise;

-- Sales department raise is retained, commit
COMMIT;

-- Session A: Transfer budget from department 1 to department 2
BEGIN;
UPDATE departments SET budget = budget - 50000 WHERE department_id = 1;
-- Wait a moment...
UPDATE departments SET budget = budget + 50000 WHERE department_id = 2;
COMMIT;

-- Session B: Simultaneously transfer budget from department 2 to department 1 (another connection)
BEGIN;
UPDATE departments SET budget = budget - 30000 WHERE department_id = 2;
UPDATE departments SET budget = budget + 30000 WHERE department_id = 1;
COMMIT;

-- Both sessions access departments in ascending order by department_id
-- Session A:
BEGIN;
UPDATE departments SET budget = budget - 50000 WHERE department_id = 1;  -- Lock 1 first
UPDATE departments SET budget = budget + 50000 WHERE department_id = 2;  -- Then lock 2
COMMIT;

-- Session B:
BEGIN;
UPDATE departments SET budget = budget + 30000 WHERE department_id = 1;  -- Also lock 1 first
UPDATE departments SET budget = budget - 30000 WHERE department_id = 2;  -- Then lock 2
COMMIT;