CODEWITHSUNDEEP
sqlsql-servertransactions
Jacko
Jacko

Reputation: 13195

Newbie sql transaction question: preventing race in read - compute -write

I want to do the following in one transaction:

transaction isolation is set to read-commited, server is SQL server.

How can I guarantee that another transaction does not write a different value to the column after I read it? Will the server reject my write if another transaction changed the column?

In other words, can SQL server be used as a distributed lock on a given column?

Upvotes: 5

Views: 168

Answers (2)

a1ex07
a1ex07

Reputation: 37374

In this case you need to use REPEATABLE READ isolation level. With READ COMMITTED another transaction can change your record.
If you can rewrite your logic in 1 query(for example, with update or merge), you can still use READ COMMITTED. But sometimes it's not the option. For instance,

SELECT ... ;
IF some_condition
BEGIN
  // execute a procedure, select from other tables, etc
END
ELSE
BEGIN
  // execute another procedure, do some other stuff
END;
// finally update the record
UPDATE ....

Update

There is another option I forgot to mention : use table hint REPEATABLEREAD in your SELECT statement (See for details)

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

Who said you have to read it first?

UPDATE yourtable
SET    yourcolumn = CASE
                      WHEN certaincondition = 1 THEN 'newvalue'
                      ELSE yourcolumn
                    END
WHERE  id = 'yourid'

You evaluate inside the UPDATE itself. It's guaranteed to be totally isolated. You can have multiple of this same UPDATE running from different instances, the transactions will be queued and processed one by one.

Upvotes: 4

Related Questions