Transactions

Code

-- Start a transaction
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Confirm or revert
COMMIT;
-- ROLLBACK;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Lock rows for update within a transaction
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- perform business logic
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

Explanation

Transactions group statements into an atomic unit, either all committed or all rolled back. MySQL's default InnoDB isolation level is REPEATABLE READ, preventing non-repeatable reads within a transaction. SELECT ... FOR UPDATE acquires write locks to safely read-then-update under concurrency.

More MySQL Snippets