Концепція блокувань в SQL Server

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

У SQL Server можна неформально визначити два процеси: обробник запитів і диспетчер блокувань Основним завданням диспетчера блокувань є якомога більш ефективне управління цілісністю транзакцій

Кожна блокування володіє трьома властивостями

■ Гранулярність, або розмір блокування

■ Режим, або тип блокування

■ Тривалість, або режим ізоляції блокування

Немає нічого неможливого в тому, щоб побачити блокування, і деякі прийоми можуть полегшити перегляд поточних блокувань До того ж змагання блокувань, тобто здатність різних типів блокувань допускати інші блокування, може несприятливо позначитися на продуктивності, якщо цього не розуміти і не управляти цим

Гранулярність блокувань

Фрагмент даних, контрольований блокуванням, може варіюватися від одного рядка до всієї бази даних (табл 512) Деякі комбінації блокувань, в залежності від їх гранулярності, можуть задовольняти вимогам співіснування

Таблиця 512 Гранулярність блокувань

Розмір блокування

Опис

Блокування рядка

Блокування одного рядка Найменша з можливих блокувань, оскільки SQL Server не блокує стовпці

Блокування сторінки

Блокування однієї сторінки розміром в 8 Кбайт На блокируемой сторінці може знаходитися одна або кілька рядків

Блокування екстента

Блокування восьми сторінок загальним обсягом в 64 Кбайт

Блокування таблиці

Блокування всієї таблиці

Блокування бази дан

Блокування всієї бази даних Це блокування, як правило, використовується при

вих

змінах схеми

Блокування ключа

Блокування вузлів індексу

Для збільшення продуктивності диспетчер підключень SQL Server намагається збалансувати кількість блокувань і їх розмір Основна боротьба йде між конкуренцією (малі блокування дозволяють організувати доступ до даних більшому числу транзакцій) і продуктивністю (менше число блокувань прискорює роботу сервера) Для досягнення цього балансу диспетчер блокувань динамічно заміщає один набір блокувань іншим

1 Блокування 25 рядків може бути розширена на блокування цілої сторінки

2 Якщо 25 заблокованих рядків розподілені по чотирьох сторінкам одного і того ж екстента, окремі блокування сторінок і цих 25 рядків можуть бути замінені блокуванням всього екстента, так як в блокировках задіяно більше 50% сторінок екстента

3 Якщо в блокуванні задіяно безліч екстентів, то весь цей набір блокувань може бути замінений блокуванням всієї таблиці

Динамічна блокування дає розробникам баз даних SQL Server масу переваг

■ Автоматично забезпечується оптимальний баланс між продуктивністю і конкуренцією, що не вимагає додаткового програмування

■ Продуктивність бази даних зберігається навіть при збільшенні її розмірів за рахунок автоматичного підвищення диспетчером гранулярності блокувань

■ Динамічні блокування спрощують адміністрування

Режими блокувань

Блокування мають не тільки гранулярность (або розмір), але також режим, що визначає їх призначення SQL Server має безліч режимів блокування (таких як загальна, ексклюзивна і блокування оновлення) Недостатнє розуміння режимів блокування призведе до розробки бази даних з поганою продуктивністю

Змагання блокувань

Взаємодія і сумісність блокувань грають життєво важливу роль у забезпеченні цілісності транзакцій в SQL Server і їх продуктивності Деякі режими блокування роблять неможливими інші нижче приведена повна таблиця сумісності блокувань (табл 513)

Таблиця 513 Сумісність блокувань

Блокування 1

IS

Блокування 2 запрошувати SU IX SIX

X

Навмисна загальна (Intent Shared, або IS)

Так

Так

Так

Так

Так

Так

Загальна (Shared, або S)

Так

Так

Так

Ні

Ні

Ні

Оновлення (Update, або U)

Так

Так

Ні

Ні

Ні

Ні

Навмисна ексклюзивна (Intent Exclusive, або IX)

Так

Ні

Ні

Так

Ні

Ні

Загальна з навмисно ексклюзивної (SIX)

Так

Ні

Ні

Ні

Ні

Ні

Ексклюзивна (X)

Ні

Ні

Ні

Ні

Ні

Ні

Загальна блокування (S)

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

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

Ексклюзивна блокування (X)

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

Блокування оновлення (U)

Назва блокування оновлення може ввести в деяку оману Вона не застосовується, коли транзакція виконує оновлення, – у цьому процесі використовується ексклюзивна блокування Насправді блокування поновлення означає підготовку транзакції до установки ексклюзивної блокування і виконання сканування даних для виявлення рядків, які необхідно блокувати Блокування оновлення можна трактувати як спільну яка готова перетворитися на ексклюзивну

Щоб уникнути виникнення взаімоблокіровок (про них ми поговоримо пізніше в цьому розділі) в конкретний момент часу тільки одна транзакція може утримувати блокування оновлення

Навмисні блокування

Навмисну ​​блокування можна уявити собі як жовтий сигнал світлофора, тобто попередження інших транзакцій про те, що має щось статися Головною метою навмисного блокування є підвищення продуктивності Так як навмисність використовується у всіх типах блокувань і у всіх гранулярності, SQL Server має кілька типів навмисних блокувань:

■ навмисна загальна блокування (IS)

■ навмисна ексклюзивна блокування (IX)

■ загальна з навмисною ексклюзивної блокуванням (SIX)

Навмисні блокування служать для того, щоб застовпити місце для загальної або ексклюзивної блокування, насправді не встановлюючи їх Таким чином вони дозволяють вирішити дві проблеми продуктивності: ієрархічну і постійну блокування

Якби не існувало навмисних блокувань і перша транзакція утримувала загальну блокування, а друга збиралася встановити ексклюзивну блокування таблиці, то останній довелося б перевіряти наявність блокувань таблиці, сторінок, екстентів, рядків і ключів

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

Навмисні блокування знімають додаткове навантаження з бази даних за рахунок виключення операцій перевірки наявності окремих блокувань на більш низькому рівні ієрархії даних – серверу достатньо перевірити наявність навмисного блокування на більш високому рівні Одночасна установка трьох блокувань зберігає згодом сотні пошуків блокувань іншими транзакціями

Навмисні блокування також виключають виникнення проблем, повязаних із змаганням загальних блокувань, – то що я називаю постійної блокуванням. Поки одна транзакція підтримує загальну блокування, інша транзакція не може встановити ексклюзивну Що трапиться, якщо хтось буде встановлювати загальну блокування кожні пять секунд і утримувати її на десять секунд, поки транзакція очікує ексклюзивної блокування У цьому випадку транзакція UPDATE теоретично може очікувати вічність У той же час, якщо транзакція встановила навмисну ​​ексклюзивну блокування (IX), ніяка інша транзакція не зможе встановити загальну блокування Навмисна ексклюзивна блокування не є в повній мірі ексклюзивної – це підготовка місця для установки ексклюзивної блокування

Блокування схеми (Sch-M, Sch-S)

Блокування схеми захищають схему бази даних SQL Server застосовує блокування стійкості схеми (Sch-S) під час будь-якого запиту, щоб уникнути застосування інструкцій мови визначення даних DDL

Блокування модифікації схеми (Sch-M) застосовується тільки тоді, коли SQL Server змінює фізичну схему бази даних Якщо SQL Server виконав операцію додавання стовпця таблиці тільки наполовину, блокування схеми запобіжить перегляд та модифікацію даних іншими транзакціями до завершення операції зміни схеми

Тривалість блокування

Третя властивість блокування – її тривалість – визначається рівнем ізоляції Чим суворіше ізоляція, тим довше утримується блокування SQL Server реалізує всі чотири перераховані вище рівня ізоляції транзакцій Абсолютний рівень ізоляції (serialization) створює найсуворіші блокування З іншого боку, самий слабкий рівень ізоляції (read uncommited) ефективно відключає блокування (табл 514)

Таблиця 514 Рівні ізоляції і тривалість блокування

Рівень ізоляції

Тривалість загальної блокування

Тривалість ексклюзивної блокування

Read Uncommited

Відсутня

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

Read Commited

Утримується під час читання даних

Утримується до підтвердження транзакції

Repeatable Read

Утримується до підтвердження транзакції

Утримується до підтвердження транзакції

Serializable

Утримується до підтвердження транзакції

Утримується до підтвердження транзакції Ексклюзивна блокування використовує і блокування ключа (також звану блокуванням діапазону) щоб уникнути вставок

Snapshot

Немає даних

Немає даних

Моніторинг блокувань

Через брак можливості переглянути блокування може здатися, що різні типи блокувань – чиста теорія На щастя, SQL Server є відносно відкритою середовищем, і в ній легко інспектувати поточні блокування

Утиліта Activity Monitor є прекрасним графічним інструментом про-Новинка ^ огляду і відстеження блокувань

2005

Використання Management Studio

У Management Studio можна переглянути транзакції обраної бази даних, використовуючи сторінку Summary, на яку поміщаються дані з динамічних уявлень управління Серед звітів, повязаних з транзакціями, наступні: All Transactions, All Blocking Transactions (рис 519), Top Transactions by Age, Top Transactions by Blocked Transaction Count Top Transactions by Lock Count, Resource Locking by Object і User Statistics

Використання Activity Monitor

Утиліта Activity Monitor (рис 5110) являє собою інструмент відстеження блокувань в розрізі процесів і обєктів Її вікно можна оновлювати вручну або автоматично кожні пять секунд Activity Monitor можна відкрити з вікна Object Explorer, вибравши в контекстному меню сервера пункт Managements SQL Server Logs

Рис 519 Сторінка Summary утиліти Management Studio відкриває простий спосіб перегляду ключової інформації про блокування транзакцій

Puc 5110 Утиліта Activity Monitor відображає масу інформації про поточні блокировках

Використання утиліти Profiler

SQL Server Profiler можна використовувати для перегляду блокованих процесів, використовуючи подія Error and Warnings: Blocked Process Report (рис 5111)

Puc 5111 SQL Server Profiler дозволяє здійснювати моніторинг блокувань і викликав їх коду в форматі XML

З безлічі доступних методів моніторингу блокувань Activity Monitor і сторінка Summary утиліти Management Studio є кращим способом визначення, коли блокування є джерелом проблем Для локалізації джерела проблеми Profiler пропонує перегляд фактичного коду транзакцій

Тонкість полягає в тому, що за замовчуванням це подія в Profiler відключено Для його включення необхідно конфігурувати параметр the blocked process threshold До того ж це додатковий параметр, який слід попередньо включити У наступному фрагменті коду встановлюється тривалість блокувань в одну секунду:

sp_c°nfigure show advanced options, 1

GO

RECONFIGURE

GO

sp_configure blocked process threshold1, 1

GO

RECONFIGURE

Результатом є повний опис всіх блокованих і блокуються процесів у форматі XML (рис 5111) Збереження цієї трасування у файлі і її подальший аналіз є прекрасною методикою налагодження блокувань

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*