Cheat sheet · Data & storage
Transactions
The problem
A wallet app moves money between two accounts. What breaks without a transaction, what breaks even with one when two servers write at once, and which isolation level or lock fixes each case.
What you learn
- Why two
UPDATEs withoutBEGIN/COMMITcan lose money - Dirty read, lost update and write skew, on real interleavings
- What
READ COMMITTED,REPEATABLE READandSERIALIZABLEprotect from SELECT ... FOR UPDATEversus an optimisticversioncolumn
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_app(["Wallet app"])
n_api["API 1"]
n_db[("Database")]
n_report("Report job")
n_api2["API 2"]
n_fraud>"Fraud check"]
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_app --> n_api2
n_api2 -.->|"retry"| n_db
n_api --> n_fraudMermaid: request sequence
sequenceDiagram
participant n_api as API 1
participant n_db as Database
participant n_report as Report job
participant n_api2 as API 2
Note over n_api,n_db: A transfer is two writes
n_api->>n_db: UPDATE accounts SET balance = balance - 50000
n_db-->>n_api: UPDATE 1
Note over n_api,n_db: The server dies between the two writes
n_api-xn_db: 10:41:07.112 UPDATE acc_42 -50000 ok
Note over n_api,n_db: BEGIN ... COMMIT: all or nothing
n_api->>n_db: BEGIN#59;
n_db-->>n_api: COMMIT
Note over n_api,n_db: The database guards the rules
n_api->>n_db: BEGIN#59;
n_db-->>n_api: ERROR: new row violates check constraint #34;balance_nonneg#34;
Note over n_report,n_db: Reading someone else's draft
n_report->>n_db: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED#59;
n_db-->>n_report: 9950000
Note over n_api2,n_db: Two servers, one balance
n_api2->>n_db: READ COMMITTED: two payments from acc_42
Note over n_api,n_db: Lock the row while you read it
n_api->>n_db: BEGIN#59;
n_db-->>n_api: T2 then reads 150000 and writes 120000
Note over n_api2,n_db: A lock held across a network call
n_api2->>n_db: LOG: process 4121 still waiting for ShareLock
Note over n_api,n_db: Optimistic: check the version at write time
n_api->>n_db: SELECT balance, version FROM accounts
n_db-->>n_api: UPDATE 0
Note over n_api2,n_db: Two checks pass, the rule breaks
n_api2->>n_db: REPEATABLE READ: u_42 may spend 100000 a day
Note over n_api2,n_db: SERIALIZABLE: as if one at a time
n_api2->>n_db: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE#59;
n_db-->>n_api2: ERROR: could not serialize accessD2
direction: right
n_app: "Wallet app" {
shape: rectangle
}
n_api: "API 1" {
shape: rectangle
}
n_db: "Database" {
shape: cylinder
}
n_report: "Report job" {
shape: rectangle
style.multiple: true
}
n_api2: "API 2" {
shape: rectangle
}
n_fraud: "Fraud check" {
shape: cloud
}
n_app -> n_api
n_api -> n_db
n_report -> n_db
n_app -> n_api2
n_api2 -> n_db: "retry" {
style.stroke-dash: 3
}
n_api -> n_fraudPlantUML: request sequence
@startuml
participant "API 1" as n_api
database "Database" as n_db
participant "Report job" as n_report
participant "API 2" as n_api2
note over n_api, n_db : A transfer is two writes
n_api -> n_db : UPDATE accounts SET balance = balance - 50000
n_db --> n_api : UPDATE 1
note over n_api, n_db : The server dies between the two writes
n_api ->x n_db : [lost] 10:41:07.112 UPDATE acc_42 -50000 ok
note over n_api, n_db : BEGIN ... COMMIT: all or nothing
n_api -> n_db : BEGIN;
n_db --> n_api : COMMIT
note over n_api, n_db : The database guards the rules
n_api -> n_db : BEGIN;
n_db --> n_api : ERROR: new row violates check constraint "balance_nonneg"
note over n_report, n_db : Reading someone else's draft
n_report -> n_db : SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
n_db --> n_report : 9950000
note over n_api2, n_db : Two servers, one balance
n_api2 -> n_db : READ COMMITTED: two payments from acc_42
note over n_api, n_db : Lock the row while you read it
n_api -> n_db : BEGIN;
n_db --> n_api : T2 then reads 150000 and writes 120000
note over n_api2, n_db : A lock held across a network call
n_api2 -> n_db : LOG: process 4121 still waiting for ShareLock
note over n_api, n_db : Optimistic: check the version at write time
n_api -> n_db : [retry] SELECT balance, version FROM accounts
n_db --> n_api : UPDATE 0
note over n_api2, n_db : Two checks pass, the rule breaks
n_api2 -> n_db : REPEATABLE READ: u_42 may spend 100000 a day
note over n_api2, n_db : SERIALIZABLE: as if one at a time
n_api2 -> n_db : [retry] SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
n_db --> n_api2 : ERROR: could not serialize access
@endumlPlantUML: components
@startuml
left to right direction
agent "Wallet app" as n_app
component "API 1" as n_api
database "Database" as n_db
collections "Report job" as n_report
component "API 2" as n_api2
cloud "Fraud check" as n_fraud
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_app --> n_api2
n_api2 ..> n_db : retry
n_api --> n_fraud
@endumlRecap
Five rules for writing money.
1) Several writes that belong together go in one transaction. 2) Rules live in the database as constraints. 3) Read-then-write needs FOR UPDATE, a version check or a single atomic statement. 4) Do not hold locks across network calls. 5) Rules across several rows need SERIALIZABLE, and every 40001 gets a full retry.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Wrong balances | 0/day | 0/day |
| Transfer p95 | 20 ms | 320 ms |
| Retries | 0/min | 9/min |
Steps
- Case A transfer is two writes
A user sends 50 000 from account
acc_42toacc_77. - Problem The server dies between the two writes
The first
UPDATEis saved. - Fix
BEGIN...COMMIT: all or nothingBoth statements now run inside one transaction.
- Case The database guards the rules
A bug tries to take 900 000 from an account that holds 200 000.
- Case Reading someone else's draft
A nightly report sums all balances.
- Problem Two servers, one balance
Traffic grew and there are now two API servers.
- Fix Lock the row while you read it
The read now takes a row lock: `SELECT ...
- Problem A lock held across a network call
A fraud check is added in the middle of the transfer: lock the row, call an external service, then write.
- Fix Optimistic: check the version at write time
The row now has a
versioncolumn. - Problem Two checks pass, the rule breaks
A new rule: a user may withdraw at most 100 000 a day.
- Fix
SERIALIZABLE: as if one at a timeThe withdrawal now runs at
SERIALIZABLE. - Recap Recap
Five rules for writing money.
Terms
- Transaction
- Several related writes inside BEGIN ... COMMIT: either all are saved or none are. Even if the server dies halfway, no half-done change remains.
- Isolation level
- Sets how much concurrent transactions see of each other's changes: READ COMMITTED, REPEATABLE READ, SERIALIZABLE. A higher level closes more anomalies but causes more retries.
- Dirty read
- Reading another transaction's uncommitted draft. If that transaction rolls back, the reader used data that never existed.
- Optimistic locking (version)
- Instead of locking the row up front, the write checks that a version column has not changed. If someone else wrote first, the update changes nothing and the operation retries.