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

I want to do the following in one transaction:

• read a column
• if the column value m开发者_运维技巧atches 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?

---

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.

---

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)