Transactions / Isolation

-- 1) Basic transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If either UPDATE fails, ROLLBACK to undo both.

-- 2) Rollback explicitly
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 42;
-- Check business rule:
SELECT stock FROM products WHERE id = 42;
-- If stock is negative:
ROLLBACK;

-- 3) Savepoints — partial rollback
START TRANSACTION;
INSERT INTO users (email) VALUES ('a@x.com');
SAVEPOINT sp1;
INSERT INTO posts (user_id, title) VALUES (LAST_INSERT_ID(), 'first');
SAVEPOINT sp2;
INSERT INTO posts (user_id, title) VALUES (LAST_INSERT_ID(), 'bad');
ROLLBACK TO SAVEPOINT sp2;     -- undo just the bad post
COMMIT;

-- 4) Isolation levels (InnoDB default: REPEATABLE READ)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ...
COMMIT;

-- Levels (weakest → strongest):
--   READ UNCOMMITTED  : dirty reads allowed (NEVER in apps)
--   READ COMMITTED    : sees committed changes during transaction
--   REPEATABLE READ   : sees a snapshot from transaction start; no phantom on basic queries (InnoDB default)
--   SERIALIZABLE      : full isolation; rare lock contention

-- 5) SELECT ... FOR UPDATE — lock rows you're about to modify
START TRANSACTION;
SELECT * FROM orders WHERE id = 100 FOR UPDATE;
-- Other transactions wait until you COMMIT.
UPDATE orders SET status = 'paid' WHERE id = 100;
COMMIT;

-- 6) Lighter — SELECT ... FOR SHARE (S-lock)
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR SHARE;
-- Allows others to read but blocks UPDATEs until you commit
COMMIT;

-- 7) Skip locked rows (great for job queues)
START TRANSACTION;
SELECT id, payload FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;        -- skip rows other workers are processing
-- ... process ...
UPDATE jobs SET status = 'done' WHERE id = ?;
COMMIT;

-- 8) Deadlocks — detect + retry
-- InnoDB detects circular waits and aborts ONE transaction:
--   ERROR 1213 (40001): Deadlock found when trying to get lock
-- Application-side: catch + retry (typically 3 attempts with backoff).

-- 9) Implicit commit traps
-- These commit any running transaction:
--   DDL: CREATE/ALTER/DROP TABLE, TRUNCATE
--   ADMIN: GRANT, SET PASSWORD
--   LOCK TABLES, UNLOCK TABLES
-- Never run DDL inside an application transaction.

-- 10) Performance + best practices
--   • Keep transactions short — long ones hold locks, bloat undo logs
--   • Order updates consistently across the app to avoid deadlock cycles
--   • Don't do network I/O inside a transaction (no API calls / sleeps)
--   • Use FOR UPDATE only on rows you actually mutate
--   • In code:
--       try { conn.beginTransaction(); ...; conn.commit(); }
--       catch (e) { conn.rollback(); throw; }

-- 11) Two-phase commits across DBs — XA transactions (rare; complex; usually wrong tool)
XA START 'tx1';
-- work on DB 1
XA END 'tx1';
XA PREPARE 'tx1';
XA COMMIT 'tx1';
-- Better pattern: outbox table + reliable event publishing.

-- 12) autocommit mode
SHOW VARIABLES LIKE 'autocommit';     -- ON by default in MySQL CLI
SET autocommit = 0;                    -- explicit BEGIN/COMMIT required

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;