T-SQL isolation when multiple update

Question

I need to update a few entries with different values and make 1 query per update.

Before I do, I check every initial values to make sure the entry can be updated.

I'm not even sure this could happen but I would like to avoid having those entries changed by another query just after I make my check.

Here is an example of what I want to avoid

1) query1 checks initial value

2) query2 checks initial value

3) query1 applies update

4) query2 applies update

I heard about the isolation level but could not figure out how to use it to avoid the problem, could you help me ?

Thank in advance for your help

score 1 · Answer 1 · answered Apr 16 '13 at 19:23

I wonder why You do not just use atomic UPDATE to do necessary check in WHERE part and then make update in order to that?

Anyway if You need to use Select and then Update: I refer You to very good article: http://samsaffron.com/blog/archive/2007/04/04/14.aspx

Transaction is necessary to avoid dirty reads etc. But another thing You should pay attention is DEADLOCK. In distributed approach, if You do not use UPDLOCK (which lock resources for further updates) You can update different data that You read.

score 0 · Answer 2 · answered Apr 16 '13 at 17:52

Use transactions.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;

--check stuff

--update stuff

COMMIT TRANSACTION;

REPEATABLE READ will make sure that everything you read (select) will remain the same until your code reaches commit transaction. No one can make changes to records you have read. More info about transactions and isolation level here: SET TRANSACTION ISOLATION LEVEL

T-SQL isolation when multiple update

2 Answers2