How Databases Manage Concurrent Transactions

When several requests read and change the same database records, the database must decide what each request sees, which changes must wait, and when it should reject work to protect correctness. A transaction is a group of database operations that commits as a unit or rolls back as a unit. Concurrency control is the set of rules and mechanisms that coordinates transactions running at the same time.

Databases use locks, stored versions of data, conflict detection, or combinations of these methods. The goal is to let independent work overlap while preventing harmful results. A strong correctness guarantee, called serializability, means that committed transactions have the same effect as if the database had run them one at a time in some order. The isolation level you choose describes the guarantees you get; the database engine determines how it enforces them.

Locks make competing changes wait

A lock gives a transaction temporary control over a database resource, such as a row or index record. A shared lock generally lets multiple transactions read the same resource, while an exclusive lock protects a change by blocking conflicting reads or writes. When a transaction requests a lock that conflicts with one held by another transaction, it waits until that conflict ends.

Lock scope affects how much work can proceed at once. A lock on a specific record can leave unrelated records available, while a lock on a larger resource can block more work. Locks on individual records let more unrelated work proceed, but require the database to manage more locks. The details vary by engine: InnoDB, for example, describes its row-level locks as locks on index records in its locking documentation.

Code can also request locks explicitly when it must read a row and then act on its current state. In PostgreSQL, a SELECT ... FOR UPDATE query locks the selected rows so another transaction cannot make a conflicting change before the current transaction finishes. MySQL offers similar locking reads. Use this approach when the read itself must reserve a particular row, and keep the locked portion of the transaction short. Locks increase waiting, and overlapping lock requests can create deadlocks, as PostgreSQL explains in its explicit-locking documentation.

A deadlock occurs when transactions wait on each other in a cycle. For example, one transaction holds a lock on row A and waits for row B, while another holds row B and waits for row A. Databases detect deadlocks and abort one transaction to break the cycle. The code that runs the transaction should handle the resulting error by retrying the entire transaction, including its reads, rather than repeating only the last statement.

MVCC lets readers use older versions

Multi-version concurrency control (MVCC) stores multiple versions of a row so a reader can use a version that matches its view of the data. A writer creates a newer version; readers continue to see a version that was committed and visible to their query or transaction. This lets many reads proceed without waiting for a writer.

MVCC reduces some read/write blocking, but it does not remove locks or prevent every write conflict. InnoDB combines multiversioning with locking, and its transaction model describes both mechanisms. Writers can still wait for one another, and code may need explicit locks to coordinate a decision made after reading data.

With a statement-level snapshot, each query sees data as it was at a particular point in time. A later query in the same transaction can therefore see newer commits. With a transaction-level snapshot, queries share a stable view for the transaction. Product behavior varies. PostgreSQL’s READ COMMITTED level takes a new snapshot for each command, while its REPEATABLE READ level uses a stable transaction snapshot, as described in the PostgreSQL isolation documentation.

Isolation levels describe what transactions can see

Isolation levels set boundaries on which changes concurrent transactions can observe. The names come from SQL standards, but databases do not all implement each name in the same way. A level’s name alone does not tell you exactly how the engine handles reads, locks, or conflicting writes.

Four common anomalies help make the guarantees concrete:

• A dirty read occurs when a transaction sees another transaction’s uncommitted change.
• A nonrepeatable read occurs when a transaction reads a row again and sees a change another transaction committed in the meantime.
• A phantom occurs when a repeated condition-based query returns a different set of rows because another transaction changed which rows match.
• A serialization anomaly occurs when transactions commit a result that cannot be explained by any order in which they ran one at a time.

Product documentation illustrates why these distinctions matter. PostgreSQL maps READ UNCOMMITTED to READ COMMITTED, and its REPEATABLE READ level prevents phantoms while still allowing some serialization anomalies. In MySQL 8.4, InnoDB defaults to REPEATABLE READ and uses next-key locks for relevant searches and index scans to help prevent phantom inserts. Those are product-specific behaviors, not universal definitions of the isolation-level names.

A stable snapshot can still allow write skew

A consistent snapshot can still let transactions violate a rule that spans several rows or a wider condition. Write skew happens when two transactions check a rule, such as whether on-call staff remain available, then update different rows based on what they saw. Because they change separate rows, neither necessarily conflicts with the other’s write.

Suppose a rule requires at least one on-call staff member to remain available. Two transactions each see two people on call. One removes the first person from duty; the other removes the second. If both commit, nobody remains on call, even though each transaction made its decision from a consistent view.

When a rule depends on multiple rows or on a condition such as “at least one person remains,” choose a database guarantee that covers the whole rule. A single atomic database statement can avoid a gap between reading a value and later writing a decision based on it. For more complex rules, explicit locking or serializable isolation may be appropriate.

At SERIALIZABLE, the database guarantees that committed transactions behave as though they ran one at a time in some order. Engines enforce this in different ways. PostgreSQL uses Serializable Snapshot Isolation to detect dangerous read/write dependencies; when it detects a serialization failure, it aborts a transaction so the application can retry. Serializable isolation adds conflict tracking and retry costs, but it does not always mean more blocking. Its effect on performance depends on the workload and the alternative.

Choose guarantees around the business rule

Use the narrowest approach that protects the actual invariant, or rule that must always hold. For a change to one row, a single update can check a condition and change the row in one database operation. This avoids a separate read followed by a write based on data that may have changed. For a read-then-write decision about a particular row, an explicit lock can reserve that row until the transaction finishes. For rules that involve several rows or depend on which rows match a condition, check whether locking the relevant records or using serializable isolation protects the rule.

Keep transactions short. A transaction that remains open while code waits for a network call or unrelated work holds locks longer and gives other requests more time to queue. When a transaction needs several locks, acquire them in a consistent order to reduce deadlock risk. In InnoDB, indexes, which the database uses to find rows, also affect which records or spans of records a search locks. Suitable indexes for the rows and conditions a transaction needs can help limit unnecessary locking.

Treat deadlocks and serialization failures as expected outcomes of concurrent work. Retry the whole transaction so the code rereads current data and makes its decision again. Make retries safe for external side effects, such as sending a message or charging a payment, because repeating the transaction’s database work does not automatically undo an action outside the database. Check your database’s documentation and configuration for the exact error behavior and isolation guarantees in use.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com