SD4.4 · LESSON 4 OF 6

Transactions and atomicity

Explain how indexes change work and how transactions protect application invariants under concurrency.

THE IDEA

Transactions and atomicity

A transaction groups changes so that, once committed, they are all applied or none are. If the program crashes after debiting account a, a rollback restores a. But atomicity covers the database's own data. An email sent before the crash stays sent. The usual fix is an outbox: write the intent to send inside the same transaction, and deliver only after commit.

Step 1 of 6

1. BEGIN

Atomicity means the committed effects of the transaction are all-or-nothing. It covers the database's own data, not an email, a payment gateway call or a message already sent. Record the intent inside the transaction and perform the outside action only after the commit.

Step through a crash with and without an outbox.

Predict first. Does a database rollback automatically unsend an email?

Chapter context

Databases execute concurrent requests against shared state. Fast lookup and correct updates are separate concerns: an index can improve a query while the application still loses updates or violates a cross-row rule.

TRY IT YOURSELF

Part 1. Do it hands-on

Trace a crash by hand, then write an atomic block and an outbox transfer.

Step 1 of 6

1. BEGIN

balance of a after rollback emails sent (email inside the code) emails sent with an outbox rollback unsends the email? (yes/no)

To do: Accounts a = 100, b = 20. A transaction debits a by 30, then crashes before crediting b, and the database rolls back. Balance of a afterwards?
To do: The code sent a confirmation email BEFORE the crash (inside the transaction code). After the rollback, how many emails were sent?
To do: With an outbox (the email request is written inside the same transaction and delivered only after commit), how many emails are sent after the same rollback?
To do: Does a database rollback automatically unsend an email? Type yes or no

Part 2. Do it in code

atomic(db, ops): ops is a list of functions that mutate the dict db. Run them in order. If any raises, restore db to exactly its state before the call (all-or-nothing) and re-raise; otherwise leave the changes. Return True on success.
debit(account, amount) and credit(account, amount) return functions for atomic: they raise ValueError for a missing account or a debit that would go below zero.
transfer_with_email(db, sent, src, dst, amount, crash_after_debit=False): atomically debit then credit, but call sent.append("confirmation") BEFORE the credit (inside the transaction code). If crash_after_debit is true the credit step raises RuntimeError.
transfer_with_outbox(db, sent, src, dst, amount, crash_after_debit=False): inside the same atomic block also append {"msg": "confirmation"} to db['outbox']; after the atomic block succeeds, deliver the outbox (append each msg to `sent` and clear the outbox). A failed transaction delivers nothing.

import copy

def atomic(db, ops):
    pass

def debit(account, amount):
    pass

def credit(account, amount):
    pass

def transfer_with_email(db, sent, src, dst, amount, crash_after_debit=False):
    pass

def transfer_with_outbox(db, sent, src, dst, amount, crash_after_debit=False):
    pass

Ctrl/⌘ + Enter runs it

Stuck, or something not working? You can come back to it any time.

CHECK YOUR UNDERSTANDING

Does a database rollback automatically unsend an email?

ANoBYes

Chapter reference: math, worked example & code

This section belongs to the whole chapter, so it is the same on every lesson in it and may cover a different lesson's topic. The code for the experiment you just ran is inside the experiment itself.

Math and notation

For an idealized search tree with effective fanout f and N distinguishable leaves, log base f of N gives a rough depth scale. Actual pages, occupancy and row-pointer costs differ. For an optimistic update, change value and version only when the stored version equals the version read by the caller.

Worked example

Two callers read count=10 at version 1 and each intends to increment. Unconditional writes of 11 lose one increment. With an atomic expected-version check, the first writes 11/version 2; the second stale attempt is rejected. After rereading, it can write 12/version 3. The model below demonstrates the condition, not database transaction implementation.

Reference code

The following example uses the Python standard library and does not call a remote service.

state={'count':10,'version':1}
def update(expected):
    if state['version'] != expected: return False
    state['count']+=1
    state['version']+=1
    return True
assert update(1)
assert not update(1)
assert update(2)
assert state == {'count':12,'version':3}

Python examples are provided for study and local execution; this page does not execute Python.

Common mistakes

A transaction's isolation level matters. A unique application-side check is race-prone without a database-enforced invariant. Index depth estimates are not actual query-latency measurements.