[ACCEPTED]-In MS SQL Server, is there a way to "atomically" increment a column being used as a counter?-transactions

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

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

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

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

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

When you UPDATE 33 the counter you escalate to a write lock 32 (on the row), preventing the other update 31 from occurring. t2 could read, but t2 will 30 block on its UPDATE until t1 is done, and 29 t2 won't be able to commit before t1 (which 28 is contrary to your timeline). Only one 27 of the transactions will get to update the 26 counter, therefore both will update the 25 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 24 Committed just means you can only read committed 23 values, but it doesn't mean you have Repeatable 22 Reads. Thus, if you use and depend on the 21 counter variable, and intend to update it 20 later, you're might be running the transactions 19 at the wrong isolation level.

You can either 18 use a repeatable read lock, or if you only 17 sometimes will update the counter, you can 16 do it yourself using an optimistic locking 15 technique. e.g. a timestamp column with 14 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 13 devx article is informative, although it talks 12 about the features while they were still 11 in beta, so it may not be completely accurate.

update: As 10 Justice indicates, if t2 is a nested transaction 9 in t1, the semantics are different. Again, both 8 would update counter correctly (+2) because 7 from t2's perspective inside t1, counter 6 was already updated once. The nested t2 5 has no access to what counter was before 4 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 3 t1 issues ROLLBACK after t1 COMMIT, counter 2 returns to it's original value because it 1 also undoes t2's commit.

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

Either use Serializable 1 level or use something like

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

There is at heart only one transaction, the 10 outermost one. The inner transactions are 9 more like checkpoints within a transaction. Isolation 8 levels affect only sibling outermost transactions, not 7 parent/child related transactions.

The counter 6 will be incremented by two. The following 5 yields one row with a value of (Num = 3). (I 4 opened up SMSS and pointed it to a local 3 SQL Server 2008 Express instance. I have 2 a database named Playground for testing 1 stuff.)

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

I used this SP to handle the case where 1 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