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

Question

I want to do the following in one transaction:

• read a column
• if the column value matches a certain condition, write out a new value to the column

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?

Answer 1

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.