Есть ли в MS SQL Server способ «атомарно» увеличить столбец, используемый в качестве счетчика?

Нет, это не так. Значение читается в совместно используемом режиме, а затем обновляется в монопольном режиме, поэтому может произойти несколько чтений.

Либо используйте Serializable level, либо используйте что-то вроде

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

Read Committed Snapshot имеет дело только с блокировками при выборе данных из таблиц.

Однако в t1 и t2 вы ОБНОВЛЯЕТЕ данные, что представляет собой другой сценарий.

Когда вы ОБНОВЛЯЕТЕ счетчик, вы переходите к блокировке записи (в строке), предотвращая выполнение другого обновления. t2 может читать, но t2 будет блокировать свое ОБНОВЛЕНИЕ до тех пор, пока t1 не будет выполнено, а t2 не сможет выполнить фиксацию до t1 (что противоречит вашей временной шкале). Только одна из транзакций сможет обновить счетчик, поэтому обе будут обновлять счетчик правильно с учетом представленного кода. (проверено)

• counter = 0
• счетчик обновлений t1 (counter => 1)
• счетчик обновлений t2 (заблокирован)
• t1 фиксация (счетчик = 1)
• t2 разблокирован (теперь может обновлять счетчик) (counter => 2)
• t2 совершить

Read Committed просто означает, что вы можете читать только зафиксированные значения, но это не значит, что у вас есть Repeatable Reads. Таким образом, если вы используете и зависите от переменной счетчика и намереваетесь обновить ее позже, возможно, вы выполняете транзакции с неправильным уровнем изоляции.

Вы можете либо использовать повторяемую блокировку чтения, либо, если вы будете обновлять счетчик только изредка, вы можете сделать это самостоятельно, используя технику оптимистической блокировки. например столбец отметки времени с таблицей счетчиков или условное обновление.

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

Эта статья devx является информативной, хотя в ней рассказывается о функциях, которые были еще на стадии бета-тестирования, поэтому она может быть не совсем точной.

update: Как указывает Джастис, если t2 является вложенной транзакцией в t1, семантика будет другой. Опять же, оба будут правильно обновлять счетчик (+2), потому что с точки зрения t2 внутри t1 счетчик уже был обновлен один раз. Вложенный t2 не имеет доступа к тому, какой счетчик был до того, как t1 обновил его.

• counter = 0
• счетчик обновлений t1 (counter => 1)
• счетчик обновлений t2 (вложенная транзакция) (counter => 2)
• t2 совершить
• t1 фиксация (счетчик = 2)

Во вложенной транзакции, если t1 выдает ROLLBACK после t1 COMMIT, счетчик возвращается к исходному значению, потому что он также отменяет фиксацию t2.

По сути, существует только одна транзакция, самая внешняя. Внутренние транзакции больше похожи на контрольные точки внутри транзакции. Уровни изоляции влияют только на самые близкие внешние транзакции, но не на транзакции, связанные с родительскими / дочерними элементами.

Счетчик увеличится на два. Следующее дает одну строку со значением (Num = 3). (Я открыл SMSS и указал на локальный экземпляр SQL Server 2008 Express. У меня есть база данных с именем Playground для тестирования.)

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

Согласно справке MSSQL, это можно сделать так:

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

Это обновит поле на единицу и вернет обновленное значение в виде набора записей SQL.

Я использовал этот SP для обработки случая, когда имя изначально не имеет счетчика

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