MySQL Transactions and Locking Cheatsheet

START TRANSACTION;

# Begin a transaction explicitly.

COMMIT;

# Persist all changes made in the current transaction.

ROLLBACK;

# Undo all uncommitted changes.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK TO SAVEPOINT after_debit;
COMMIT;

# Undo only part of a transaction.

SET autocommit = 0;

# Keep statements in a transaction until you commit or rollback.

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

# Prevent concurrent modifications to matched rows inside a transaction.

SELECT * FROM products WHERE id = 10 FOR SHARE;

# Read rows while protecting against conflicting writes.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

# Control read consistency and concurrency behavior.

SHOW ENGINE INNODB STATUS\G

# Display detailed InnoDB diagnostics.

SET SESSION innodb_lock_wait_timeout = 10;

# Avoid waiting too long on blocked locks.