Главная » Microsoft SQL Server, Базы данных » Управление блокировками в SQL Server

0

Если вы уже когда-либо писали схемы блокировок на других языках баз данных для преодоления недостатка блокировок (как я), то у вас могло остаться чувство, что обязательно нужно самому заниматься блокировками. Позвольте вас заверить, что диспетчеру блокировок можно полностью доверять. Тем не менее SQL Server предлагает несколько методов управления блокировками, о которых мы детально поговорим в этом разделе.

Не применяйте параметры блокировки и не изменяйте уровни изоляции случайным образом — доверьте менеджеру блокировок SQL Server выполнять балансировку конкуренции и целостности транзакций. Только если вы абсолютно уверены, что схема базы данных хорошо настроена, а программный код буквально отшлифован, можете слегка подкорректировать работу диспетчера блокировок, чтобы решить конкретную проблему. В некоторых случаях настройка запросов select на отсутствие блокировок способна решить большинство проблем.

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

Уровень изоляции определяет продолжительность общей или эксклюзивной блокировки подключения. Установка уровня изоляции оказывает влияние на все запросы и все таблицы, используемые на протяжении всего подключения или до явной замены одного уровня изоляции другим. В следующем примере устанавливается более плотная изоляция, чем задана по умолчанию, и предотвращаются неповторяющиеся чтения:

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

?                                                                                        read uncommited   ? serializable

?                                                                                        read commited       ? snapshot

?               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, выждите пару секунд и попробуйте повторить транзакцию снова.

Использование параметров блокировки

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

Таблица 51.5. Параметры блокировки

Параметр

блокировки

Описание

ReadUnCommi ted

Уровень изоляции. He устанавливает и не удерживает блокировку. Равносилен отсутствию блокировок

ReadCommited

Уровень изоляции, установленный для транзакций по умолчанию

RepeatableRead

Уровень изоляции. Удерживает общую и эксклюзивную блокировки до момента подтверждения транзакции

Serializable

Уровень изоляции. Удерживает общую блокировку до завершения транзакции

ReadPast

Пропуск заблокированных строк вместо ожидания

RowLock

Включение блокировки на уровне строк вместо уровня страницы, экстента или таблицы

PagLock

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

TabLock

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

Параметр

блокировки

Описание

 

NoLock

Неприменение и неудержание блокировок. То же, что и ReadUnCommited

 

TablockX

Включение эксклюзивной блокировки таблицы. Запрет другим транзакциям работать с таблицей

 

HoldLock

Удержание общей блокировки до подтверждения транзакции (аналогично Serializable)

 

Updlock

Использование блокировки обновления вместо общей и ее удержание. Блокировка других записей в данные между изначальными операциями чтения и записи

 

Xlock

Удержание эксклюзивной блокировки данных до подтверждения транзакции

 

В следующем примере в предложении 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

‘ProductCategory.PK__ 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 < 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 (рис. 51.12). Первая и вторая транзакции пытаются обновить одни и те же строки, однако в противоположном порядке. Используя третье окно для запуска процедуры pGetLocks, можно выполнять мониторинг блокировок.

1.              Создайте в редакторе запросов второе окно.

2.              Поместите код блока Шаг 2 во второе окно.

3.              В первое окно поместите код блока Шаг 1 и нажмите клавишу <F5>.

4.              Во втором окне аналогично выполните код Шаг 2.

5.              Вернитесь в первое окно и выполните код блока Шаг 3.

6.              Через короткий промежуток времени SQL Server обнаружит взаимоблокировку и автоматически устранит ее.

Ниже приведен программный код примера.

– Транзакция 1 — Шаг 1 USE OBXKites BEGIN TRANSACTION UPDATE Contact

SET LastName = ‘Jorgenson’

WHERE ContactCode = 401′

Puc. 51.12. Создание ситуации взаимоблокировки в 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 (рис. 51.13):

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)

Рис. 51.13. SQL Server Profiler позволяет выполнять мониторинг взаимоблокировок с помощью события Locks:Deadlock Graph и выявлять ресурс, вызвавший взаимоблокировку

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

Как было продемонстрировано в приведенном выше коде, SQL Server автоматически выявляет ситуацию взаимоблокировки, проверяя блокирующие процессы и откатывая транзакции,

выполнившие наименьший объем работы. SQL Server постоянно проверяет существование перекрестных блокировок. Задержка выявления взаимоблокировок может варьироваться от нуля до двух секунд (на практике дольше всего мне приходилось ожидать этого пять секунд).

Обработка взаимоблокировок

Когда происходит взаимоблокировка, подключение, выбранное в качестве “жертвы” взаимоблокировки, должно повторить свою транзакцию. Так как работа должна быть переделана, хорошо, что откатывается именно та транзакция, которая успела выполнить наименьший объем работы, — именно она будет повторена сначала.

Ошибка с кодом 12 05 должна перехватываться клиентским приложением, которое и должно перезапускать транзакцию. Если все происходит как нужно, пользователь даже не заподозрит о том, что произошла взаимоблокировка.

Вместо того чтобы позволять самому серверу решать, какую из транзакций выбирать на роль “жертвы”, самой транзакции можно “сыграть в поддавки”. Следующий код, будучи помещенным в транзакцию, информирует SQL Server о том, что в случае возникновения взаимоблокировки данную транзакцию следует откатить:

SET DEADLOCKJPRIORITY LOW

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

Даже несмотря на то, что взаимоблокировки легко выявить и обработать, лучше все-таки их избегать. Приведенные ниже рекомендации помогут вам избежать взаимоблокировок.

?               Старайтесь делать транзакции короткими и не содержащими излишнего кода. Если некоторый код не обязательно должен присутствовать в транзакции, он должен быть выведен из нее.

?               Никогда не ставьте код транзакции в зависимость от ввода пользователя.

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

?               Планируйте физическую схему так, чтобы хранить одновременно отбираемые данные как можно ближе на страницах данных. Для этого используйте нормализацию и продуманно выбирайте кластеризованные индексы. Уменьшение разброса блокировок поможет избежать их эскалации. Небольшие блокировки помогут избежать их конкуренции.

?               Не увеличивайте уровень изоляции, если в этом нет необходимости. Более строгий уровень изоляции увеличивает продолжительность блокировок.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии