How to avoid dirty read?

  • Dear all,

    I have a table which is updated and queried frequently, about 50 updates and 10 reads per second.

    I used below command to update the data.

    delete from table1 where columna = @columna

    insert into table1(columna,...) values(@columna,...)

    My issue is when the table is queried sometimes the result will be without a row with columna = @columna because it is queried just after the delete happens.

    Do you think change "delete & insert" way to "update" can fix my issue? Or, I need to add a transaction to the "delete & insert"? But I am afraid of the blocks of the queries.

    Thanks for your help in advance.

    Thanks,

    coby

  • you'll need to begin trans and commit trans around the statements.

    if you're worried about the locking use read uncommited in your select statements

    MVDBA

  • Just a note:

    1 "write" equals about 8 "read"s  in terms of consumed resourses.

    So, your approach ir really wrong for such a busy system like yours.

    You must choose UPDATE + INSERT ... WHERE NOT EXISTS, and inside UPDATE you must check that the new values are different from existing ones and update only those which actually are changed.

    _____________
    Code for TallyGenerator

  • Thanks a lot.

    I will change "delete + insert" to "update + insert if not exists". The begin trans and commit trans are still needed, right?

    Another question is:

    Is it right that below command A will get better performance than command B?

    A.

    if exists( select columna from tablea where columna = @columna )

    begin

           update tablea set columnb = @columnb where columna = @columna

    end

    B.

    update tablea set columnb = @columnb where columna = @columna

    Thanks,

    coby

  • 1. No need fr transaction.

    Imagine scenario when 2 processes try to do the same save interrupting each other:

    1st runs UPDATE, 2nd runs UPDATE,

    1st runs INSERT WHERE NOT EXIST, 2nd runs INSERT WHERE NOT EXIST

    Where do you see the chance for wrong data to end up in database?

    2. Compare execution plans for both commands.

    Both commands will not update any row if there is no match;

    Both will do te same UPDATE if there is match, but command A will do it in 2 steps.

    BTW. You don't need update anything if new value is already there:

    update tablea

    set columnb = @columnb

    where columna = @columna and columnb <> @columnb

    If columnb is nullable you need to change the query to handle the case of NULLs.

    _____________
    Code for TallyGenerator

  • 1. I was told that update itself is "delete + insert" actually but I think you are right because update is atomic itself and will not provide wrong data to the other select process.

     

    2. Both commands are doing the same thing. So, will command A's performance a bit bad because it needs 2 steps if there is an update needed?

     

    Thanks,

    coby

  • 1. Generally speaking yes. But actually UPDATE will be equal to DELETE + INSERT in the worst possible case.

    Don't forget about single record to LOG file instead of 2.

    Also, if you delare transaction you must be sure this code not gonna be used inside another transaction.

    2. Right.

    _____________
    Code for TallyGenerator

  • Sergiy, Michael,

    I appreciate your answers.

    Thanks,

    coby

Viewing 8 posts - 1 through 7 (of 7 total)

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