Database Concurrency Control: Managing Transaction Overlap

Updated 14 November 2026 · Standard formula, shown below

This guide explains how database concurrency control manages transaction overlap—when multiple transactions read and write the same data at the same time. You’ll learn the core techniques (locking, isolation levels, optimistic concurrency, and multi-versioning) and how they prevent lost updates, dirty reads, and other anomalies. No prior knowledge beyond basic database concepts is assumed.

What You’ll Need

A basic understanding of SQL transactions (BEGIN, COMMIT, ROLLBACK).
Familiarity with the concepts of read and write operations on database tables.
Access to a database system (like PostgreSQL, MySQL, or SQL Server) if you want to test the examples—though the principles apply universally.

Step-by-Step

Understand the problem: transaction overlap. When two transactions run at the same time and touch the same rows, the order of their operations can lead to anomalies. The classic ones are: dirty read (reading uncommitted data), non-repeatable read (same query returns different results within a transaction), phantom read (new rows appear in a range query), and lost update (two transactions overwrite each other’s changes).
Know the two main strategies: pessimistic and optimistic. Pessimistic concurrency assumes conflicts are frequent and locks data upfront. Optimistic concurrency assumes conflicts are rare and checks for conflicts at commit time. Most databases use a mix, but you can choose which to apply per transaction.
Use locking to serialize access. The most common mechanism is locking. A transaction acquires a shared lock before reading and an exclusive lock before writing. Shared locks are compatible with other shared locks, but exclusive locks block everything. If a transaction tries to acquire a lock that conflicts, it waits until the lock is released (or times out).
Set the isolation level. The SQL standard defines four levels that control how much locking and visibility you get: READ UNCOMMITTED (no locks on reads, can see uncommitted data), READ COMMITTED (reads only committed data, but can see different snapshots), REPEATABLE READ (same snapshot for all reads, but phantoms possible), and SERIALIZABLE (transactions appear to run one after another, usually by locking ranges or using multi-versioning). Choose the highest level that your application can tolerate for performance.
Understand deadlocks and how to resolve them. A deadlock occurs when two transactions each hold a lock the other needs. Databases detect deadlocks and automatically roll back one of the transactions (the “victim”). To minimize deadlocks, access tables in a consistent order, keep transactions short, and use lower isolation levels when possible.
Consider multi-version concurrency control (MVCC). Most modern databases (PostgreSQL, MySQL InnoDB, Oracle) use MVCC. Instead of locking reads, they keep multiple versions of each row. A transaction sees a snapshot of the database as of its start time. This allows readers not to block writers and vice versa. MVCC is the reason READ COMMITTED and REPEATABLE READ work without long shared locks.
Implement optimistic concurrency in your application. If you use optimistic locking, you don’t lock rows during the transaction. Instead, you include a version number or timestamp in each row. When updating, you set a condition: UPDATE ... WHERE id = ? AND version = ?. If the version doesn’t match, the update affects 0 rows, and you know a conflict occurred. Then you retry or abort.
Test with concurrent transactions. Open two database sessions and run overlapping transactions. Observe the behavior at different isolation levels. For example, at READ COMMITTED, a second transaction can see a committed change from the first, but at REPEATABLE READ, it cannot. Use tools like pg_stat_activity (PostgreSQL) or SHOW PROCESSLIST (MySQL) to see locks and waits.

Common Mistakes

Assuming SERIALIZABLE is always safe. Even at SERIALIZABLE, you can get serialization failures (in MVCC databases) where the database aborts a transaction because it can’t guarantee serializability. You must handle these retries.
Ignoring lock timeouts. A transaction that waits too long for a lock will eventually time out and be aborted. Don’t set timeouts too high, or you’ll have long stalls.
Forgetting to commit or rollback. Leaving a transaction open holds locks and can block others indefinitely. Always commit or rollback as soon as possible.
Using SELECT without FOR UPDATE when you plan to update. If you read a row and later update it, another transaction might change it in between. Use SELECT ... FOR UPDATE to lock the row for the duration of your transaction.
Confusing isolation levels with locking. READ COMMITTED doesn’t mean no locks; it means no shared locks on reads. Writes still use exclusive locks.

Tips

Keep transactions short. The less time a transaction is open, the less chance of lock contention and deadlocks.
Use the lowest isolation level that is correct. For most web applications, READ COMMITTED is sufficient and gives the best concurrency. Only use SERIALIZABLE when you truly need it.
Design your schema to reduce conflicts. For example, use separate tables for hot data, or use append-only patterns instead of updating counters.
Handle deadlock retries in your application. When a deadlock is detected, the database aborts one transaction. Your code should catch that error and retry the transaction from the beginning.
Monitor lock waits. Use database monitoring tools to see which queries are waiting for locks. This helps you identify bottlenecks.

Troubleshooting

Symptom: Transactions are slow or time out. Likely cause: lock contention. Check for long-running transactions that hold locks. Kill them if they are stuck. Reduce the isolation level if possible.

Symptom: You get a “deadlock detected” error. This is normal—the database is doing its job. Your application should retry the transaction. To reduce frequency, ensure all transactions access tables in the same order.

Symptom: You see “could not serialize access” errors. This happens in SERIALIZABLE mode with MVCC. The database couldn’t guarantee serializability, so it aborted. Retry the transaction. If it happens often, consider lowering the isolation level or redesigning the transaction.

Symptom: A SELECT returns data that was never committed. This is a dirty read. Check your isolation level—if it’s READ UNCOMMITTED, that’s expected. Switch to READ COMMITTED or higher.

Symptom: Updates disappear. This is a lost update. Use SELECT FOR UPDATE or optimistic locking (version column) to prevent it.

Frequently Asked Questions

Q: What is the difference between pessimistic and optimistic concurrency?
Pessimistic locks data before accessing, preventing conflicts. Optimistic doesn’t lock; it checks for conflicts at commit time and retries if needed.

Q: What does MVCC stand for and why is it useful?
Multi-Version Concurrency Control. It keeps multiple versions of rows so readers see a consistent snapshot without blocking writers, improving concurrency.

Q: Can I set the isolation level per transaction?
Yes, most databases allow you to set it with a command like SET TRANSACTION ISOLATION LEVEL REPEATABLE READ before starting the transaction.

Q: What is a phantom read?
A phantom read occurs when a transaction runs the same range query twice and gets different sets of rows because another transaction inserted or deleted rows in between. SERIALIZABLE prevents this.

Q: How do I know if my database uses MVCC?
Most modern relational databases (PostgreSQL, MySQL InnoDB, Oracle) do. You can check the documentation for your specific database.

Q: What happens if I don’t handle concurrency at all?
You risk data corruption: lost updates, inconsistent reads, and incorrect results. Concurrency control is essential for any multi-user system.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.