The most basic level of transaction isolation is read committed, and it makes two guarantees:
-
When reading from the database, you will see only data that has been committed (no dirty reads).
-
When writing to the database, you will overwrite only data that has been committed (no dirty writes).
Let’s discuss these two guarantees in more detail.
No dirty reads
Imagine a transaction has written some data to the database, but the transaction has not yet committed or aborted. Can another transaction see that uncommitted data? If so, that’s called a dirty read.
Transactions running at the read-committed isolation level must prevent dirty reads. This means that any writes by a transaction become visible to others only when that transaction commits (and then all its writes become visible at once). This is illustrated in Figure 8-4, where user 1 has set x = 3, but user 2’s get x still returns the old value, 2, while user 1 has not yet committed.
Diagram showing User 1 setting keys x and y to new values, while concurrently User 2 repeatedly reads x. Under read-committed isolation, the reads return a value that has been written only once the write has committed.
Figure 8-4. No dirty reads: user 2 sees the new value for x only after user 1’s transaction has committed
Preventing dirty reads is useful for a few reasons:
-
If a transaction needs to update several rows, a dirty read means that another transaction may see some of the updates but not others. For example, in Figure 8-2, the user sees the new unread email but not the updated counter. This is a dirty read of the email. Seeing the database in a partially updated state is confusing to users and may cause other transactions to make incorrect decisions.
-
If a transaction aborts, any writes it has made need to be rolled back (as in Figure 8-3). If the database allows dirty reads, a transaction may see data that is later rolled back—that is, data that is never actually committed to the database. Any transaction that read uncommitted data would also need to be aborted, leading to a problem called cascading aborts.
No dirty writes
What happens if two transactions concurrently try to update the same row in a database? We don’t know in which order the writes will happen, but we normally assume that the later write overwrites the earlier one.
However, what happens if the earlier write is part of a transaction that has not yet committed, so the later write overwrites an uncommitted value? This is called a dirty write. Transactions running at the read-committed isolation level must prevent dirty writes, usually by delaying the second write until the first write’s transaction has committed or aborted.
By preventing dirty writes, this isolation level avoids some kinds of concurrency problems:
-
If transactions update multiple rows, dirty writes can lead to a bad outcome. For example, consider Figure 8-5, which illustrates a used car sales website on which two people, Aaliyah and Bryce, are simultaneously trying to buy the same car. Buying a car requires two database writes: the listing on the website needs to be updated to reflect the buyer, and the sales invoice needs to be sent to the buyer. In the case of Figure 8-5, the sale is awarded to Bryce (because he performs the winning update to the listings table), but the invoice is sent to Aaliyah (because she performs the winning update to the invoices table). Read-committed isolation prevents such mishaps.
-
However, read-committed isolation does not prevent the race condition between two counter increments in Figure 8-1. In this case, the second write happens after the first transaction has committed, so it’s not a dirty write. It’s still incorrect, but for a different reason; in “Preventing Lost Updates”, we will discuss how to make such counter increments safe.
Diagram illustrating a dirty write scenario where simultaneous transactions cause the sale to be awarded to Bryce while the invoice is sent to Aaliyah, highlighting a transactional inconsistency.
Figure 8-5. With dirty writes, conflicting writes from different transactions can be mixed up
Implementing read-committed
Read-committed is a very popular isolation level. It is the default setting in Oracle Database, PostgreSQL, SQL Server, and many other databases.
Most commonly, databases prevent dirty writes by using row-level locks. When a transaction wants to modify a particular row (or document or some other object), it must first acquire a lock on that row. It must then hold that lock until the transaction is committed or aborted. Only one transaction can hold the lock for any given row; if another transaction wants to write to the same row, it must wait until the first transaction is committed or aborted before it can acquire the lock and continue. This locking is done automatically by databases in read-committed mode (or at stronger isolation levels).
How do we prevent dirty reads? One option would be to use the same lock and to require any transaction that wants to read a row to briefly acquire the lock and then release it again immediately after reading. This would ensure that a read couldn’t happen while a row had a dirty, uncommitted value (because during that time the lock would be held by the transaction that was making the write).
However, the approach of requiring read locks does not work well in practice, because one long-running write transaction can force many other transactions to wait until the long-running transaction has completed, even if the other transactions only read and do not write anything to the database. This harms the response time of read-only transactions and is bad for operability: a slowdown in one part of an application can have a knock-on effect in a completely different part of the application, due to waiting for locks.
Nevertheless, locks are used to prevent dirty reads in some databases, such as IBM Db2 and Microsoft SQL Server with the read_committed_snapshot=off setting.
A more commonly used approach to preventing dirty reads is the one illustrated in Figure 8-4. For every row that is written, the database remembers both the old committed value and the new value set by the transaction that currently holds the write lock. While the transaction is ongoing, any other transactions that read the row are simply given the old value. Only when the new value is committed do transactions switch over to reading the new value (see “Multiversion concurrency control” for more detail).
Some databases support an even weaker isolation level called read uncommitted. It prevents dirty writes but does not prevent dirty reads. In other words, it immediately returns the latest written value, even if the writing transaction hasn’t committed yet. This can provide better performance, since the database does not need to store two versions of the row. It can also reduce the probability of (but not prevent) lost updates, which we will talk about in “Preventing Lost Updates”.