Блокування в SQL Server

За матеріалами статті Andres Taylor Advanced SQL Server Locking
Переклад Віталія Степаненко

Я думав, що знаю SQL Server досить добре. Я використовую цей продукт вже більше 6 років, і мені подобається знати про використовувані мною інструментах все. Коли я викладав на курсах програмування SQL Server, я помітив, що в матеріалах Microsoft представлена ​​таблиця сумісності блокувань. Та ж таблиця була представлена ​​і в MSDN. Розглядаючи цю таблицю, я здивувався - невже тут немає блокування Intent Update? Це привело мене до дослідження блокувань. Ця стаття і є результат цього дослідження. Я написав цю статтю для певного читача - для того, хто розуміє рівні ізоляції, блокування наміри, мертві блокування і рівні блокувань. Якщо ви недостатньо впевнено розбираєтеся в цих областях, вам потрібно спочатку ознайомитися з ними перед читанням цієї статті.

Я сподіваюся, що я розширю ваше розуміння блокувань в SQL Server і, можливо, навчу вас деяким прийомам, які ви зможете використовувати під час програмування на SQL Server.
Повинен сказати, що ви можете цілком успішно працювати з SQL Server довгий час і не знати, як він блокує свої ресурси, і в той же час писати високоякісні код і схеми баз даних. Але якщо ви схожі на мене і хочете знати внутрішню будову речей або якщо ви працюєте з системою, яка вимагає хоча б невеликого приросту продуктивності, то я можу навчити вас дечому корисного.

[На початок]

Update блокування

Я впевнений, що ви чули про мертвих блокування, коли системний процес Х блокує ресурс А і чекає, щоб заблокувати ресурс B, в той час як системний процес Y блокує процес B і чекає звільнення процесу А. Якщо не чули, то пошукайте додаткову інформацію про цьому в MSDN.
Отже, уявіть, що типів мертвих блокувань більше. Розглянемо наступну ситуацію: системний процес Х має shared блокування на ресурсі А, і системний процес Y теж. Це не проблема, так як дві shared блокування сумісні один з одним. Тепер процес Х хоче перетворити shared блокування в ексклюзивну блокування, щоб оновити ресурс. Для цього процес Х повинен почекати, поки процес Y зніме shared блокування, і поки процес Х чекає, процес Y вирішує зробити те ж саме, тобто перетворити свою блокування в ексклюзивну блокування. Процес Х чекає процес Y, а процес Y чекає процес Х. Це і є мертва блокування. Такі блокування називаються мертві блокування конвертації (conversion deadlocks).
Це досить звичайна ситуація, і щоб її уникнути, були введені update блокування. Update блокування дозволяють з'єднанню читати ресурс, але в той же час показують свій намір блокувати його пізніше, щоб змінити ресурс. Проблема з update блокуваннями полягає в тому, що SQL Server заздалегідь не знає, чи хоче транзакція перетворити shared блокування в ексклюзивну блокування, якщо тільки це не поодинока команда SQL, як команда UPDATE, яка повинна спочатку прочитати таблицю, перш ніж оновлювати її.
Щоб використовувати update блокування в команді SELECT, ви повинні явно визначити, що ви хочете, щоб SQL Server використовував їх, і це можна зробити за допомогою хинта блокування UPDLOCK.

Мені подобається ілюструвати те, про що я говорю, прикладами коду. У вікні QA я запускаю наступний пакет:

У вікні QA я запускаю наступний пакет:

Зауважте, що я відкриваю транзакцію, але не закриваю її. Це зроблено для утримання блокувань.
Якщо іншу сполуку спробує накласти іншу update блокування на ту ж рядок, то це блокування буде відкладена, поки не буде знята перша блокування - дві update блокування одного ресурсу несумісні.

Результат SP_LOCK з потрібними рядками і стовпцями показаний нижче:

Як і очікувалося, ключ, який ми блокуємо, OrderId, заблокований update блокуванням. Дивне значення в стовпці Resource (89003da47679) - це хешірованное значення нашого ключа, 10633. SQL Server використовує у себе хеш таблицю як таблицю блокування, і це те, що ми бачимо тут.
Сторінка, що містить цей рядок, як і очікувалося, заблокована за допомогою Intent Update блокування. Значення стовпця Resource (1: 242) показує, що сторінка розміщена в першому файлі бази даних і її номер - 242 в цьому файлі. І, нарешті, хоча і не очікувалося, SQL Server накладає IX блокування на рівень таблиці. SQL Server ніколи не використовує U / IU блокування на рівні таблиці, так що ми побачимо тільки X / IX блокування на цьому рівні.
Коли ви виконуєте оновлення з виразом where, SQL Server повинен прочитати таблицю і / або індекси, щоб визначити, які рядки вимагають зміни. Перед тим, як він прочитає всю інформацію з таблиці / індексу, SQL Server блокує об'єкт, до якого потрібен доступ. Оскільки SQL Server знає, що ви виконуєте оновлення, то він вибере update блокування, а не shared блокування. Це потрібно для уникнення виникнення мертвої блокування, про яку я говорив раніше - про мертву блокування конвертації.
Коли SQL Server знайшов рядок або рядки для поновлення, то він перетворює update блокування рядки в ексклюзивну блокування на RID, якщо таблиця є купою, або на KEY, якщо таблиця має кластерний індекс. Це означає, що зазвичай ви не бачите update блокувань, коли виконуєте команду UPDATE - вони майже відразу перетворюються в ексклюзивні блокування.
Але, звичайно, є деякі винятки з цього правила. Якщо SQL використовує індекс для пошуку рядків, то він блокує сторінки і ключі індексу за допомогою update блокувань. Якщо оновлення не змінює ніяких стовпців, що містяться в індексі, то update блокування не перетворяться в ексклюзивні блокування. Наведу приклад:

Region - це таблиця-купа з унікальним некластерние первинним ключем по стовпцю RegionId. Щоб заповнити цей запит, SQL Server переглядає індекс по RegionId і блокує сторінки і ключі індексу за допомогою update блокувань. Нарешті, він знайде рядок, що вимагає зміни. Оскільки команда оновлення не змінює RegionId, то їй не потрібно накладати ексклюзивну блокування на індекс. Результат з SP_LOCK показаний нижче:

Ми бачимо IX блокування на таблиці і X блокування на RID оновлюваної рядки. KEY блокування накладена на індекс по RegionId. Це зрозуміло по стовпцю Indid. Як видно, у нас все ще зберігається update блокування на індексі. Це один з небагатьох випадків, коли ви можете безпосередньо побачити update блокування.
І, щоб закінчити, у нас є дві сторінкові блокування - одна на сторінці індексу (1: 306) і одна на таблиці-купі (1: 300). Звідки я це знаю? Подивіться на стовпець Indid. Id індексу, що дорівнює 0, завжди позначає таблицю-купу.

[На початок]

рівні блокувань

SQL Server має кілька типів блокувань, але він також вибирає і різні рівні блокувань.
Якщо ви запускали SP_LOCK хоча б раз або дивилися поточний стан Enterprise Manager, то ви точно бачили принаймні 4 або 5 різних типів блокувань. Я швидко пройду по кожному з них.

Database (DB): Це блокування сесії - тобто вона не належить ні до якої транзакції, а тільки до користувача, підключеного до певної базі даних. Це потрібно, щоб запобігти видалення бази даних, коли до неї підключені один або більше користувачів. Зауважте, що SQL Server знає, що бази даних master і tempdb не можуть бути видалені, і не накладає DB блокування на ці бази даних.

Table (TAB): Це найгрубіша логічна блокування, яку може використовувати SQL Server. Часто ви виявляєте intent блокування на цьому рівні. (Не пам'ятаєте точно, що таке intent блокування? Читайте далі.)

Extent (EXT): Ці блокування не використовуються для блокування логічних рядків, а використовуються, коли SQL Server створює нові таблиці або розширює існуючі, також ви можете їх бачити, коли файл збільшується в розмірі.

Page (PAG): Коли SQL Server потрібно заблокувати одночасно безліч рядків, а вільні слоти блокувань закінчуються, то він може використовувати сторінкові блокування. Частіше за все ви будете бачити intent блокування на цьому рівні. До SQL Server версії 6.5 включно це була найкраща блокування.

Key (KEY): Кращий рівень блокування, можливий в SQL Server, разом з RID блокуванням. KEY блокування використовуються в індексах, а RID блокування - в таблицях-купах. Вивчаючи в даний час поведінка блокувань в SQL Server, я виявив, що SQL Server в більшості випадків цінує конкурентність вище, ніж швидкість. Висока конкуренція означає, що безліч користувачів може працювати одночасно. По можливості це досягається шляхом невеликих блокувань, щоб не блокувати без необхідності дані, потрібні іншим користувачам. З іншого боку, висока швидкість може бути досягнута за допомогою великих блокувань, що швидше, ніж накладання безлічі маленьких блокувань.

SQL Server 2000 може також піднімати рівень ваших блокувань, якщо він помітить, що ви блокуєте все більше і більше рядків. У цьому випадку SQL Server застосовує табличную блокування і видаляє всі індивідуальні блокування на сторінках, ключах і RID. Зауважте - підняття рівня блокування призводить до встановлення табличних блокувань. SQL Server не підвищує рівень RID / KEY блокувань до сторінкових блокувань.
Коли ж SQL Server піднімає рівень блокувань? Його не цікавить, наскільки великий відсоток таблиці заблокований; єдина річ, яка має значення - це загальна кількість блокувань в системі. Коли блокування починають використовувати досить великий відсоток пам'яті, SQL Server 2000 намагається підняти рівень блокувань всіх транзакцій у всіх з'єднаннях. Також він намагається підняти рівень блокувань, якщо закінчуються слоти блокувань. Ви можете самі це спробувати, зменшивши кількість слотів блокувань, які використовує SQL Server, за допомогою процедури SP_CONFIGURE.
SQL Server намагається утримувати високу конкурентність за допомогою малих блокувань, наскільки це можливо. Але іноді ви знаєте про свої дані то, що SQL Server не знає, і ця інформація впливає на рівень блокувань, який ви хочете використовувати. Наприклад, існує велика таблиця для пошуку, на якій проводиться тільки читання даних. Замість накладання безлічі key блокувань ви хочете заблокувати цілу таблицю за один крок. В цьому випадку ви можете використовувати хинти блокування або SP_INDEXOPTION.
Хинти блокування добре відомі і добре документовані в BOL, так що я не буду повторювати їх тут. Системна збережена процедура SP_INDEXOPTION - це хороший шлях для вказівки SQL Server використовувати певний розмір блокувань.
Використовуючи SP_INDEXOPTION, ви можете вимкнути блокування рівня рядка або рівня сторінки. Це означає, що вам не потрібні хинти блокування - всі блокування таблиці / індексу матимуть заданий вами розмір. Хоча BOL і стверджує, що ця процедура використовується для вибору рівня блокування на індексах, вона також може використовуватися і для таблиці-купи. Передавайте назву таблиці в змінну @IndexNamePattern. Дуже хороший і практично невідомий спосіб.
Кількість факторів, що впливають на цьому не закінчується. Якщо ви використовуєте один з двох вищих рівнів ізоляції і у вас немає жодного індексу за критерієм вибірки, то SQL Server змушений блокувати велику частину, якщо не всю таблицю, щоб виконати запит. Наведу приклад.

Наведу приклад

Тепер, в іншому вікні, давайте перевіримо результат sp_lock. На моєму комп'ютері я відстежив 853 блокування, що відповідають Вашому з'єднанню, на якому я запустив UPDATE. Таблиця Orders в базі Northwind має 830 рядків, так що я заблокував кожен рядок. Відкотити транзакцію і спробуйте знову застосувати update після створення індексу, як показано нижче.

Відкотити транзакцію і спробуйте знову застосувати update після створення індексу, як показано нижче

Тепер результат SP_LOCK показує всього 25 блокувань. Ця частина продуктивності часто не помічається. Навіть якщо ви використовуєте READ COMMITTED, рівень ізоляції за замовчуванням, то ви все одно побачите велику різницю - зниження кількості блокувань зі 136 до 24 після створення індексу.

[На початок]

Прапори трасування блокувань

Існує кілька прапорів трасування блокувань, які допоможуть налаштувати ваші блокування і виявити проблеми з мертвими блокуваннями.
Прапори трасування використовуються, щоб включати або вимикати певну поведінку в SQL Server. Ви можете встановити прапор трасування, використовуючи DBCC TRACEON, і якщо ви хочете, щоб прапор трасування включався при кожному запуску SQL Server, то ви можете визначити його як стартовий параметр, використовуючи '-T'.

1200: Показує всі блокування по всьому соедіненіям.Очень об'ємний результат, я рекомендую використовувати його в контрольованому середовищі, де працює лише один виклик.

1204: Показує безліч інформації про учасників мертвої блокування. Ось приклад, покращений спеціально для вас.

KEY: означає, що ресурс, заблокований мертвої блокуванням, є ключем індексу. Цей ресурс також може бути будь-яким іншим типом блокування, таким, як сторінка, RID, таблиця, і т.д.

ECID узятий з master.dbo.sysprocesses. Він використовується для розрізнення блокувань, накладених різними потоками. Режим - це режим блокування, що призвела до мертвої блокування, такий, як S, X або U.

Рядок "6: 885578193: 2" означає: база даних з Id 6, об'єкт з Id 885578193, і індекс з Id 2. Число в дужках - це хешірованное значення, яке використовується, щоб визначити блокування. Ви можете побачити це в стовпці rsc_text таблиці master.dbo.syslockinfo. На жаль, це односторонній хеш, що означає, що ви не можете знайти заблоковану рядок тільки за допомогою хешірованного значення. Spid - це, звичайно, системний процес, який наклав блокування.
Щоб прочитати результат, потрібно спочатку подивитися на вузли 1 і 2, які показують вам блокування, вже дозволені і залучені в мертву блокування. Після цього ви побачите дві блокування в черзі очікування, помічені "Requested By:".

1205: Виводить інформацію про роботу менеджера блокувань. Кожен раз, коли активізується пошук мертвих блокувань, цей прапор трасування змушує менеджера блокувань виводити інформацію про пошук. Працює, тільки якщо встановлений прапор трасування 1204.

1211: Вимикає всі підняття рівня блокувань. Цей прапор трасування не дає менеджеру блокувань піднімати рівень блокувань, навіть якщо закінчуються слоти блокувань.

[На початок]

блокування стовпців

Як ви знаєте, найменшою одиницею блокування в SQL Server 2000 є блокування рядки. Блокування стовпців недоступна безпосередньо в SQL Server. Я покажу вам, як можна використовувати блокування індексу для імітації блокування рівня стовпчика.
Блокування стовпців часто вважаються занадто повільними для практичного використання, і SQL Server не є винятком. Але тому що блокування рядки не означає автоматично, що індекс таблиці заблокований, то ви завжди можете використовувати дані сторінок індексу, щоб працювати з заблокованими даними. Знову ж таки, я поясню це, використовуючи таблицю Region бази даних Northwind.
Таблиця Region - це таблиця-купа з двома стовпцями, RegionDescription і RegionId. Унікальний некластерние індекс існує на стовпці RegionId.

Зараз ми змінимо значення RegionDescription у одній з рядків за допомогою простої команди update, як показано нижче:

Щоб виконати цей запит, SQL Server не може використовувати індекс - індекс не покриває стовпець RegionDescription. Тому SQL Server використовує повне сканування таблиці, щоб знайти рядок, що вимагає зміни. Коли він знаходить цей рядок, він перетворює update блокування цього рядка в ексклюзивну блокування. Щоб переконатися в цьому, запустіть SP_LOCK в іншому вікні Query Analyzer. Ви повинні побачити ексклюзивну RID блокування на відповідному об'єкті. У тому ж вікні, в якому ви запустили SP_LOCK, ви можете виконати наступний select:

Якщо тільки ви не вказали READPAST або що не встановили рівень ізоляції транзакції як read uncommitted, то ви будете чекати, поки перше з'єднання не зніме блокування.
Це очікуваний результат. Але ось де несподіванка:

Цього разу ви чекати не будете. Якщо ви схожі на мене, то ви любите дивитися на план виконання, і саме план виконання пояснює, чому нам не довелося чекати.

Як бачите, SQL Server вибрав сканування індексу, щоб отримати потрібні дані для виконання запиту. І тому ви запросили інформацію, яка може бути надана читанням індексу, то SQL Server'у не потрібно читати дані з таблиці-купи. Цей тип запиту називається покриває запитом.
Існують два критерії для цього випадку. Перший - це те, що всі потрібні запитом дані містяться в індексі. Пам'ятайте, що якщо таблиця має кластерний індекс, то все некластерние індекси містять стовпці самого індексу і стовпці кластерного індексу.
Другий критерій - це те, що команда UPDATE не повинна змінювати будь-який з стовпців, що містяться в індексі. Якщо індекс змінюється, то він повинен бути заблокований ексклюзивно і цей трюк не спрацює.

[На початок]

Розширена таблиця сумісності блокувань

Доступні зараз в BOL і MSDN таблиці не надто повні в плані сумісності різних блокувань. Через це я включив у цю статтю повнішу таблицю. Думаю, ви знайдете її корисною.

Думаю, ви знайдете її корисною

[На початок]

висновок

Я дійсно знайшов вислизає intent update блокування, і ще багато чого, коли шукав її. Блокування і поведінку блокувань погано документовані в BOL, що зробило це дослідження набагато важчим, ніж це повинно було бути. Я дізнався багато нового під час написання це статті, і сподіваюся, що поділився частиною цих знань з вами.

[На початок]

коротка біографія

Andres Taylor намагається приділяти однакову увагу його улюбленим заняттям: вивчення SQL Server, Бразильському Джиу-Джитсу та його вродливу дружину Мімі. Він здав 20 іспитів MCP, є MCT і працює в Dotway в якості консультанта по .NET / SQL Server.

[На початок]

Розглядаючи цю таблицю, я здивувався - невже тут немає блокування Intent Update?
Звідки я це знаю?
Не пам'ятаєте точно, що таке intent блокування?
Коли ж SQL Server піднімає рівень блокувань?