What is a Transaction

-- Explicit transaction: process an order
BEGIN;  -- or START TRANSACTION (MySQL)

-- Step 1: insert the order
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 999.99, 'pending')
RETURNING id;  -- use the id in subsequent steps

-- Step 2: insert payment
INSERT INTO payments (order_id, amount, payment_method, paid_at)
VALUES (1, 999.99, 'credit_card', NOW());

-- Step 3: decrement product stock
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- All succeeded - make permanent
COMMIT;

-- If any step fails, undo everything:
ROLLBACK;

-- Auto-commit mode (default): each statement is its own transaction
INSERT INTO users (name, email) VALUES ('Tom', 'tom@.com');
-- Immediately committed, can't rollback

-- MySQL START TRANSACTION
START TRANSACTION;
UPDATE products SET stock = 50 WHERE id = 1;
INSERT INTO orders (user_id, total_amount, status) VALUES (2, 599.99, 'pending');
COMMIT;