MermaidDatabaseTransactionsSQLData Consistency

Mermaid database transaction flows and query execution diagrams

June 23, 20267 min readThe MermaidCreator team

When multiple clients query or modify a database simultaneously, understanding transaction order, locking behavior, and consistency becomes critical. A Mermaid diagram that visualizes database transactions makes the flow explicit—perfect for explaining ACID properties, race conditions, and database design to developers and stakeholders. This guide shows how to document transaction flows with Mermaid.

Why visualize database transactions

Database behavior is hard to reason about from code alone. A transaction diagram reveals:

Query sequence — what operations happen, in what order
Transaction boundaries — which queries are atomic (all-or-nothing)
Locks and blocking — where one operation waits for another
Isolation levels — whether dirty reads, non-repeatable reads, or phantom reads are possible
Concurrent updates — how the database handles conflicting writes
Rollback scenarios — what happens if a transaction fails mid-stream

Without a diagram, developers guess at database behavior—a transaction diagram makes it deterministic.

Basic transaction flow

Here's a simple transaction: transfer money between two accounts:

graph TD
    A["START TRANSACTION"]
    B["SELECT balance FROM account WHERE id = 1"]
    C["balance = 1000"]
    D["UPDATE account SET balance = 900 WHERE id = 1"]
    E["INSERT INTO log VALUES sender"]
    F["SELECT balance FROM account WHERE id = 2"]
    G["balance = 500"]
    H["UPDATE account SET balance = 600 WHERE id = 2"]
    I["INSERT INTO log VALUES receiver"]
    J["COMMIT"]
    K["✓ Both updates applied"]
    
    A --> B
    B --> C
    C --> D
    D --> E
    E --> F
    F --> G
    G --> H
    H --> I
    I --> J
    J --> K
    
    style A fill:#4CAF50,stroke:#2E7D32,color:#fff
    style K fill:#4CAF50,stroke:#2E7D32,color:#fff
    style J fill:#FFC107,stroke:#F57F17,color:#000

The diagram shows:

The transaction starts, reads from account 1, updates it.
Then reads from account 2, updates it.
All operations are atomic: either both updates apply, or neither does (if an error occurs between rows).
The log entries document what happened.

Real-world example: order processing with rollback

When a customer places an order, multiple tables must update. If inventory is out of stock, rollback everything:

graph TD
    A["START TRANSACTION"]
    B["INSERT INTO orders (customer_id, total)"]
    C["Order created"]
    D["SELECT inventory FROM products WHERE id = 42"]
    E{Stock >= <br/>quantity?}
    F["UPDATE inventory SET qty = qty - 5"]
    G["INSERT INTO order_items"]
    H["INSERT INTO payment_queue"]
    I["COMMIT"]
    J["✓ Order complete"]
    K["ROLLBACK"]
    L["❌ Order cancelled, inventory unchanged"]
    
    A --> B
    B --> C
    C --> D
    D --> E
    E -->|yes| F
    E -->|no| K
    F --> G
    G --> H
    H --> I
    I --> J
    K --> L
    
    style A fill:#4CAF50,stroke:#2E7D32,color:#fff
    style J fill:#4CAF50,stroke:#2E7D32,color:#fff
    style L fill:#FF5252,stroke:#C62828,color:#fff
    style K fill:#FF5252,stroke:#C62828,color:#fff

The decision diamond shows the critical check: if stock is available, proceed; otherwise, ROLLBACK. This makes the atomicity guarantee explicit: if anything fails, the customer's order record is deleted, and inventory is untouched.

Concurrent transactions: the race condition

When two transactions run at the same time, conflicts can occur. Here's a classic race condition:

graph TD
    subgraph TX1["Transaction 1 (Client A)"]
        A1["SELECT balance = 100"]
        A2["balance = balance + 50"]
        A3["UPDATE balance = 150"]
    end
    
    subgraph TX2["Transaction 2 (Client B)"]
        B1["SELECT balance = 100"]
        B2["balance = balance - 30"]
        B3["UPDATE balance = 70"]
    end
    
    START["Time 0: balance = 100"]
    
    START --> A1
    START --> B1
    A1 --> B2
    B2 --> A2
    A2 --> B3
    B3 --> A3
    A3 --> END["Time 5: balance = 150<br/>BUG: B's update lost!"]
    
    style START fill:#FFC107,stroke:#F57F17,color:#000
    style END fill:#FF5252,stroke:#C62828,color:#fff

Both transactions read the same balance (100). TX1 adds 50 (→150), TX2 subtracts 30 (→70). But since TX2's write happens after TX1's, TX2 wins, and the +50 is lost. The balance ends at 70 instead of the correct 120.

Isolation levels: dirty reads

Here's what happens with a low isolation level (READ UNCOMMITTED):

graph TD
    subgraph TX1["Transaction 1 (Writer)"]
        A1["START"]
        A2["UPDATE balance = 150"]
        A3["⚠️ Not yet committed"]
        A4["ROLLBACK (error occurred)"]
    end
    
    subgraph TX2["Transaction 2 (Reader)"]
        B1["START"]
        B2["READ balance"]
        B3["Gets 150 (dirty read)"]
        B4["balance = 150"]
    end
    
    START["Initial: balance = 100"]
    
    START --> A1
    START --> B1
    A1 --> A2
    A2 --> A3
    A3 --> B2
    B2 --> B3
    A3 --> A4
    A4 --> END["Final: balance = 100<br/>But TX2 saw 150!"]
    
    style END fill:#FF9800,stroke:#E65100,color:#fff
    style B3 fill:#FF9800,stroke:#E65100,color:#fff

TX1 writes 150 but hasn't committed. TX2 reads the uncommitted value (a dirty read). Then TX1 rolls back, reverting to 100. TX2 saw data that never existed in the final database—a consistency violation.

Serializable isolation (two-phase locking)

At the highest isolation level (SERIALIZABLE), the database uses locks to prevent conflicts:

graph TD
    subgraph TX1["Transaction 1"]
        A1["LOCK account_1 (exclusive)"]
        A2["SELECT balance = 100"]
        A3["UPDATE balance = 150"]
        A4["RELEASE lock"]
    end
    
    subgraph TX2["Transaction 2"]
        B1["REQUEST lock account_1"]
        B2["⏳ Waiting..."]
        B3["LOCK acquired"]
        B4["SELECT balance = 150"]
        B5["UPDATE balance = 120"]
        B6["RELEASE lock"]
    end
    
    START["Time 0"]
    START --> A1
    A1 --> A2
    A2 --> A3
    A3 --> A4
    A4 --> B1
    B1 --> B2
    B2 --> B3
    B3 --> B4
    B4 --> B5
    B5 --> B6
    B6 --> END["Final: balance = 120 ✓"]
    
    style B2 fill:#FF9800,stroke:#E65100,color:#fff
    style END fill:#4CAF50,stroke:#2E7D32,color:#fff

TX1 locks the account, does its work, and releases. TX2 waits for the lock (B2), then proceeds serially. There's no race condition—TX2 reads the final value 150 (from TX1's work) and applies its update correctly. The result is correct but potentially slower (serialization can reduce concurrency).

Transaction log and recovery

When a database crashes, the transaction log ensures recovery:

graph TD
    A["Client sends UPDATE"]
    B["Database logs transaction"]
    C["Log written to disk"]
    D["Execute query in memory"]
    E["💥 Power failure!"]
    F["Database restarts"]
    G["Replay log from disk"]
    H["Re-execute all committed transactions"]
    I["✓ Database consistent"]
    
    A --> B
    B --> C
    C --> D
    D --> E
    E --> F
    F --> G
    G --> H
    H --> I
    
    style E fill:#FF5252,stroke:#C62828,color:#fff
    style I fill:#4CAF50,stroke:#2E7D32,color:#fff

The key: the log is written to disk before the query executes in memory. Even if the server crashes, the log survives. On restart, the database replays the log and reconstructs the state—no data is lost.

ACID properties in one diagram

Here's a compact summary of ACID guarantees:

graph TD
    A["ACID Transaction"]
    B["Atomicity"]
    C["Consistency"]
    D["Isolation"]
    E["Durability"]
    B_desc["All-or-nothing:<br/>transfer $100 either<br/>both accounts change<br/>or neither changes"]
    C_desc["Valid state:<br/>balance updates<br/>obey constraints<br/>FK/PK intact"]
    D_desc["Concurrent safety:<br/>no dirty reads<br/>no race conditions<br/>serializable order"]
    E_desc["Permanent:<br/>committed data<br/>survives crashes<br/>logged to disk"]
    
    A --> B
    A --> C
    A --> D
    A --> E
    B --> B_desc
    C --> C_desc
    D --> D_desc
    E --> E_desc

A developer reviewing this knows what ACID means in practice—not just theory.

Best practices for transaction diagrams

Show the happy path first — diagram the success case (COMMIT) before error cases (ROLLBACK).
Mark decision points — use diamonds for lock waits, constraint checks, and rollback triggers.
Label with timestamps — if concurrency matters, add time markers (T0, T1, T2) to show order.
Annotate locks — use 🔒 emoji or "LOCK" labels to show where blocking occurs.
Separate transactions visually — use subgraphs to show which operations belong to which transaction.
Keep it concrete — use real table/column names, not abstract "operation A/B."
Test against the database — verify the diagram matches your database's actual isolation level.

FAQ

What isolation level should I use?

READ UNCOMMITTED: risky, allows dirty reads; avoid unless you need extreme speed.
READ COMMITTED: safe default; prevents dirty reads but allows non-repeatable reads.
REPEATABLE READ: prevents non-repeatable reads; good for reports and analytics.
SERIALIZABLE: safest but slowest; use for critical financial transactions.

How do I handle deadlocks?
A deadlock occurs when TX1 waits for a lock held by TX2, which waits for a lock held by TX1. Diagram it as a cycle: A locks X, B locks Y, A waits for Y, B waits for X. The database detects cycles and rolls back one transaction. Design transactions to acquire locks in the same order (lock ordering) to prevent deadlocks.

Can I show connection pools and timeouts?
Yes. Use a node for the connection pool and edges to show acquire/release. Add a timeout node for when a client waits too long.

How do I document a materialized view refresh?
Treat a refresh as a transaction: lock the view table, drop old data, insert new data, unlock. Show it like any other batch transaction.

Document your database transactions in the MermaidCreator editor. Clear transaction diagrams help teams reason about consistency, concurrency, and recovery—and catch race conditions before they reach production.

Mermaid