45808 (Блокировки в MS SQL Server 2000), страница 3

No trashing of data (запрещение «загрязнения» данных). Запрещается изменение одних их тех же данных двумя и более параллельными транзакциями. Изменять данные может только одна транзакция, если какая-то другая транзакция попытается сделать это, она должна быть заблокирована до окончания работы первой транзакции.

No dirty read (запрещение «грязного» чтения). Если данная транзакция изменяет данные, другим транзакциям запрещается читать эти данные до тех пор, пока первая транзакция не завершится.

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

No phantom (запрещение фантомов). Если данная транзакция производит выборку данных, соответствующих какому-либо логическому условию, другие транзакции не могут ни изменять эти данные, ни вставлять новые данные, которые удовлетворяют тому же логическому условию.

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

В таблице 1 подводится итог этого раздела и изучения уровней изоляции.

Блокировки

Блокировки в MS SQL Server 2000 (в дальнейшем просто сервер) – это механизм реализации требования изолированности транзакций. Вся последующая информация специфична только для указанного сервера.

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

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

Более подробно эту команду и хинты для операторов T-SQL мы рассмотрим в следующем разделе. Пока же я хочу подробно остановиться на типах блокировок.

Блокировки применяются для защиты совместно используемых ресурсов сервера. В качестве объектов блокировок могут выступать следующие сущности:

База данных (обозначается DB). При наложении блокировки на базу данных блокируются все входящие в нее таблицы.

Таблица (обозначается TAB). При наложении блокировки на таблицу блокируются все экстенты данной таблицы, а также все ее индексы.

Экстент (обозначается EXT). При наложении блокировки на экстент блокируются все страницы, входящие в данный экстент.

Страница (обозначается PAG). При наложении блокировки на страницу блокируются все строки данной страницы.

Строка (обозначается RID).

Диапазон индекса (обозначается KEY). Блокируются данные, соответствующие диапазону индекса, на обновление, вставку и удаление.

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

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

Вообще говоря, существует два метода управления конкуренцией для обеспечения параллельной работы множества пользователей – оптимистический и пессимистический. SQL Server использует оптимистическую конкуренцию только при использовании курсоров (cursors). Для обычных запросов на выборку и обновление используется пессимистическая конкуренция. Рассмотрим подробнее, что они собой представляют:

Оптимистический метод управления характеризуется тем, что вместо непосредственного чтения данных берется значение из буфере. Никаких блокировок при этом не накладывается. Другие транзакции могут спокойно читать или даже изменять данные. В момент фиксирования транзакции система сравнивает предыдущее (заранее сохраненное) значение данных с текущим. Если они совпадают, выполняются операции блокировки, обновления и разблокировки данных. Если же значения отличаются, то система генерирует ошибку и откатывает транзакцию. Хотя такой подход не удовлетворяет требованиям стандарта, он позволяет в определенных случаях добиться лучшей производительности, чем пессимистический подход. Достоинства этого режима очевидны: система не теряет времени на установку блокировок и ресурсов для их создания. Однако для систем с большим количеством пользователей, часто изменяющих данные, такой режим использовать не рекомендуется, так как цена отката транзакции и ее повторного выполнения значительно выше установки блокировки при чтении данных.

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

Блокировки – чрезвычайно важный и неотъемлемый механизм функционирования сервера. Они применяются для каждого запроса на чтение или обновления данных, а также во многих других случаях (например, при создании новой сессии). Работой с блокировками занимается специальный модуль SQL Server’а – менеджер блокировок (Lock Manager). В его задачи входит:

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

снятие блокировок;

эскалация блокировок;

определение совместимости блокировок;

устранение взаимоблокировок (deadlocks) и многое другое.

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

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

Простые блокировки

SQL Server поддерживает три основных типа блокировок:

Разделяемая блокировка (Shared Lock), обозначается латинской буквой S. Эта самый распространенный тип блокировки, который используется при выполнении операции чтения данных. Гарантируется что данные, на которые она наложена, не будут изменены другой транзакцией. Однако чтение данных возможно.

Монопольная блокировка (Exclusive Lock), обозначается латинской буквой X. Этот тип применяется при изменении данных. Если на ресурс установлена монопольная блокировка, гарантируется, что другие транзакции не могут не только изменять данные, но даже читать их.

Блокировка обновления (Update Lock), обозначается латинской буквой U. Эта блокировка является промежуточной между разделяемой и монопольной блокировкой. Так как монопольная блокировка не совместима ни с одним видом других блокировок (есть одно исключение, о котором попозже), ее установка приводит к полному блокированию ресурса. Если транзакция хочет обновить данные в какой-то ближайший момент времени, но не сейчас, и, когда этот момент придет, не хочет ожидать другой транзакции, она может запросить блокировку обновления. В этом случае другим транзакциям разрешается устанавливать разделяемые блокировки, но не позволяет устанавливать монопольные. Другими словами, если данная транзакция установила на ресурс блокировку обновления, никакая другая транзакция не сможет получить на этот же ресурс монопольную блокировку или блокировку обновления до тех пор, пока установившая блокировку транзакция не будет завершена.

Прежде чем идти дальше, давайте рассмотрим небольшой пример. Для просмотра текущих блокировок существует системная хранимая функция sp_lock. Она возвращает информацию о блокировках в формате, описанном в таблице 2.

Эта процедура возвращает данные о блокировках из системной таблицы syslockinfo, которая находится в базе данных master.

Во всех примерах используется таблица test, которая создается следующим скриптом:

Во-первых, давайте действительно убедимся, что при чтении данных с уровнем изоляции ниже REPEATABLE READ разделяемые блокировки снимаются сразу же после извлечения данных:

Мы начали транзакцию, но оставили ее открытой. Для того чтобы посмотреть, какие блокировки наложены предыдущим скриптом, вызовем процедуру sp_lock (в другой сессии) с параметром, выведенным print @@spid (у меня это 54).

Результат приведен в таблице 3.

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