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

Предполагая, что параметр изоляции транзакции Read Committed Snapshot является следующим утверждением "атомарным" в том смысле, что вы никогда не "потеряете" параллельное приращение?

update mytable set counter = counter + 1

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

обновить счетчик в транзакции # 1
выполните некоторые другие действия в транзакции № 1
обновить счетчик с транзакцией № 2
совершить транзакция № 2
совершить транзакцию # 1

В этой ситуации не будет ли счетчик только увеличиваться на 1? Это имеет значение, если это единственное утверждение в транзакции?

Как сайт, подобный stackoverflow, обрабатывает это для своего счетчика вопросов? Или есть возможность "потерять" некоторые приращения, которые считаются приемлемыми?

Ответ 1

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

Однако в t1 и t2 вы обновляете данные, что является другим сценарием.

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

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

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

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

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: Как указывает Justice, если t2 является вложенной транзакцией в t1, семантика отличается. Опять же, оба будут корректно обновлять счетчик (+2), потому что с точки t2 внутри t1 счетчик уже обновлялся один раз. Вложенный t2 не имеет доступа к тому, что счетчик был до того, как t1 обновил его.

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

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

Ответ 2

В соответствии с помощью справки MSSQL вы можете сделать это следующим образом:

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

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

Ответ 3

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

Используйте уровень Serializable или используйте что-то вроде

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

Ответ 4

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

Счетчик будет увеличен на два. Ниже приведена одна строка со значением (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

Ответ 5

Я использовал этот 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