0

I understand that nonrepeatable read problem occurs when a transaction t1 retrives a row, transaction t2 then updates that row and transaction t1 then retrieves the same row again. T1 retrieved the same row, but saw two different values. I wonder why a transaction read the same row again. Why it did not use the old value? If it retrieve again to get updated value, getting different values should not be problem. Can you please explain me? When repeatable read isolation level should be used?

0

I wonder why a transaction read the same row again. Why it did not use the old value? If it retrieve again to get updated value, getting different values should not be problem.

My guess is that the developer simply didn't consider the possibility of concurrent updates and the complications around that.

When repeatable read isolation level should be used?

Assuming T1 is a explicit transaction (i.e. it has Begin Tran, Commit Tran), I believe running T1 under repeatable read isolation level would protect you from this particular problem. However it may cause blocking or deadlocks more than is necessary.

If the one problematic row is the only possible problem, then it may be sufficient to select it just once using the WITH (UPDLOCK) hint, which will prevent other transactions modifying it until T1 completes. However other transactions will still be able to read the row (which may then be updated by T1), unless they also use WITH (UPDLOCK).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.