COMMIT and ROLLBACK in Practice

COMMIT makes everything the transaction has done permanent and visible to other transactions. ROLLBACK undoes everything back to the state at the start of the transaction or the last savepoint. Both end the transaction, and in both cases the locks it held are released.

The decision is made for you when you are in autocommit mode, which is the default. Every single statement is its own transaction: it commits as soon as it finishes successfully, and it rolls back on its own if it fails. That is why an UPDATE with a missing WHERE clause is committed the instant it runs — there is no open transaction to roll back, because the statement was its own transaction.

Beginning a transaction explicitly with BEGIN switches off autocommit for that block, and only the COMMIT or ROLLBACK decides the outcome. The exception is when the transaction is left open: most clients close the connection at the end of the session, and a transaction with no explicit outcome is rolled back automatically when the connection closes. SQLite behaves the same way — an uncommitted transaction is discarded when the session ends.

Two practical consequences. First, a client that crashes mid-transaction leaves the work uncommitted, which is the safe default: nothing partial survives. Second, a transaction left open by a bug holds its locks until the connection is reaped, which is why production systems set a statement timeout and an idle-in-transaction timeout — PostgreSQL has both, and the second one is specifically designed to kill sessions that have been idle inside a transaction for too long.

The habit worth forming is to make the transaction boundary explicit at the top of the unit of work and to commit as soon as that work is done. A long-running transaction that spans a web request is holding locks on data that other people need to write to.

A savepoint is a marker inside a transaction. Rolling back to it undoes everything since the marker was set, but keeps the transaction open, so you can carry on and still commit the earlier work.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO orders (id, customer_id, amount, status) VALUES
  (1, 1, 120.00, 'open'),
  (2, 2, 340.50, 'open');

BEGIN;

UPDATE orders SET status = 'paid' WHERE id = 1;

SAVEPOINT before_correction;
UPDATE orders SET status = 'refunded' WHERE id = 1;

ROLLBACK TO SAVEPOINT before_correction;

COMMIT;

SELECT id, amount, status FROM orders ORDER BY id;

Two rows come back: order 1 with status 'paid' and order 2 still 'open'. The refund correction was undone by the rollback to the savepoint, while the earlier payment update survived and was committed.

The vocabulary is worth getting right. SAVEPOINT name sets a marker. ROLLBACK TO SAVEPOINT name undoes the work back to that marker but keeps the transaction open and the marker itself in place — you can roll back to it again. RELEASE SAVEPOINT name discards the marker without undoing anything. And a full ROLLBACK undoes the entire transaction, savepoints included.

Where this earns its keep is a batch of independent operations where one might legitimately fail. Without savepoints you have two bad options: roll the whole batch back and lose the successful items, or commit each item separately and lose atomicity. With savepoints you can attempt each item, roll back to the marker if it fails, and commit the ones that worked at the end. The pattern is to set a savepoint before each risky operation and roll back to it in the failure path.

Two caveats. Savepoints are not free — they cost the engine bookkeeping, so do not set one per row in a large loop. And a rollback to a savepoint does not release the locks taken after that savepoint on some engines, so a savepoint is a tool for undoing work, not a substitute for keeping transactions short.

The first statement is already committed and stays committed. The second never runs. You are left with half the work done — which is precisely the situation a transaction exists to prevent.

CREATE TABLE ledger (
  id INTEGER PRIMARY KEY,
  entry VARCHAR(60) NOT NULL,
  posted_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO ledger (entry) VALUES ('opening balance');

-- Autocommit is on: each statement is its own transaction.
INSERT INTO ledger (entry) VALUES ('debit 1500');

-- If the process died right here, the row above is already permanent.
-- There is no open transaction to roll back.

SELECT id, entry FROM ledger ORDER BY id;

Two rows come back: the opening balance and the debit. Both are permanent, because each INSERT was its own transaction and committed as soon as it finished.

This is why transactional design matters most for multi-step operations. Moving money means two statements — take from one account, add to another — and a crash between them destroys 1500 cents. Writing an invoice means writing the invoice and its line items. Reserving stock means decrementing stock and creating the reservation. Each of those is a unit of work that is only correct if all of it happens.

The fix is to wrap the unit of work in a transaction and let the database roll it back if the connection dies. In SQLite and PostgreSQL, an uncommitted transaction is discarded when the connection closes, so the crash leaves no partial state. In MySQL with InnoDB the same applies. The client library matters too: it has to be configured not to auto-commit, and it has to send a ROLLBACK on the connection teardown rather than just closing the socket.

There is one subtlety worth raising in an interview. The database can roll back the transaction, but it cannot roll back an external side effect. If your transaction sends an email or calls a payment provider, that call does not get undone when the transaction rolls back. The standard solution is the transactional outbox pattern: write the message to a table inside the transaction, and have a separate process read that table and deliver the messages after the commit. The failure mode then becomes at-least-once delivery with duplicate messages, which is a much easier problem to handle than money disappearing.