locked
What is dirty and phantom read RRS feed

  • Question

  • please help me to understand what is  dirty and phantom read with example. thanks
    Friday, October 28, 2016 9:58 AM

Answers

  • Dirty read is when you are reading uncommitted data.

    consider a large table - if you update a column on it like this

    update tablename set col1=col2 where col3=col4

    the values from col2 are being written to the column col1 for cases where col3 =col4.

    if you were to query this table using the no lock hint while this update is running you would see some values for col1 which have been updated, and some which have not yet being updated.

    This is called a dirty read as the update has not completed yet.

    A phantom read is when you read some data which is not there. For example you are reading data which has been deleted, or perhaps summing data where the rows which form part of the aggregate operation are newly inserted.

     

    • Marked as answer by Mou_kolkata Monday, October 31, 2016 9:45 AM
    Friday, October 28, 2016 11:12 AM

All replies

  • Dirty read is when you are reading uncommitted data.

    consider a large table - if you update a column on it like this

    update tablename set col1=col2 where col3=col4

    the values from col2 are being written to the column col1 for cases where col3 =col4.

    if you were to query this table using the no lock hint while this update is running you would see some values for col1 which have been updated, and some which have not yet being updated.

    This is called a dirty read as the update has not completed yet.

    A phantom read is when you read some data which is not there. For example you are reading data which has been deleted, or perhaps summing data where the rows which form part of the aggregate operation are newly inserted.

     

    • Marked as answer by Mou_kolkata Monday, October 31, 2016 9:45 AM
    Friday, October 28, 2016 11:12 AM
  • Hello,

    Please read the following article:

    https://technet.microsoft.com/en-us/library/ms190805(v=sql.105).aspx

    https://msdn.microsoft.com/en-us/library/cc546518.aspx (Examples here)

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Friday, October 28, 2016 12:46 PM
  • Hi Monu,

    Dirty read occurs when one transaction is changing the record, and the other transaction can read this record before the first transaction has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid data.

    Dirty Read Example:-

    Transaction A begins.
    UPDATE EMPLOYEE SET SALARY = 10000 WHERE EMP_ID= ‘123’;

    Transaction B begins.
    SELECT * FROM EMPLOYEE;
    (Transaction B sees data which is updated by transaction A. But, those updates have not yet been committed.)

    Phantom Read:-

    Phantom read occurs where in a transaction execute same query more than once, and the second transaction result set includes rows that were not visible in the first result set. This is caused by another transaction inserting new rows between the execution of the two queries. This is similar to a non-repeatable read, except that the number of rows is changed either by insertion or by deletion.

    Phantom Read Example:-

    Transaction A begins.
    SELECT * FROM EMPLOYEE WHERE SALARY > 10000 ;

    Transaction B begins.
    INSERT INTO EMPLOYEE (EMP_ID, FIRST_NAME, DEPT_ID, SALARY) VALUES (‘111′, ‘Jamie’, 10, 35000);
    Transaction B inserts a row that would satisfy the query in Transaction A if it were issued again.

    http://www.ongoinghelp.com/difference-between-dirty-read-non-repeatable-read-and-phantom-read-in-database/


    Please click Mark As Answer if my post helped.

    • Proposed as answer by Sudip_inn Saturday, November 5, 2016 7:33 PM
    Saturday, October 29, 2016 12:24 AM
  • phantom read example is not clear.

    Phantom Read Example:-

    Transaction A begins.
    SELECT * FROM EMPLOYEE WHERE SALARY > 10000 ;

    Transaction B begins.
    INSERT INTO EMPLOYEE (EMP_ID, FIRST_NAME, DEPT_ID, SALARY) VALUES (‘111′, ‘Jamie’, 10, 35000);
    Transaction B inserts a row that would satisfy the query in Transaction A if it were issued again.

    Monday, October 31, 2016 9:47 AM