Transactions

When an editor publishes a post, the application writes to two tables. It updates the row in posts, setting state to published and published_at to the current time. It inserts a row into post_state_changes with from_state submitted, to_state published, the editor’s id as actor_id, and the same time as occurred_at.

These are two separate writes, and something can go wrong between them. The application can crash after the first write. The database connection can drop. The second write can be refused because it violates a constraint. In each case, posts says the post is published and post_state_changes has no record of it. The two copies no longer match, and this is the failure the Denormalization section said the application must prevent.

The application cannot prevent it by being careful. It can check every input before writing, and the crash can still happen between the two writes. It needs a way to tell the database that the two writes are one operation.

A transaction is work that the database treats as one unit. A transaction can contain one write or several. Either every write in it takes effect, or none of them does. The application marks where the transaction begins, issues the writes, and marks where it ends. Marking the end is called committing the transaction. If any write in the transaction fails, or the application decides to abandon it, the database undoes every write made since the beginning. That is called rolling back.

The guarantee that all of the writes take effect or none of them do is called atomicity. It holds against every kind of failure between the beginning and the commit. If the application crashes after the update to posts and before the insert into post_state_changes, the database rolls back the update when it recovers. posts still says submitted. Nothing was published, and nothing was recorded, which is consistent.

For HopPress, every state transition is one transaction:

  • Creating a draft: insert into posts, insert into post_state_changes.
  • Submitting: update state on posts, insert into post_state_changes.
  • Publishing: update state and published_at on posts, insert into post_state_changes.
  • Returning: update state on posts, insert into post_state_changes with the editor’s note.

If the database allows grouping writes into transactions, then it can be used for keeping a denormalized schema consistent. The application still has to decide which writes belong together and wrap them in one transaction. The database makes sure that, once it has, the writes cannot take effect separately.

Please note that technically, every single write can run in a transaction. Usually, the database starts and commits a transaction automatically for each SQL statement. This default is called autocommit. A single write, such as changing a title, is automatically wrapped in a transaction.

UPDATE posts
SET title = 'Campus Shuttle Route Changes'
WHERE post_id = 42;

Publishing needs two writes. This is where the application must start a transaction explicitly, so that the two writes are one unit. Here is what it looks like in SQL:

BEGIN;

UPDATE posts
SET state = 'published', published_at = CURRENT_TIMESTAMP
WHERE post_id = 42;

INSERT INTO post_state_changes
  (post_id, actor_id, from_state, to_state, occurred_at)
VALUES
  (42, 7, 'submitted', 'published', CURRENT_TIMESTAMP);

COMMIT;

BEGIN starts an explicit transaction. COMMIT makes its writes permanent.

Transactions in relational databases guarantee more than atomicity. We will look at the other guarantees in a later chapter.