Проблема SQL - сделка большого объема и нарушение PK

Почему это происходит?

SELECT TOP 1 MessageSequence FROM TableA WHERE MessageSequence = @MessageSequence

Этот SELECT попытается найти строку, если она не найдена, оператор EXISTS вернет FALSE, и INSERT продолжится. Однако решение о INSERT основано на состоянии, которое было истинным во время SELECT, но больше не гарантируется, что будет истинным во время INSERT. Другими словами, у вас есть условия гонки, когда два потока могут искать одну и ту же @MessageSequence, оба возвращают НЕ СУЩЕСТВУЕТ и оба пытаются ВСТАВИТЬ, когда только первый будет успешным, второй вызовет нарушение PK.

Как решить эту проблему?

Самым быстрым решением является добавление подсказки WITH (UPDLOCK) к SELECT, это приведет к принудительному сохранению блокировки, помещенной в ключ @MessageSequence, и, следовательно, INSERT / SELECT вести себя атомарно:

INSERT TableA(MessageSequence, Data )
   SELECT @MessageSequence, @Data
   WHERE NOT EXISTS (
      SELECT TOP 1 MessageSequence FROM TableA WITH(UPDLOCK) WHERE MessageSequence = @MessageSequence)

Чтобы SQL не выполнял такие хитрые вещи, как блокировка страницы, вы также можете добавить подсказку ROWLOCK.

Однако это не моя рекомендация. Моя рекомендация может вас удивить, но вот что: выполните операцию, которая с наибольшей вероятностью завершится успешно, и обработайте ошибку, если она не удалась . Т.е. если ваш бизнес-пример увеличивает вероятность того, что @MessageSequnce будет новым, попробуйте INSERT и обработайте PK, если он не удался. Таким образом, вы избегаете ложных поисков, а стоимость перехвата / повторной попытки амортизируется во многих случаях, когда это удается с первой попытки.

Кроме того, возможно, стоит исследовать, используя встроенный очереди, которые поставляются с SQL Server .

выполните операцию, которая с наибольшей вероятностью завершится успешно, и обработайте ошибку, если она не удалась . Т.е. если ваш бизнес-пример увеличивает вероятность того, что @MessageSequnce будет новым, попробуйте INSERT и обработайте PK, если он не удался. Таким образом вы избегаете ложных поисков, а стоимость перехвата / повторной попытки амортизируется во многих случаях, когда это удается с первой попытки.

Кроме того, возможно, стоит исследовать, используя встроенный очереди, которые поставляются с SQL Server .

выполните операцию, которая с наибольшей вероятностью завершится успешно, и обработайте ошибку, если она не удалась . Т.е. если ваш бизнес-пример увеличивает вероятность того, что @MessageSequnce будет новым, попробуйте INSERT и обработайте PK, если он не удался. Таким образом, вы избегаете ложных поисков, а стоимость перехвата / повторной попытки амортизируется во многих случаях, когда это удается с первой попытки.

Кроме того, возможно, стоит исследовать, используя встроенный очереди, которые поставляются с SQL Server .

Общая проблема. Объясняется здесь:

Защитное программирование базы данных: исключение операторов IF

Это может быть связано с уровнем изоляции транзакции. Вам может потребоваться

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

перед тем, как вы начнете транзакцию.

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

Это очень похоже на сообщение 939831 . В конечном итоге вы хотите использовать подсказки (ROWLOCK, READPAST, UPDLOCK). READPAST сообщает серверу sql перейти к следующей записи, если текущая заблокирована. UPDLOCK сообщает серверу sql, что блокировка чтения перерастет в блокировку обновления.

Когда я реализовал нечто подобное, я заблокировал следующую запись по идентификатору потока

UPDATE TOP (1)
    foo
SET
    ProcessorID = @PROCID
FROM
    OrderTable foo WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE
    ProcessorID = 0

Затем выбрал запись

SELECT *
FROM foo WITH (NOLOCK)
WHERE ProcessorID = @PROCID

Затем пометил ее как обработанную

UPDATE foo
SET ProcessorID = -1
WHERE ProcessorID = @PROCID

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

Атомарность следующего утверждения - это то, что вам нужно:

INSERT TableA(MessageSequence, Data )
SELECT @MessageSequence, @Data
WHERE NOT EXISTS
(
  SELECT TOP 1 MessageSequence FROM TableA WHERE MessageSequence = @MessageSequence
)

Согласно этому человеку , это зависит от текущего уровня изоляции.

С другой стороны, если вы думаете о системе торговли с большими объемами, вам может потребоваться тиковая база данных, разработанная для таких данных [я не совсем уверен, какое у вас «сообщение» сохранение здесь], например, обсуждаемых в этой ветке: http://www.elitetrader.com/vb/showthread.php?threadid=81345 .

Обычно это решения в памяти с собственные языки запросов. В нашем магазине мы используем kdb +.

Не уверен, какой продукт обмена сообщениями вы используете - но, возможно, стоит посмотреть на транзакции не на уровне БД, а на уровне MQ.

Конечно, если вы используете TM (диспетчер транзакций), две операции: 1) Получение из MQ и 2) Запись в БД оба «заключены в скобки» в рамках одного и того же родительского коммита.

Поэтому я не уверен, используете ли вы здесь неявный или явный или какой-либо TM (например, Microsoft DTC).

• MessageSequence - это PK, так что одно и то же сообщение из MQ может обрабатываться дважды.
• Когда вы выполняете GET из MQ, убедитесь, что GET зафиксирован (т.е. не db-commit, а a MQ-commit) - это гарантирует, что тот же MessageID не может быть «извлечен» следующим потоком, который записывает сообщения в БД.