Как стать автором
Обновить

Комментарии 18

Призы можно сделать так:

UPDATE BonusPool 
SET RemainingCount -= 1
WHERE BonusID = @BonusID AND RemainingCount > 0

INSERT INTO UserBonus (UserID, BonusID) 
SELECT @UserID, @BonusID 
WHERE @@ROWCOUNT > 0

IF @@ROWCOUNT > 0
    RETURN 1 -- SUCCESS
RETURN 0 -- FAIL (resource exhausted)


UPDATE сам наложит атомарную блокировку, соответственно в соревновании за последюю единицу бонуса @@ROWCOUNT > 0 придет только для одного потока, он соответственно добавит запись в UserBonus, который в свою очередь отдаст @@ROWCOUNT > 0, говорящий о том, что этому потоку добавить бонус удалось
Да, так тоже можно. Но при использовании ORM такой подход не получится использовать, пожалуй (сделать RAW SQL запрос в начале транзакции — это еще приемлемо, а переводить всю транзакцию на RAW SQL — это уже перебор). К тому же у нас более сложная логика и запись о типе приза нам в любом случае нужно вытащить, чтобы проверить другие ограничения на выдачу приза.
Информация о типе приза наверняка не меняется с момента создания записи о нем — соответственно, ее можно вытащить вне транзакции, или лучше вообще с хинтом NOLOCK, чтобы не грузить базу ненужными локами.

А насчет использования ORM для модификации данных, особенно сложной, многопоточной, и в условиях конкуренции за одни и те же data items — я бы лучше писал хитрую логику модификации в сторед-процедурах, а в ORM создавал их обертки — сигнатуры и типы возвратов. Слишком уж скудны средства ORM в части модификации по сравнению со средствами самой БД
Хотя, можно выполнить с помощью RAW SQL только запрос
UPDATE BonusPool 
SET RemainingCount -= 1
WHERE BonusID = @BonusID AND RemainingCount > 0

И выполнять дальше с помощью ORM любую логику, если @@ROWCOUNT > 0. Это будет практически полностью аналогично подходу с использованием update блокировки. Разве что, в момент транзакции между наложением update блокировки и изменением RemainingCount, могут выполнятся транзакции, которым нужно только прочитать из таблицы BonusPool, а в случае с update'ом будет наложена эксклюзивная блокировка и любые транзакции, обращающиеся к призу с этим Id, будут ждать.
НЛО прилетело и опубликовало эту надпись здесь
Там фильтрация по PK, а RemainingCount — это уточняющее условие, так что будет наложет U на одну запись. В любом случае (даже если и на диапазон), UPDATE накладывает сразу U, а не (S, then U) — как это было бы сделано в случае IF EXISTS [S]… UPDATE [U] — соответственно, конкурирующие потоки, делающие подобные апдейты, не задедлочатся
или даже так:

INSERT INTO UserBonus (UserID, BonusID) 
SELECT @UserID, A.BonusID
FROM (
   UPDATE BonusPool
   SET RemainingCount -= 1
   OUTPUT inserted.BonusID
   WHERE BonusID = @BonusID AND RemainingCount > 0
) A

IF @@ROWCOUNT > 0
    RETURN 1 -- SUCCESS
RETURN 0 -- FAIL (resource exhausted)

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

CREATE TABLE User (FacebookID INT PRIMARY KEY CLUSTERED)
. . .

BEGIN TRY
  INSERT INTO User (FacebookID)
  SELECT @FacebookID
  WHERE NOT EXISTS(
      SELECT 1 FROM User WITH (NOLOCK) WHERE FacebookID = @FacebookID   -- чтобы не лочить друг друга
  ) 
  IF @@ROWCOUNT > 0
     RETURN  @FacebookID
  RETURN 0
END TRY
BEGIN CATCH
   RETURN 0
END CATCH


тут штука в том, что… not exists… выполняется грязным чтением, и соответственно, не 100% достоверен. Однако, вероятность того, что он прочтет фантом и соответственно, INSERT зафейлится по нарушению первичного ключа, близка к нулю. Чтобы не городить локи или (боже упаси!) serializable транзакции только для того, чтобы прибить эту мизерную вероятность, лучше обернуть все в try/catch и ловить уже по факту сбоя.
У нас сложная гибко настраиваемая логика импорта с выводом подробных валидационных сообщений. Поддерживать ее на процедурах было бы слишком сложно. Поэтому мы используем ORM и serializable транзакции, хотя и жертвуем при этом скоростью в пользу гибкости.
Так отделите логику импорта и валидацию от операций многопоточной синхронизации. Можно делать так:
1. захватили ресурс (SP) — хотя бы поставили DateTaken / ThreadIDTaken
2. спокойно забрали захваченную запись (EF), обрабатывайте/валидируйте по метаданным как угодно
3. завершили обработку ресурса (EF или SP) — отметили DateProcessed = getUTCDate()

На случай, если треды в слое приложения свалятся, не достигнув состояния 3, сделайте бекграунд-тред или SQL-джоб, который будет прибивать или ставить в очередь заново задания, у которых getDate() > DateTaken + MAX_EXEC_TIME AND DateProcessed IS NULL
Не уверен, что за счет такого подхода можно ощутимо выиграть в скорости (хотя, это, конечно, зависит от конкретной ситуации). Подход с update блокировкой как-то проще.
НЛО прилетело и опубликовало эту надпись здесь
Насчет отправки писем и колдовства с identity — может быть не надо сшивать факт отправки и корневую строку с ID операции по завершению каждой отправки? Я бы сделал буферную таблицу, в которую отправляющие агенты пишут ID операции и фактическую дату отправки (вообще без индекса и PK для скорости вставки). Если несколько потоков сообщили об этом — ну и пусть — в этой таблице появятся несколько одинаковых ID. Дальше я бы пустил сервисный поток в бекграунде или SQL job, который, скажем, раз в минуту собирает данные из этой таблицы, старше какой-то даты и маркирует исходные как посланные
Боюсь, в этом случае больше проблем будет из-за того, что сервисный поток при обработке буферной таблицы будет блокировать вставку в нее потоками отправки. В принципе это все решаемо, конечно, но подход с лишней записью как-то проще.
Скажите, пожалуйста, а зачем в реальной жизни проверять отсутствие записи с заданным FacebookId?
Что мешает сделать это до запуска транзакции с уровнем изоляции read committed/uncommitted?
Мы делали похожим образом (правда там был email, а не FacebookId). Если точнее, мы создавали во время транзакции отдельную транзакцию (в другом connection'е) с уровнем изоляции read uncomitted. В итоге мы получали потребителя по email и, так как он уже есть, вместо добавления пытались его отредактировать, но в другой транзакции оказывалось, что потребителя в базе нет, потому что:
  • либо транзакция, добавившая потребителя, еще не была завершена
  • либо она была откачена
  • либо потребитель успел поменять email

Если использовать read comitted транзакцию, для проверки есть ли потребитель в БД, то останется только проблема с параллельным редактированием потребителя, но с ней все равно надо как-то бороться.

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

Вы не сможете эту запись обновить пока транзакция, добавившая потребителя не завершится. Профит от проверки в транзакции, которая обновляет данные неясен. Т.е. при попытке update'а незакомиченной записи Ваша транзакция просто подвиснет в ожидании пока завершится незакоммиченная транзакция.

либо транзакция, добавившая потребителя, еще не была завершена
либо потребитель успел поменять email

Проверьте ROWCOUNT после обновления.
Ну да, с тем, что транзакция могла быть не завершена — это я погорячился)

Вариант с ROWCOUNT мы уже подробно обсудили веткой выше.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий