Опасно ли выполнять такой код в С# + T-SQL? Или каким способом можно написать по-другому?

Question

[Борис Животное]

Есть 3 таблицы. Основная SOME_GROUP и две дочерние, что видно из столбцов GROUP_ID.

У всех трёх таблиц первичный ключ:
CONSTRAINT PK_BLA_BLA PRIMARY KEY CLUSTERED (ID)

Так же у дочерних таблиц есть внешние ключи (GROUP_ID), которые указывают на ID в таблице SOME_GROUP.

Эти строки в запросе приведены для удобства и понимания, что как выполняется.

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED -- REPEATABLE READ

В реальности же из C# кода создаётся транзакция и вызывается получение данных для 3-ёх таблиц. То есть к MSSQL серверу отправляется 3 запроса по очереди в одной транзакции.

Вопрос: Есть ли вероятность, что на одном из запросов строка...

SELECT TOP(@Quantity) ID FROM SOME_GROUP WITH(UPDLOCK) WHERE REPLICATED <> 1

...вернёт другой результат? По идее строки отсортированы по первичному ключу. Если другая транзакция залочит одну из необходимых записей, то мой даже первый запрос будет ждать, пока записи разлочатся, а далее сразу же залочит все записи, которые будут выбраны и в пределах одной транзакции все 3 запроса выполнятся и все 3 раза получение SELECT TOP(@Quantity) вернёт один и тот же результат. Верно?

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED -- REPEATABLE READ

SET DEADLOCK_PRIORITY LOW;
DECLARE @Quantity INT = 5;

-- Первый запрос, вызываемый из C# кода в одной транзакции.
-- 1 ------------------------------------------------------------------
UPDATE SOME_GROUP SET REPLICATED = 2 
OUTPUT 
  INSERTED.ID
 ,INSERTED.NAME
WHERE ID IN(SELECT TOP(@Quantity) ID FROM SOME_GROUP WITH(UPDLOCK) WHERE REPLICATED <> 1)

-- Второй запрос, вызываемый из C# кода в одной транзакции.
-- 2------------------------------------------------------------------
UPDATE SOME_CHILD_ONE SET REPLICATED = 2 
OUTPUT 
  INSERTED.ID
 ,INSERTED.GROUP_ID
 ,INSERTED.UPDATED
WHERE GROUP_ID IN(SELECT TOP(@Quantity) ID FROM SOME_GROUP WITH(UPDLOCK) WHERE REPLICATED <> 1) AND REPLICATED <> 1

-- Третий запрос, вызываемый из C# кода в одной транзакции.
-- 3 ------------------------------------------------------------------
UPDATE SOME_CHILD_TWO SET REPLICATED = 2 
OUTPUT 
  INSERTED.ID
 ,INSERTED.GROUP_ID
 ,INSERTED.NAME
 ,INSERTED.CREATED
WHERE GROUP_ID IN(SELECT TOP(@Quantity) ID FROM SOME_GROUP WITH(UPDLOCK) WHERE REPLICATED <> 1) AND REPLICATED <> 1
-- 3 ------------------------------------------------------------------

COMMIT TRANSACTION

Answer 1

[Eduard]

Ваш код не будет работать всегда предсказуемым образом. Основная проблема в том, что SELECT TOP без ORDER BY не гарантирует ни в коем случае одни и те же данные. Да, в вашем случае они скорее всего приходят в порядке из сохранения в кластерном индексе. Но если изменится индекс или добавится новый, то ваш код может перестать работать как ожидается.
Другая проблема в том, что несмотря на то, что подзапросы в WHERE одинаковые, они могут возвращать разные данные из-за первого UPDATE. Если у вас было 10 записей с REPLICATED =-10 (минус!), то до первого обновления подзапрос выдаст записи с 1 по 5, а после него с 6 по 10.
Совет: сохраните обновляемые ID в отдельную временную таблицу и присоединяйте ее для обновления.
Либо настройте внешние ключи и каскадное обновление.

Comments

[Борис Животное]

"Если у вас было 10 записей с REPLICATED =-10 (минус!), то до первого обновления подзапрос выдаст записи с 1 по 5, а после него с 6 по 10."
Это по какой причине? Как вообще значение REPLICATED влияет на возвращаемый запросом результат?

[Борис Животное]

Кстати, столбец REPLCATED не может быть меньше 0 - REPLICATED TINYINT NOT NULL

[Борис Животное]

Мда... Сделал с временной таблицей. В ms sql management studio всё работает в пределах одной транзакции. В C# коде нет. Пишет при вызове второго запроса: Invalid object name '#GroupIDs'

[Борис Животное]

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

[Eduard]

> Это по какой причине? Как вообще значение REPLICATED влияет на возвращаемый запросом результат?
Борис Животное: а вы сделайте индекс по REPLICATED, и тогда данные могут выбираться из этого индекса. А поскольку вы его обновили первым апдейтом, то повторная выборка вернёт уже другие данные.
Вы конечно можете сказать, что никто не будет добавлять такой индекс. Но как вы сможете это гарантировать? В общем случае бизнес-логика не должна зависеть от наличия отсутствия индексов.
Насчет вашего решения ниже: вы правильно сделали, что сначала сохранили обновляемые ID, но для этой задачи вам не нужны глобальные временные таблицы, достаточно было бы и обычных - локальных

[Eduard]

Борис Животное:
if object_id('some_group') is not null
drop table some_group

create table some_group
(
id int identity(1,1) primary key
, replicated tinyint not null
)
create index ix_some_group on some_group (replicated)

insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)
insert into some_group (replicated) values (0)

DECLARE @Quantity INT;
set @Quantity = 5

SELECT TOP(@Quantity) ID FROM SOME_GROUP WITH(UPDLOCK) WHERE REPLICATED <> 1

UPDATE SOME_GROUP SET REPLICATED = 2
WHERE ID IN(SELECT TOP(@Quantity) ID FROM SOME_GROUP WITH(UPDLOCK) WHERE REPLICATED <> 1)

SELECT TOP(@Quantity) ID FROM SOME_GROUP WITH(UPDLOCK) WHERE REPLICATED <> 1

[Борис Животное]

Eduard: А, понял. Ну вообще индекса у меня нет на столбце REPLICATED. Он там, как я понимаю, и не нужен. Да и в итоге я переделал. А как именно видно из моего ответа на свой же вопрос. Кстати, если есть проблемные места, был бы благодарен, если бы вы прокомментировали.

[Eduard]

Борис Животное: Нормальное решение, только вот, как я уже заметил, глобальные временные таблицы лучше заменить на обычные или табличную переменную. Можно ещё оформить всё это как процедуру и в коде вызывать ее. Но это уже дело вкуса и политики разработки.
Уберите лишнее из кода: первичный ключ на временной таблице, хинты на update.
Не хватает обработчика ошибок, который будет откатывать транзакцию. Предполагаю, что он есть в коде на C#, но тем не менее

Answer 2

[Борис Животное]

В итоге решил это дело так. На место {0} в C# коде подставляется сгенерированный GUID

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET DEADLOCK_PRIORITY LOW;
DECLARE @Quantity INT = 5;

IF OBJECT_ID('tempdb..##GroupIDs_{0}') IS NULL
BEGIN
	CREATE TABLE ##GroupIDs_{0} (
		ID INT NOT NULL,
		CONSTRAINT PK_GroupIDs_ID_{0} PRIMARY KEY(ID),
	);
END

INSERT INTO ##GroupIDs_{0} (ID) 
SELECT TOP(@Quantity) ID FROM SOME_GROUP WITH(UPDLOCK, READPAST) WHERE REPLICATED <> 1

-- Первый запрос, вызываемый из C# кода в одной транзакции.
-- 1 ------------------------------------------------------------------
UPDATE SOME_GROUP SET REPLICATED = 2 
OUTPUT 
  INSERTED.ID
 ,INSERTED.NAME
WHERE ID IN(SELECT ID FROM ##GroupIDs_{0})

-- Второй запрос, вызываемый из C# кода в одной транзакции.
-- 2------------------------------------------------------------------
UPDATE SOME_CHILD_ONE SET REPLICATED = 2 
OUTPUT 
  INSERTED.ID
 ,INSERTED.GROUP_ID
 ,INSERTED.UPDATED
WHERE GROUP_ID IN(SELECT ID FROM ##GroupIDs_{0}) AND REPLICATED <> 1

-- Третий запрос, вызываемый из C# кода в одной транзакции.
-- 3 ------------------------------------------------------------------
UPDATE SOME_CHILD_TWO SET REPLICATED = 2 
OUTPUT 
  INSERTED.ID
 ,INSERTED.GROUP_ID
 ,INSERTED.NAME
 ,INSERTED.CREATED
WHERE GROUP_ID IN(SELECT ID FROM ##GroupIDs_{0}) AND REPLICATED <> 1

DROP TABLE ##GroupIDs_{0};
-- 3 ------------------------------------------------------------------

COMMIT TRANSACTION