|
|
Публикувано в 20.07.2016 г. 12:37:53 ч.
|
|
|
Преглед на шлюзовете
1. Защо да се въвеждат ключалки
Когато няколко потребители извършват едновременни операции в базата данни едновременно, възникват следните несъответствия в данните:
Липсващи актуализации
Двама потребители, А и Б, четат едни и същи данни и ги модифицират, а резултатът от промяната на единия потребител унищожава резултата от другата модификация, като например системата за резервация на билети
Мръсно четиво
Потребител А променя данните, след което потребител Б ги чете, но по някаква причина потребител А отменя промяната на данните и данните се връщат към първоначалната си стойност
Не четете многократно
Потребител А чете данните, а след това потребител Б ги чете и ги модифицира
Основният метод за контрол на конкурентността е блокирането, което е да се забрани на потребителите да извършват определени операции за определен период от време, за да се избегнат несъответствия в данните
2. Класификация на шлюзовете
Има две дивизии на категории шлюзове:
1 . От гледна точка на системата с бази данни: тя е разделена на ексклузивни ключалки (т.е. ексклузивни ключалки), споделени ключалки и обновяващи ключалки
MS - SQL Server използва следните шаблони на заключване на ресурси.
Описание на режима на заключване
Share(s) се използват за операции, които не променят или обновяват данни (операции само за четене), като например SELECT оператори.
Актуализация (U) се използва в обновяеми ресурси. Предотвратява често срещани форми на блокиране, когато се четат, заключват множество сесии и евентуално може да се случи обновяване на ресурса.
Exclusive (X) се използва за операции по модификация на данни, като INSERT, UPDATE или DELETE. Уверете се, че не се извършват множество актуализации на един и същ ресурс едновременно.
Заключванията на намерението се използват за установяване на йерархия от ключалки. Видовете заключвания за намерение са: Споделено намерение (IS), Изключително намерение (IX) и Изключително намерение (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(S) на страница или ред в таблицата. Поставянето на заключване на намерение на ниво маса предотвратява последващо получаване на ексклузивна (X) заключване на друга транзакция върху таблицата, която съдържа тази страница. Заключванията на намерение могат да подобрят производителността, тъй като SQL Server проверява заключването на намерение само на ниво таблица, за да определи дали транзакцията може безопасно да получи заключване върху тази таблица. Вместо да проверяват заключванията на всеки ред или страница в таблицата, за да се определи дали една транзакция може да заключи цялата таблица.
Заключванията за намерение включват споделяне на намерение (IS), ексклузивно намерение (IX) и ексклузивно споделяне на намерение (SIX).
Описание на режима на заключване
Споделянето на намерение (IS) показва, че намерението на транзакцията е част от основните ресурси в йерархията на четене чрез поставяне на S-заключвания върху всеки ресурс.
Intent Exclusive (IX) показва, че целта на транзакцията е да се модифицират някои, но не всички, основни ресурси в йерархията, като се постави X-lock на всеки ресурс. IX е надмножество на IS.
Ексклузивно споделяне с намерение (SIX) показва, че целта на транзакцията е да се прочете всички основни ресурси в йерархията и да се модифицират някои, но не всички, от тях, чрез поставяне на IX заключвания върху всеки ресурс. Позволете едновременно заключване на IS-и върху ресурси на най-високо ниво. Например, заключването на SIX в таблицата поставя SIX заключване на таблицата (позволявайки едновременно заключване на IS) и IX заключване на текущо модифицираната страница (X заключване на модифицирания ред). Докато всеки ресурс може да има само едно заключване на SIX за определен период от време, за да се предотврати актуализирането на ресурса от други транзакции, други транзакции могат да четат основните ресурси в йерархията, като придобиват IS заключвания на ниво таблица.
Exclusive lock: Само програмата, която извършва операцията за заключване, може да я използва, а всички други операции върху нея няма да бъдат приети. Когато изпълните команда за обновяване на данни, SQL Server автоматично използва изключителна заключваща система. Когато съществуват други заключвания върху даден обект, не можете да добавите изключителна ключалка към него.
Споделена заключваща връзка: Ресурсът, заключен от споделената заключваща връзка, може да бъде прочетена от други потребители, но други потребители не могат да го променят.
Update lock: Когато SQL Server е готов да актуализира данните, първо заключва обекта с данни, така че данните да не могат да бъдат променяни, но да могат да бъдат прочетени. Когато SQL Server определи, че иска да обнови данните, автоматично ще замени заключването за актуализация с изключителна заключване и не може да добави заключване за актуализация, когато има други заключвания върху обекта.
2 . От гледна точка на програмиста: тя е разделена на оптимистично заключване и песимистично заключване.
Optimism Lock: Разчита изцяло на базата данни за управление на работата на ключалката.
Песимистични заключвания: Програмистите управляват обработката на заключване на данни или обекти сами.
MS - SQLSERVER използва заключвания, за да реализира песимистичен контрол на конкурентността между множество потребители, които извършват модификации в базата данни едновременно
3. Размерът на частицата на заключването
Грануларността на заключването е размерът на блокираната мишена, малката блокираща грануларност е висока конкурентност, но надразходът е голям, а голямото блокиране е ниско съвпадане, но натоварването е малко
SQL Server поддържа детайлност на заключване за редове, страници, ключове, диапазони от ключове, индекси, таблици или бази данни
Описание на ресурса
RID идентификатор на реда. Използвах се за заключване на ред в маса поотделно.
Заключване на ключовия ред в индекса. Използва се за защита на диапазона от ключове в сериализирани транзакции.
8 килобайта (KB) страници с данни или индексни страници.
Разширен диск Набор от осем съседни страници с данни или индексни страници.
Таблица Цялата таблица, включваща всички данни и индекси.
DB база данни.
4. Продължителността на времето за заключване
Продължителността на задържане на шлюз е времето, необходимо за защита на ресурса на поисканото ниво.
Времето за задържане на споделената заключваща система, използвана за защита на операциите по четене, зависи от нивото на изолация на транзакцията. При стандартното ниво на изолация на транзакции READ COMMED, споделената заключване се контролира само за продължителността на четената страница. При сканиране заключването не се освобождава, докато не бъде получено на следващата страница в сканирането. Ако зададете HOLDLOCK подсказка или зададете нивото на изолация на транзакцията на REPEATABLE READ или SERIALIZABLE, заключването не се освобождава, докато транзакцията не приключи.
В зависимост от опцията за паралелност, зададена за курсора, курсорът може да получи заключване на свитъка в споделен режим, за да защити екстракта. Когато е необходима заключване на скрол, заключването на скрола не се освобождава до следващия път, когато курсорът бъде извлечен или затворен, което се случи първо. Въпреки това, ако посочите HOLDLOCK, scroll lock не се освобождава до края на транзакцията.
Ексклузивната ключалка, използвана за защита на актуализацията, няма да бъде освободена до края на транзакцията.
Ако една връзка се опита да получи заключване, което влиза в конфликт с заключване, контролирано от друга връзка, връзката, която се опитва да я получи, ще бъде блокирана, докато:
Конфликтният катинар се освобождава и връзката получава искания катинар.
Таймаутът на връзката изтече. По подразбиране няма интервал за тайм-аут, но някои приложения задават интервали, за да предотвратят безсрочно чакане
Пет персонализирания на заключвания в SQL Server
1 Обработване на задни заключения и задаване на приоритети за заключване
Блокирането е безкрайното чакане, причинено от множество потребители, кандидатстващи за различни блокове, защото кандидатът има право на част от блокирането и чака частично блокиране, собственост на други потребители
Можеш да използваш SET DEADLOCK_PRIORITY, за да контролираш как сесията реагира при състояние на задънена ситуация. Ако и двата процеса заключат данните и всеки процес не може да освободи своя заключване, докато другият процес не освободи своя заключване, възниква ситуация на блокиране.
2 Обработване на таймаутите и задайте продължителност на заключването.
@@LOCK_TIMEOUT Връща текущата настройка за тайм-аут за текущата сесия в милисекунди
Настройката SET LOCK_TIMEOUT позволява на приложението да зададе максималното време, през което операторът чака, за да блокира ресурса. Когато времето за изчакване на оператора е по-голямо от настройката за LOCK_TIMEOUT, системата автоматично отменя блокиращото изявление и връща на приложението съобщение за грешка 1222, че периодът на изтичане на заявката за заключване е надишен
пример
В следващия пример периодът на заключване е зададен на 1 800 милисекунди.
СЕТ 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:
Update table set 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) Ексклузивен катинар
Създай две нови връзки
Изпълнете следното изявление при първата връзка
Започни TRAN
Актуализация на таблица 1
множество A= ' aa '
където B= ' b2'
чакай за забавяне' 00:00:30' -чакай 30 секунди
Commit tran
Изпълнете следното изявление във втората връзка
Започни TRAN
Изберете *от таблица1
където B= ' b2'
Commit tran
Ако горните две оператори се изпълняват едновременно, заявката select трябва да изчака обновяването, тоест да изчака 30 секунди
2) Споделен катинар
Изпълнете следното изявление при първата връзка
Започни TRAN
изберете *от таблица1 holdlock - Holdlock се добавя изкуствено към заключването
където B= ' b2'
чакай за забавяне' 00:00:30' -чакай 30 секунди
Commit tran
Изпълнете следното изявление във втората връзка
Започни TRAN
изберете A,C от таблица 1
където B= ' b2'
Актуализация на таблица 1
множество A= ' aa '
където B= ' b2'
Commit tran
Ако горните две оператори се изпълняват едновременно, заявката select във втората връзка може да бъде изпълнена
Актуализацията трябва да изчака първата транзакция, за да освободи споделеното заключване и да я превърне в ексклузивна, преди да може да бъде изпълнена, тоест да изчака 30 секунди
3) Задънена ситуация
Добавена таблица 2(D,E)
Д Е
d1 e1
d2 e2
Изпълнете следното изявление при първата връзка
Започни TRAN
Актуализация на таблица 1
множество A= ' aa '
където B= ' b2'
чакай забавяне' 00:00:30'
Таблица за актуализация 2
набор D= ' d5 '
където E= 'e1'
Commit tran
Изпълнете следното изявление във втората връзка
Започни 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 неангажирани транзакции пропуска само RID заключвания, не страници, зони и таблици
ROWLOCK налага използването на rowlock
TABLOCKX налага използването на изключителна заключване на ниво таблица, което предотвратява използването на таблицата от друга транзакция по време на транзакцията
UPLOCK принуждава използването на актуализации при четене на таблица без споделена заключване
Заключване на приложението:
Заключването на приложението е заключване, генерирано от клиентски код, а не заключване, генерирано от самия SQL Server
Два процеса за обработка на заключване на приложения
sp_getapplock Заключване на ресурси за приложения
sp_releaseapplock Отключване на ресурсите на приложението
Забележка: Разликата между заключването на таблица в база данни
SELECT *FROM TABLE WITH( ЗАДЪРЖАНЕ ) Други транзакции могат да четат таблицата, но не могат да актуализират и изтрият
SELECT *FROM TABLE WITH(TABLOCKX) Други транзакции не могат да четат, актуализират и изтриват таблицата
|
Предишен:Нямаше крайна точка, която да слуша http://localhost:111/xxx.svc този к...Следващ:SQL заключва NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
