Database Reference
In-Depth Information
Table 17-4.
Transaction Isolation Levels and data inconsistency anomalies
Dirty Reads
Non-Repeatable Reads
Duplicated Reads
Phantom Reads
Skipped Rows
Read uncommitted
Yes
Yes
Yes
Yes
Yes
Read committed
No
Yes
Yes
Yes
Yes
Repeatable reads
No
No
No
Yes
Yes
Serializable
No
No
No
No
No
As you can see, with pessimistic transaction isolation levels only Serializable would be free from any data
inconsistency issues. But that transaction isolation level is not good from a concurrency standpoint when we have
both readers and writers working with the same data in the system.
Fortunately there is another solution: Optimistic transaction isolation levels—read committed snapshots and
snapshots which, we will discuss in Chapter 21.
It is important to understand how different lock types work. Knowledge about their behavior and compatibility
help us to troubleshoot the blocking issues and design the transaction strategies in a way that provides optimal data
consistency and concurrency in our systems.
