sql
START TRANSACTION;Notes
Explicit transactions let you group related writes into a single atomic unit.
MySQL Transactions and Locking Cheatsheet
start transaction, commit, rollback, savepoints, select for update, isolation levels, and InnoDB lock troubleshooting.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
transactions
Atomic writes, commit, rollback, and savepoints.
sql
COMMIT;Notes
After `COMMIT`, changes become durable and visible according to isolation semantics.
sql
ROLLBACK;Notes
Crucial when a multi-step operation fails partway through.
sql
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;Notes
Savepoints are useful when a larger transaction contains optional or retryable steps.
Disable autocommit for the session
Keep statements in a transaction until you commit or rollback.
sqlANYautocommitsession
sql
SET autocommit = 0;Notes
Use carefully; long-lived transactions can hold locks and increase contention.
locking and concurrency
Read and write contention patterns for transactional workloads.
Lock selected rows for update
Prevent concurrent modifications to matched rows inside a transaction.
sqlANYfor-updatelocking
sql
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;Notes
Common in balance transfers, inventory reservations, and queue consumers.
Lock rows for shared reads
Read rows while protecting against conflicting writes.
sqlANYfor-sharelocking
sql
SELECT * FROM products WHERE id = 10 FOR SHARE;Notes
Use shared locks when you want consistency without taking an exclusive update lock.
Set transaction isolation level
Control read consistency and concurrency behavior.
sqlANYisolation-leveltransaction
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Notes
Isolation level affects phenomena such as dirty reads, nonrepeatable reads, and phantoms.
Inspect InnoDB lock and transaction state
Display detailed InnoDB diagnostics.
sqlANYinnodbdeadlockstatus
sql
SHOW ENGINE INNODB STATUS\GNotes
A classic source of deadlock and locking information during production troubleshooting.
Set lock wait timeout for the session
Avoid waiting too long on blocked locks.
sqlANYlock-wait-timeoutinnodb
sql
SET SESSION innodb_lock_wait_timeout = 10;Notes
Shorter timeouts are often helpful in APIs that should fail fast under contention.
More in MySQL
MySQL Procedures, Triggers, and Events Cheatsheet
stored procedures, stored functions, delimiters, call, triggers, and scheduled events in MySQL.
MySQL Functions, JSON, and Date Cheatsheet
now, date_add, date_sub, date_format, timestampdiff, json_extract, json_set, json_object, concat, coalesce, and case in MySQL.
MySQL Backup, Restore, Import, and Export Cheatsheet
mysqldump, mysql restore, load data infile, select into outfile, mysqlimport, and MySQL Shell dump/load workflows.
MySQL Users, Grants, and Privileges Cheatsheet
create users, alter passwords, grant privileges, show grants, revoke access, and manage MySQL accounts safely.
MySQL Indexes, EXPLAIN, and Performance Cheatsheet
index design, show index, explain, explain analyze, analyze table, and practical performance tuning workflows in MySQL.
MySQL Aggregation and Window Functions Cheatsheet
group by, having, count, sum, avg, row_number, lag, running totals, and analytical query patterns in MySQL.