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

Question

Assuming the Read Committed Snapshot transaction isolation parameter is the next “atomic” statement in the sense that you will never “lose” the parallel increment?

update mytable set counter = counter + 1 

I would suggest that in the general case, when this update statement is part of a larger transaction, this would not be the case. For example, I think this scenario is possible:

• update counter in transaction # 1
• follow some other steps in transaction no. 1
• update the counter with transaction No. 2
• complete transaction number 2
• complete transaction # 1

In this situation, will the counter only increase by 1? Does it matter if this is the only statement in the transaction?

How does a site like stackoverflow handle this for its question counter? Or is it possible to “lose” some of the increments that are considered acceptable?

Answer 1

Read Committed Snapshot uses only locks when selecting data from tables.

However, in t1 and t2 you are updating the data, which is another scenario.

When you UPDATE the counter, you go to write lock (in line), preventing another update from appearing. t2 can read, but t2 will block it UPDATE until t1 is executed, and t2 cannot commit to t1 (which contradicts your timeline). Only one of the transactions will receive a counter update, so both will correctly update the counter, taking into account the code presented. (Verified)

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

---

Read Committed simply means that you can only read fixed values, but that does not mean that you have repeated reads. Thus, if you use and depend on a counter variable and intend to update it later, you may be performing transactions at the wrong isolation level.

You can use a repeatable read lock, or if you only occasionally update the counter, you can do it yourself using optimistic locking technology. for example, a timestamp column with a counter table or 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 features while they are still in beta, so it may not be entirely accurate.

---

update: As Justice indicates, if t2 is a nested transaction in t1, the semantics are different. Again, both will correctly update the counter (+2), because from the point t2 inside t1 the counter has already been updated once. The nested t2 does not have access to what the 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)

In a nested transaction, if t1 returns a ROLLBACK after t1 COMMIT, the counter returns its original value, since it also cancels the commit t2.

Answer 2

According to the MSSQL help, you can 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 set of SQL records.

Answer 3

No no. The value is read in shared mode and then updated in exclusive mode, so multiple reads may occur.

Use a Serializable level or use something like

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

Answer 4

There is only one transaction, the most external. Internal transactions are more like breakpoints within a transaction. Isolation levels only affect external transactions unrelated to parents and subsidiaries.

The counter will be increased by two. Below is one line with a value (Num = 3). (I opened SMSS and pointed it to a local instance of SQL Server 2008 Express. I have a database called Playground for testing.)

 use Playground drop table C create table C ( Num int not null) insert into C (Num) values (1) begin tran X update C set Num = Num + 1 begin tran Y update C set Num = Num + 1 commit tran Y commit tran X select * from C 

Answer 5

I used this SP to handle the case when the name does not have a counter initially

 ALTER PROCEDURE [dbo].[GetNext]( @name varchar(50) ) AS BEGIN SET NOCOUNT ON DECLARE @Out TABLE(Id BIGINT) MERGE TOP (1) dbo.Counter as Target USING (SELECT 1 as C, @name as name) as Source ON Target.name = Source.Name WHEN MATCHED THEN UPDATE SET Target.[current] = Target.[current] + 1 WHEN NOT MATCHED THEN INSERT (name, [current]) VALUES (@name, 1) OUTPUT INSERTED.[current]; END