|
|
Опубліковано 20.07.2016 12:37:53
|
|
|
Огляд шлюзів
1. Навіщо вводити замки
Коли кілька користувачів одночасно виконують операції над базою даних, виникають такі невідповідності даних:
Відсутні оновлення
Два користувачі, A і B, читають одні й ті ж дані та змінюють їх, і результат зміни одного користувача знищує результат іншої модифікації, наприклад, системи бронювання квитків
Брудне читання
Користувач A змінює дані, потім користувач B їх зачитує, але користувач A з якоїсь причини скасовує зміну даних, і дані повертаються до свого початкового значення
Не читайте знову і знову
Користувач А зчитує дані, а потім користувач B зчитує їх і змінює їх
Основним методом контролю паралелізму є блокування, тобто заборона користувачам виконувати певні операції протягом певного часу, щоб уникнути невідповідностей даних
2. Класифікація шлюзів
Існує два поділи на категорії шлюзів:
1 . З точки зору системи баз даних: вона поділяється на ексклюзивні замки (тобто ексклюзивні замки), спільні замки та оновлені замки
MS - SQL Server використовує такі шаблони блокування ресурсів.
Опис режиму замка
Share(s) використовуються для операцій, які не змінюють і не оновлюють дані (операції лише для читання), таких як оператори SELECT.
Оновлення (U) використовується в оновлених ресурсах. Запобігає поширеним типам глухих кутів при читанні, блокуванні кількох сесій і, можливо, оновлення ресурсів.
Ексклюзивний (X) використовується для операцій модифікації даних, таких як INSERT, UPDATE або DELETE. Переконайтеся, що не виконується кілька оновлень на одному й тому ж ресурсі одночасно.
Замки наміру використовуються для встановлення ієрархії замків. Типи замків на намір такі: Intent Shared (IS), Intent Exclusive (IX) та Intent Exclusive (SIX).
Схемні блокування використовуються при виконанні операцій, що залежать від схеми таблиці. Типи схемних замків такі: модифікація схеми (Sch -M) та стабільність схеми (Sch -S).
Масові оновлення (BU) використовуються, коли великі обсяги даних копіюються до таблиці та задається підказка TABLOCK.
Спільні замки
Спільний блок дозволяє одночасно зчитувати (SELECT) ресурс. Коли існує спільне (S) блокування ресурсу, жодна інша транзакція не може змінити ці дані. Знімайте спільне (S) блокування ресурсу одразу після прочитання даних, якщо рівень ізоляції транзакції не встановлений на повторюваний або вищий, або якщо спільне (S) блокування не зберігається з підказкою блокування протягом усього терміну служби транзакції.
Оновлення замка
Оновлення (U) блокування запобігає звичним блокуванням у звичному вигляді. Типовий патерн оновлення складається з транзакції, яка зчитує запис, отримує спільне (S) блокування ресурсу (сторінки або рядка), а потім змінює рядок, що вимагає конвертації блокування в ексклюзивне (X) блокування. Якщо дві транзакції отримують спільний режим блокування ресурсу і намагаються одночасно оновити дані, одна транзакція намагається конвертувати блокування в ексклюзивне (X) блокування. Перехід від спільного режиму до ексклюзивного блокування має почекати деякий час, оскільки ексклюзивне блокування однієї транзакції несумісне з блокуванням спільного режиму іншої; Відбувається очікування замка. Друга транзакція намагається отримати ексклюзивне (X) блокування для оновлення. Глухий кут виникає, оскільки обидві транзакції конвертуються в ексклюзивні (X) блокування, і кожна транзакція чекає, поки інша транзакція звільнить блокування спільного режиму.
Щоб уникнути цієї потенційної проблеми з глухим сітком, використовуйте оновлений (U) замок. Лише одна транзакція одночасно може отримати оновлений (U) замок для ресурсу. Якщо транзакція змінює ресурс, блокування оновлення (U) конвертується в ексклюзивне (X) блокування. Інакше замок перетворюється на спільний.
Ексклюзивні замки
Ексклюзивні (X) блокування запобігають доступу до одночасних транзакцій до ресурсів. Інші транзакції не можуть читати або змінювати дані, заблоковані ексклюзивним (X) блокуванням.
Фіксація наміру
Блокування наміру означає, що SQL Server має отримати спільний (S) або ексклюзивний (X) блокування на деякі базові ресурси в ієрархії. Наприклад, блокування наміру share-intent, встановлене на рівні таблиці, означає, що транзакція має намір встановити блокування share(S) на сторінці або рядку в таблиці. Встановлення блокування наміру на рівні таблиці запобігає подальшому отриманню іншою транзакцією ексклюзивного (X) блокування на таблиці, що містить цю сторінку. Блокування намірів може покращити продуктивність, оскільки SQL Server перевіряє блокування наміру лише на рівні таблиці, щоб визначити, чи може транзакція безпечно отримати блокування на цій таблиці. Замість того, щоб перевіряти блокування в кожному рядку або сторінці таблиці, щоб визначити, чи може транзакція заблокувати всю таблицю.
Замки з намірами включають Intent Sharing (IS), Intent Exclusive (IX) та Intent Exclusive Sharing (SIX).
Опис режиму замка
Спільне використання намірів (IS) вказує, що намір транзакції — це деякі, а не всі базові ресурси в ієрархії читання, накладаючи S-блокування на кожен ресурс.
Intent Exclusive (IX) вказує, що мета транзакції — змінити деякі, але не всі базові ресурси в ієрархії, накладаючи X-lock на кожен ресурс. IX — це надмножина IS.
Ексклюзивне спільне використання з наміром (SIX) означає, що мета транзакції — прочитати всі базові ресурси в ієрархії та змінити частину, але не всі, шляхом встановлення IX блокувань на кожен ресурс. Дозволити одночасні блокування IS-систем на ресурсах верхнього рівня. Наприклад, блокування SIX у таблиці розміщує блокування SIX на таблиці (що дозволяє паралельні блокування IS) і блокування IX на поточно зміненій сторінці (блокування X на зміненому рядку). Хоча кожен ресурс може мати лише одне блокування SIX протягом певного часу, щоб запобігти оновленню ресурсу іншими транзакціями, інші транзакції можуть зчитувати базові ресурси в ієрархії, отримуючи блокування IS на рівні таблиці.
Ексклюзивне блокування: Лише програма, яка виконує операцію блокування, має право використовувати його, а інші операції на ньому не приймаються. Коли ви виконуєте команду оновлення даних, SQL Server автоматично використовує ексклюзивне блокування. Коли на об'єкті існують інші замки, ви не можете додати до нього ексклюзивний замок.
Спільне блокування: Ресурс, заблокований спільним блокуванням, може бути прочитаний іншими користувачами, але інші користувачі не можуть його змінювати.
Блокування оновлення: Коли SQL Server готовий оновити дані, він спочатку блокує об'єкт даних, щоб дані не можна було змінити, але можна було прочитати. Коли SQL Server визначає бажання оновити дані, він автоматично замінює блокування оновлення на ексклюзивне і не може додати блокування оновлення, якщо на об'єкті існують інші блокування.
2 . З точки зору програміста: він поділяється на оптимістичний і песимістичний блокування.
Optimism Lock: повністю залежить від бази даних для керування роботою замка.
Песимістичні блокування: Програмісти керують обробкою замків на даних або об'єктах.
MS - SQLSERVER використовує блокування для реалізації песимістичного контролю конкурентності між кількома користувачами, які одночасно змінюють базу даних
3. Розмір частинки замка
Гранулярність блокування — це розмір заблокованої цілі, мале блокування — це висока паралельність, але верхні витрати великі, а велика блокувальна гранулярність — низька паралельність, але накладні витрати невеликі.
SQL Server підтримує деталізацію блокування для рядків, сторінок, ключів, діапазонів ключів, індексів, таблиць або баз даних
Опис ресурсу
Ідентифікатор рядка RID. Використовував для окремого блокування ряду в столі.
Блокування рядка ключів у індексі. Використовується для захисту діапазону ключів у серіалізованих транзакціях.
8 кілобайт (КБ) сторінок даних або індексних сторінок.
Розширений диск Набір із восьми суміжних сторінок даних або індексних сторінок.
Таблиця Вся таблиця з усіма даними та індексами.
База даних.
4. Тривалість часу утримання
Тривалість утримання замка — це час, необхідний для захисту ресурсу на запитуваному рівні.
Час утримання спільного блокування, що використовується для захисту операцій читання, залежить від рівня ізоляції транзакцій. З стандартним рівнем ізоляції транзакцій READ COMMED, спільне блокування контролюється лише протягом часу читання сторінки. Під час сканування замок не знімається, доки замок не буде отриманий на наступній сторінці сканування. Якщо ви вкажете запит HOLDLOCK або встановите рівень ізоляції транзакції на REPEATABLE READ або SERIALIZABLE, блокування не буде відкрито до завершення транзакції.
Залежно від опції конкурентності, встановленої для курсора, курсор може отримати замок сувоїв у спільному режимі для захисту екстракту. Коли потрібне блокування прокрутки, воно не відпускається до наступного вилучення або закриття курсора, залежно від того, що станеться раніше. Однак, якщо вказати HOLDLOCK, він не звільняється до кінця транзакції.
Ексклюзивне блокування, яке використовувалося для захисту оновлення, буде оприлюднене лише після завершення транзакції.
Якщо з'єднання намагається отримати замок, який конфліктує з замком, контрольованим іншим з'єднанням, з'єднання, яке намагається отримати цей замок, буде заблоковане, доки:
Конфліктний замок знімається, і з'єднання отримує запитуваний замок.
Тайм-аут з'єднання минув. За замовчуванням немає інтервалу тайм-ауту, але деякі додатки встановлюють інтервали тайм-ауту, щоб уникнути безстрокового очікування
П'ять налаштувань замків у SQL Server
1 Обробка глухих кутів і встановлення пріоритетів у глухих точках
Глухий кут — це нескінченне очікування, спричинене тим, що кілька користувачів подають заявки на різні блокування, оскільки заявник має частину права на блокування і чекає на часткове блокування, що належить іншим користувачам
Ви можете використовувати DEADLOCK_PRIORITY SET, щоб контролювати реакцію сесії у разі глухої ситуації. Якщо обидва процеси блокують дані, і кожен процес не може зняти власний блок, поки інший процес не розблокує свій власний блок, виникає ситуація глухого кута.
2 Обробляйте тайм-аути, встановлюйте тривалість блокування.
@@LOCK_TIMEOUT Повертає поточне налаштування тайм-ауту для поточної сесії у мілісекундах
Налаштування SET LOCK_TIMEOUT дозволяє застосунку встановити максимальний час, протягом якого оператор чекає на блокування ресурсу. Коли час очікування оператора більший за налаштування LOCK_TIMEOUT, система автоматично скасовує блокуючий оператор і повертає заяві повідомлення про помилку 1222 про перевищення періоду тайм-ауту запиту блокування
приклад
У наступному прикладі період тайм-ауту блокування встановлений на рівні 1800 мілісекунд.
SET LOCK_TIMEOUT1800
3) Встановити рівень ізоляції транзакцій.
4) Використовуйте підказки для блокування на рівні таблиці для операторів SELECT, INSERT, UPDATE та DELETE.
5) Налаштувати фіксуючу гранулярність індексу
Ви можете використати sp_indexoption системних збережених процедурах для встановлення гранулярності блокування для індексації
6. Перегляньте інформацію про замок
1 Виконати EXEC SP_LOCK повідомити інформацію про замок
2 Натисніть Ctrl + 2 у аналізаторі запитів, щоб побачити інформацію про замок
7. Заходи безпеки при використанні
Як уникнути глухих кутів
1. При використанні транзакцій намагайтеся скоротити логічний процес обробки транзакцій і достроково подавати або відкотувати транзакції.
2 Встановіть параметр тайм-ауту глухого блокування в розумному діапазоні, наприклад: 3 хвилини - 10 хвилин; Після цього операція автоматично припиняється, щоб уникнути зависання процесу;
3. Оптимізувати програму, перевірити та уникнути явища глухого кута;
4. Ретельно протестуйте всі скрипти та SP перед точною версією.
5 Усі SP повинні мати обробку помилок (через @error)
6 Не змінюйте стандартний рівень транзакцій SQL SERVER. Примусове блокування не рекомендується
Розв'яжіть проблему Як заблокувати базу даних таблиці рядків
8. Кілька запитань щодо замків
1 Як заблокувати рядок таблиці
ВСТАНОВИТИ РІВЕНЬ TRANSACTIONISOLATION READUNCOMMITTED
ВИБРАТИ *З таблиці rowlock, де id = 1
2 Заблокування таблиці в базі даних
ВИБРАТИ *ЗІ СТОЛУ З (УТРИМАТИ БЛОКУВАННЯ)
Заява про блокування:
sybase:
Оновлення таблиці COL1 = COL1, де 1= 0 ;
MSSQL:
Виберіть COL1 зі таблиці (Tablockx), де 1= 0 ;
oracle:
СТІЛ З БЛОКУВАННЯМ В ЕКСКЛЮЗИВНОМУ РЕЖИМІ;
Після того, як замок заблоковано, ніхто інший не може ним керувати, поки користувач не розблокує його, і він розблоковується за допомогою commit або rollback
Кілька прикладів допомагають поглибити враження
Встановити стіл1(A,B,C)
А Б В
A1 B1 C1
A2 B2 C2
a3 b3 c3
1) Ексклюзивний замок
Створіть два нових зв'язки
Виконайте наступний оператор у першому з'єднанні
Почни Тран
Оновлення таблиці 1
множина A= ' aa '
де B= ' b2 '
чекай затримки' 00:00:30' --чекай 30 секунд
Commit tran
Виконайте наступний оператор у другому з'єднанні
Почни Тран
Вибрати *з таблиці1
де B= ' b2 '
Commit tran
Якщо наведені вище два оператори виконуються одночасно, запит select повинен чекати на оновлення, тобто чекати 30 секунд
2) Спільний замок
Виконайте наступний оператор у першому з'єднанні
Почни Тран
вибрати *з таблиці 1 holdlock - Фіксація штучно додається до замка
де B= ' b2 '
чекай затримки' 00:00:30' --чекай 30 секунд
Commit tran
Виконайте наступний оператор у другому з'єднанні
Почни Тран
виберіть A,C зі таблиці 1
де B= ' b2 '
Оновлення таблиці 1
множина A= ' aa '
де B= ' b2 '
Commit tran
Якщо два вищезазначені оператори виконуються одночасно, можна виконати запит select у другому з'єднанні
Оновлення має чекати, поки перша транзакція звільнить спільне блокування і конвертує його в ексклюзивне блокування, перш ніж його можна виконати, тобто чекати 30 секунд
3) Глухий кут
Додано таблицю 2(D,E)
Д Е
d1 e1
d2 e2
Виконайте наступний оператор у першому з'єднанні
Почни Тран
Оновлення таблиці 1
множина A= ' aa '
де B= ' b2 '
чекай затримки' 00:00:30'
Оновлення таблиці 2
множина D= ' d5 '
де E= ' e1'
Commit tran
Виконайте наступний оператор у другому з'єднанні
Почни Тран
Оновлення таблиці 2
множина D= ' d5 '
де E= ' e1'
чекай затримки' 00:00:10'
Оновлення таблиці 1
множина A= ' aa '
де B= ' b2 '
Commit tran
Водночас система виявляє глухий кут і припиняє процес
Додам:
Підказки щодо блокування на рівні таблиці, що підтримуються SQL Server 2000
HOLDLOCK зберігає спільний замок до завершення всієї транзакції і повинен бути звільнений, щойно заблокований об'єкт не потрібен, що дорівнює рівню ізоляції СЕРІАЛІЗОВАНОЇ транзакції
Оператор NOLOCK виконується без видачі спільного блокування, що дозволяє здійснювати брудні читання, що дорівнює рівню ізоляції транзакції READ UNCOMMITTED
PAGLOCK використовує кілька сторінкових замків, де застосовується один замок для столу
READPAST дозволяє sql-серверу пропускати будь-які заблоковані рядки та виконувати транзакції, а для рівнів ізоляції READ UNCOMMITTED транзакцій пропускає лише блокування RID, а не блокування сторінок, зони та таблиць
ROWLOCK забезпечує застосування rowlock
TABLOCKX забезпечує використання ексклюзивного блокування на рівні таблиці, яке не дозволяє будь-яким іншим транзакціям використовувати таблицю під час транзакції
UPLOCK змушує використовувати оновлення при читанні таблиці без спільного блокування
Блокування додатку:
Блокування додатка — це блокування, згенероване клієнтським кодом, а не блокування, яке згенерує сам SQL Server
Два процеси для обробки блокування додатків
sp_getapplock Блокувати ресурси додатків
sp_releaseapplock Розблокування ресурсів додатку
Примітка: Різниця між блокуванням таблиці в базі даних
ВИБРАТИ *З ТАБЛИЦІ З (УТРИМАТИ БЛОКУВАННЯ) Інші транзакції можуть читати таблицю, але не можуть оновлювати та видаляти
ВИБРАТИ *З ТАБЛИЦІ З(TABLOCKX) Інші транзакції не можуть читати, оновлювати та видаляти таблицю
|
Попередній:Не було кінцевої точки, слухаючи http://localhost:111/xxx.svc цей к...Наступний:SQL блокує NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
