Skip to content

CONCEPT Cited by 2 sources

Database transaction

A database transaction is a sequence of SQL statements executed as a single atomic unit of work, bracketed by begin; at the start and either commit; (apply all changes together) or rollback; (discard all changes as if the transaction never began) at the end. Between begin and commit, a transaction may perform any mix of reads, inserts, updates, and deletes; either all of its modifications take effect together at commit, or none of them do.

Commit and rollback

From Ben Dicken's PlanetScale explainer (Source: sources/2026-02-22-planetscale-what-is-a-database-transaction): "trillions of transactions execute every single day, across the thousands of applications that rely on SQL databases."

  • commit atomically applies every change the transaction made. Before commit, the transaction's writes are invisible to other sessions.
  • rollback undoes the transaction's changes in full; the database is left in the exact state it was before begin.

Rollback has two classes of trigger:

  1. Intentional — midway through a transaction, the application encounters missing or unexpected data, or the client cancels the request, and the application issues rollback to back out cleanly.
  2. Unexpected physical events — a hard-drive failure, power outage, or other crash. Databases use disaster- recovery mechanisms (Postgres's write-ahead log (WAL), for example) to guarantee that on recovery, any transaction that had not yet committed is rolled back to the pre-begin state, even if some of its changes had been written partway to disk.

Isolation between concurrent transactions

A transaction's uncommitted changes are invisible to every other session (subject to isolation level). If Session A opens a transaction and updates a user's name from ben to joe, then Session B — running its own transaction or just a plain query — continues to see ben until Session A commits. If Session A rolls back instead, Session B never observes the change at all. This is the operational reason transactions exist: they let many queries run concurrently without interfering with each other (Source: sources/2026-02-22-planetscale-what-is-a-database-transaction).

The same isolation underpins consistent reads — at REPEATABLE READ and stricter modes, a transaction sees a stable view of the database for its entire duration, even while other transactions insert, update, and delete rows beneath it. How that consistent view is maintained differs radically across engines: Postgres keeps multiple row versions (see concepts/xmin-xmax-row-versioning), MySQL uses an undo log to reconstruct old versions on-the-fly.

Atomicity vs. the other ACID properties

Atomicity — all-or-nothing — is one of the four ACID guarantees; transactions are the unit over which those guarantees apply. Isolation governs what concurrent transactions can see of each other's in-progress work. Durability means that once commit returns successfully, the change survives crashes (backed by WAL/binlog-level disk persistence). Consistency means the transaction cannot leave the database in a state that violates declared constraints.

Seen in

Last updated · 517 distilled / 1,221 read