Post reply

Can I have dirty reads caused by the time the cache buffer needs to write to disk?

  • July 23, 2008 at 1:00 pm

    #125024

    I know SQL Server does not write to disk inmediately modifications on the data. First the buffer is updated. Then those changes are written to the disk but there´s some time where you have your modifications on the buffer and not on the disk.

    My questions are:

    1. Is it possible to have dirty reads because of that? Maybe I update something, it is done on the buffer, query that value and as it is brought from the disk it will be different.

    2. a. If question one´s answer is "no": Why?

    b. If question one´s answer is "yes": How can I avoid those dirty reads.

    Thanks.

  • July 23, 2008 at 1:37 pm

    #847347

    The changes are written to the log, so they're on disk, just not in the data file.

    If you request a page that's been changed in memory, but not on disk, it's pulled from memory.

    A dirty read is something that's changed in a transaction, but not committed to the transaction log. So it could change back if there's a rollback.

  • July 23, 2008 at 1:44 pm

    #847355

    Thanks so much for your fast answer.

    Please let me rephrase to ensure I have understood correctly.

    When you query some value that has not written to disk because it is still on the buffer it will be searched in the buffer, so both values - the updated and the queryed - are the same. Is that correct?

    I am pretty sure I have seen this behavior on my SQL. I mean, update a value, query it and have the old value for a few seconds, and then without doing nothing else getting finally the new correct value. Can you imagine some reason for this behaviour?

    Thank you very much.

  • July 23, 2008 at 1:56 pm

    #847363

    spamhdp (7/23/2008)

    I am pretty sure I have seen this behavior on my SQL. I mean, update a value, query it and have the old value for a few seconds, and then without doing nothing else getting finally the new correct value. Can you imagine some reason for this behaviour?

    Thank you very much.

    You should never get the old value once an update statement is sent in. A dirty read will show you the new value if you run a select with the nolock hint and then the old value if the transaction is rolled back. So if you run this code in adventureworks:

    [font="Courier New"]BEGIN TRANSACTION

    UPDATE Production.Product

        SET Name = 'Third Day'

    WHERE

        ProductID = 1

    WAITFOR Delay '00:00:10'

    ROLLBACK TRANSACTION[/font]

    And this code in another session:

    Select name from Production.Product with (nolock) Where PRoductID = 1

    The first time you run the select you will get Third Day (New Value) returned and then after the transaction rollbacks in 10 seconds you will get the original value.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 24, 2008 at 8:04 am

    #847780

    Thank you very much for your help !

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply