Управління блокуваннями в SQL Server

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

Чи не застосовуйте параметри блокування і не змінюйте рівні ізоляції випадковим чином – довірте менеджеру блокувань SQL Server виконувати балансування конкуренції та цілісності транзакцій Тільки якщо ви абсолютно впевнені, що схема бази даних добре налаштована, а програмний код буквально відшліфований, можете злегка підкоригувати роботу диспетчера блокувань, щоб вирішити конкретну проблему У деяких випадках настройка запитів select на відсутність блокувань здатна вирішити більшість проблем

Установка рівня ізоляції підключення

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

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Допустимими рівнями ізоляції є:

■&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp read uncommited   ■ serializable

■&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp read commited       ■ snapshot

■&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp repeatable read

Поточний рівень ізоляції можна перевірити за допомогою команди перевірки цілісності бази даних (DBCC):

DBCC USEROPTIONS

Результати будуть наступними (скорочено):

Set Option        Value

isolation level repeatable read

Рівні ізоляції можуть бути також встановлені на рівні запиту або таблиці за допомогою параметрів блокування

Використання ізоляції рівня знімків бази даних

Існують два варіанти рівня ізоляції знімків бази даних: snapshot і read commited snapshot Ізоляція snapshot працює подібно repeatable read, не займаючись питаннями блокування Ізоляція read commited snapshot імітує встановлений за умовчанням в SQL Server рівень read commited, так само знімаючи питання блокування

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

ефективно відстежує версійність рядків в базі Версіонност рядків – це технологія, яка створює для оновлення копії рядків у базі даних TempDB Крім основної завантаження бази TempDB, версійність рядків також додає 14-байтовий ідентифікатор рядка

Використання ізоляції Snapshot

У наступному фрагменті включається рівень ізоляції snapshot Для коригування бази даних і включення рівня ізоляції snapshot до цієї бази не повинні бути встановлені інші підключення

USE Aesop

ALTER DATABASE Aesop

SET ALLOW_SNAPSHOT_ISOLATION ON

| Для перевірки того, чи включена в базі даних ізоляція snapshot, виконайте SVS наступний запит: SELECT name, snapshot_isolation_state_desc FROM [* sysdatabases

Тепер перша транзакція починає читання і залишається відкритою (тобто не підтвердженої): USE Aesop

SET TRANSACTION ISOLATION LEVEL Snapshot

BEGIN TRAN SELECT Title FROM FABLE WHERE FablelD = 2

Буде отримано наступний результат:

Title

The Bald Knight

У цей час друга транзакція починає оновлення того ж рядка, яка відкрита перша транзакцією:

USE Aesop

SET TRANSACTION ISOLATION LEVEL Snapshot

BEGIN TRAN UPDATE Fable

SET Title = Rocking with Snapshots

WHERE FablelD = 2

SELECT * FROM FABLE WHERE FablelD = 2

Результат наступний:

Title

Rocking with Snapshots

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

SELECT Title FROM FABLE WHERE FablelD = 2

Результат наступний:

Title

The Bald Knight

Якщо відкрити третю і четверту транзакції, то вони побачать все те ж початкове значення The Bald Knight:

Навіть після того як друга транзакція підтвердить зміни, перша буде як і раніше бачити початкове значення, а все наступні транзакції – нове, Rocking with Snapshots

Використання ІЗОЛЯЦІЇ Read Commited Snapshot

Ізоляція Read Commited Snapshot включається за допомогою аналогічного синтаксису:

ALTER DATABASE Aesop

SET READ_COMMITTED_SNAPSHOT ON

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

Так як Read Commited є рівнем ізоляції, прийнятим в SQL Server за замовчуванням, потрібно тільки установка параметрів бази даних

Вирішення конфліктів записи

Транзакції, записуючі дані при встановленому рівні ізоляції Snapshot, можуть бути заблоковані попередніми непідтвердженими транзакціями запису Таке блокування не змусить нову транзакцію очікувати – Просто буде згенерована помилка Для обробки подібних ситуацій використовуйте вираз try catch, почекайте пару секунд і спробуйте повторити транзакцію знову

Використання параметрів блокування

Параметри блокування дозволяють вносити тимчасову корекцію в статегію блокування У той час як рівень ізоляції впливає на підключення в цілому, параметри блокування специфічні для кожної таблиці в конкретному запиті (табл 515) Параметр WITH (параметр_блокіровкі) поміщається після імені таблиці в реченні FROM запиту Для кожної таблиці можна задати декілька параметрів, розділяючи їх комами

Таблиця 515 Параметри блокування

Параметр

блокування

Опис

ReadUnCommi ted

Рівень ізоляції He встановлює і не утримує блокування Рівносильний відсутності блокувань

ReadCommited

Рівень ізоляції, встановлений для транзакцій за замовчуванням

RepeatableRead

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

Serializable

Рівень ізоляції Утримує загальну блокування до завершення транзакції

ReadPast

Пропуск заблокованих рядків замість очікування

RowLock

Включення блокування на рівні рядків замість рівня сторінки, екстента або таблиці

PagLock

Включення блокування на рівні сторінок замість рівня таблиці

TabLock

Автоматична ескалація блокувань рівня рядків, сторінок і екстента до гранулярності рівня таблиці

Параметр

блокування

Опис

&nbsp

NoLock

Незастосування і неутримання блокувань Те ж, що і ReadUnCommited

&nbsp

TablockX

Включення ексклюзивної блокування таблиці Заборона іншим транзакцій працювати з таблицею

&nbsp

HoldLock

Утримання загальної блокування до підтвердження транзакції (аналогічно Serializable)

&nbsp

Updlock

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

&nbsp

Xlock

Утримання ексклюзивної блокування даних до підтвердження транзакції

&nbsp

У наступному прикладі в реченні FROM інструкції UPDATE використаний параметр блокування, що забороняє диспетчеру ескаліровать гранулярность блокування:

USE OBXKites UPDATE Product

FROM Product WITH (RowLock)

SET ProductName = ProductName + Updated1

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

Обмеження блокувань рівня індексів

Рівні ізоляції і параметри блокування застосовуються на рівні підключень і запитів Єдиним способом управління блокуваннями на рівні таблиці є обмеження гранулярності блокувань на основі конкретних індексів За допомогою системної збереженої процедури sp_indexoption блокування рядків і / або сторінок можна відключити для конкретного індексу, використовуючи наступний синтаксис: sp_indexoption імя_індексу1 ,

AllowRowlocks або AllowPagelocks,

1 або Про

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

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

Наступна команда конфігурує таблицю ProductCategory як рідко оновлюваний класифікатор Спочатку команда sp_help виводить імя індексу первинного ключа таблиці: sp_help ProductCategory

Результат (усічений) такий:

index                                              index                         index

name                                               description keys

PK_____________ ProductCategory                79A814 03 nonclustered, ProductCategorylD

unique, primary key located on PRIMARY

Маючи в наявності реальне імя первинного ключа, системна збережена процедура може встановити параметри блокування індексу:

EXEC sp_indexoption

‘ProductCategoryРК__ ProductCategory_______ 7 9А814 03,

‘AllowRowlocks, FALSE EXEC sp_indexoption

‘ProductCategoryPK__ ProductCategory_______ 79A81403,

‘AllowPagelocks, FALSE

Управління часом очікування блокувань

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

На щастя, ви можете встановити час очікування блокування за допомогою параметра підключення set lock_timeout Встановіть для цього параметра кількість мілісекунд або, якщо хочете не обмежувати час, встановіть для нього значення -1 (воно прийнято за замовчуванням) Якщо для цього параметра встановлено значення 0, то транзакція буде негайно відхилена при наявності будь-якої блокування У цьому випадку програму буде виключно швидкодіючим, але малоефективним

У наступному запиті час очікування блокування встановлюється у дві секунди (2000 мілісекунд):

SET Lock_Timeout 2 00 0

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

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

Оцінка продуктивності конкуренції в базі даних

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

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

перегляд користувачем даних та їх оновлення Хороший тест повинен запускати 20 примірників сценарію, який постійно завантажує базу даних, а потім дати можливість команді тестувальників використовувати додаток Кількість блокувань допоможе побачити монітор продуктивності, про який говорилося в главі 49

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

Блокування додатки

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

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

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

Блокування додатків може застосовуватися у транзакціях при цьому може бути оголошений режим блокування Shared, Update, Exclusive, IntentExclusice або IntentShared Що повертається процедурою значення вказує, успішним чи було застосування блокування

■ 0 Блокування встановлена ​​успішно

■ 1 Блокування була встановлена, коли інша процедура зняла свою блокування

■ -1 Запит на блокування не був реалізований через перевищення часу очікування

■ -2 Запит на блокування не був реалізований з причини скасування

■ -3 Запит на блокування не був реалізований через взаимоблокировки

■ -999 Блокування не була встановлена ​​з іншої причини

Збережена процедура sp_ReleaseApLock знімає блокування У наступному прикладі продемонстровано, як блокування додатку може використовуватися в пакеті чи процедурою: DECLARE @ ShareOK INT EXEC @ ShareOK = sp_GetAppLock

@Resource = CableWorm,

@LockMode = Exclusive

IF @ShareOK &lt 0

… Код обробки помилки

… Програмний код ..

EXEC sp_ReleaseAppLock @Resource = CableWorm

Go

Коли блокування додатки проглядаються за допомогою Management Studio або процедури sp_Lock, вони відображаються з типом АРР У наступному лістингу приведений скорочений висновок процедури sp_Lock, запущеної одночасно з наведеним вище кодом: spid dbid Objld Indld Type Resource Mode Status

57 8        0                       0                       APP Cabllf 94cl36 X                                                        GRANT

Слід звернути увагу на два невеликих відмінності в тому, як блокування додатки обробляються в SQL Server:

■ взаимоблокировки не виявляються автоматично

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

Взаимоблокировки

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

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

Раніше взаимоблокировки представляли собою серйозну проблему, але тепер SQL Server дозволяє успішно вирішити її

Створення взаимоблокировки

Найпростіше створити ситуацію взаимоблокировки в SQL Server за допомогою двох підключень в редакторі запитів утиліти Management Studio (мал 5112) Перша і друга транзакції намагаються оновити одні й ті ж рядки, проте в протилежному порядку Використовуючи третє вікно для запуску процедури pGetLocks, можна виконувати моніторинг блокувань

1 Створіть у редакторі запитів друге вікно

2 Помістіть код блоку Крок 2 в друге вікно

3 У перше вікно помістіть код блоку Крок 1 і натисніть клавішу

4 У другому вікні аналогічно виконайте код Крок 2

5 Поверніться в перше вікно і виконайте код блоку Крок 3

6 Через короткий проміжок часу SQL Server виявить взаємоблокування і автоматично усуне її

Нижче наведено програмний код прикладу

– Транзакція 1 – Крок 1 USE OBXKites BEGIN TRANSACTION UPDATE Contact

SET LastName = Jorgenson

WHERE ContactCode = 401

Puc 5112 Створення ситуації взаимоблокировки в Management Studio за допомогою двох підключень (їх вікна розташовані вгорі)

Тепер перша транзакція встановила ексклюзивну блокування на запис із значенням 101 в поле ContactCode Друга транзакція встановить ексклюзивну блокування рядки зі значенням 1001 в поле ProductCode, а потім спробує ексклюзивно заблокувати запис, вже заблоковану перший транзакцією (ContactCode = 101)

– Транзакція 2 – Крок 2 USE OBXKites BEGIN TRANSACTION UPDATE Product SET ProductName

= DeadLock Repair Kit

WHERE ProductCode = 1001

UPDATE Contact

SET FirstName = Neals

WHERE ContactCode = 101

COMMIT TRANSACTION

Поки ще взаимоблокировки не існує, оскільки транзакція 2 очікує завершення транзакції 1, але транзакція 1 поки що не очікує завершення транзакції 2 У цій ситуації, якщо транзакція 1 завершить свою роботу і виконає інструкцію COMMIT TRANSACTION, ресурс даних буде звільнений, і транзакція 2 благополучно отримає можливість необхідної їй блокування і продовжить свої дії

Проблема виникає, коли транзакція 1 спробує оновити рядок з ProductCode = l Однак необхідну для цього ексклюзивну блокування вона не отримає, оскільки ця запис заблокований транзакцією 2:

– Транзакція 1 – Крок 3 UPDATE Product SET ProductName

= DeadLock Identification Tester

WHERE ProductCode = 1001

COMMIT TRANSACTION

Транзакція 1 поверне наступне текстове повідомлення про помилку через пару секунд Виниклу взаємоблокування можна також побачити в SQL Server Profiler (рис 5113):

Server: Msg 1205, Level 13,

State 50, Line 1 Transaction (Process ID 51) was

deadlocked on lock resources with another process and has been chosen as the deadlock victim Rerun the transaction

Транзакція 2 завершить свою роботу, нібито проблеми і не існувало:

(1 row(s) affected)

(1 row(s) affected)

Рис 5113 SQL Server Profiler дозволяє виконувати моніторинг взаімоблокіровок за допомогою події Locks: Deadlock Graph і виявляти ресурс, що викликав взаємоблокування

Автоматичне виявлення взаімоблокіровок

Як було продемонстровано в наведеному вище коді, SQL Server автоматично виявляє ситуацію взаимоблокировки, перевіряючи блокуючі процеси і відкочуючись транзакції,

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

Обробка взаімоблокіровок

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

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

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

SET DEADLOCKJPRIORITY LOW

Мінімізація взаімоблокіровок

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

■ Намагайтеся робити транзакції короткими і не містять зайвого коду Якщо деякий код не обовязково повинен бути присутнім в транзакції, він повинен бути виведений з неї

■ Ніколи не ставте код транзакції в залежність від введення користувача

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

■ Плануйте фізичну схему так, щоб зберігати одночасно відбираються дані якомога ближче на сторінках даних Для цього використовуйте нормалізацію і продумано вибирайте Групові індекси Зменшення розкиду блокувань допоможе уникнути їх ескалації Невеликі блокування допоможуть уникнути їх конкуренції

■ Чи не збільшуйте рівень ізоляції, якщо в цьому немає необхідності Більш суворий рівень ізоляції збільшує тривалість блокувань

Джерело: Нільсен, Пол 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>

*

*