BEGIN, COMMIT, ROLLBACK
The MySQL 8.4 autocommit contract starts new sessions with autocommit=1. A successful standalone InnoDB statement commits its transaction. BEGIN, an alias for START TRANSACTION, starts an explicit transaction that ends with COMMIT or ROLLBACK. With autocommit=0, transactions remain open between statements, and a new one starts after COMMIT or ROLLBACK. Drivers can issue BEGIN or change these settings, so check the actual connection.
Autocommit and visibility
This MySQL 8.4.11 Scenario asserts autocommit and isolation settings. B's standalone consistent SELECT takes a new snapshot for each query and sees A's successful commit. A pre-existing REPEATABLE READ snapshot need not see a later commit, and READ UNCOMMITTED can see uncommitted changes. COMMIT does not refresh every reader's view.
A> SELECT @@autocommit AS autocommit, @@transaction_isolation AS isolation, @@default_storage_engine AS engine;
autocommit | isolation | engine
------------+-----------------+--------
1 | REPEATABLE-READ | InnoDB
(1 row)
B> SELECT @@autocommit AS autocommit;
autocommit
------------
1
(1 row)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)B's next standalone SELECT takes a new snapshot after A commits.
A> COMMIT;
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
999
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Duplicate-key error without IGNORE preserves the transaction
The demonstrated duplicate-key INSERT error does not abort this transaction. The InnoDB error-handling contract documents statement rollback for duplicate-key errors without IGNORE. The Scenario asserts the earlier and later successful INSERTs after COMMIT.
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'This 1062 error leaves the earlier INSERT intact. A chooses to continue and COMMIT.
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.11 · Run it yourself · Scenario source
Do not treat every error this way. Documented exceptions in that same manual section:
- A deadlock rolls back the entire transaction. Start a fresh attempt, repeating reads and decisions, or return a controlled failure.
- A row-lock wait timeout rolls back the waiting statement by default. With
innodb_rollback_on_timeout=ON, it rolls back the entire transaction.
Entailed guarantee, by inference: a missing COMMIT reply does not establish rollback. The COMMIT contract makes successful changes permanent. If COMMIT succeeds but its reply is lost, the changes remain committed; if COMMIT never reaches the server, that commit has not happened. The missing reply alone cannot distinguish these cases. After connection loss, reconnect and determine the outcome before repeating the work. No Transcript here demonstrates a network failure.
If the application abandons an open transaction after a statement error, explicitly ROLLBACK. Continuing to COMMIT can retain earlier changes. A savepoint can discard a multi-statement branch, but cannot recover a transaction already rolled back by a deadlock. PostgreSQL's failed explicit-transaction example has a different error state and savepoint recovery path.
Implicit transaction boundaries
The MySQL 8.4 implicit-commit list includes ALTER TABLE and CREATE INDEX, which commit preceding work before running. START TRANSACTION also commits an existing transaction: transactions do not nest. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE do not implicitly commit, but their creation or removal cannot be rolled back. These are Documented contracts, not SQL executed in the two Transcripts above. See the DDL example for the existing index-build Scenario.