sqlANYtransactionstart
sql
START TRANSACTION;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.
sqlANYcommittransaction
sql
COMMIT;After `COMMIT`, changes become durable and visible according to isolation semantics.
sqlANYrollbacktransaction
sql
ROLLBACK;Crucial when a multi-step operation fails partway through.
sqlANYsavepointrollback
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;Savepoints are useful when a larger transaction contains optional or retryable steps.
sqlANYautocommitsession
sql
SET autocommit = 0;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;Common in balance transfers, inventory reservations, and queue consumers.
sqlANYfor-sharelocking
sql
SELECT * FROM products WHERE id = 10 FOR SHARE;Use shared locks when you want consistency without taking an exclusive update lock.
sqlANYisolation-leveltransaction
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Isolation level affects phenomena such as dirty reads, nonrepeatable reads, and phantoms.
sqlANYinnodbdeadlockstatus
sql
SHOW ENGINE INNODB STATUS\GA classic source of deadlock and locking information during production troubleshooting.
sqlANYlock-wait-timeoutinnodb
sql
SET SESSION innodb_lock_wait_timeout = 10;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.