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 a group of writes that the database treats as one unit. Either every write in the group takes effect, or none of them does. The application marks where the group begins, issues the writes, and marks where it ends. Marking the end is called committing the transaction. If any write in the group fails, or the application decides to abandon the group, 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.
• Assigning an editor: update editor_id on posts, insert into post_state_changes.

The check constraint from the Post States section gives one example of a write that fails. If an editor is assigned to a post they authored, the update to posts is refused, the transaction rolls back, and no row is written to post_state_changes.

A transaction is the database’s tool 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.

Transactions guarantee more than atomicity. They also control what happens when two users write to the same rows at the same time, for example two editors assigning themselves the same post. That is a separate topic, and it gets its own chapter.