Цель работы– изучение проблем надежности, возникающих в многопользовательских средах обработки данных, и механизмов блокирования операций различного типа и уровня, начиная с отдельной строки таблицы и заканчивая базой данных в целом, которые используются в MS SQL Server 2000 для успешного выполнения транзакций, а также для приобретения навыков управления блокировками в запросе и текущими блокировками с помощью Enterprise Manager, обеспечивающего их мониторинг.
В многопользовательских средах обработки данных при одновременном чтении и изменении одних и тех же данных несколькими пользователями могут возникнуть следующие четыре проблемы одновременного доступа:
The Lost Update Problem – проблема последнего изменения, заключающаяся в том, что если несколько пользователей будут одновременно изменять одни и те же данные, то сохраняться изменения того пользователя, который запишет их последним; для решения этой проблемы надо обеспечить последовательное внесение изменений.
The Uncommitted Dependency Problem – проблема грязного чтения, когда пользователь считывает данные, обработка которых еще не завершена другим пользователем; для ее решения необходимо ожидание окончания всех изменений данных.
The Inconsistent Analysis Problem – проблема не повторяемого чтения, когда требуется многократное чтение одних и тех же данных, а они изменяются в это время другим пользователем; для ее решения необходим запрет на изменение таких данных другими пользователями.
The Phantom Read Problem – проблема чтения фантомов, когда один пользователь выбирает данные из таблицы, а второй пользователь вставляет новые строки; здесь также необходим запрет на изменение данных вторым пользователем.
Временно накладываемые ограничения на выполнение некоторых операций обработки данных называются блокировками (locks). Специальный стандарт ANSI поддерживает четыре уровня блокировок, при этом каждый последующий уровень поддерживает требования предыдущего и налагает дополнительные ограничения:
Уровень 0 – запрещение загрязнения данных; для выполнения этого требования надо, чтобы изменения проводил лишь один пользователь, а остальные ожидали окончания этого изменения.
Уровень 1 – запрещение грязного чтения: если один пользователь начал изменение данных, то другие пользователи не должны читать эти данные до окончания их изменения.
Уровень 2 – запрещение неповторяемого чтения: если пользователь считывает данные, то никакой другой пользователь не сможет их изменить.
Уровень 3 – запрещение фантомов: если пользователь работает с данными, то никакой другой пользователь не должен добавлять новые или удалять имеющиеся строки.
MS SQL Server 2000 поддерживает все четыре уровня блокировок. Управлением блокировками занимается менеджер блокировок (lock manager), контролирующий их наложение и разрешение конфликтов. Блокировки и транзакции тесно связаны друг с другом. Транзакции накладывают блокировки на данные, чтобы обеспечить выполнение требований ASID, так что
пользователю чаще всего не нужно предпринимать никаких действий по управлению блокировками. Однако при необходимости в запросе можно явно указать, какой тип блокировки надо использовать в том или ином случае. Команда SET LOCK_TIMEOUT позволяет регулировать время ожидания разблокирования ресурса, по истечении которого выдается ошибка. Системная хранимая процедура sp_configure ‘locks’, n устанавливает максимальное количество блокировок в системе.
SQL Server 2000 поддерживает различные уровни блокирования объектов, начиная с отдельной строки и заканчивая базой данных в целом:
RID– на уровне строки таблицы при вставке новых строк;
Key– на уровне индекса, когда блокируется его часть, соответствующая изменяемым в транзакции данным;
Page– на уровне страницы; Extent– на уровне экстента; Table– на уровне таблицы; DB– на уровне базы данных.
Основной единицей блокирования является страница, а минимальным элементом – строка. Сервер может динамически управлять уровнем блокирования для конкретной транзакции в зависимости от характера запросов.
Для транзакции SQL Server 2000 может устанавливать различные уровни изоляции, чтобы делать их независимыми друг от друга. Эти уровни соответствуют рассмотренными уровням стандарта ANSI и решают четыре проблемы одновременного доступа:
READ UNCOMMITED– незавершенное чтение: гарантируется только физическая целостность данных;
READ COMMITED– завершенное чтение: не возникает проблемы грязного чтения;
REPEATABLE READ– повторяющееся чтение: помимо решения проблемы грязного чтения решается проблема неповторяемого чтения;
SERIALIZABLE– сериализуемость: обеспечивает полную изоляцию транзакций друг от
друга.
Установка уровня изоляции транзакций устанавливается командой SET TRANSACTION ISOLATION LEVEL.
В зависимости от выполняемых над данными действий SQL Server обеспечивает четыре типа блокировок:
S (Shared)– коллективная блокировка при чтении данных несколькими пользователями;
V (Update)– блокировка обновления как подготовительная для перехода от коллективной к монопольной блокировке;
E (Exclusive)– монопольная блокировка, если транзакция изменяет данные;
BU (Bulk update)– блокировка массового обновлении при выполнении операций массового копирования в таблицу.
Помимо основных типов блокировок SQL Server 2000 поддерживает ряд специальных блокировок, предназначенных для повышения производительности и функциональности обработки данных. Блокировки этого типа называются блокировками намерения (intent locks). Они используются сервером в том случае, если транзакция намеривается получить доступ к данным вниз по иерархии и необходимо запретить другим транзакциям накладывать блокировки, которые будут конфликтовать с блокировкой, накладываемой первой транзакцией.
Помимо рассмотренных блокировок на данные SQL Server 2000 использует несколько блокировок на метаданные, описывающие структуру объектов. Такими блокировками являются блокировка схемы и блокировка диапазона ключа.
При работе многопользовательской системы возможны ситуации, когда две транзакции блокируют два блока данных и для завершения работы каждой из транзакций необходим доступ к данным, заблокированным ранее другой транзакцией. Это так называемые “мертвые”, или
тупиковые (deadlocks) блокировки. Для избежания подобных проблем имеется специальный механизм разрешения конфликтов тупикового блокирования. Кроме того для минимизации возможности их образования необходимо соблюдать ряд правил при разработке кода транзакции: минимизировать длительность транзакции, использовать низкий уровень изоляции, избегать в транзакции взаимодействия с пользователем и т.д.
В командах SELECT, DELETE, UPDATEи INSERTможно с помощью специальных ключевых слов, или хинтов (hint) управлять блокировками и уровнем изоляции:
HOLDLOCK– для удержания коллективной блокировки до конца транзакции;
NOLOCK– разрешает грязное чтение; PAGLOCK– блокировка на уровне страницы; READCOMMITTED– установка первого уровня изоляции; ROWLOCK– блокировка на уровне строки; SERIALIZABLE– установка высшего уровня изоляции; TABLOCK– блокировка на уровне таблицы;
TABLOCKX– блокировка на уровне таблицы до завершения транзакции.
Имеются и другие хинты.
Задание 1.Создать несколько одновременно работающих процессов, соединенным с одним и тем же сервером и использующих одни и те же базы данных, и с помощью утилиты Enterprise Manager произвести мониторинг текущих блокировок сервера, используя следующие его папки:
Management/Current Activity/Process Info – папка с подробной информацией обо всех процессах, которые в текущий момент обращаются к серверу;
Management/Current Activity/Locks/Process ID – папка с информацией о текущих блокировках, которые установлены процессами;
Management/Current Activity/Locks/Object – папка с информацией об объектах, которые блокированы в текущий момент процессами.
Поскольку за время просмотра состояние блокировок процессов изменяется, следует обновлять информацию с помощью команды Refresh контекстного меню процесса. В этом окне можно посмотреть последнюю выполненную команду, послать сообщение пользователю и уничтожить процесс, который вызвал мертвые блокировки.
В папке Process Info содержится следующая информация: Process ID – идентификационный номер процесса;
User – имя учетной записи пользователя;
Database – имя используемой процессом базы данных;
Status – состояние процесса: работает в основном режиме (Runable), работает в фоновом режиме (Background) или бездействует (Sleeping);
Open Transaction – количество транзакций, открытых процессов; Command – последняя выполненная команда;
Application – имя приложения, установившего соединение; Wait time – время ожидания разблокирования ресурса;
Wait type – тип ожидания разблокирования ресурса; Wait resource – имя ожидаемого ресурса;
CPU time – время работы процесса;
Physical ID – физический идентификатор процесса; Memory Usage – объем памяти, занятой процессом; Login time – время регистрации процесса;
Last Batch – время выполнения процессом последней команды; Host – имя компьютера, на котором был запущен процесс;
Network Library – имя сетевой библиотеки соединения с процессом;
Network Address – адрес сетевой карты, которую использовал процесс для установления соединения;
Blocked by – количество процессов, блокируемых данным процессом;
Blocking – количество процессов, блокирующих выполнение данного процесса.
В папках Locks/Process ID и Locks/Object содержится одна и та же информация,
сгруппированная соответственно по процессам и по объектам:
Process ID – идентификационный номер процесса; Object – имя блокированного объекта;
Lock Type – тип блокировки: базы данных (DB), таблица (TAB), экстент (EXT), страница
(PAG), диапазон ключа (KEY) и строка (RID); Mode – вид блокировки:
S – коллективная блокировка; V – блокировка обновления;
X – монопольная блокировка;
IS, IX, SIX – блокировки намерения; Sch-C, Sch-M – блокировки схемы;
BV – блокировка массового обновления; Status – статус блокировки:
GRANT – блокировка установлена и успешно работает;
WAIT – блокировка ожидает ресурса;
CNVT – выполняется конвертирование блокировки;
Owner – владелец блокировки: Sess – сессия, Xact – транзакция, Curs – курсор; Index – имя индекса, связанного с ресурсом;
Resource – идентификатор заблокированного ресурса.
Задание 2.Используя системные хранимые процедуры sp_lock, sp_who и команды Transact – SQL DBCC OPENTRANT, DB_ID, произвести мониторинг текущих блокировок сервера. Описание процедур и команд найти в документации.