-- Both are equivalent
START TRANSACTION;
-- ... SQL statements ...
COMMIT;

BEGIN;
-- ... SQL statements ...
COMMIT;

-- Classic bank transfer example
START TRANSACTION;

-- Deduct from sender
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Add to receiver
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- Both updates succeeded — save permanently
COMMIT;

-- -----------------------------------------------

-- If an error occurred instead:
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- Suppose account 2 was closed and the update fails
-- Roll back both changes — account 1 is restored
ROLLBACK;

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

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

-- Now every statement must be explicitly committed
UPDATE products SET price = 9.99 WHERE product_id = 1;
COMMIT;   -- Must commit manually, or the change is lost when session ends

-- Re-enable autocommit
SET autocommit = 1;

START TRANSACTION;

INSERT INTO orders (customer_id, order_date, status)
VALUES (42, NOW(), 'pending');

SAVEPOINT after_order;   -- Mark this point

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 7, 2, 29.99);

-- Suppose the item insert causes a problem
ROLLBACK TO SAVEPOINT after_order;
-- The order row is still in place, the item row was rolled back

-- Try a different item
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 12, 1, 59.99);

COMMIT;   -- Commits the order + the second item attempt

START TRANSACTION;

SAVEPOINT sp1;
-- ... work ...

SAVEPOINT sp2;
-- ... more work ...

-- Remove sp2 — cannot roll back to it anymore, but does NOT commit
RELEASE SAVEPOINT sp2;

-- Can still roll back to sp1
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

START TRANSACTION;

INSERT INTO customers (first_name, last_name, email)
VALUES ('Test', 'User', 'test@example.com');

-- WARNING: This implicitly commits the INSERT above!
-- The INSERT cannot be rolled back after this point
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

ROLLBACK;  -- Does NOT undo the INSERT or the ALTER TABLE

-- Pattern used in application code (Python, Node, PHP, Java):
-- try {
--   START TRANSACTION;
--   ... DML statements ...
--   COMMIT;
-- } catch (error) {
--   ROLLBACK;
--   throw error;
-- }

-- Replicated in a stored procedure:
DELIMITER //

CREATE PROCEDURE transfer_funds(
  IN p_from_account INT,
  IN p_to_account   INT,
  IN p_amount       DECIMAL(10,2),
  OUT p_result      VARCHAR(100)
)
BEGIN
  DECLARE v_balance DECIMAL(10,2);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_result = 'Transfer failed — transaction rolled back';
  END;

  START TRANSACTION;

  -- Check sufficient funds
  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_from_account FOR UPDATE;

  IF v_balance < p_amount THEN
    ROLLBACK;
    SET p_result = 'Insufficient funds';
    LEAVE transfer_funds;
  END IF;

  UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account;
  UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account;

  INSERT INTO transfers (from_account, to_account, amount, transferred_at)
  VALUES (p_from_account, p_to_account, p_amount, NOW());

  COMMIT;
  SET p_result = 'Transfer successful';
END //

DELIMITER ;

-- See open transactions in InnoDB
SELECT * FROM information_schema.INNODB_TRXG

-- See locks held
SELECT * FROM information_schema.INNODB_LOCKSG
-- (MySQL 8.0+: use performance_schema.data_locks instead)
SELECT * FROM performance_schema.data_locksG

-- See lock waits
SELECT * FROM performance_schema.data_lock_waitsG

START TRANSACTION;

-- 1. Reserve stock (lock the product rows)
SELECT product_id, stock_qty FROM products
WHERE product_id IN (7, 12, 15) FOR UPDATE;

-- 2. Create the order header
INSERT INTO orders (customer_id, order_date, status, total_amount)
VALUES (42, NOW(), 'pending', 0);

SET @order_id = LAST_INSERT_ID();

-- 3. Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (@order_id, 7, 2, 29.99),
  (@order_id, 12, 1, 59.99),
  (@order_id, 15, 3, 9.99);

-- 4. Deduct stock
UPDATE products SET stock_qty = stock_qty - 2 WHERE product_id = 7;
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 12;
UPDATE products SET stock_qty = stock_qty - 3 WHERE product_id = 15;

-- 5. Update order total from actual line items
UPDATE orders
SET total_amount = (
  SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = @order_id
)
WHERE order_id = @order_id;

-- 6. All good — commit permanently
COMMIT;

-- Declare a read-only transaction (optimization hint for InnoDB)
START TRANSACTION READ ONLY;

SELECT SUM(total_amount) FROM orders WHERE customer_id = 42;
SELECT * FROM customers WHERE customer_id = 42;

COMMIT;  -- or ROLLBACK — both end the read-only transaction

-- Use a consistent snapshot for a long analytical read
-- START TRANSACTION READ ONLY prevents InnoDB from assigning a write TRX ID
START TRANSACTION READ ONLY;

SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  COUNT(*)                         AS orders,
  SUM(total_amount)                AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

-- Even if other sessions commit new orders while we read,
-- we see a consistent snapshot from when we opened the transaction
COMMIT;

# Node.js (mysql2) pattern:
# const conn = await pool.getConnection();
# try {
#   await conn.beginTransaction();
#   await conn.query('UPDATE accounts SET balance = balance - 500 WHERE id = 1');
#   await conn.query('UPDATE accounts SET balance = balance + 500 WHERE id = 2');
#   await conn.commit();
# } catch (err) {
#   await conn.rollback();
#   throw err;
# } finally {
#   conn.release();
# }

# Python (mysql-connector) pattern:
# cnx = mysql.connector.connect(...)
# cursor = cnx.cursor()
# try:
#   cursor.execute('UPDATE accounts SET balance = balance - 500 WHERE id = 1')
#   cursor.execute('UPDATE accounts SET balance = balance + 500 WHERE id = 2')
#   cnx.commit()
# except Exception as e:
#   cnx.rollback()
#   raise e