Part 2: The Non-Repeatable Read
The non-repeatable read occurs when a transaction re-reads a single record and finds that it has been changed or deleted.
From ISO/ANSI: Process P1 reads a row. Process P2 then modifies or deletes that rows and commits the change. If P1 rereads the row it receives the modified value or discovers the row has been deleted.
I like to think of Non-Repeatable Reads as being about reading data that has changed.
(I apologize in advance to my friends at work whose reaction I know will be “Boo”.)
But Non-Repeatable reads are not always a bad thing. In fact they’re often harmless or even required. When data has changed, you have to decide whether you want consistent data or whether you want current data. If you need to rely on consistent data, then a non-repeatable read is detrimental. Here is an example of a non-repeatable read that causes problems:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION IF EXISTS( SELECT 1 FROM Theatre.AvailableSeats WHERE seat = 'B23') BEGIN -- imagine a concurrent transaction -- sells seat B23 here and removes the record -- from table Theatre.AvailableSeats -- then no rows are returned here: SELECT price FROM Theatre.AvailableSeats WHERE seat = 'B23' -- and zero rows are affected here: DELETE FROM Theatre.AvailableSeats WHERE seat = 'B23' END COMMIT
Isolation Levels that Experience Non-Repeatable Reads
Just like dirty reads, this phenomenon is possible when using the READ UNCOMMITTED isolation level, but it is also possible when using the READ COMMITTED isolation level. And in SQL Server READ COMMITTED is the default isolation level for transactions.
To avoid this phenomena, you could use isolation level REPEATABLE READ. But often I find it’s possible to rewrite a transaction and stick with the default READ COMMITTED.