Transactions and What COMMIT Really Means

A refund goes out twice because the first attempt timed out and the retry ran anyway. An order row exists with no line items because the process died between two inserts. A report is wrong because someone updated a row while a long-running transaction was still reading it. None of these are query bugs. They are decisions about where a transaction starts and ends, and most of them were made by accident.

The examples ran on MySQL 9.6 and SQLite 3.51. Where PostgreSQL differs, the difference is named, because PostgreSQL is stricter in two places that matter.

Atomicity, isolation and durability are the database's. Consistency is shared

ACID is usually recited as one property. It is four, and they do not come from the same place.

Atomicity is the database's: everything inside a transaction commits or none of it does, including the case where the process is killed mid-statement. Isolation is the database's at whatever level you asked for, and the level you actually get is the subject of the next lesson. Durability is the database's as far as a crash of the process goes: once COMMIT returns, the data survives a server restart. It does not mean the data survives the disk, which is what replication and backups are for.

Consistency is only partly the database's. Constraints, foreign keys and unique indexes are enforced by the engine on every write, from every code path, which is why lesson three calls them the only validation that cannot be bypassed. Rules that span rows are not constraints, and the engine will not enforce them. "A balance is never negative" can be a CHECK. "The ledger equals the sum of the balances" cannot, and two concurrent transactions can each preserve their own half of it while breaking the whole, which is the write skew case in lesson nine.

BEGIN and COMMIT mark the unit, and a failed statement does not end it

Here is a transfer that cannot complete. Ana has 100, Bo has 10, and the second half of the transfer would push Bo negative:

CREATE TABLE wallet (
  id      INT NOT NULL PRIMARY KEY,
  owner   VARCHAR(20) NOT NULL,
  balance INT NOT NULL,
  CONSTRAINT chk_wallet_balance CHECK (balance >= 0)
) ENGINE=InnoDB;
INSERT INTO wallet VALUES (1,'ana',100),(2,'bo',10);

BEGIN;
UPDATE wallet SET balance = balance - 50 WHERE id = 1;
UPDATE wallet SET balance = balance - 50 WHERE id = 2;
ROLLBACK;

+------------------------+----+---------+
| step                   | id | balance |
+------------------------+----+---------+
| inside the transaction |  1 |      50 |
| inside the transaction |  2 |      10 |
+------------------------+----+---------+
ERROR 3819 (HY000) at line 13: Check constraint 'chk_wallet_balance' is violated.
+----------------+----+---------+
| step           | id | balance |
+----------------+----+---------+
| after ROLLBACK |  1 |     100 |
+----------------+----+---------+
| after ROLLBACK |  2 |      10 |
+----------------+----+---------+

Ana's 50 was real inside the transaction and invisible outside it, and the ROLLBACK removed it. Now the trap: a failed statement aborts the statement, not the transaction. MySQL leaves the transaction open after an error, so an application that logs the failure and commits anyway writes half the transfer:

BEGIN;
UPDATE wallet SET balance = balance - 50 WHERE id = 1;
UPDATE wallet SET balance = balance - 50 WHERE id = 2;
ERROR 3819 (HY000): Check constraint 'chk_wallet_balance' is violated.
COMMIT;

+--------------+----+---------+
| step         | id | balance |
+--------------+----+---------+
| after COMMIT |  1 |      50 |
| after COMMIT |  2 |      10 |
+--------------+----+---------+

Fifty units left the system. The application saw one error and one success, and the success was inside the transaction it was supposed to abort. This is why an error handler in a transactional block returns without committing. PostgreSQL differs here: an error leaves the transaction in a failed state, and every later statement is rejected until you ROLLBACK, so the mistake cannot be committed by accident.

SQLite has one difference worth knowing before you rely on a transaction at all: foreign key enforcement is off by default until the connection runs PRAGMA foreign_keys = ON.

Autocommit is on by default, and turning it off opens a transaction nobody asked for

Both engines run in autocommit mode on a fresh connection. Every statement outside an explicit transaction is its own transaction and commits when it finishes. That is what makes UPDATE ledger SET settled = 1 from the previous lesson so expensive: there was no transaction to roll back, only a statement that had already committed.

The other direction is the one that catches people. Setting autocommit = 0 means the next statement opens a transaction that never closes until you say so. No BEGIN appears anywhere in the code, and the transaction exists:

SET autocommit = 0;
UPDATE ledger SET settled = 1 WHERE entry_id = 1;

+-----------+---------------------+-------------------+---------------------+
| trx_state | trx_started         | trx_rows_modified | trx_isolation_level |
+-----------+---------------------+-------------------+---------------------+
| RUNNING   | 2026-10-16 12:49:44 |                 1 | REPEATABLE READ     |
+-----------+---------------------+-------------------+---------------------+
+-------------------+
| visible_to_others |
+-------------------+
|                 0 |
+-------------------+
+--------------+
| after_commit |
+--------------+
|            1 |
+--------------+

An UPDATE and one setting produced a transaction nobody else can see through. The row stayed at 0 for everyone else until that connection committed.

This is why connection pools are dangerous with autocommit = 0. A pooled connection keeps its session settings, so a transaction left open by one request can be picked up by the next, which commits work it never issued. Leave autocommit on and write BEGIN and COMMIT around the unit of work, because that is visible in the code. Open transactions show up in information_schema.INNODB_TRX.

A savepoint rolls back part of the work, not part of the counter

A savepoint marks a position inside a transaction that you can return to without ending it. The usual reason is an operation you want to attempt and survive: try the insert, and if a duplicate arrives, roll back to the marker and take a different branch while keeping what came before.

BEGIN;
INSERT INTO savegame (note) VALUES ('one');
SAVEPOINT after_one;
INSERT INTO savegame (note) VALUES ('two');
ROLLBACK TO SAVEPOINT after_one;
INSERT INTO savegame (note) VALUES ('three');
COMMIT;
SELECT id, note FROM savegame ORDER BY id;

+----+-------+
| id | note  |
+----+-------+
|  1 | one   |
|  3 | three |
+----+-------+

Three statements ran after the marker and two survived. Note the ids: 'two' took value 2 and rolling back did not return it, so a savepoint loop that retries often burns through ids. PostgreSQL behaves the same way, and its manual is explicit that changes to a sequence are not rolled back when the transaction aborts.

Two limits are assumed wrongly. A partial rollback undoes data changes but is not a way to release locks, and the engines do not agree on what happens: PostgreSQL documents that a lock acquired after a savepoint is released if you roll back to it, while MySQL's manual says InnoDB does not release row locks stored in memory after the savepoint. On the MySQL 9.6 server used here, a second session took the row lock immediately after a ROLLBACK TO SAVEPOINT, which is not what that manual describes. Do not build a contention strategy on either answer. Savepoints are also cleared by any DDL inside the transaction.

In MySQL, DDL commits your work behind your back

In MySQL, data definition statements cause an implicit commit. The transaction you were holding is committed before the statement runs and a new one starts after, and any ROLLBACK you issue afterwards applies to the new one:

BEGIN;
INSERT INTO ddl_demo (v) VALUES (1);
CREATE TABLE created_inside_tx (id INT) ENGINE=InnoDB;
ROLLBACK;
SELECT (SELECT COUNT(*) FROM ddl_demo)  AS ddl_demo_rows,
       (SELECT COUNT(*) FROM information_schema.TABLES
         WHERE TABLE_SCHEMA='m3lab' AND TABLE_NAME='created_inside_tx') AS table_still_exists;

+---------------+--------------------+
| ddl_demo_rows | table_still_exists |
+---------------+--------------------+
|             1 |                  1 |
+---------------+--------------------+

Both the insert and the table survived a rollback that reported success. TRUNCATE, ALTER TABLE, DROP TABLE, RENAME TABLE, CREATE INDEX and LOCK TABLES are in the same group, so a migration runner that wraps each migration in a transaction does not get atomicity from MySQL for the DDL parts.

PostgreSQL and SQLite do not share this behaviour. Both support transactional DDL, so a CREATE TABLE that is rolled back leaves no table. That is the largest practical difference between the two engines for anyone writing migrations.

An open transaction dies with the connection that owns it

If the client disappears, the server rolls the transaction back. Nothing else is safe: the client is not there to commit it, and leaving it open would hold locks forever. Killing a connection with an uncommitted update shows both halves of that:

+-------------------+
| visible_to_others |
+-------------------+
|               100 |
+-------------------+
session A output: ERROR 2013 (HY000) at line 3: Lost connection to MySQL server during query
+------------+
| after_kill |
+------------+
|        100 |
+------------+

Another session saw 100 while the transaction was open, and after the connection died the value was still 100. The uncommitted change never existed as far as the table is concerned. The same applies to a dropped socket and a killed application server, and it is why a client that times out waiting for a commit cannot assume the commit failed: if the server processed the COMMIT and the acknowledgement was lost, the work is committed and the client does not know.

For a connection that is idle rather than gone, the server waits. An open transaction holds its locks and its snapshot the whole time. MySQL's wait_timeout (28800 seconds, eight hours, by default) closes a connection with no activity and rolls its transaction back, and an open transaction with no running statement counts as inactive. PostgreSQL has a separate idle_in_transaction_session_timeout, and its manual lists leaving connections dangling "idle in transaction" among the things not to do. Neither default is a substitute for closing your own transactions.

Never hold a transaction open across a network call

The rule is short: open the transaction, do the database work, commit. Do not make an HTTP request, call a payment provider, send an email or prompt a user while a transaction is open. Everything the transaction touched is locked, its snapshot is a fixed point in the past, and the duration of that state is now controlled by something outside your database.

Three things go wrong. Locks are held for the duration of the remote call, so every other writer to those rows waits behind a timeout you do not control. The snapshot ages, so reads inside the transaction get staler while it is still deciding, and a long-running read on PostgreSQL also holds back vacuum cleanup of dead rows. And the connection is occupied, so a pool of twenty connections with one slow external call per request runs out of connections long before it runs out of database capacity.

If the external call must happen inside a logical unit of work, split the unit. Write the intent, commit, make the call, then commit the outcome in a second transaction made idempotent so a retry cannot apply it twice. It is more code than one long transaction, and it is the only version that behaves when the other side is slow.

Show the solution and the reasoning

The two outcomes for the same failure are the whole lesson. With ROLLBACK, Ana stays at 100 and Bo at 10, because neither update committed. With COMMIT, Ana drops to 50 and Bo stays at 10, because the failure aborted one statement and left the other sitting in an open transaction that the COMMIT then wrote. Both runs report one error, and only one loses money.

The savepoint version is the interesting one, and the answer is that the constraint does not do what people assume. chk_wallet_balance enforces a rule about a single row and has no opinion about whether the two rows move together, so a transaction that debits Ana and rolls back Bo's credit commits cleanly with the money gone. Conservation across rows is a consistency rule, and the engine only helps if every writer runs the transfer in one transaction and every reader is isolated from the half-finished state.

During the four-second call, two things are true: the updated rows are locked, so any other writer to them waits, and the snapshot is fixed, so its reads are fixed too. If the call hangs for sixty seconds, the third thing is that nothing in the database has failed. The transaction is still open, the locks are still held, and the only things that will end it are a timeout you configured, an operator killing the connection, or the client dying and letting the server roll back.

What that shape cannot survive is a pool with fewer connections than concurrent requests, since each request holds a connection for the whole call. Move the call outside the transaction and make the second half safe to run twice.