Why databases need transactions
A transaction groups database operations into one unit of work. Imagine moving a balance between two accounts: one row must decrease and another must increase. If the application stops after the first update, the data is left in a state the application did not intend. Placing both updates in a transaction lets the database either commit the complete change or roll it back.
Transactions also matter when requests arrive at the same time. Two shoppers might try to buy the last item in stock, or two workers might update the same job record. A transaction provides a boundary for each request, but that boundary alone does not settle every concurrency question. The database’s isolation behavior determines what each transaction can observe while other transactions are running.
What ACID promises
ACID names four properties. Atomicity means a transaction’s changes commit together or are rolled back together. Consistency means a successful transaction preserves the rules the database enforces, such as declared constraints. It does not mean the database can detect every mistake in application logic. Isolation concerns how concurrent transactions interact, including which intermediate or committed changes they can see. Durability means that once a commit is confirmed, its changes should survive failures within the database system’s stated guarantees.
These properties work together but solve different problems. Atomicity protects the two-part balance update from being half-applied. A constraint might reject a value that violates a declared rule. Isolation addresses what happens if another transaction reads or changes those balances at the same time. Durability addresses whether the committed result remains available after a restart. For a particular database, the exact guarantees depend on its configuration and documentation.
The concurrency problems isolation levels address
A dirty read occurs when one transaction sees another transaction’s uncommitted change. If the writer later rolls back, the reader acted on a value that never became permanent. A non-repeatable read occurs when a transaction reads the same row twice and gets different committed values because another transaction changed it between reads. A phantom is a change in the rows matching a query: for example, a second search for open orders finds a newly committed order.
Another important pattern is write skew. Suppose two staff members may go off duty only if at least one remains on duty. Two transactions each read that both are on duty, then each updates a different staff row to off duty. Because they write different rows, both changes may commit under some isolation implementations, leaving nobody on duty. Preventing this requires a suitable constraint, an appropriate locking strategy, or isolation strong enough to handle the invariant.
A practical map of isolation levels
Read Uncommitted permits the weakest separation and, where supported as defined, may allow dirty reads. Read Committed prevents dirty reads but generally allows a later query in the same transaction to see changes committed by someone else. It can be a reasonable starting point when each statement can safely work with the latest committed data and the application handles competing updates explicitly.
Repeatable Read aims to keep previously read data stable for the transaction. Its behavior for range queries and conflicting writes varies by database: some systems use a transaction-wide snapshot, while others rely on different combinations of locks or versioning. Snapshot-based reads can give a consistent view yet still permit write skew if concurrent transactions update separate rows. Serializable is the strongest standard level: the outcome should be equivalent to some order in which the transactions ran one at a time. A database may achieve this with locks, conflict detection, or both, and may require an application to retry a transaction it cannot safely commit.
Treat these names as a map, not a complete specification. Database products can differ in their defaults and in what each named level prevents. Before relying on a level for a particular invariant, check the database’s documented behavior and test the competing operations that matter to your application.
Choosing safeguards for a workload
Start with the rule that must remain true. For an inventory purchase, “stock must not fall below zero” is more useful than a vague goal of “strong consistency.” Where possible, enforce such rules with database constraints or an update that checks its condition as it writes. Keep related changes in one transaction, and check whether an update actually affected the expected row. If correctness depends on the result of an earlier read, consider what another transaction could change before the write.
Stronger isolation can simplify reasoning, but it may increase waiting, conflicts, or retries for some workloads. Weaker isolation may offer more concurrency while placing more responsibility on queries and application logic. Keep transactions short, avoid external side effects such as sending an email before commit, and make any retry path safe to run again. The best choice is the one whose documented guarantees protect your specific rules under concurrent use—not simply the isolation level with the strongest name.
