Dirty Reads and Phantom Reads

SQL Tips

Add a Unique Constraint to a Column



Dirty Reads and Phantom Reads


Recursive SQL

ROW_NUMBER() - Automatic Paging


SQL Default Date Format



Sponsored Links

Dirty Reads and Phantom Reads

Here we look at what dirty reads and phantom reads are.

Dirty Reads

Dirty Reads are where if you are selecting some data, depending on the transaction isolation level set, you may be able to read data which has not been committed from another transaction. So for instance, (if your isolation allows it,) if you are reading the top 10 prices for books, and person B has is running a transaction where person B sets the name of one of those books to something else and hasn't committed their transaction, the dirty read is reading the new name for that book, even though the transction hasn't been completed. So once you've done your read, person B may in fact ROLLBACK their transcation, reversing the UPDATE they performed, and setting that book to it's previous name. However, because you allowed dirty reads, and read person B's update for the book name, you have now read the new book name, even though the database has reversed it back to it's previous value, so you could be looking at invalid data (depending on whether you only wanted to read committed data)

You can avoid dirty reads by using an ISOLATION level other than READ UNCOMMITTED, such as READ COMMITTED. READ COMMITTED means that you only read committed data, whereas READ UNCOMMITTED allows you to read uncommitted data, and therefore allows you to make Dirty Reads.

So a Dirty Read is reading data which has not been committed.

Phantom Reads

Phantom Reads are where you could be doing a SELECT on some data in your own transaction, and then person B changes some of the data you're looking at, say changes that book name again. Only this time they do COMMIT their changes to the data, so it has become 'valid' data. The problem here is that the data in your transaction has changed, so if you read your list of books at the start of your transction, and then person B makes an UPDATE or INSERT at the data your looking at and COMMITs this, then should you re read the data you were looking at a second time, the second read will have different results from the first read.

Phantom Reads are not fully protected against using the REPEATABLE READ isolation level. REPEATABLE READ puts shared locks on the data you're reading to prevent other transactions from making UPDATEs on your read data, however it doesn't stop the INSERTs based on the search criteria in your commands.

You can protect against Phantom reads by using the SNAPSHOT or SERIALIZABLE isolation levels. SNAPSHOT works by taking a 'photo snapshot' of the data, ie it caches the data, such that if you need to re-read the data, it taked it from the cache, or your personal version. Other transctions can still run their transactions, but their changes are not visible to yours. SERIALIZABLE is the strongest isolation level, and locks out any other transactions from making UPDATEs or INSERTs at the data you are reading from.

So a Phantom Read is reading data which has changed, from the first time you read the data to the next time you read the data, and has been changed by someone else, the phantom slipped in to your data, and also phantom reads are from data which is committed, unlike dirty reads which are just basically reading uncommitted data.