All templates
Flowchart template
Database transaction lifecycle
ACID compliance: isolation levels, commit, rollback, and conflict detection.
Every database application relies on transactions to keep data consistent, yet many teams gloss over what actually happens when multiple transactions run simultaneously. This template shows the lifecycle: begin, execute SQL, validate the changes, check for conflicts depending on the isolation level, then commit (if all is well) or rollback (if something failed). The diagram makes visible where locks are held, when conflicts are detected, and why isolation levels matter.
Transactions are the boundary between data that is merely transient and data that is durably correct. Without explicit transaction boundaries, a network failure mid-write corrupts your database. With transactions, either all changes stick or none do — no middle ground.
When to use this template
- Database design sessions — sketch your transaction boundaries before writing queries, so the whole team agrees on what atomicity and isolation levels you need.
- Multi-step data operations — when an operation touches multiple tables (book a flight, debit the account, issue a confirmation), draw the transaction boundaries to clarify what happens if any step fails.
- Handling concurrent updates — show how your application detects and resolves conflicts when two users update the same row, and why isolation levels matter.
How to adapt it
Customize the diagram for your specific patterns:
- Add savepoints — explicit intermediate commit points within a long transaction, so you can rollback to a savepoint rather than the whole transaction.
- Show retry logic — if a transaction fails due to a conflict or lock timeout, the application retries with exponential backoff. Add a retry loop after the conflict-detected node.
- Expand isolation-level branches — add more detail to each isolation level's validation check, showing which anomalies (dirty reads, non-repeatable reads, phantom reads) each prevents.
Visual edits regenerate clean Mermaid code, so you can map your transaction patterns without Mermaid syntax.
Mermaid code
Copy it anywhere Mermaid is supported — GitHub, Notion, or your docs.
flowchart TD
A[Begin transaction] --> B[Read initial state]
B --> C[Execute SQL: UPDATE/INSERT/DELETE]
C --> D{Validation passes?}
D -->|No| E[Rollback transaction]
E --> F[Release locks]
F --> G[Return error to client]
D -->|Yes| H{Check isolation level}
H -->|Read Uncommitted| I[Proceed to commit]
H -->|Read Committed| J{Conflict detected?}
H -->|Repeatable Read| J
H -->|Serializable| K{Conflicting transaction?}
J -->|Yes| L[Rollback transaction]
K -->|Yes| L
J -->|No| I
K -->|No| I
L --> F
I --> M[Write to transaction log]
M --> N[Commit to database]
N --> O[Release locks]
O --> P[Return success to client]
Frequently asked questions
- What is an ACID transaction and why do I need one?
- ACID stands for Atomicity (all-or-nothing), Consistency (data stays valid), Isolation (concurrent transactions don't interfere), and Durability (committed data survives crashes). A transaction groups multiple SQL operations so they either all succeed or all fail together. Without ACID, a bank transfer might debit one account but crash before crediting the other — money vanishes. ACID guarantees prevent data corruption and inconsistency.
- What are isolation levels and when should I use each one?
- Isolation levels control how much concurrent transactions can see each other's changes. Read Uncommitted (fastest, least safe) lets transactions see uncommitted changes. Read Committed (default) sees only committed changes. Repeatable Read prevents dirty and non-repeatable reads. Serializable (slowest, safest) runs transactions one at a time. Most systems default to Read Committed; use higher levels for financial transactions or inventory where precision matters.
- What happens if two transactions conflict (both updating the same row)?
- The database locks the row so only one transaction can write it at a time. When the first transaction commits, the lock releases and the second transaction proceeds with the now-updated data. If both are reading (no writes), most systems allow concurrent reads. Serializable isolation prevents this by detecting conflicts early and rolling back one transaction. The trade-off: safety vs. throughput.
- How do I implement rollback if something fails mid-transaction?
- Most databases support explicit ROLLBACK commands. In your application, wrap the transaction in a try-catch: if any SQL operation fails, call ROLLBACK to undo all changes since BEGIN. If all operations succeed, call COMMIT. Visual edits regenerate clean code, so you can sketch your transaction flow and share it with your database team to align on isolation strategy and conflict handling.