Нулевой тупик SQL по своей природе - какие-нибудь шаблоны кодирования?
Я сталкиваюсь с очень редкими, но раздражающими взаимоблокировками SQL в веб-приложении .NET 2.0, работающем поверх MS SQL Server 2005. В прошлом мы имели дело с взаимоблокировками SQL очень эмпирическим способом - в основном настраивая запросы до тех пор, пока они не сработают.
Тем не менее, я нашел этот подход очень неудовлетворительным: отнимающим много времени и ненадежным. Я бы очень предпочел следовать детерминированным шаблонам запросов, которые гарантировали бы по дизайну, что тупиковые ситуации SQL никогда не возникнут.
Например, в многопоточном программировании на C# простое правило проектирования, такое как замки должны быть взяты в соответствии с их лексикографическим порядком, гарантирует, что Когда-либо не возникнет тупиковой ситуации.
Существуют ли какие-либо шаблоны кодирования SQL, которые гарантированно защищают от взаимоблокировок?
Ответы 10
Как вы сказали, всегда обращаться к таблицам в одном и том же порядке - это очень хороший способ избежать взаимоблокировок. Кроме того, максимально сократите свои транзакции.
Еще один интересный трюк - по возможности объединить 2 оператора sql в один. Отдельные операторы всегда транзакционны. Например, используйте «UPDATE ... SELECT» или «INSERT ... SELECT», используйте «@@ ERROR» и «@@ ROWCOUNT» вместо «SELECT COUNT» или «IF (EXISTS ...)»
Наконец, убедитесь, что ваш вызывающий код может обрабатывать взаимоблокировки, повторно отправляя запрос настраиваемое количество раз. Иногда это просто случается, это нормальное поведение, и ваше приложение должно уметь с этим справляться.
Написание кода с защитой от взаимоблокировок действительно сложно. Даже если вы обращаетесь к таблицам в том же порядке, вы все равно можете получить тупиковые ситуации [1]. Я написал сообщение в моем блоге, в котором подробно описаны некоторые подходы, которые помогут вам избежать и разрешить тупиковые ситуации.
Если вы хотите гарантировать, что два оператора / транзакции никогда не войдут в тупик, вы можете добиться этого, наблюдая, какие блокировки потребляет каждый оператор, используя системную хранимую процедуру sp_lock. Для этого вы должны либо действовать очень быстро, либо использовать открытую транзакцию с подсказкой о блокировке.
Примечания:
- Любой оператор SELECT, которому требуется более одной блокировки одновременно, может зайти в тупик против грамотно спроектированной транзакции, которая захватывает блокировки в обратном порядке.
Writing deadlock-proof code is really hard: Вы не хотите сказать impossible, поскольку план выполнения базы данных может измениться за время ее существования?
@Sam, можно ли вручную управлять нашей собственной блокировкой и сообщить sql-server / mysql, что мы собираемся обрабатывать наши собственные блокировки, и сказать ему, чтобы он прекратил автоматическое захват блокировок?
Отсутствие взаимоблокировок в общем случае является чрезвычайно дорогостоящей проблемой, потому что вы должны знать все таблицы / объекты, которые вы собираетесь читать и изменять для каждой запущенной транзакции (включая SELECT). Общая философия называется заказана строгая двухфазная блокировка (не путать с двухфазной фиксацией) (http://en.wikipedia.org/wiki/Two_phase_locking; даже 2PL не гарантия без взаимоблокировок)
Очень немногие СУБД на самом деле реализуют строгий 2PL из-за значительного снижения производительности, которое это вызывает (нет бесплатных обедов), в то время как все ваши транзакции ждут выполнения даже простых операторов SELECT.
В любом случае, если вас это действительно интересует, взгляните на SET ISOLATION LEVEL в SQL Server. Вы можете настроить это по мере необходимости. http://en.wikipedia.org/wiki/Isolation_level
Для получения дополнительной информации см. Википедию о сериализуемости: http://en.wikipedia.org/wiki/Serializability
Тем не менее - отличная аналогия - это как исправления исходного кода: проверяйте рано и часто. Делайте ваши транзакции небольшими (количество операторов SQL, количество измененных строк) и быстрыми (время на настенных часах помогает избежать столкновений с другими). Может быть приятно и аккуратно делать МНОГО вещей в одной транзакции - и в целом я согласен с этой философией - но если вы столкнулись с большим количеством тупиковых ситуаций, вы можете разбить транзакцию на более мелкие, а затем проверяйте их статус в приложении по мере продвижения. TRAN 1 - ОК Да / Нет? Если да, отправьте TRAN 2 - OK Y / N? и т. д. и т. д.
Кстати, за многие годы работы администратором баз данных, а также разработчиком (многопользовательских приложений БД, измеряющих тысячи одновременных пользователей) я ни разу не обнаружил, что тупиковые ситуации являются такой серьезной проблемой, что мне требовалось особое внимание к ней (или изменение изоляции уровни волей-неволей и т. д.).
Однако это похоже на бегство от проблемы, чем на ее решение.
Если у вас есть достаточный контроль над дизайном своего приложения, ограничьте свои обновления / вставки определенными хранимыми процедурами и удалите привилегии обновления / вставки из ролей базы данных, используемых приложением (только явно разрешайте обновления через эти хранимые процедуры).
Изолируйте подключения базы данных к определенному классу в своем приложении (каждое подключение должно исходить из этого класса) и укажите, что подключения типа «только запрос» устанавливают уровень изоляции на «грязное чтение» ... эквивалент (nolock) при каждом подключении .
Таким образом, вы изолируете действия, которые может вызывают блокировки (для определенных хранимых процедур), и извлекаете «простые чтения» из «цикла блокировки».
Быстрый ответ - нет, гарантированной техники нет.
Я не понимаю, как вообще можно сделать какое-либо доказательство взаимоблокировки приложения в качестве принципа проектирования, если оно имеет нетривиальную пропускную способность. Если вы упреждающе заблокируете все ресурсы, которые потенциально могут вам понадобиться в процессе, в том же порядке, даже если они вам не понадобятся, вы рискуете получить более дорогостоящую проблему, когда второй процесс ожидает получения первой необходимой ему блокировки. , и это повлияет на вашу доступность. И по мере того, как количество ресурсов в вашей системе растет, даже тривиальные процессы должны блокировать их все в одном порядке, чтобы предотвратить взаимоблокировки.
Лучший способ решить проблемы взаимоблокировки SQL, как и большинство проблем с производительностью и доступностью, - это посмотреть на рабочую нагрузку в профилировщике и понять ее поведение.
В дополнение к последовательной последовательности получения блокировок - еще один путь - явное использование подсказок блокировки и изоляции для сокращения времени / ресурсов, которые тратятся впустую на непреднамеренное получение блокировок, таких как совместное намерение во время чтения.
Не прямой ответ на ваш вопрос, а пища для размышлений:
http://en.wikipedia.org/wiki/Dining_philosophers_problem
«Проблема обедающих философов» - это старый мысленный эксперимент по исследованию проблемы тупика. Прочтение об этом может помочь вам найти решение в конкретных обстоятельствах.
То, о чем никто не упомянул (что удивительно), заключается в том, что в случае с SQL-сервером многие проблемы с блокировкой могут быть устранены с помощью правильного набора покрывающих индексов для рабочей нагрузки запросов БД. Почему? Потому что это может значительно уменьшить количество поисков закладок в кластеризованном индексе таблицы (при условии, что это не куча), тем самым уменьшая конкуренцию и блокировку.
Не существует волшебного универсального решения этой проблемы, которое работало бы на практике. Вы можете направить параллелизм в приложение, но это может быть очень сложно, особенно если вам нужно координировать свои действия с другими программами, работающими в отдельных областях памяти.
Общие ответы по уменьшению возможности тупика:
Базовая оптимизация запросов (правильное использование индекса), избегание горячих точек, предотвращение транзакций в кратчайшие сроки и т. д.
По возможности установите разумные тайм-ауты запроса, чтобы в случае возникновения взаимоблокировки он очищался автоматически по истечении периода тайм-аута.
Взаимоблокировки в MSSQL часто возникают из-за его модели параллелизма чтения по умолчанию, поэтому очень важно не зависеть от нее - предполагайте, что MVCC в стиле Oracle во всех проектах. Используйте изоляцию моментальных снимков или, если возможно, уровень изоляции READ UNCOMMITED.
по поводу вашего 3-го пункта - только что получил тот же отзыв от консультанта, утверждающего, что взаимоблокировки могут возникать между обновлением и выбранными транзакциями при использовании «чтение зафиксировано» из-за эскалации блокировки sqlserver. мы планируем перейти к чтению незафиксированных и посмотреть, исчезнут ли взаимоблокировки. не видел, чтобы это упоминалось очень часто.
@YonatanKarni, вы уверены, что консультант не ошибается?
@Pacerier, это было давно, однако переход к чтению незафиксированных, похоже, решил эту проблему - не предполагать, что это не приведет к появлению новых проблем, если не будет сделано осторожно ...
Я считаю, что следующий полезный шаблон чтения / записи является доказательством мертвой блокировки с учетом некоторых ограничений:
Ограничения:
- Один стол
- Для чтения / записи используется индекс или PK, поэтому механизм не прибегает к блокировкам таблиц.
- Пакет записей можно прочитать с помощью единственного предложения SQL where.
- Используя терминологию SQL Server.
Цикл записи:
- Все записи в рамках одной транзакции «Чтение зафиксировано».
- Первое обновление в транзакции относится к определенной, всегда присутствующей записи. внутри каждой группы обновлений.
- Тогда несколько записей могут быть записаны в любом порядке. (Они «защищены» записью в первую запись).
Цикл чтения:
- Уровень зафиксированной транзакции чтения по умолчанию
- Нет транзакции
- Чтение записей как одного оператора выбора.
Выгоды:
- Вторичные циклы записи блокируются при записи первой записи до тех пор, пока первая транзакция записи не завершится полностью.
- Чтения блокируются / ставятся в очередь / выполняются атомарно между фиксациями записи.
- Обеспечьте согласованность на уровне транзакций без использования «сериализуемого».
Мне нужно, чтобы это работало, так что прокомментируйте / исправьте !!
Это, безусловно, одна из самых неприятных вещей, с которыми я когда-либо сталкивался в своей карьере программиста. Я провел более 5 лет, ломая зубы, пытаясь заставить базы данных вести себя как императивные языки программирования. +1 за отличный вопрос. Я просто хотел бы получить более удовлетворительный ответ.