Why Concurrency Control Is Needed
A plain-language look at why databases need concurrency control, the four classic anomalies it prevents, and how isolation levels trade strictness for speed.
-
DBMS Fundamentals
- Data, Information and Databases
- What a DBMS Is and Why It Exists
- File System versus DBMS
- Advantages and Limitations of a DBMS
- Database Users and the Role of the DBA
- Three Level Architecture and Data Abstraction
- Logical and Physical Data Independence
- Schema, Instance and Metadata
- Database Applications and the Database System Environment
- Database Architecture
- Data Models
-
ER Model
- Entities, Entity Sets and Entity Types
- Types of Attributes in the ER Model
- Keys in the ER Model
- Relationships, Relationship Sets and Degree
- Cardinality and Participation Constraints
- Strong and Weak Entities
- Drawing and Reading ER Diagrams
- Extended ER: Generalisation, Specialisation and Aggregation
- Converting an ER Diagram into Relational Tables
- ER Design Projects
- Relational Model
- Relational Algebra
- Functional Dependencies
-
Normalisation
- Why Normalisation Exists: Anomalies and Redundancy
- First Normal Form
- Second Normal Form and Partial Dependency
- Third Normal Form and Transitive Dependency
- BCNF and BCNF Decomposition
- 4NF, 5NF, Multivalued and Join Dependencies
- Lossless Decomposition and Dependency Preservation
- Complete Worked Normalisation: Unnormalised to BCNF
- Denormalisation and When to Use It
- Concurrency Control
Concept
Concurrency control is the set of rules a DBMS follows so that many transactions can run at the same time without corrupting the database. Running transactions strictly one after another is safe, but far too slow once hundreds of users touch the same tables at once.
The goal in one line: let transactions overlap in time, while guaranteeing the final result is the same as some one-at-a-time order of those same transactions.
Anomalies uncontrolled concurrency causes
| Anomaly | What goes wrong |
|---|---|
| Lost update | Two transactions read the same value, both compute a new value, and the second write silently overwrites the first. |
| Dirty read | A transaction reads a value written by another transaction that has not committed yet, and that write is later rolled back. |
| Unrepeatable read | A transaction reads the same row twice and gets two different values because another transaction updated it in between. |
| Phantom read | A transaction re-runs the same query and a new row appears or an old one disappears because another transaction inserted or deleted rows in between. |
Lost update, worked example
Account balance = 1000
T1: read balance -> 1000
T2: read balance -> 1000
T1: balance = balance+200 -> writes 1200
T2: balance = balance-300 -> writes 700
Final balance = 700
Correct answer, if T1 then T2 had run one after another: 1000+200-300 = 900
900 was never written. T1's update was lost.Why concurrency control matters
- Throughput. Letting independent transactions overlap uses the CPU, disk and network far better than forcing a queue.
- Isolation. It is the "I" in ACID: each transaction must behave as if it were the only one running.
- Correctness without giving up parallelism. The DBMS decides, through locking or ordering, which overlaps are safe and which are not, instead of leaving it to chance.
Important terminology
| Term | Meaning |
|---|---|
| Transaction | A sequence of read and write operations treated as one logical unit of work. |
| Schedule | The actual order in which operations from one or more transactions execute. |
| Serial schedule | A schedule where transactions run one after another with no interleaving. |
| Concurrent schedule | A schedule where operations from different transactions are interleaved. |
| Serializable schedule | A concurrent schedule whose result matches some serial schedule of the same transactions. |
| Isolation level | A DBMS setting that trades strictness of isolation for performance, by permitting some anomalies. |
Isolation levels and what they still allow
| Isolation level | Dirty read | Unrepeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
Every level below Serializable is a deliberate trade: it accepts a named anomaly in exchange for less locking and higher throughput.
Common mistakes
- Treating isolation as all-or-nothing. Real systems offer levels, and most applications run below Serializable on purpose.
- Confusing atomicity with isolation. Atomicity is about a transaction being all-or-nothing on failure; isolation is about transactions not seeing each other's half-finished work.
- Assuming more concurrency is always better. Past a point, extra interleaving only adds anomaly risk without a real throughput gain.
Exam and interview questions
- Define concurrency control and state why serial execution alone is not acceptable in practice.
- Illustrate the lost update problem with a two-transaction example.
- List the four classic concurrency anomalies and describe each in one line.
- Which isolation level prevents dirty reads but still allows phantom reads?
- Explain the difference between a serial schedule and a serializable schedule.
Practice
- Write a two-transaction interleaving that produces a dirty read, then mark the exact point where the anomaly happens.
- Take a ticket-booking system where two users book the last seat at the same time. Name the anomaly this risks and the isolation level that would prevent it.
- Explain in two sentences why Serializable isolation, despite being the safest, is not always the default in production databases.
Conclusion
Concurrency control exists to let a DBMS run many transactions in parallel while still promising the one guarantee applications depend on: the outcome is exactly as if those transactions had happened one at a time, in some order.