Transaction Management

How a database makes a group of changes all-or-nothing and durable: ACID mechanisms, commit and the write-ahead log, savepoints, and what changes when a transaction spans several databases or services.

Overview

A transaction is a group of reads and writes that the database treats as one unit. Either every change in it takes effect, or none does, and once the database says it committed, the change survives a crash. A money transfer is the classic example: taking money from one account and adding it to another must never happen halfway.

Inside one database this is a solved problem, and the interesting questions are how it is built and what it costs: the write-ahead log, the commit flush, constraint checks, and savepoints. Across several databases or services it is hard again, and the choices are two-phase commit, which blocks when its coordinator fails, or sagas, which give up isolation and undo with compensating steps.

The examples use PostgreSQL, with notes on MySQL's InnoDB where it differs. Isolation levels and locking have their own topics on this page; this chapter covers them only where they touch commit and rollback.

What you will be able to do

  • Say what each ACID property promises and which mechanism provides it in a real database.
  • Explain what happens at COMMIT, why the write-ahead log makes it cheap, and what synchronous_commit = off risks.
  • Use savepoints to recover from part of a transaction, and know what they cost.
  • Walk through two-phase commit, explain why it blocks, and say how systems such as Spanner reduce that.
  • Design a saga with compensating steps, and choose between a local transaction, 2PC and a saga.

Key concepts

  • Transaction lifecycle
  • ACID
  • Write-ahead log and commit
  • synchronous_commit
  • Deferred constraints
  • Savepoints
  • Two-phase commit
  • Sagas and compensation
  • Retries and timeouts

Interview questions

  • Explain each ACID property and the mechanism that provides it.
  • What exactly happens when you run COMMIT, and why is it fast?
  • Walk through two-phase commit. Why does it block, and how do real systems deal with that?
  • Design the checkout flow across order, payment and shipping services without a distributed transaction.
  • What are savepoints for, and what do they cost?
  • Your service sometimes gets serialization errors under load. How should it handle them?

Sign up free to read the full topic

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio