Transaction Control in MySQL

-- Start transaction
START TRANSACTION;  -- or BEGIN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback on error
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 1 WHERE id = 123;
-- Error occurs
ROLLBACK;

-- Savepoints
START TRANSACTION;
UPDATE users SET credits = 100;
SAVEPOINT before_delete;
DELETE FROM logs;
ROLLBACK TO SAVEPOINT before_delete;
COMMIT;

-- DETAILED_TAB:
-- Isolation levels
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- Default
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Check current level
SELECT @@transaction_isolation;

-- Autocommit control
SET autocommit = 0;  -- Manual commit required
SET autocommit = 1;  -- Default, auto-commit each statement

-- Transaction with lock timeout
SET innodb_lock_wait_timeout = 5;
START TRANSACTION;
-- Operations here
COMMIT;