ROLLBACK

-- Basic ROLLBACK
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- Something goes wrong...
ROLLBACK;  -- Undo all changes — balance restored

-- ROLLBACK in error handling (PL/pgSQL)
DO $$
BEGIN
  UPDATE inventory SET stock = stock - 5 WHERE product_id = 101;

  IF NOT FOUND THEN
    ROLLBACK;
    RAISE EXCEPTION 'Product not found';
  END IF;

  INSERT INTO sales (product_id, qty) VALUES (101, 5);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;  -- re-raise the exception
END;
$$;

-- Application-level pattern (Python example in comments)
-- try:
--     cursor.execute("BEGIN")
--     cursor.execute("UPDATE ...")
--     cursor.execute("INSERT ...")
--     conn.commit()
-- except Exception as e:
--     conn.rollback()  -- ROLLBACK on any error

-- Price update transaction with validation and rollback
BEGIN;

-- Update prices
UPDATE products
SET price = price * 1.10  -- 10% price increase
WHERE category_id = 5;

-- Validate: no product should exceed ₹1,00,000
SELECT COUNT(*) INTO price_violations
FROM products
WHERE category_id = 5 AND price > 100000;

-- If validation fails, roll back
-- (In application code: check affected rows and rollback if needed)
DO $$
DECLARE
  violations INT;
BEGIN
  SELECT COUNT(*) INTO violations
  FROM products WHERE category_id = 5 AND price > 100000;

  IF violations > 0 THEN
    ROLLBACK;
    RAISE EXCEPTION '% products exceed ₹1,00,000 after price increase', violations;
  END IF;

  COMMIT;
  RAISE NOTICE 'Price update committed successfully';
END;
$$;