Home > DB Concepts, Differences > DIRTY reads and PHANTOM reads

DIRTY reads and PHANTOM reads


DIRTY READS: Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction, thus getting you incorrect or wrong data.

This happens at READ UNCOMMITTED transaction isolation level, the lowest level. Here transactions running do not issue SHARED locks to prevent other transactions from modifying data read by the current transaction. This also do not prevent from reading rows that have been modified but not yet committed by other transactions.

To prevent Dirty Reads, READ COMMITTED or SNAPSHOT isolation level should be used.

PHANTOM READS: Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.

In REPEATABLE READ isolation levels Shared locks are acquired. This prevents data modification when other transaction is reading the rows and also prevents data read when other transaction are modifying the rows. But this does not stop INSERT operation which can add records to a table getting modified or read on another transaction. This leads to PHANTOM reads.

PHANTOM reads can be prevented by using SERIALIZABLE isolation level, the highest level. This level acquires RANGE locks thus preventing READ, Modification and INSERT operation on other transaction until the first transaction gets completed.

MSDN References: http://msdn.microsoft.com/en-us/library/ms173763.aspx

About these ads
  1. saurabhsharma313
    July 20, 2011 at 11:44 am | #1

    Thanks ManuB22

  2. Yunus
    May 5, 2012 at 4:54 pm | #2

    thanks

  3. December 29, 2012 at 4:50 pm | #3

    good answer !

  4. mahesh
    August 6, 2013 at 11:43 am | #4

    This is not correct answers.

  5. November 2, 2013 at 9:21 am | #5

    thanks manoj ,your are telling simple words ,so everybody will understand,thanks once again……………………

  6. November 19, 2013 at 9:23 pm | #6

    Good explanation. I was having trouble explaining the difference between dirty and phantom reads.

  7. November 19, 2013 at 9:26 pm | #7

    You might need to update this with newer isolation methods such as snapshot isolation.

    • November 20, 2013 at 10:15 am | #8

      thanks @Eric for your comments.
      Sure I’ll update or put up a new post regarding this.

      ~manoj

  8. sulabh
    January 31, 2014 at 8:55 am | #9

    thanks manoj for your valuable guidelines on this topic…

  1. July 20, 2011 at 11:31 am | #1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 379 other followers

%d bloggers like this: