Transactions, Autocommit, COMMIT, ROLLBACK, SAVEPOINT, and Error Handling

Learning outcomes

A transaction is a database unit of work, not a comment you add around SQL. InnoDB uses transactions for every user operation. With the default autocommit=1, each successful standalone statement normally forms its own transaction. START TRANSACTION lets several statements share one atomic commit/rollback boundary.

Standalone prerequisite for this lesson

If you opened Lesson 3 directly, first run the schema setup from Lesson 1 and insert the MOD-002 row used by the savepoint example:

INSERT INTO work_orders(request_key,customer_id,status,priority)VALUES ('MOD-002',1,'open',2)ON DUPLICATE KEY UPDATE request_key=request_key;

Use only the disposable servicehub_write_lab schema. The DDL implicit-commit experiment intentionally demonstrates behavior that would be unsafe to explore casually in an unrelated database.

Autocommit is session state

Open two mysql clients. We will call them Session A and Session B. First inspect the variables independently:

USE servicehub_write_lab;SELECT CONNECTION_ID() AS connection_id,       @@SESSION.autocommit AS autocommit,       @@SESSION.transaction_isolation AS isolation;

A new connection normally reports autocommit=1. Changing @@SESSION.autocommit affects that connection, not every user. For application code, explicit START TRANSACTION is often clearer because the unit of work is visible at the point it begins.

-- With autocommit=1, this successful UPDATE commits as its own transaction.UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=1;-- Group two related changes into one unit.START TRANSACTION;UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=1;UPDATE parts_inventory SET on_hand=on_hand+1 WHERE part_id=2;COMMIT;

If the two inventory changes represent one business transfer, committing them independently would expose a partially applied state if the second statement failed.

ROLLBACK and SAVEPOINT: whole transaction versus partial repair

A savepoint is a named position inside the current transaction. Rolling back to it undoes later changes but does not end the transaction. This is useful when an optional sub-step can be abandoned while earlier work remains valid.

START TRANSACTION;UPDATE work_ordersSET status='assigned', technician_id=1WHERE request_key='MOD-002';SAVEPOINT after_assignment;UPDATE work_ordersSET parts_cost=parts_cost+500.00WHERE request_key='MOD-002';-- The optional cost change is wrong; undo only work after the savepoint.ROLLBACK TO SAVEPOINT after_assignment;RELEASE SAVEPOINT after_assignment;SELECT request_key,status,technician_id,parts_costFROM work_orders WHERE request_key='MOD-002';COMMIT;

The assignment remains while the later cost modification is undone. Savepoints are not nested transactions: there is still one outer commit/rollback boundary.

Failure case: duplicate-key error does not automatically erase prior work

This distinction is critical. Start a transaction, make one valid change, then issue a separate INSERT that violates the unique request key.

START TRANSACTION;UPDATE parts_inventorySET on_hand=on_hand-2WHERE part_id=1;INSERT INTO work_orders(request_key,customer_id,priority)VALUES ('MOD-001',1,1);  -- duplicate key error-- Run these after the error:SELECT part_id,on_hand FROM parts_inventory WHERE part_id=1;SELECT @@SESSION.autocommit;-- We choose the business outcome explicitly:ROLLBACK;SELECT part_id,on_hand FROM parts_inventory WHERE part_id=1;

The INSERT statement fails, but the earlier inventory UPDATE remains part of the still-open transaction until you decide to COMMIT or ROLLBACK. This is why application code must not assume “an exception means MySQL rolled everything back.” The handler should explicitly roll back the unit of work when the operation cannot continue safely.

Implicit commits: why DDL does not belong in the same mental bucket

Many data-definition statements—such as CREATE TABLE, ALTER TABLE, and DROP TABLE—cause implicit commits. Some statements commit before execution and may also commit after successful execution. They are not ordinary rollback-able DML.

DROP TABLE IF EXISTS ddl_probe;START TRANSACTION;UPDATE parts_inventory SET on_hand=on_hand-1 WHERE part_id=1;CREATE TABLE ddl_probe (id INT PRIMARY KEY) ENGINE=InnoDB;-- This ROLLBACK cannot undo the UPDATE that was committed by the DDL boundary.ROLLBACK;SELECT part_id,on_hand FROM parts_inventory WHERE part_id=1;DROP TABLE ddl_probe;

Run this only in the disposable lab and note the before/after inventory value. The lesson is operational: do not mix schema migration assumptions with DML transaction assumptions. Versioned migration tooling should treat DDL boundaries explicitly.

Observe the transaction from another session

In Session A, hold a transaction open:

START TRANSACTION;UPDATE parts_inventorySET on_hand=on_hand-1WHERE part_id=1;SELECT CONNECTION_ID() AS session_a_id;-- Do not commit yet.

From Session B (or a third diagnostic session), inspect active InnoDB transactions and locks:

SELECT trx_id,trx_state,trx_started,trx_mysql_thread_id,trx_rows_locked,trx_queryFROM information_schema.innodb_trxORDER BY trx_started;SELECT ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,       OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATAFROM performance_schema.data_locksWHERE OBJECT_SCHEMA='servicehub_write_lab'ORDER BY ENGINE_TRANSACTION_ID,OBJECT_NAME;

The diagnostic tables are live, fast-changing observations. They can change between statements and should not be treated as a transactionally consistent historical record. Finish Session A with ROLLBACK to restore the lab.

Hands-on lab: transaction state machine

1. Record @@SESSION.autocommit and isolation in two sessions.
2. Run one autocommit UPDATE and verify another session sees it immediately.
3. Run an explicit transaction and verify another session does not see its uncommitted write through a normal consistent read.
4. Use a savepoint to preserve an earlier change while undoing a later optional change.
5. Trigger a duplicate-key statement failure after a successful statement; prove the earlier statement still requires explicit rollback.
6. Run the DDL implicit-commit drill only in the disposable schema and record the evidence.

Error taxonomy: what failed, and what remains open?

Robust transaction handling begins by classifying the error. InnoDB does not use one universal rollback rule. A duplicate-key error normally rolls back the failing statement. A lock-wait timeout normally rolls back the statement that waited too long under the default server setting. A detected deadlock rolls back the entire victim transaction. Other engine or MySQL-layer errors have their own documented behavior.

This means the application needs a transaction-state discipline rather than a blanket “catch and continue.” If an operation consists of three statements and statement two fails, decide whether statement one is still a valid business outcome. Most service operations are all-or-nothing, so the safest handler is normally: catch the database exception, issue ROLLBACK, record enough diagnostic context, and either return failure or retry the complete idempotent unit where the error class allows it.

Do not return a connection to a pool while its transactional state is ambiguous. A connection is a session, so session variables, an open transaction, temporary objects, and locks belong to that session. Pool code should ensure commit/rollback has completed before reuse. Later application-integration lessons revisit this contract with real connectors.

Also distinguish rollback from durability. COMMIT makes the transaction’s changes durable according to the server’s configured durability settings and makes them visible to other transactions according to their isolation behavior. The exact redo flush path is an InnoDB internals topic. At this stage, never “optimize” durability variables merely to make a lab benchmark look faster.

Production judgment and next step

Prefer explicit, short transaction scopes aligned to one business invariant. Always commit or roll back deliberately; do not leave idle transactions holding locks. Treat migration/DDL boundaries separately. In application code, catch errors at the transaction boundary, roll back when the unit cannot safely continue, and release the connection back to the pool only after its transaction state is known.

Lesson 4 asks what each transaction is allowed to observe while other transactions are working: isolation levels, snapshots, locking reads, MVCC, and gap/next-key locks.

Authoritative references

• MySQL 8.4 Reference Manual — autocommit, Commit, and Rollback
• MySQL 8.4 Reference Manual — START TRANSACTION, COMMIT, ROLLBACK
• MySQL 8.4 Reference Manual — Statements That Cause an Implicit Commit
• MySQL 8.4 Reference Manual — SAVEPOINT
• MySQL 8.4 Reference Manual — InnoDB Error Handling