In MS SQL Server, is there a way to "atomically" increment a column being used as a counter?
- by Dan P
Assuming a Read Committed Snapshot transaction isolation setting, is the following statement "atomic" in the sense that you won't ever "lose" a concurrent increment?
update mytable set counter = counter + 1
I would assume that in the general case, where this update statement is part of a larger transaction, that it wouldn't be. For example, I think this scenario is possible:
update the counter within transaction #1
do some other stuff
in transaction #1
update the counter
with transaction #2
commit
transaction #2
commit transaction #1
In this situation, wouldn't the counter end up only being incremented by 1? Does it make a difference if that is the only statement in a transaction?
How does a site like stackoverflow handle this for its question view counter? Or is the possibility of "losing" some increments just considered acceptable?
