Transaction with Isolation Levels

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Step 1: Check available stock
SELECT stock_quantity INTO v_stock
FROM products
WHERE product_id = 101
FOR UPDATE;

IF v_stock < 5 THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient stock for product 101';
END IF;

-- Step 2: Create order
INSERT INTO orders (customer_id, order_date, status)
VALUES (p_customer_id, CURRENT_TIMESTAMP, 'pending')
RETURNING order_id INTO v_order_id;

-- Savepoint before risky operation
SAVEPOINT before_inventory_update;

-- Step 3: Update inventory
UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 101;

-- Step 4: Add order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (v_order_id, 101, 5, 29.99);

COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT before_inventory_update;
        RAISE;
END;