diagrams
Database

Transaction Lifecycle

A database transaction is a unit of work that groups one or more SQL statements into an atomic operation — either all statements succeed and are persisted, or none of them take effect.

diagram.mmd — sequence
Edit in Graphlet →
Transaction Lifecycle sequence diagram
About Source

A database transaction is a unit of work that groups one or more SQL statements into an atomic operation — either all statements succeed and are persisted, or none of them take effect.

This sequence diagram traces the complete lifecycle of a single transaction from the client's BEGIN through to the final COMMIT (or ROLLBACK). The client issues BEGIN, which opens a transaction context in the database engine. The engine allocates a transaction ID and establishes the transaction's visibility snapshot — the set of committed rows the transaction is allowed to see, determined by the isolation level.

Within the transaction, the client executes one or more statements. For each write (UPDATE, INSERT, DELETE), the engine locks the affected rows (in pessimistic concurrency mode) or creates new row versions (in MVCC — see MVCC Workflow). The changes are written to the write-ahead log before being applied to the heap, so they survive a crash even before COMMIT.

At COMMIT, the engine writes a commit record to the WAL, flushing it to durable storage. Only after the WAL is fsynced does the engine release row locks and make the changes visible to other transactions. If a ROLLBACK is issued instead — because of an application error or an explicit decision — the engine uses the WAL or undo log to reverse all changes made during the transaction, as if it never started.

Understanding this lifecycle is the foundation for reasoning about ACID Properties. Atomicity is enforced by the all-or-nothing commit/rollback decision. Durability is guaranteed by the WAL fsync before commit returns. Isolation is controlled by lock acquisition and snapshot management during the active phase. When multiple databases participate in a single logical transaction, the Two Phase Commit protocol extends this lifecycle across nodes.

Free online editor
Edit this diagram in Graphlet
Fork, modify, and export to SVG or PNG. No sign-up required.
Open in Graphlet →

Frequently asked questions

A database transaction is a unit of work that groups one or more SQL statements into an atomic operation — either all statements succeed and their changes are persisted, or none take effect. Transactions are delimited by BEGIN and ended with COMMIT (success) or ROLLBACK (abort).
BEGIN opens a transaction context and allocates a transaction ID. The engine establishes a visibility snapshot based on the isolation level. Each write operation locks affected rows (or creates new row versions under MVCC) and appends changes to the Write-Ahead Log. At COMMIT, the engine writes a commit record to the WAL and fsyncs it to durable storage before releasing locks and making changes visible. A ROLLBACK uses the undo log to reverse all changes as if the transaction never ran.
Use explicit transactions whenever multiple statements must succeed or fail together — transferring money between accounts, creating an order with its line items, or any multi-step operation with referential integrity requirements. Use autocommit for single independent statements where partial failure is acceptable. Wrapping large batches of unrelated inserts in a single transaction also improves throughput by amortising WAL fsync overhead across many rows.
Holding transactions open for long periods is the most common mistake — long-running transactions block row locks, inflate MVCC storage, and delay replication. Another frequent error is starting a transaction but forgetting to commit or roll back, leaving connections in a hung state. Catching exceptions without rolling back in application code can also leave the database in an inconsistent state if subsequent operations in the same connection inherit the open transaction context.
mermaid
sequenceDiagram participant Client participant Engine as DB Engine participant WAL as Write-Ahead Log participant Storage as Heap Storage participant LockMgr as Lock Manager Client->>Engine: BEGIN Engine->>Engine: Allocate transaction ID (txid 4821) Engine->>Engine: Create visibility snapshot Engine-->>Client: Transaction started Client->>Engine: UPDATE accounts SET balance = 500 WHERE id = 1 Engine->>LockMgr: Acquire row lock (id=1) LockMgr-->>Engine: Lock granted Engine->>WAL: Write update record WAL-->>Engine: Logged Engine->>Storage: Apply change (not yet visible) Engine-->>Client: 1 row updated Client->>Engine: INSERT INTO audit_log VALUES (4821, 'update', NOW()) Engine->>WAL: Write insert record WAL-->>Engine: Logged Engine->>Storage: Apply insert Engine-->>Client: 1 row inserted Client->>Engine: COMMIT Engine->>WAL: Write commit record + fsync WAL-->>Engine: Durable Engine->>LockMgr: Release all row locks Engine->>Storage: Mark changes visible to other transactions Engine-->>Client: COMMIT complete
Copied to clipboard