Настройка блокировок

Хотя SQL Server реализует блокирование автоматически, в приложениях эту функцию можно настроите отщм; способами;

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

задавая обработку тайм-аутов и определяя продолжительность тайм-аутаблокировки; устанавливая уровень изоляции транзакции;

назначая блокировки на уровне таблицы с операторами SELECT, INSERT, UPDATE и DELETE;

настраивая блокировки для индекса.

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

Взаимоблокировка возникает, когда два или более потоков, конкурирующих за ресурс, становятся взаимозависимыми. Взаимоблокировка возможна не только в РСУБД, но и в любой многопоточной системе, где поток способен захватить один или несколько ресурсов (например, блокировку). Если захватываемый ресурс в настоящее время принадлежит другому то первый поток ожидает освобождения целевого ресурса его владельцем.

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

Если пытается захватить другой ресурс, принадлежащий в настоящее

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

На рис. 12-1 показан пример, две пытаются получить доступ к данным

двух таблиц. Поток i i в котором работает транзакция I, владеет монопольной блокировкой таблицы Supplier. Поток Т2, в котором работает транзакция 2, получает монопольную блокировку Kiainiii.: Part, а затем пытается получить блокировку таблицы Supplier. Потранзакции 2 ается получить блокировку к как она уже при надлежит транзакции 1, поэтому транзакция 2 блокируется в ожидании транзакции 1. Транзакция в свою очередь, нуждается в блокировке таблицы Part, но не получает ее, поскольку таблица уже заблокирована транзакцией 2. Ни одна из транзакций не сможет освободить удерживаемые блокировки до фиксации или отката. Но транзакциям не удастся зафиксироваться или выполнить откат, поскольку для продолжения работы необходима блокировка, удерживаемая другой транзакцией.

Во избежание возникновения потерянных обновлений, необходимо испол-

нять на уровне изоляции чтение или боле высоком. Потерянные обновле-

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

ний исключено.

Транзакция 1

Supplier

Удерживает блокировку на Supplier

про- . iEiaHi

Необходима блокировка на Part

транзакция 2.

Необходима блокировка на Supplier

Рис. 12-1. Взаимоблокировка дву бращагошихся к таблицам Supplier и Part

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

транзакция просто блокируется, но не возникает.

Пото ТЕ показанный на рис зависит от потока Т2 в отношении ресурса, кото-

рым является блокировка на таблиц; Part. Точно также поток Т2 зависит от потока Т1 в отношении блокировки на таблице Supplier. Поскольку эти зависимости замкнуты в круг, имеет место взаимоблокировка потоков Т1 и Т2.

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

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

Чтобы минимизировать число взаимоблокировок, необходимо придерживаться следу-

правил:

не изменятьпорялок обращения к объектам; избегать взаимодействия с пользователем во время транзакции;

следить, чтобы были короткими и не выходили за пределы пакета;

низкие уровни использовать привязанные соединения.

Привязанные соединения позволяют двум (и соединениям совместно

использовать одну и ту же транзакцию и блокировку. Такие соединения способны одновременно работать с одними и теми же данными без конфликтов из-за блокировки. Привязанные соединения создают для нескольких соединений одного приложения или для нескольких приложений со своими соединениями. Помимо прочего привязка облегчает координацию действий нескольких соединений. Чтобы получить более полные сведения о привязанных соединениях, обратитесь к SQE ScrvErBools Online.

Настройка тайм-аутов

Когда SQL Server не может предоставить транзакции блокировку для какого-либо ресурса, поскольку эта блокировка уже занята другой транзакцией, первая блоки-

Ззишив 2 Управление транзакциями в SQL Server 37-j

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

не задан. Проверить, заблокирован ли ресурс, прежде чем пытаться получить для блокировку, можно лишь, обратившись к данным (при этом существует потенциальная опасность заблокировать процесс на неопределенное время).

Примечание Определить, заблокирован ли процесс, а также кем он заблокирован, позволяет системная хранимая процедура

Если вы настроите значение приложение сможет устанавливать

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

ный оператор автоматически а приложению сообщение об

ошибке 1222 Lock request time-out period exceeded* (Превышение тайм-аута при ожидании блокировки).

Однако SQL Server не откатывает транзакцию, содержащую этот оператор. Поэтому у нриложения должен быть обработчик ошибок, перехватывающий сообщение об ошибке 1222. Если приложение не перехватывает эту ошибку, оно продолжив оту, не зная об отмене отдельного оператора транзакции, что может стать причиной ошибки при исполнении последующих операторов транзакции, зависящих от операторов.

Реализация обработчика ошибок, перехватывающего сообщение об ошибке 1222, позволяет

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

Посредством оператора SET удается задать длительность ожидания

освобождения блокировки (в миллисекундах), как показано в следующем примере:

SE IMEOUT -1 SELECT @@LOGKIMEOUT

Оператор SET позволяет максимальный период ожидания

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

позволяет текущее значение при

ожидании блокировки (в миллисекундах) для текущего сеанса:

SELECT @<аШСК Т1МЕ0иТ

Если для сеанса не задано значение то

OUT вернет значение -1.

Настройка уровней изоляции транзакции

По умолчанию SQL Server работает на уровне изоляции READ COMMITTED (подтверж-

UcHhol- чтение). Однако для работы приложения иногда требуется другой уровень изоляции. Чтобы использовать в приложениях более или менее строгие уровни изоляции, следует настроить блокировку для сеанса, установив уровень изоляции сеанса с помощью

оператора SET TRANSACTION ISOLATION LEVEL.

Если уровень изоляции задан, режим блокирования для всех операторов SELECT са SQL Server функционирует именно на этом уровне. Он действует до завершения сеанса или до изменения уровня изоляции.