How to avoid dirty read?

Question

Post reply

How to avoid dirty read?

coby_cai

SSC-Addicted

Points: 439
More actions
October 28, 2006 at 2:54 am

#113990

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

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

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

Sergiy SSC Guru Points: 110208 More actions · Answer 2

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

coby_cai SSC-Addicted Points: 439 More actions · Answer 3

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

Sergiy SSC Guru Points: 110208 More actions · Answer 4

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

coby_cai SSC-Addicted Points: 439 More actions · Answer 5

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

Sergiy SSC Guru Points: 110208 More actions · Answer 6

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

coby_cai SSC-Addicted Points: 439 More actions · Answer 7

Sergiy, Michael,

I appreciate your answers.

Thanks,

coby