SERIES: Concurrent Transaction Phenomena
In Books Online (BOL), Microsoft describes different kinds of transaction isolation levels in terms of phenomena that can occur during concurrent transactions. Specifically, they mention three kinds of phenomena: Dirty Reads, Non-Repeatable Reads, and Phantom Reads. You may have heard of these before, but correct me if I’m wrong, I just can’t find a good definition anywhere on BOL.
And that’s too bad, because these phenomena don’t just help describe the different transaction isolation levels. The phenomena actually define these levels.
These terms actually come from the ISO/ANSI standard for SQL-92. The terms themselves however do little to illuminate exactly the meaning of each. (What’s a phantom read?) When used by the ANSI/ISO standard, they mean something very specific and they’re key to understanding the different isolation levels.
In the next few days, I’d like to illustrate each phenomenon:
- Part 1: The Dirty Read (reading tentative data)
- Part 2: The Non-Repeatable Read (reading changed data)
- Part 3: The Phantom Read (reading new data)
- Part 4: Serializable vs. Snapshot
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.
Changing Data
I like to think of Non-Repeatable Reads as being about reading data that has changed.
Changing Data
(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.
My experience
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.
Hey! What do people think of my cartooning skills?
Comment by Michael J. Swart — March 25, 2010 @ 10:44 am
Boooooooo!!
Comment by Cressa — March 25, 2010 @ 11:09 am
I do not see pun. ( is it the data changing thing? cause that’s awesome )
My only boo here is the dreadful sql example. If you write sql like this… i’m disappointed. I’m hoping that in 30 seconds you could come up with the right answer, that wouldn’t be affected by the COMMITTED level.
( though I do see the problem when a modify operation requires that more than one table requires updates… )
Comment by Richard — March 25, 2010 @ 12:01 pm
Yeah, the data changing thing is the pun.
No, I don’t write SQL like this. But I have a hard time trying to come up with a better example. You’re right, 30s would be all it takes. My last sentence mentions that “often I find it’s possible to rewrite a transaction”.
Your scenario of updating more than one table would have made for a better example. Live and learn.
Comment by Michael J. Swart — March 25, 2010 @ 12:09 pm
Richard, its specifically a visual pun.
Comment by David Swart — March 25, 2010 @ 5:02 pm
Hi Michael,
Its excellent example i have ever seen. It is really helpful for me.
Keep it up.
Excellent work.
Comment by Ramkrishna — April 23, 2010 @ 11:09 am
I really like your description and example of code that would cause this phenomena. I think it would also be helpful to discuss *why* the code causes this issue and how you could re-write the code to avoid the issue. I can see why the code causes problems, but there are those who are new enough to SQL that they might not understand it. Providing an analysis of the bad code and a possible solution would be helpful to people new to these ideas.
Thanks,
-dougl
Comment by Doug Ledbetter — August 16, 2011 @ 9:54 am
Thanks for visiting Doug!
Sounds like a great idea for a follow up post! I’ll keep it in mind if I ever run out of blog ideas. 🙂
Comment by Michael J. Swart — August 16, 2011 @ 10:17 am
Why is non-repeatable read an issue in database? I think it’s actually a good thing
Comment by Emily — November 16, 2011 @ 1:57 am
Hi Emily,
Non-Repeatable reads are not always a bad thing. In fact they’re often harmless or even required sometimes.
It all depends on the situation. If you absolutely require an Atomic transaction, then the non-repeatable read would be a problem.
But you’re right most often a query doesn’t need that level of isolation.
Comment by Michael J. Swart — November 16, 2011 @ 8:13 am
Thanks for clarifying that, Michael!
Comment by Emily — November 16, 2011 @ 7:45 pm