BEGIN, COMMIT, ROLLBACK

MySQL runs with autocommit = 1 by default: every statement outside an explicit transaction is its own transaction, committed the instant it finishes. BEGIN (an alias for START TRANSACTION) suspends that until you COMMIT or ROLLBACK.

Autocommit and visibility

A

B

UPDATE balance = 150 (autocommit)

SELECT balance→ 150 (visible at once)

UPDATE balance = 999 (uncommitted)

SELECT balance→ 150 (A's change hidden)

COMMIT

SELECT balance→ 999 (now visible)

No BEGIN — the UPDATE is its own transaction, committed the instant it finishes.

A> UPDATE accounts SET balance = 150 WHERE id = 1;
Query OK, 1 row affected

B> SELECT balance FROM accounts WHERE id = 1; -- B sees it immediately
 balance 
---------
     150 
(1 row)

Inside an explicit transaction, A's change is invisible to B…

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected

B> SELECT balance FROM accounts WHERE id = 1; -- still the old value
 balance 
---------
     150 
(1 row)

…until A commits.

A> COMMIT;
Query OK

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     999 
(1 row)

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

An error does NOT abort the transaction

Here MySQL and PostgreSQL part ways completely. In PostgreSQL, any error aborts the whole transaction — every further statement is refused with 25P02 until you roll back. MySQL rolls back only the failed statement and carries on as if nothing happened:

A> BEGIN;
Query OK

A> INSERT INTO items VALUES (2, 'gadget');
Query OK, 1 row affected

A> INSERT INTO items VALUES (3, 'widget'); -- ER_DUP_ENTRY
ERROR 1062 (23000): Duplicate entry 'widget' for key 'items.name'

PostgreSQL would now refuse every statement until ROLLBACK. MySQL just carries on.

A> INSERT INTO items VALUES (3, 'doohickey');
Query OK, 1 row affected

A> COMMIT;
Query OK

A> SELECT id, name FROM items ORDER BY id; -- survived the error in the middle
 id |   name    
----+-----------
  1 | widget    
  2 | gadget    
  3 | doohickey 
(3 rows)

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

This is convenient — and dangerous: application code that assumes "an error means nothing committed" is wrong on MySQL. If your error handler doesn't explicitly ROLLBACK, every statement that succeeded before and after the error will commit.

Without BEGIN, every statement commits on its own, so there's nothing to roll back a moment later; wrap the work in a transaction and your changes stay invisible to others until COMMIT, with chapter 2 covering the exact visibility rules. After an error the transaction is still alive and only the failed statement was undone, so decide explicitly whether to retry that statement or ROLLBACK everything — the same lesson for PostgreSQL proves the opposite behavior. And watch for statements that commit implicitly: DDL like ALTER TABLE or CREATE INDEX commits your open transaction before it runs.

Further reading

• MySQL docs: autocommit, Commit, and Rollback
• MySQL docs: Statements That Cause an Implicit Commit
• The same lesson on PostgreSQL