MySQL Transactions

-- Place an order: insert order + items atomically
START TRANSACTION;

-- Step 1: Insert the order
INSERT INTO orders (customer_id, total, status)
VALUES (1, 0.00, 'pending');

-- Step 2: Get the new order ID
SET @order_id = LAST_INSERT_ID();

-- Step 3: Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (@order_id, 1, 1, 1299.99),
    (@order_id, 2, 2, 29.99);

-- Step 4: Update the order total
UPDATE orders
SET total = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = @order_id
)
WHERE order_id = @order_id;

-- Step 5: Reduce stock
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
UPDATE products SET stock = stock - 2 WHERE product_id = 2;

-- All steps succeeded — commit the transaction
COMMIT;

-- If any step fails, roll back all changes
-- ROLLBACK;

START TRANSACTION;

-- Insert a new customer
INSERT INTO customers (first_name, last_name, email, active)
VALUES ('Henry', 'Adams', 'henry@example.com', 1);

-- Create a savepoint after the customer insert
SAVEPOINT after_customer;

-- Try to insert an order for the new customer
INSERT INTO orders (customer_id, total, status)
VALUES (LAST_INSERT_ID(), 150.00, 'pending');

-- Something went wrong with the order — roll back to the savepoint
-- (the customer insert is preserved, only the order is undone)
ROLLBACK TO SAVEPOINT after_customer;

-- Release the savepoint (optional cleanup)
RELEASE SAVEPOINT after_customer;

-- Commit the customer insert
COMMIT;

-- Check autocommit setting
SHOW VARIABLES LIKE 'autocommit';

-- Disable autocommit for the session
SET autocommit = 0;

-- Now every statement requires an explicit COMMIT or ROLLBACK
INSERT INTO customers (first_name, last_name, email, active)
VALUES ('Iris', 'Clark', 'iris@example.com', 1);
COMMIT;  -- must commit manually

-- Re-enable autocommit
SET autocommit = 1;

-- Check current isolation level
SELECT @@transaction_isolation;

-- Set isolation level for the next transaction only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

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

-- Isolation levels (from least to most strict):
-- READ UNCOMMITTED : can read uncommitted changes from other transactions (dirty reads)
-- READ COMMITTED   : only reads committed data; non-repeatable reads possible
-- REPEATABLE READ  : default in MySQL; same SELECT returns same data within a transaction
-- SERIALIZABLE     : strictest; transactions execute as if serial (no concurrency issues)

-- Example: use REPEATABLE READ for a financial report
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
    SELECT SUM(total) AS total_revenue FROM orders WHERE status = 'delivered';
    -- ... other report queries ...
    -- All SELECTs see the same snapshot of data
COMMIT;