Как возможна взаимоблокировка в SELECT

Я запускал процедуру внутри курсора. После многих успешных итераций я получил это: Transaction (Process ID 104) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Я не публикую полную информацию, поэтому я не ожидаю подробного ответа по отладке. Факты:

  1. Я уверен, что никто другой (включая меня в другом сеансе) не использовал процесс, так как я его разрабатывал и
  2. Эта транзакция застряла при выполнении select (я видел текущий запрос из запросов dm exec)

Если я не ошибаюсь по моим 2 пунктам, возможен ли когда-нибудь тупик? Разве взаимоблокировка не потребует, чтобы все вовлеченные пользователи ресурса выполняли операции записи на них, что создало бы цикл в графе запросов ресурсов? Я понимаю ошибку тайм-аута в select, но не могу понять взаимоблокировку. Что мне не хватает?

Обновление:

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

Однако в надежде сохранить это полезным и, надеюсь, найти ответ, вот еще несколько вещей, которые я исследовал, некоторые факты и мысли о комментариях:

Во первых версия sql server 2008. Я так понимаю не поддерживается. Я не в состоянии давать рекомендации, тем более обновлять сервер.

Я нашел комментарий Jeroen Mostert интересным. Сколько стоит прошлое? Я заметил в sys.dm_os_waiting_tasks, что сеанс блокируется сам по себе несколько раз с типом ожидания CXPACKET. Я немного поискал, но опция (maxdop 1) не решила проблему. Однако помните об несуществующем индексе, который привел бы к ужасной производительности. Может быть, правильный параллелизм добавлен, но операций слишком много? Тем не менее, я также стал свидетелем огромного dm_exec_requests.wait_time. Таким образом, даже несмотря на то, что запрос был плохим, я пришел к выводу, что вокруг были странные (мертвые) блокировки.

Если в ответе/комментарии появятся конкретные запросы/шаги для отслеживания проблемы, я буду рад воссоздать ее.


sql-server tsql database-deadlocks
person George Menoutis    schedule 29.09.2020    source источник
comment
SELECT по-прежнему требует блокировки таблицы, а таблица не должна быть заблокирована, чтобы она возвращала правильные данные на уровень приложения/представления. Когда вы игнорируете блокировки (и делаете глупости, например, спамите подсказку NOLOCK), вы начинаете получать всевозможные ошибочные данные.   -  person Larnu    schedule 29.09.2020
comment
Однако замок является общим, верно? Таким образом, либо мой процесс будет ждать, потому что другой процесс записывает в таблицу, либо другой будет ждать, прежде чем писать, потому что мой процесс читает. Тот или иной будет ждать общий ресурс, но циклов в этом нет. Я не использую nolock, кстати.   -  person George Menoutis    schedule 29.09.2020
comment
Однако блокировка является общей, правильно Нет, если это оператор DML no, а совместное использование блокировки, если я не ошибаюсь, является корпоративной функцией.   -  person Larnu    schedule 29.09.2020
comment
Но это не оператор DML. Это select. Вот что меня интересует.   -  person George Menoutis    schedule 29.09.2020
comment
Я говорю о том, что SELECT не может совместно использовать блокировку с оператором DML.   -  person Larnu    schedule 29.09.2020
comment
Вы говорите, что никто другой не использовал эту процедуру, но кто-нибудь еще использовал ту же таблицу (таблицы)?   -  person seanb    schedule 29.09.2020
comment
Обратите внимание, что в прошлом были случаи взаимоблокировок внутри запроса - взаимоблокировка запроса при его собственном выполнении из-за того, что движок неправильно брал блокировки (все еще с сообщением, заблокированным другим процессом), обычно при использовании параллелизма. Однако это настоящие ошибки, а не норма.   -  person Jeroen Mostert    schedule 29.09.2020

Ответы (1)


arrow_upward
3
arrow_downward

SELECT может вызвать взаимоблокировку, если кто-то другой использует таблицу.

Этот пример почти на 100% скопирован из видео Брента Озара о взаимоблокировках, но одна команда заменена на ВЫБРАТЬ.

Для начала создадим две таблицы

CREATE TABLE Lefty (ID int PRIMARY KEY)
CREATE TABLE Righty (ID int PRIMARY KEY)
INSERT INTO Lefty (ID)  VALUES (1)
INSERT INTO Righty (ID) VALUES (2)

Затем откройте два окна в SSMS. Сначала поместите этот код (пока не запускайте его)

BEGIN TRAN
UPDATE Lefty SET ID = 5

SELECT * FROM Righty
COMMIT TRAN

Во втором окне введите этот код (тоже пока не запускайте его).

BEGIN TRAN
UPDATE Righty SET ID = 5
UPDATE Lefty SET ID = 5
COMMIT TRAN

Теперь в первом окне запустите первые две команды (BEGIN TRAN И UPDATE LEFTY). Это начинается.

Во втором окне запустите всю транзакцию. Он сидит там, ожидая вашего первого окна, и будет ждать вечно.

В первом окне вернитесь и запустите SELECT * FROM Righty и COMMIT TRAN. 5, 4, 3, 2, 1 Boom deadlock - потому что второе окно уже заблокировало таблицу и, следовательно, SELECT в первом окне не мог запуститься (а второе окно не могло запуститься, потому что первое было заблокировано). на столе это нужно).

(Хочу повторить - это демо Брента Озара, а не мое! Я просто передаю его. Действительно, я их рекомендую).

person seanb    schedule 29.09.2020
comment
Больше точности при возникновении взаимоблокировок: 1) может произойти при чтении, 2) должен быть почти один запрос на запись или DDL (ALTER, DROP...), 3) вероятность увеличивается при параллелизме, 4) вероятность уменьшается при хороший индексный план 5) вероятность увеличивается с продолжительностью транзакции - person SQLpro; 29.09.2020
comment
и, наконец, его избегают, когда SELECT выполняется в оптимистическом режиме, но это может привести к потере обновлений транзакции. - person SQLpro; 29.09.2020
comment
Я не могу принять этот ответ, поскольку, хотя текущий левый запрос является select, он заключен в неразрешенную транзакцию, которая включает обновление, что явно не в моем случае. - person George Menoutis; 29.09.2020
comment
Операции SQL, включая SELECT, обычно выполняются в транзакции, даже если это делается за кулисами. Тупик возможен в любой ситуации. - person Mike Robinson; 29.09.2020
comment
Если вы не выполняете неявные транзакции, транзакция включает только сам select. Никакой dml туда не пролезет. - person George Menoutis; 29.09.2020
comment
Ну, это вставка в одну таблицу # через вставку exec(@txt). Тем не менее, я получаю тот же план (не стал ждать ошибки, так как это занимает 50 минут), если я запускаю только select, поскольку я получаю его от печати динамического запроса. - person George Menoutis; 29.09.2020
comment
На самом деле - я думал, что сообщение о взаимоблокировке не является нормальным. Если вы ищете сообщение, вы получаете информацию о том, что оно связано с операторами обмена в параллелизме, например, stackoverflow.com/questions/18996157/ - person seanb; 29.09.2020