Take the 2-minute tour ×

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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
  // execute a procedure, select from other tables, etc
  // execute another procedure, do some other stuff
// finally update the record


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

share|improve this answer
add comment

Who said you have to read it first?

UPDATE yourtable
SET    yourcolumn = CASE
                      WHEN certaincondition = 1 THEN 'newvalue'
                      ELSE yourcolumn
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.

share|improve this answer
add comment

Your Answer


