Тупикова блокування - це замкнутий ланцюжок блокування, в якій два або більше потоку блокують один одного так, що жоден з них не може продовжувати виконання. Коли потік відстеження тупикової блокування в SQL Server виявляє замкнутий ланцюжок блокування, одного з учасників тупикової блокування він вибирає в якості жертви, скасовує поточний пакет цього spid, і відкочується його транзакції, щоб дозволити іншому spid продовжити виконання. Жертва тупикової блокування отримає повідомлення про помилку 1205:
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Тупикова блокування - це спеціальний тип сценарію блокування, але блокування і тупиковий блокування - це не одне і те ж. Іноді люди говорять про тупикової блокування, хоча насправді бачать блокування.
З кількома винятками, тупикові блокування виявляються, як природний побічний ефект блокування, і їх не можна вважати помилками в роботі SQL Server. Типовим рішенням для запобігання тупикової блокування є налагодження збережених процедур / коду програми, або зміна схеми / індексації.
Прочитати інформацію, отриману при включеному прапорі -T1222. Ця інформація буде присутній в журналі помилок SQL Server після того, як відбудеться тупикова блокування.
Вона виглядає приблизно так:
"Розшифровка" отриманої інформації для того, щоб краще зрозуміти сценарій блокування.
Інформація про тупикової блокування представлена розділами "process-list" (список процесів) і "resource-list" (список ресурсів). "Процес" - це spid або робочий потік, який бере участь в тупиковій блокування. Кожному процесу призначається свій ідентифікатор, подібне до цього "processdceda8". Ресурс - це ресурс, який зайнятий (зазвичай заблокований) і звільнення якого чекає один з учасників. Мені подобається використовувати наведений нижче формат для того, щоб узагальнити інформацію про тупикової блокування. Якщо хочете, можете пропустити цей крок, але я ніколи цього не роблю; мені це допомагає більш ясно зрозуміти ситуацію тупиковою блокування. Я виділив жовтим кольором дані в результуючому наборі при використанні 1222, щоб Ви змогли самостійно відновити таку картину.
Проженете запити, залучені в тупикову блокування, через Database Tuning Advisor. Помістіть текст запиту в вікно запиту Management Studio, виберіть базу даних, в контексті якої повинен бути виконаний запит, клацніть правою кнопкою миші по тексту запиту, і виберіть "Analyze Query in DTA". Не пропустіть цей крок; більше половини проблем, що призводять до тупикової блокування, вирішуються просто додаванням відповідного індексу, щоб один із запитів виконувався швидше і блокував менше ресурсів. Якщо DTA рекомендує побудувати індекси, створіть їх, і подивіться, чи зберігається тупикова блокування.
Переконайтеся що Ваші транзакції настільки короткі, наскільки це дозволяють бізнес - правила. Уникайте неявних транзакцій, оскільки ця модель управління транзакціями породжує надмірно довгі транзакції.
Вишукайте інші можливості для підвищення ефективність запитів. залучених в тупикову блокування: або через зміну запиту, або через індексацію. Запит, який блокує мінімальне число ресурсів, з меншою ймовірністю призведе до тупикової блокування. Якщо в плані запиту присутній сканування таблиці, сканування індексу, ємне хешування або сортування великої кількості даних, це показники необхідності внесення удосконалень.
Якщо один або обидва spid виконують многооператорную транзакцію. можливо, Вам буде необхідно налаштувати трасу профайлера, щоб отримати інформацію про тупикової блокування, і ідентифікувати повний набір запитів, що викликають виникнення тупикової блокування. На жаль, -T1204 і -T1222 показують лише два запити, які "забили цикл", але можливо, що одна з блокувань була накладена більш раннім запитом, виконаним в рамках тієї ж транзакції.
Нижче наведені загальні рекомендації, які можна застосувати до будь-якої тупикової блокування. Якщо після виконання цих рекомендацій проблема не вирішилася, Ви повинні будете зануритися в проблему трохи глибше, і знайти рішення за допомогою спеціального сценарію. Нижче представлений невеликий список типових методів, з яких Ви можете вибрати найбільш підходящий метод боротьби з тупикової блокуванням:
Доступ до об'єктів виконуйте в одному порядку. Розглянемо наступні два пакети.
1. Begin Transaction
4. Commit Transaction
Ці два пакети, з високою ймовірністю, можуть стати причиною тупикової блокування. Якщо обидва збираються виконувати крок 3, кожен з них може виявитися заблокованим іншим, тому що вони обидва звертаються до ресурсу, який інше підключення блокувало на кроці 2.
Якщо обидва учасники тупикової блокування використовують один і той же індекс, необхідно створити індекс, який може забезпечити додатковий шлях доступу для одного з spid. Наприклад, додаючи покриває кластерний індекс для SELECT, залученого в тупикову блокування, можна запобігти проблемі (за умови що жоден з ключів покриває індексу не змінюється іншим учасником тупикової блокування).
З іншого боку, якщо spid стали учасниками тупикової блокування через те, що були використані альтернативні шляхи (індекси) для доступу до необхідної рядку даних або сторінці, розгляньте можливість видалення одного з індексів, або можливість використання підказки індексу, щоб обидва запити спільно використовували один шлях доступу. Будьте обережні, результатом такого підходу може стати зниження продуктивності.
Тупикові блокування, це спеціальний тип блокування, де два spid блокують один одного. Іноді кращим способом запобігти появі тупикової блокування є примусове блокування в більш ранній точці однієї з транзакцій. Наприклад, якщо Ви заздалегідь заблокуєте spid A в транзакції з spid B, тоді з самого початку транзакція з spid А не зможе отримати ресурси до закінчення блокування, накладеної spid B. Чи означає це що Ви навмисно ініціюєте блокування? Так, оскільки ресурси вже заблоковані, не виникне ситуації тупикової блокування, і в цьому сенсі, просте блокування - більш оптимальне рішення тупикового блокування. Також, можна продовжувати блокування за допомогою підказок HOLDLOCK і UPDLOCK до завершення транзакції spid B.
Якщо потрібно, що б в якості жертви при вирішенні тупикової блокування вибирається процес з більш високим пріоритетом, для процесу c нижчим пріоритетом можна використовувати установку SET DEADLOCK_PRIORITY LOW. Spid, для якого використовується дана установка буде завжди була обрана в якості жертви при виникненні тупикової блокування, учасником якої він стане.
Уникайте використання кластерних індексів на шпальтах, які часто змінюються. Зміни складових ключ кластерного індексу стовпців будуть накладати блокування в кластерному індексі (щоб перемістити рядок) і викликати блокування всього некластерного індексу (так як рядки на листовому рівні некластерного індексу посилаються на значення ключа кластерного індексу).
У деяких випадках може бути корисним використовувати підказку NOLOCK, якщо один із запитів є інструкцією SELECT. Хоча цей шлях є найбільш привабливим, тому що є швидке і просте рішення в багатьох ситуаціях тупикового блокування, використовуйте цей підхід обережно, оскільки це створює проблеми, що виникають при рівні ізоляції транзакцій read uncommitted (запит може повернути суперечливі уявлення даних). Якщо Ви ще не знайомі з проблемами використання цього рівня ізоляції, зверніться до розділу "SET TRANSACTION ISOLATION LEVEL" в SQL Books Online або проконсультуйтеся у знаючих фахівців, перед використанням цієї опції.