A beginner’s guide to Non-Repeatable Read anomaly

(Last Updated On: June 12, 2018)

Introduction

Database transactions are defined by the four properties known as ACID. The Isolation Level (I in ACID) allows you to trade off data integrity for performance.

The weaker the isolation level, the more anomalies can occur, and in this article, we are going to describe the Non-Repeatable Read phenomenon.

Observing data changed by a concurrent transaction

If one transaction reads a database row without applying a shared lock on the newly fetched record, then a concurrent transaction might change this row before the first transaction has ended.

Non-Repeatable Read

In the diagram above, the flow of statements goes like this:

  1. Alice and Bob start two database transactions.
  2. Bob’s reads the post record and title column value is Transactions.
  3. Alice modifies the title of a given post record to the value of ACID.
  4. Alice commits her database transaction.
  5. If Bob’s re-reads the post record, he will observe a different version of this table row.

This phenomenon is problematic when the current transaction makes a business decision based on the first value of the given database row (a client might order a product based on a stock quantity value that is no longer a positive integer).

How the database prevents it

If a database uses a 2PL (Two-Phase Locking) and shared locks are taken on every read, this phenomenon will be prevented since no concurrent transaction would be allowed to acquire an exclusive lock on the same database record.

Most database systems have moved to an MVCC (Multi-Version Concurrency Control) model, and shared locks are no longer mandatory for preventing non-repeatable reads.

By verifying the current row version, a transaction can be aborted if a previously fetched record has changed in the meanwhile.

Repeatable Read and Serializable prevent this anomaly by default. With Read Committed, it is possible to avoid non-repeatable (fuzzy) reads if the shared locks are acquired explicitly (e.g. SELECT FOR SHARE).

Some ORM frameworks (e.g. JPA/Hibernate) offer application-level repeatable reads. The first snapshot of any retrieved entity is cached in the currently running Persistence Context.

Any successive query returning the same database row is going to use the very same object that was previously cached. This way, the fuzzy reads may be prevented even in Read Committed isolation level.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

This phenomenon is typical for both Read Uncommitted and Read Committed isolation levels. The problem is that Read Committed is the default isolation level for many RDBMS like Oracle, SQL Server or PostgreSQL, so this phenomenon can occur if nothing is done to prevent it.

Nevertheless, preventing this anomaly is fairly simple. All you need to do is use a higher isolation level like Repeatable Read (which is the default in MySQL) or Serializable. Or, you can simply lock the database record using a share(read) lock or an exclusive lock if the underlying database does not support shared locks (e.g. Oracle).

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

2 thoughts on “A beginner’s guide to Non-Repeatable Read anomaly

  1. Hi Vlad. In MySQL Repeatable Read isolation level if i execute a DELETE statement with WHERE clause having single condition on non-indexed column, Innodb locks the whole table, I believe it applies next-key locks on all the records in Clustered Index. What I can’t understand is why in in this particular case the whole the table is being locked, what Phenomena does Innodb try to prevent ?
    For more info please check my question here: https://stackoverflow.com/questions/53452571/why-does-mysql-lock-the-table-in-repeatable-read-isolation-level-upon-executing

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.