mufassall

Cheat sheet · Data & storage

Transactions

14 min · Intermediate · mufassall.dtwin.uz/en/l/transactions/

← Open the lesson

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 without BEGIN/COMMIT can lose money
  • Dirty read, lost update and write skew, on real interleavings
  • What READ COMMITTED, REPEATABLE READ and SERIALIZABLE protect from
  • SELECT ... FOR UPDATE versus an optimistic version column
Final scene
25%

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_fraud

Mermaid: 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 access

D2

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_fraud

PlantUML: 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
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Wrong balances0/day0/day
Transfer p9520 ms320 ms
Retries0/min9/min

Steps

  1. Case A transfer is two writes

    A user sends 50 000 from account acc_42 to acc_77.

  2. Problem The server dies between the two writes

    The first UPDATE is saved.

  3. Fix BEGIN ... COMMIT: all or nothing

    Both statements now run inside one transaction.

  4. Case The database guards the rules

    A bug tries to take 900 000 from an account that holds 200 000.

  5. Case Reading someone else's draft

    A nightly report sums all balances.

  6. Problem Two servers, one balance

    Traffic grew and there are now two API servers.

  7. Fix Lock the row while you read it

    The read now takes a row lock: `SELECT ...

  8. 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.

  9. Fix Optimistic: check the version at write time

    The row now has a version column.

  10. Problem Two checks pass, the rule breaks

    A new rule: a user may withdraw at most 100 000 a day.

  11. Fix SERIALIZABLE: as if one at a time

    The withdrawal now runs at SERIALIZABLE.

  12. 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.