In MS SQL Server, is there a way to "atomically" increment a column being used as a counter?

According to the MSSQL Help, you could do it like this:

UPDATE tablename SET counterfield = counterfield + 1 OUTPUT INSERTED.counterfield

This will update the field by one, and return the updated value as a SQL recordset.

Read Committed Snapshot only deals with locks on selecting data from tables.

In t1 and t2 however, you're UPDATEing the data, which is a different scenario.

When you UPDATE the counter you escalate to a write lock (on the row), preventing the other update from occurring. t2 could read, but t2 will block on its UPDATE until t1 is done, and t2 won't be able to commit before t1 (which is contrary to your timeline). Only one of the transactions will get to update the counter, therefore both will update the counter correctly given the code presented. (tested)

• counter = 0
• t1 update counter (counter => 1)
• t2 update counter (blocked)
• t1 commit (counter = 1)
• t2 unblocked (can now update counter) (counter => 2)
• t2 commit

---

Read Committed just means you can only read committed values, but it doesn't mean you have Repeatable Reads. Thus, if you use and depend on the counter variable, and intend to update it later, you're might be running the transactions at the wrong isolation level.

You can either use a repeatable read lock, or if you only sometimes will update the counter, you can do it yourself using an optimistic locking technique. e.g. a timestamp column with the counter table, or a conditional update.

DECLARE @CounterInitialValue INT
DECLARE @NewCounterValue INT
SELECT @CounterInitialValue = SELECT counter FROM MyTable WHERE MyID = 1234

-- do stuff with the counter value

UPDATE MyTable
   SET counter = counter + 1
WHERE
   MyID = 1234
   AND 
   counter = @CounterInitialValue -- prevents the update if counter changed.

-- the value of counter must not change in this scenario.
-- so we rollback if the update affected no rows
IF( @@ROWCOUNT = 0 )
    ROLLBACK

This devx article is informative, although it talks about the features while they were still in beta, so it may not be completely accurate.

---

update: As Justice indicates, if t2 is a nested transaction in t1, the semantics are different. Again, both would update counter correctly (+2) because from t2's perspective inside t1, counter was already updated once. The nested t2 has no access to what counter was before t1 updated it.

• counter = 0
• t1 update counter (counter => 1)
• t2 update counter (nested transaction) (counter => 2)
• t2 commit
• t1 commit (counter = 2)

With a nested transaction, if t1 issues ROLLBACK after t1 COMMIT, counter returns to it's original value because it also undoes t2's commit.

No, it's not. The value is read in shared mode and then updated in exclusive mode, so multiple reads can occur.

Either use Serializable level or use something like

update t
set counter = counter+1
from t with(updlock, <some other hints maybe>)
where foo = bar