SQL Transactions & TCL — COMMIT, ROLLBACK, SAVEPOINT

-- Basic transaction structure
BEGIN;                           -- Start transaction (PostgreSQL)
-- or: START TRANSACTION;        -- MySQL/SQL Server

    UPDATE Inventory SET Stock = Stock - 1 WHERE ProductID = 42 AND Stock > 0;

    -- Check if update affected a row (stock was available)
    -- If not, we should rollback

    INSERT INTO Orders (CustomerID, ProductID, Quantity, Amount)
    VALUES (101, 42, 1, 299.99);

    UPDATE Account SET Balance = Balance - 299.99
    WHERE AccountID = 'CUST101' AND Balance >= 299.99;

COMMIT;                          -- All changes become permanent
-- If any statement fails, the DBMS auto-rollbacks (in most modes)

-- Explicit ROLLBACK on error (PL/pgSQL stored procedure)
CREATE OR REPLACE PROCEDURE place_order(
    p_customer_id INT, p_product_id INT, p_qty INT
) LANGUAGE plpgsql AS $$
DECLARE
    v_stock  INT;
    v_price  DECIMAL(10,2);
    v_balance DECIMAL(12,2);
BEGIN
    -- Check stock
    SELECT Stock INTO v_stock FROM Inventory WHERE ProductID = p_product_id;
    IF v_stock < p_qty THEN
        RAISE EXCEPTION 'Insufficient stock: % available', v_stock;
    END IF;

    -- Deduct stock
    UPDATE Inventory SET Stock = Stock - p_qty WHERE ProductID = p_product_id;

    -- Get price
    SELECT Price INTO v_price FROM Product WHERE ProductID = p_product_id;

    -- Create order
    INSERT INTO Orders (CustomerID, ProductID, Quantity, Amount)
    VALUES (p_customer_id, p_product_id, p_qty, v_price * p_qty);

    -- Charge account
    UPDATE Account SET Balance = Balance - (v_price * p_qty)
    WHERE CustomerID = p_customer_id;

    COMMIT;
    RAISE NOTICE 'Order placed successfully';

EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    RAISE EXCEPTION 'Order failed: %', SQLERRM;
END;
$$;

-- Autocommit mode (default in most SQL clients)
-- Without explicit BEGIN/COMMIT, each statement is its own transaction
UPDATE Employee SET Salary = 50000 WHERE EmpID = 1;  -- Auto-committed immediately
-- No ROLLBACK possible after this!

-- SAVEPOINT marks a point within a transaction to roll back to
-- Without losing ALL work done since BEGIN

BEGIN;

    -- Step 1: Update department budgets
    UPDATE Department SET Budget = Budget * 1.10;
    SAVEPOINT after_budget_update;     -- Mark this point

    -- Step 2: Give raises to all active employees
    UPDATE Employee SET Salary = Salary * 1.05 WHERE Status = 'Active';
    SAVEPOINT after_salary_update;

    -- Step 3: Calculate and insert bonus records (this might fail)
    INSERT INTO Bonus (EmpID, Amount, Year)
    SELECT EmpID, Salary * 0.10, 2024 FROM Employee WHERE Status = 'Active';

    -- If bonus calculation has an error, rollback ONLY to after_salary_update
    -- (undoes the INSERT but keeps the salary UPDATE)
    ROLLBACK TO SAVEPOINT after_salary_update;

    -- Try a different bonus calculation
    INSERT INTO Bonus (EmpID, Amount, Year)
    SELECT EmpID, LEAST(Salary * 0.10, 50000), 2024 FROM Employee WHERE Status = 'Active';

COMMIT;  -- Saves: budget update + salary update + corrected bonus insert

-- RELEASE SAVEPOINT: remove a savepoint (free memory, cannot rollback to it anymore)
RELEASE SAVEPOINT after_budget_update;

-- Nested transactions (PostgreSQL uses savepoints internally)
-- Some DBMS support nested BEGIN...COMMIT within a transaction
-- PostgreSQL does NOT support true nested transactions — use SAVEPOINTs instead

-- Set for the current transaction (PostgreSQL/SQL Server)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- or:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- SQL Server
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- MySQL

-- PostgreSQL: set default isolation level for session
SET default_transaction_isolation = 'serializable';

-- Check current isolation level
SHOW default_transaction_isolation;    -- PostgreSQL
SELECT @@TX_ISOLATION;                 -- MySQL

-- Deadlock example and handling:
-- T1: UPDATE Account SET Balance = Balance - 100 WHERE ID = 1;
-- T1: UPDATE Account SET Balance = Balance + 100 WHERE ID = 2;
-- T2: UPDATE Account SET Balance = Balance - 50  WHERE ID = 2;  -- Waits for T1
-- T2: UPDATE Account SET Balance = Balance + 50  WHERE ID = 1;  -- T1 waits for T2 → DEADLOCK!
-- DBMS detects cycle and aborts the younger transaction with error:
-- ERROR: deadlock detected — DETAIL: Process 12345 waits for T2; Process 12346 waits for T1
-- Handle in application: catch deadlock error and retry the transaction