We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
A sequence of database operations treated as a single atomic unit. Either all operations succeed (commit) or none of them do (rollback).
In short
A database transaction is a group of read and write operations that the database treats as one indivisible unit: either every operation in the group takes effect (commit) or none of them do (rollback), so the database is never left in a half-finished state.
In short
A database transaction is a group of read and write operations that the database treats as one indivisible unit: either every operation in the group takes effect (commit) or none of them do (rollback), so the database is never left in a half-finished state.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Transaction across a run of lessons, not one page. These 4 alone are about 134 minutes of step-by-step reading, 2 of them with code you run in the browser, all with a quiz.
Coordinating atomic operations across multiple nodes, the hardest problem in databases.
Guarantee that your database write and message publish either both happen or neither does.
How two-phase commit makes a distributed transaction all-or-nothing, and why it blocks if the coordinator crashes. Includes a 2PC coordinator in code.
Long-lived transactions without distributed locking, orchestration vs choreography for microservices.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Distributed Transactionspay once,
yours for life
Picture a bank transfer that moves 500 from account A to account B. That is two writes: subtract 500 from A, add 500 to B. If the server crashes after the first write and before the second, the money has vanished. A transaction stops that from happening. You wrap both writes in BEGIN and COMMIT, and the database promises that either both writes land or neither does.
The guarantee is summarized by ACID: Atomicity (all or nothing), Consistency (the data follows every rule and constraint before and after), Isolation (concurrent transactions do not step on each other), and Durability (once committed, the change survives a crash). The acronym is from a 1983 paper by Theo Harder and Andreas Reuter, and it is still the contract every relational database advertises.
Without transactions you have to write fragile cleanup code by hand for every multi-step operation. With them, you describe the work as one unit and the database handles partial failure for you.
Atomicity and durability are usually backed by a write-ahead log (WAL). Before changing the actual data pages, the database appends a record of the intended change to an append-only log on disk. On COMMIT it flushes that log and tells you the work is safe. If the process dies mid-transaction, recovery on restart replays committed log records and discards uncommitted ones, so you never see a half-applied transaction. PostgreSQL, MySQL InnoDB, and SQLite in WAL mode all work this way.
Isolation is the harder part because many transactions run at once. Databases use locks, multi-version concurrency control (MVCC), or both. MVCC keeps multiple versions of each row so readers see a consistent snapshot without blocking writers. PostgreSQL and Oracle lean heavily on MVCC; that is why a long-running read in Postgres does not stall writers.
SQL defines four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Lower levels are faster but allow anomalies like dirty reads, non-repeatable reads, and phantom reads. SERIALIZABLE is the strictest and behaves as if every transaction ran one after another, at the cost of more aborts and retries. Most databases default to READ COMMITTED.
Use a transaction any time correctness depends on several writes happening together: transferring money, decrementing inventory while creating an order, or updating a record plus its audit row. If a partial result would corrupt your data, that work belongs in a transaction.
The cost is contention and latency. Holding locks or open snapshots while a transaction is alive blocks or slows other work, so keep transactions short and never wait on a network call or user input while one is open. Long transactions in MVCC systems also bloat storage because old row versions cannot be cleaned up until the transaction ends.
Transactions are easy on a single node and hard across many. Spanning multiple databases or services needs distributed protocols like two-phase commit, which is slow and can stall if a participant dies. That is why many distributed systems drop strict transactions in favor of eventual consistency or application-level patterns like the Saga, where each step has a compensating undo step instead of a global rollback.
Say a customer buys the last unit of a product. In one transaction the app inserts an order row, decrements the stock count from 1 to 0, and inserts a payment record. If the payment insert fails because the card is declined, the transaction rolls back: the order disappears and stock returns to 1, as if nothing happened. The next shopper still sees one unit available.
Now imagine two shoppers hit checkout at the same instant for that single unit. Isolation decides the outcome. Under SERIALIZABLE or with a SELECT FOR UPDATE row lock, one transaction wins and the other is rejected or retried, so you never oversell. Under a weaker level with no locking, both could read stock as 1 and both decrement it, leaving you at -1 and two angry customers. This is exactly why high-volume systems like Stripe and Shopify lean on transactional databases for the money and inventory paths.
PostgreSQL
Full ACID transactions backed by a write-ahead log and MVCC, with all four SQL isolation levels including true SERIALIZABLE.
MySQL (InnoDB)
The InnoDB storage engine adds ACID transactions, row-level locking, and crash recovery via its redo log.
Stripe
Runs payment and ledger operations inside database transactions so a charge and its balance update either both commit or both roll back.
Google Spanner
Provides externally consistent distributed transactions across regions using TrueTime clocks and two-phase commit.
COMMIT makes all the transaction's changes permanent and visible to other transactions. ROLLBACK throws every change away and returns the database to exactly how it looked before the transaction started. A transaction always ends in one or the other.
Atomicity (all operations succeed or none do), Consistency (the data obeys all rules and constraints), Isolation (concurrent transactions do not interfere), and Durability (committed changes survive crashes). It is the standard set of guarantees a transactional database gives you.
Many now do, at least for single documents or items. MongoDB added multi-document ACID transactions in version 4.0, and DynamoDB supports transactions across up to 100 items. Early NoSQL systems traded transactions for scale and availability, but the line has blurred.
An open transaction holds locks and keeps old row versions alive, which blocks or slows other work and bloats storage. Long transactions, especially ones that wait on network calls or user input, are a common cause of contention and deadlocks.
A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle and aborts one transaction so the other can finish; the aborted one should be retried by the application.
Distributed Transactions
Coordinating atomic operations across multiple nodes, the hardest problem in databases
Transactional Outbox Pattern
Guarantee that your database write and message publish either both happen or neither does
Two-Phase Commit
How two-phase commit makes a distributed transaction all-or-nothing, and why it blocks if the coordinator crashes. Includes a 2PC coordinator in code.
Saga Pattern
Long-lived transactions without distributed locking, orchestration vs choreography for microservices
ACID Properties
Database transaction guarantees that keep your data correct
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
Two-Phase Commit
A protocol ensuring all nodes in a distributed transaction either commit or abort together. Phase 1: prepare (vote). Phase 2: commit or rollback.
Isolation Level
Controls how much one transaction can see changes made by other concurrent transactions. Ranges from Read Uncommitted (fastest, least safe) to Serializable (slowest, safest).
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
This page explains the idea. The course covers it step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
See the System Design Masterclass coursecourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Transaction in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
