Concurrency Control
Database Concurrency Control and Locking Mechanisms · Also known as: locking, MVCC
Concurrency control is the set of mechanisms used to coordinate concurrent transactions accessing shared data without corrupting the database. Formalized by database theorists in the 1970s-1980s, concurrency control ensures that multiple simultaneous transactions produce the same result as if they executed sequentially (serializability).
Read the full method
Sign in with a free account to read this section.
Method map
The neighbourhood of related methods — select a node to explore.
When to use it
Concurrency control is essential in any database with multiple simultaneous users. It is particularly important for systems requiring strong consistency guarantees. Assumptions include that conflicts occur rarely enough that lock wait times are acceptable, or that MVCC provides sufficient performance.
Strengths & limitations
- Prevents data corruption through anomalies (dirty reads, lost updates, phantom reads)
- Provides predictable behavior even under high concurrency
- Enables fair scheduling of concurrent transactions
- Lock-based approaches can reduce concurrency and throughput under contention
- Deadlocks may require transaction rollback and retry
- MVCC requires storage overhead for maintaining multiple versions
Frequently asked
What is a deadlock and how is it resolved?
A deadlock occurs when two transactions wait for each other: Transaction A holds lock X and waits for Y; Transaction B holds Y and waits for X. Databases detect deadlocks and rollback one transaction, allowing the other to proceed.
What is the difference between optimistic and pessimistic locking?
Pessimistic locking (2PL) acquires locks before accessing data, preventing conflicts proactively. Optimistic locking (MVCC) checks for conflicts at commit time, allowing higher concurrency if conflicts are rare.
How do isolation levels affect concurrency?
Higher isolation levels (Serializable) prevent anomalies but reduce concurrency through stricter locking. Lower levels (Read Uncommitted) improve throughput but allow anomalies. Choose based on consistency needs.
Should I use row-level or table-level locking?
Row-level locking allows higher concurrency but adds coordination overhead. Use row-level for large tables with selective access; use table-level for small tables or full-table operations where coordination costs outweigh benefits.
Sources
- Gray, J. (1981). The transaction concept: Virtues and limitations. VLDB Endowment, 7(6), 519-539. link ↗
- Reed, D. P. (1978). Naming and synchronization in a decentralized computer system. Ph.D. Dissertation, MIT. link ↗
- Papadimitriou, C. H. (1986). The Theory of Database Concurrency Control. Computer Science Press. link ↗
How to cite this page
ScholarGate. (2026, June 3). Database Concurrency Control and Locking Mechanisms. ScholarGate. https://scholargate.app/en/information-systems/concurrency-control
Which method?
Set this method beside its closest kin and read them side by side — the library lays the books on the table; the choice is yours.
- Transaction ManagementInformation Systems↔ compare