BEGIN, COMMIT and ROLLBACK
How to group statements into one unit of work, and why autocommit means you are always inside a transaction anyway.
START TRANSACTION [WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY]; ... COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE];MySQL runs with autocommit enabled by default. Every statement you send outside an explicit transaction is wrapped in its own transaction and committed the moment it finishes. You are never not in a transaction; the question is only how big it is.
Starting one explicitly
BEGIN and START TRANSACTION are the same statement. BEGIN is the short form, accepts an optional noise word WORK, and takes no modifiers; START TRANSACTION is the long form that also accepts characteristics:
BEGIN; -- same as
BEGIN WORK; -- same as
START TRANSACTION;
START TRANSACTION READ ONLY; -- only the long form takes these
START TRANSACTION WITH CONSISTENT SNAPSHOT;COMMIT makes the work permanent and visible to other sessions; ROLLBACK discards it. Both accept the same optional WORK keyword, so COMMIT and COMMIT WORK are identical.
The other way in
SET autocommit = 0 turns implicit commits off for the session. From then on a transaction opens as soon as you touch a table and stays open until you commit or roll back — including after a plain SELECT, which is how sessions end up holding a read view open for hours.
What trips people up
Transactions do not nest. A second BEGIN commits the first one rather than opening an inner scope. For partial undo inside one transaction you need a savepoint.
Example
-- Placing an order: three tables must move together.
START TRANSACTION;
INSERT INTO orders (customer_id, status, placed_at)
VALUES (4821, 'pending', NOW());
-- LAST_INSERT_ID() is per-connection, so it is safe inside the transaction.
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, sku, qty, unit_price)
VALUES (@order_id, 'KB-88', 1, 79.00),
(@order_id, 'MS-12', 2, 24.50);
-- Reserve stock. ROW_COUNT() is 0 if the WHERE matched nothing,
-- which is how the application detects an oversell.
UPDATE inventory
SET reserved = reserved + 1
WHERE sku = 'KB-88' AND on_hand - reserved >= 1;
-- If ROW_COUNT() = 0 here, the application sends ROLLBACK instead.
COMMIT;When to use it
- Grouping an order insert, its line items and a stock decrement so a crash cannot leave a half-order.
- Turning off autocommit in a migration script so the whole migration can be rolled back.
- Running several SELECTs that must report the same instant for a daily reconciliation.
- Deleting old rows in chunks that each commit, so locks are not held for minutes.
More examples
Autocommit, and the longhand it stands for
The shorthand every MySQL session uses by default, shown beside the longhand it desugars to. Worth internalising because the two styles behave identically for one statement and completely differently for two: with autocommit on, a crash between two UPDATEs leaves the first one committed. Note also that SET autocommit = 1 itself commits any open transaction.
SELECT @@autocommit; -- 1 by default in MySQL 8.4
-- With autocommit on, this single statement ...
UPDATE accounts SET balance = balance + 10 WHERE id = 7;
-- ... is exactly equivalent to this:
START TRANSACTION;
UPDATE accounts SET balance = balance + 10 WHERE id = 7;
COMMIT;
-- Turning autocommit off for the session inverts the default:
SET autocommit = 0;
UPDATE accounts SET balance = balance + 10 WHERE id = 7;
-- nothing is visible to other sessions, and no lock is released,
-- until you say so:
COMMIT;COMMIT AND CHAIN for a loop of batches
Chunked deletes and backfills are the usual reason to reach for AND CHAIN: you want many short transactions so locks are released often, but you do not want any statement running outside a transaction between batches. COMMIT AND NO CHAIN is the explicit spelling of plain COMMIT.
SET autocommit = 0;
START TRANSACTION;
DELETE FROM events WHERE created_at < NOW() - INTERVAL 90 DAY LIMIT 5000;
-- Commits this batch and immediately opens a new transaction with the
-- same isolation level and access mode. Saves a round trip and removes
-- the window where a statement could slip out into autocommit.
COMMIT AND CHAIN;
DELETE FROM events WHERE created_at < NOW() - INTERVAL 90 DAY LIMIT 5000;
COMMIT AND NO CHAIN; -- the default form; ends the chainA read-only transaction for a consistent report
Use this for any multi-query report or export that must add up. Two SELECTs in autocommit are two separate transactions and can straddle a concurrent write; wrapping them makes the numbers reconcile. READ ONLY is the safety belt — an UPDATE inside it raises an error rather than going through.
-- READ ONLY lets InnoDB skip transaction-ID overhead, and makes an
-- accidental write fail loudly instead of modifying production data.
START TRANSACTION READ ONLY;
SELECT COUNT(*) FROM orders WHERE placed_at >= CURDATE();
SELECT SUM(amount) FROM payments WHERE received_at >= CURDATE();
-- Both queries see the same snapshot under REPEATABLE READ, so the
-- counts cannot disagree because a payment landed between them.
COMMIT;
Discussion