I'm a bit confused with the READ COMMITED isolation level. If a transaction X (starts) reads a row, changes the row and reads the row back again, is this considered a dirty read for the specific isolation level or not?
Asked
Active
Viewed 175 times
2 Answers
0
A transaction can always read changes it has made, regardless of its transaction isolation level. That's not a dirty read.
READ COMMITTED means your transaction will see the most recent committed change made by other transactions.
READ UNCOMMITTED means your transaction can see changes made in other transactions, even if they haven't committed yet. That's a dirty read. This is really not likely to be what you want.
Re your comment.
Say I have an app for a bookstore. Someone tries to buy 100 copies of the latest Dan Brown book. First my app updates the stock for the book to zero, and then tries to validate their credit card. Their credit card doesn't have enough credit to pay for 100 copies of the book, so it is rejected. Then the transaction rolls back, restoring the original number of books in stock.
Meanwhile, your app is monitoring stock levels to see when to re-order books. If you use READ UNCOMMITTED, you could easily see the stock reduced to zero. That is, after my app updates but before it rolls back. Your app is very efficient, and it immediately sends a new order to the publisher for a new box of books.
A few days later you receive the extra box of books, but you still have the old stock, so you have no space on the bookshelf and the extra box is left in the aisle until someone trips over it and hurts themselves. :-(
If you had only used READ COMMITTED, you wouldn't be responsible for someone's twisted ankle.
Bill Karwin
- 538,548
- 86
- 673
- 828
-
My typo. I mean read *commited* data. Starting a transaction, reading a row, writing a row and reading it back *before* committing is essentially reading uncommited data. Otherwise why read the row back? Just assume that the data we wrote exist – Jim Oct 14 '13 at 21:22
-
See the first sentence in my answer above. – Bill Karwin Oct 14 '13 at 21:27
0
READ COMMITTED isolation level can prevent dirty read on MySQL.
This table below shows which problems occur in each isolation level on MySQL according to my experiments. *Yes means Occurs and No means Doesn't occur:
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Yes | Yes | Yes |
| READ COMMITTED | No | Yes | Yes |
| REPEATABLE READ | No | No | *No (Basically) |
| SERIALIZABLE | No | No | No |
*Phantom read basically doesn't occur in REPEATABLE READ but in some complicated case, it occurs. You can check How to produce "phantom read" in REPEATABLE READ? (MySQL).
Super Kai - Kazuya Ito
- 22,221
- 10
- 124
- 129