Hello! Welcome to the next lesson in our journey through database fundamentals.
In the last lesson, we learned how to group multiple SQL statements into a single, atomic unit using BEGIN, COMMIT, and ROLLBACK. This ensures that a set of operations, like a bank transfer, either succeeds completely or fails completely.
That addresses the Atomicity property of ACID. But what happens when hundreds of users are performing transactions on the same data at the same time? How does a database prevent one user's partial transaction from corrupting the data another user is trying to read? This is the challenge of concurrency, and it brings us to the Isolation property of ACID.
Today's learning outcome is to describe how concurrent transactions can lead to data anomalies like dirty reads or phantom reads. We will explore the specific, undesirable side effects that can occur when transactions are not properly isolated from one another. Understanding these problems is the first step toward learning how to prevent them.
The Challenge of Concurrency
Modern database systems are designed for concurrent use, meaning multiple transactions can execute in parallel. This is crucial for performance. Imagine an e-commerce website where only one customer could shop at a time—it would be unusable.
However, allowing transactions to run simultaneously on the same data is like having multiple people edit a shared document at once. Without strict rules, chaos can ensue. One person might see another's half-finished sentence, or one person's edits might completely overwrite another's. In database terms, these "glitches" are called concurrency anomalies or read phenomena.
Understanding Database Concurrency Problems with Simple ...
To build a strong mental model for this, start by reading the introduction of this article from Medium. It uses a great analogy of a busy library to explain the core concept of concurrency.
Read the first three sections, from the beginning down to 'What is Concurrency in Databases?'.
Let's dive into the specific problems that can arise.
1. Dirty Read
A dirty read occurs when a transaction reads data that has been modified by another transaction, but has not yet been committed. It's "dirty" because if the modifying transaction performs a ROLLBACK, the data that was read is now invalid—it never officially existed.
Analogy: The Premature Bank Balance Check
Imagine you're checking your bank balance. A transaction to deposit your $500 paycheck has started, and your balance temporarily shows the new amount. You see this and proceed to make a large purchase. However, the deposit transaction fails and is rolled back. You have now made a decision based on data that was never made permanent.
The Wikipedia article on Isolation provides a very clear, technical breakdown of this phenomenon. Pay close attention to the table showing the sequence of commands in two parallel transactions.
Read the section titled 'Dirty reads'. Notice how Transaction 1 reads a value of 21, but Transaction 2 then rolls back, making that read invalid.
Dirty reads are one of the most dangerous anomalies because they can cause your application to make decisions based on data that is essentially a ghost.
2. Lost Update
A lost update occurs when two concurrent transactions read the same data, both perform a modification, and one overwrites the other's changes without seeing them. The update from the first transaction to commit is "lost."
Analogy: The Conflicting Address Change
Two customer service reps pull up your customer record at the same time. Both see your address is "123 Pine St."
- Rep A updates it to "456 Oak Ave" and saves.
- A moment later, Rep B, still looking at the original "123 Pine St.", updates it to "789 Maple Dr" and saves.
Rep B's save overwrites Rep A's change. The update to "456 Oak Ave" is now lost forever.
This is a classic problem in concurrent systems. Given your interest in systems programming, you can think of this as the database equivalent of a read-modify-write race condition that you might encounter in multithreaded C programming when two threads access a shared variable without a mutex lock.
3. Non-Repeatable Read
A non-repeatable read occurs when a transaction reads the same row twice, but gets different data each time because another transaction modified that row and committed its changes in between the reads.
Analogy: The Fluctuating Flight Price
You search for a flight and see a price of $300. You take a few minutes to confirm your travel dates. Within your "shopping session" (your transaction), you check the price again just before booking, and it's now $400. An airline pricing algorithm (another transaction) updated the price while you were deciding. The same query yielded two different results.
The Wikipedia article on Isolation also has a precise definition and a table-based example for this anomaly.
Read the section 'Non-repeatable reads'. Compare this scenario to the 'Dirty reads' example. The key difference here is that Transaction 2 commits its change, making the update permanent.
4. Phantom Read
A phantom read is similar to a non-repeatable read, but more subtle. It occurs when a transaction executes a query twice, and the set of rows returned is different each time. This happens when another transaction inserts or deletes rows that match the query's WHERE clause. The new or missing rows are called "phantoms."
The key difference:
- Non-repeatable read: An existing row is updated.
- Phantom read: A new row appears or an existing row disappears.
Analogy: The Ever-Changing Property Listing
A real estate agent runs a search for all available three-bedroom houses in a specific zip code and finds 10 properties. While preparing a report for a client (still within the same transaction), another agent adds a new three-bedroom listing in that zip code and commits the change. The first agent runs the same search again to double-check the count and now finds 11 properties. A "phantom" property has appeared.
For a more detailed, practical look at how this happens, the following video provides an excellent demonstration.
Phantom Reads in Postgres Explained Compared to Other DBMS
This video from Hussein Nasser gives a fantastic, hands-on explanation of phantom reads using actual SQL queries in PostgreSQL. It's a great deep-dive that aligns with your interest in understanding the low-level 'why'.
Watch from the beginning to 03:26. Focus on how the INSERT from the second transaction causes the SUM() in the first transaction to produce a different, inconsistent result.
Test your understanding!
For each scenario below, identify which concurrency anomaly is being described: Dirty Read, Lost Update, Non-Repeatable Read, or Phantom Read.
- Scenario A: Your transaction calculates the average employee salary. While it's running, another transaction gives one employee a raise and commits the change. Your transaction then reads that employee's new salary for the calculation, leading to an inconsistent average.
- Scenario B: You run a query to count the number of open support tickets for "Project X", and it returns 5. Another support agent then creates a new ticket for "Project X" and commits it. You run your count query again and it returns 6.
- Scenario C: You read the inventory level for a product, which is 10. Another transaction starts, reduces the inventory to 0, but hasn't committed yet. Your transaction reads the inventory again and sees 0. The other transaction then fails and rolls back, setting the inventory back to 10. Your transaction acted on the incorrect value of 0.
Show answer
- Scenario A is a Non-Repeatable Read. The same employee row, when read twice, returned different data (
salary) because another transaction committed anUPDATEin between. - Scenario B is a Phantom Read. A new row that matched your query's
WHEREclause (project = 'Project X') was added by another transaction, changing the number of rows returned by your query. - Scenario C is a Dirty Read. Your transaction read the value of 0, which was uncommitted data from another transaction. That data was later rolled back, meaning the value of 0 was never officially valid.
Conclusion
In this lesson, we explored the dark side of concurrency. While running transactions in parallel is essential for performance, it can lead to several data anomalies if they are not properly isolated.
Key Takeaways:
- Concurrency allows multiple transactions to run at the same time but introduces risks.
- Dirty Read: Reading uncommitted data that may later be rolled back.
- Lost Update: An update from one transaction is overwritten by another.
- Non-Repeatable Read: Re-reading a row returns different data because it was updated by another transaction.
- Phantom Read: Re-running a query returns a different set of rows because another transaction inserted or deleted records.
Preview of the Next Lesson:
Now that we've clearly defined the problems, how do we solve them? The SQL standard provides a solution: Transaction Isolation Levels. In the next lesson, we will explore these levels (e.g., READ COMMITTED, SERIALIZABLE), which act as a control knob to define how much a transaction should be isolated from others, allowing you to make a trade-off between performance and data consistency.