авторефераты диссертаций БЕСПЛАТНАЯ БИБЛИОТЕКА РОССИИ

КОНФЕРЕНЦИИ, КНИГИ, ПОСОБИЯ, НАУЧНЫЕ ИЗДАНИЯ

<< ГЛАВНАЯ
АГРОИНЖЕНЕРИЯ
АСТРОНОМИЯ
БЕЗОПАСНОСТЬ
БИОЛОГИЯ
ЗЕМЛЯ
ИНФОРМАТИКА
ИСКУССТВОВЕДЕНИЕ
ИСТОРИЯ
КУЛЬТУРОЛОГИЯ
МАШИНОСТРОЕНИЕ
МЕДИЦИНА
МЕТАЛЛУРГИЯ
МЕХАНИКА
ПЕДАГОГИКА
ПОЛИТИКА
ПРИБОРОСТРОЕНИЕ
ПРОДОВОЛЬСТВИЕ
ПСИХОЛОГИЯ
РАДИОТЕХНИКА
СЕЛЬСКОЕ ХОЗЯЙСТВО
СОЦИОЛОГИЯ
СТРОИТЕЛЬСТВО
ТЕХНИЧЕСКИЕ НАУКИ
ТРАНСПОРТ
ФАРМАЦЕВТИКА
ФИЗИКА
ФИЗИОЛОГИЯ
ФИЛОЛОГИЯ
ФИЛОСОФИЯ
ХИМИЯ
ЭКОНОМИКА
ЭЛЕКТРОТЕХНИКА
ЭНЕРГЕТИКА
ЮРИСПРУДЕНЦИЯ
ЯЗЫКОЗНАНИЕ
РАЗНОЕ
КОНТАКТЫ


Pages:     | 1 |   ...   | 21 | 22 || 24 | 25 |   ...   | 33 |

«Е.Мамаев MS SQL SERVER 2000 Книга посвящена одной из самых мощных и популярных современных систем управления базами данных - Microsoft SQL Server 2000. ...»

-- [ Страница 23 ] --

На этом рассмотрение правил можно считать оконченным.

Определяемые пользователем типы данных В SQL Server 2000 имеется набор встроенных типов данных, которые вполне подходят для решения множества задач. Однако в некоторых случаях возникает необходимость определения дополнительных типов данных. В SQL Server это реализуется с помощью определяемых пользователем типов данных (UDDT, User Defined Data Type). Пользовательские типы данных создаются на основе встроенных в SQL Server 2000 системных типов данных. Таким образом, нельзя создать тип данных с совершенно новыми свойствами, которые не имеет ни один системный тип данных.

Однако пользовательские типы данных могут иметь конкретные свойства. На пример, можно создать тип данных phone, который будет иметь длину 9 симво лов и разрешать хранение значений NULL. С пользовательским типом данных можно связать умолчание и правило. В итоге получается довольно мощный комплект, который работает как одно целое. На основе пользовательского типа 840 Часть IV. Разработка и сопровождение баз данных данных можно создавать переменные или столбцы таблиц. Однако, при исполь зовании его для переменных правила и умолчания не действуют.

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

Например, изменим название встроенного типа данных r e a l на barrakuda:

UPDATE systypes SET [name] = 'barrakuda 1 WHERE [name] = ' r e a l ' Теперь же попытаемся использовать новый тип данных:

DECLARE @aa barrakuda SET @aa = -10. SELECT Saa В ответ будет выдан результат:

-10. (1 row(s) affected) Использовать же тип данных r e a l будет нельзя:

DECLARE @aa real При попытке выполнить указанную команду будет выдано следующее сообще ние:

Server: Msg 2715, Level 16, State 3, Line Column or parameter #-1: Cannot find data type real.

Parameter '@aa' has an invalid data type.

Данное сообщение говорит о невозможности создания переменной @аа с типом данных r e a l, т. к. такой тип данных найти не удалось. Заметим, однако, что выполненное изменение касается только одной базы данных. Следует быть ос торожным с изменением встроенных типов данных, т. к. это может привести к неожиданным последствиям.

Для создания нового пользовательского типа данных существует системная хра нимая процедура s p a d d t y p e, имеющая синтаксис:

sp_addtype [ @typename = ] type, [ Sphystype = ] system_data_type [, [ Snulltype = ] 'null_type' ] [, [ Sowner = ] 'owner name' ] Глава 18. Архитектура баз данных Рассмотрим назначение параметров этой процедуры:

П [ gtypename = ] type С помощью этого параметра указывается имя, которое будет иметь создавае мый пользовательский тип данных. При выборе имени следует придержи ваться стандартных правил именования объектов. Допускается использование ограничителей (квадратных скобок или двойных кавычек), если имя типа данных включает недопустимые символы.

О [ @phystype = j system_data_type Посредством этого параметра определяется системный тип данных, на осно ве которого будет создан пользовательский тип данных. При попытке указать в качестве исходного типа данных пользовательский тип данных будет выда но сообщение об ошибке, гласящее о невозможности найти указанный тип данных. Указанный системный тип данных ищется в таблице systypes той базы данных, в которой создается пользовательский тип данных. Поэтому, если имена системных типов данных были изменены, то следует указывать измененное имя. Для создания пользовательских типов данных разрешается применять не все системные типы данных. В частности, не разрешено ис пользовать типы данных cursor, t a b l e, timestamp. В табл. 18.1 приведен список системных типов данных, которые могут быть использованы при соз дании пользовательского типа данных.

Таблица 18.1. Список доступных системных типов данных Image I 'binary(n)' Smalldatetime Int Smallint : bit • nchar(n)' ! 'char(n)' Text i Datetime Ntext Tinyint Numeric Decimal Uniqueidentifier 'decimal[(p[, s ] ) ] ' 'numeric[(p[, s J ) ] ' 'varbinary(n)' i float 'nvarchar(n)' 'varchar(n)' ;

'float(n)' Real Bigint I sql variant С Замечайте ) Подробно работа с системными типами данных будет рассмотрена в главе 29.

[ Snulltype = ] 'null_type' ] Данный параметр позволяет контролировать, будет ли разрешаться в столбце, имеющем создаваемый пользовательский тип данных, хранение значений NULL. Параметр может иметь всего три значения:

• "NULL1 — хранение значений NULL разрешено;

• ' NOT NULL • — хранение значений NULL не разрешается;

• ' NONULL ' — хранение значений NULL не разрешается.

842 Часть IV. Разработка и сопровождение баз данных Если параметр gnuiltype опускается, то возможность хранения значений NULL определяется значением свойств соединения SET A N S I N U L L D F L T O N И SET ANSI NULL DFLT OFF.

( Замечание ^ Возможность хранения значений N U L L В столбце таблицы помимо пользовательско го типа данных также может быть определена и на уровне столбца.

П [ @owner = ] 'owner_name' С помощью этого параметра можно указать имя владельца, которому должен принадлежать создаваемый пользовательский тип данных. Отметим, что ука зание произвольных пользователей разрешается только членам фиксирован ной роли базы данных dbowner. Остальные же пользователи могут указать только свое имя. Если параметр опускается, то владельцем объекта будет яв ляться пользователь, выполняющий процедуру s p a d d t y p e.

Мы рассмотрели синтаксис процедуры s p a d d t y p e, а теперь приведем несколь ко примеров ее применения. Предсталенный ниже пример создает пользова тельский тип phone на основе системного типа данных char. Размер пользова тельского типа данных будет 8 символов. При этом разрешается хранение значений NULL В столбце таблицы, с которым будет связан пользовательский тип данных:

sp_addtype 'phone', 'char(8)', 'NULL' Этот тип данных может использоваться как при создании столбцов, так и при определении переменных. Попробуем создать переменную на основе нового типа данных:

DECLARE @aa phone SET @aa='295469283758923' ' SELECT @aa В ответ будет получен результат:

(1 row(s) affected) Как видно из выше приведенного примера, сервер выполнил усечение строки до 8 символов, как того требует тип данных phone.

Мы рассмотрели создание пользовательских типов данных. Удаление же пользо вательского типа данных выполняется с процедурой:

sp_droptype [ @typename = ] ' t y p e ' С помощью единственного параметра этой процедуры следует указать имя поль зовательского типа данных, который следует удалить.

Управление пользовательскими типами данных средствами Enterprise Manager выполняется с помощью папки User Defined Data Type (рис. 18.12).

Глава 18. Архитектура баз данных Рис. 18.12. Папка User Defined Data Type Рис. 18.13. Окно User-Defined Data Type Properties Информация о пользовательских типах данных отображается в следующих столбцах:

• Name — имя пользовательского типа данных;

844 Часть IV. Разработка и сопровождение баз данных • Owner — владелец пользовательского типа данных;

П Base Type — системный тип данных, на основе которого был создан соответ ствующий определяемый пользователем тип данных;

П Length — максимальное количество символов или байт, которое отведено для пользовательского типа данных;

П Allow Nulls — если в столбце указывается 1, то тип данных разрешает хране ние значений NULL, при задании 0 хранение этих значений не разрешено;

О Default — имя умолчания, которое было связано с типом данных;

П Rule — имя правила, которое было связано с типом данных.

Для создания нового пользовательского типа данных используется окно User Defined Data Type Properties (рис. 18.13), открыть которое можно, выбрав в кон текстном меню папки User Defined Data Type пункт New User Defined Data Type.

Рассмотрим назначение элементов управления, имеющихся в окне User-Defined Data Type Properties.

• Name. В этом текстовом поле приводится имя, которое будет иметь созда ваемый пользовательский тип данных. При выборе имени необходимо следо вать стандартным правилам именования объектов.

О Data type. С помощью этого раскрывающегося списка необходимо выбрать один из системных типов данных, на основе которого и будет создан новый пользовательский тип данных.

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

П Allow NULLs. Установка этого флажка разрешает хранение значений NULL В столбцах таблицы, которые будут использовать создаваемый тип данных.

• Rule. В раскрывающемся списке перечислены имена всех созданных в базе данных правил. Выбирая имя того или иного правила, вы тем самым выпол няете связывание этого правила с создаваемым типом данных.

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

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

В момент создания эта кнопка неактивна.

На этом рассмотрение работы с определяемыми пользователем типами данных можно считать оконченным.

Глава Транзакции и блокировки Если с базой данных работает несколько человек, то неизменно возникает про блема совместного использования данных. Простейшим примером этой пробле мы является одновременная попытка изменения одной и той же информации несколькими пользователями. Система управления базами данных должна ка ким-то образом решать, изменения какого из пользователей должны быть вы полнены. Приведенный пример — это лишь простейший случай. Рассмотрим более сложную ситуацию. Предположим, что для подготовки отчета необходимо трижды провести сканирование нескольких таблиц по определенным критери ям. Во время между второй и третьей выборкой один из пользователей добавля ет или удаляет строки в таблицах таким образом, что изменяется набор строк, которые рассматриваются при генерировании отчета. То есть в третьей выборке будут анализироваться уже другие данные, чем при первых двух выборках. Есте ственно, данные в отчете окажутся некорректными.

Если с базой данных работает всего один пользователь, то описанные проблемы решаются автоматически. То есть никто, кроме самого пользователя не может изменить данных. Как было сказано ранее, при работе с данными более одного пользователя в СУБД должны быть реализованы механизмы, обеспечивающие нормальную работу множества пользователей. В качестве таких механизмов вы ступают транзакции и блокировки. Эта глава будет посвящена подробному рас смотрению различных проблем, возникающих при коллективной работе с дан ными множества пользователей, применении транзакций и блокировок в SQL Server 2000.

Основы транзакций Для решения вышеописанных проблем в SQL Server 2000 используются тран закции и блокировки. Транзакция (transaction) представляет собой набор из од ной или более команд, обрабатываемых как единое целое. То есть будет выпол нен либо весь набор команд, либо не выполнена ни одна из них. Одна транзакция может изменять как отдельную строку таблицы, так и миллионы строк в различных таблицах. Кроме того, с помощью распределенных транзакций (distributed transaction) можно изменять строки в разных базах данных. SQL Server 2000 гарантирует, что данные, к которым обращается транзакция, оста нутся неизменными на всем протяжении существования транзакции.

846 Часть IV. Разработка и сопровождение баз данных По умолчанию каждая команда Transact-SQL рассматривается как отдельная транзакция. В обычном режиме ни одна команда изменения данных не может быть выполнена вне транзакции. Тем самым обеспечивается целостность дан ных. Не может быть такого, что из тысячи строк, которые должны быть изме нены, девяносто процентов будут изменены, а остальные десять — нет. При не обходимости пользователь может явно определить начало и конец транзакции, тем самым включив в нее более одной команды.

Имеются определенные требования к выполнению транзакций системой управ ления базами данных. Эти требования, известные как требования ACID (Atomicity, Consistency, Isolation и Durability), описывают то, как должны обра батываться данные и в каком состоянии они должны находиться после завер шения транзакции. Рассмотрим эти требования.

• Атомарность (Atomicity). Все изменения данных, выполненные в транзакции, рассматриваются как единый минимальный блок. Не может быть такого, что изменения, внесенные одной командой, будут зафиксированы, а изменения, выполненные остальными командами, будут откачены. Зафиксированными могут оказаться либо все изменения, выполненные в транзакции, либо данные будут восстановлены в состоянии, в котором они были до начала транзакции.

• Согласованность (Consistency). После того, как транзакция будет успешно завершена, данные должны удовлетворять всем ограничениям целостности, определенным в базе данных. Кроме того, все связанные с измененными данными индексы должны находиться в корректном состоянии и отображать сделанные изменения. Хотя транзакция рассматривается как атомарный не делимый блок, который может быть выполнен целиком или не выполнен во обще, все же транзакция состоит из отдельных команд, выполняемых после довательно. Каждая команда может производить изменение данных в таблице. В ходе этих изменений могут быть нарушены правила (rules) и огра ничения целостности (constraints), наложенные на данные. SQL Server позволяет контролировать целостность данных двумя способами: целостность данных может проверяться после выполнения каждой команды или только при фиксировании транзакции. В первом случае необходимо гарантировать, что каждая из команд транзакций не нарушает ограничений целостности. В противном случае вся транзакция откатывается (rollback) и система возвра щается в состояние, в котором она была до начала транзакции. Во втором случае в ходе выполнения транзакции могут возникать нарушения ограниче ний целостности и правил. Однако необходимо гарантировать, что к моменту фиксирования транзакции целостность данных не будет нарушена. Послед ний метод контроля целостности часто используется, когда для выполнения изменений данных надо на время нарушить установленные ограничения це лостности и правила.

П Изолированность (Isolation). Изменения данных, выполняемые различными транзакциями, должны быть независимыми друг от друга. То есть операции изменения данных, выполняемые одной транзакцией, не дблжны зависеть от изменений, вносимых другой транзакцией. Транзакция не должна произво дить изменения, основываясь на данных, изменяемых в другой транзакции.

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

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

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

) у_ Замечание Хотя нельзя выполнить откат уже зафиксированной транзакции и тем самым вер нуть систему в состояние, в котором она была до начала транзакции, тем не менее, в случае необходимости можно восстановить резервную копию базы данных.

Выполнение требования ACID по обработке транзакций распространяется на систему управления базами данных. То есть пользователь не должен предпри нимать никаких дополнительных усилий, чтобы соблюсти перечисленные тре бования. SQL Server 2000, как и многие другие системы, автоматически гаран тирует выполнение требований ACID, скрывая их от пользователей. Например, чтобы обеспечить соблюдение требования изолированности транзакций, в SQL Server 2000 используются блокировки (locks). Во время выполнения транзакции сервер устанавливает блокировки на данные, к которым обращается транзакция.

Существует множество типов блокировок, каждый из которых используется при выполнении транзакцией определенных действий. Например, если транзакция только читает данные, то можно не запрещать другим транзакциям читать эти же данные. Однако изменение данных при этом запрещается. Когда же транзак 28 Зи. S48 Часть IV. Разработка и сопровождение баз данных ция выполняет изменение данных, то эти данные не должны быть прочитаны другими транзакциями. Только после завершения транзакции данные смогут быть снова прочитаны. В каждом из описанных случаев устанавливается разный тип блокировок. Более подробно описание и типы блокировок будут рассмотре ны далее в этой главе.

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

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

Основы блокировок Блокировкой (locks) называется временное ограничение, накладываемое систе мой на использование тех или иных ресурсов. Как уже было сказано выше, бло кировки используются для обеспечения изолированности транзакций друг от друга. Пользователи SQL Server 2000 могут выполнять множество разнообразных операций обработки данных. Простейшим способом обеспечения изолирован ности транзакций явилось бы запрещение на любое обращение к данным, уже используемым в одной из транзакций. Однако в большинстве случаев не требу ется столь жестких мер. Часто достаточно просто запретить изменение данных, Глава 19. Транзакции и блокировки оставив возможность чтения. Тем не менее, в случае, когда транзакция изменяет данные, необходимо полностью блокировать их. Это гарантирует, что другие транзакции не будут использовать промежуточные данные.

В SQL Server 2000 имеется масса различных типов блокировок, обеспечивающих максимально эффективную работу множества пользователей с одними и теми же данными. По возможности система старается применить как можно менее жесткий режим блокирования, обеспечивая тем самым доступ к данным пользо вателей. Управлением наложением и снятием блокировок, а также разрешением конфликтов в SQL Server 2000 занимается менеджер блокировок (Lock Manager).

Механизмы подсистемы блокирования в SQL Server 2000 были существенно пе реработаны по сравнению с предыдущими версиями. Блокировки могут нала гаться как на отдельную строку таблицы, так и на всю таблицу целиком. Поми мо этих крайних вариантов, блокировки могут возникать на уровне страницы (page) или группы страниц — экстента (extent).

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

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

• Проблема последнего изменения (The lost update problem). При одновремен ной попытке нескольких транзакций изменить одни и те же данные часть их будет неизменно утеряна. Основываясь на первоначальном состоянии дан ных, несколько транзакций могут начать изменение данных. Однако, т. к.

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

• Проблема "грязного" чтения (The uncommitted dependency problem). Эта про блема возникает, когда транзакция пытается считать данные, обрабатывае мые другой транзакцией, и находящиеся в стадии обработки. При этом дан ные могут нарушать ограничения целостности и правила, тем самым нарушая общую целостность данных. Конечно, к моменту завершения тран закции данные должны соответствовать всем предъвляемым ограничениям • 28* Часть IV. Разработка и сопровождение баз данных целостности и правилам. Тем не менее, другая транзакция может прочитать промежуточные данные и применить их для каких-либо операций. Естест венно, результат, полученный при использовании неверных данных, также будет неверен.

П Проблема неповторяемого чтения (The inconsistent analysis problem). Эта про блема связана с многократным чтением транзакцией одних и тех же данных.

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

П Проблема чтения фантомов (The phantom read problem). Эта проблема возни кает, когда во время выполнения транзакции в таблицу вставляются новые строки, которые могут быть обработаны транзакцией. Предположим, что транзакция осуществляет несколько раз выборку данных из таблицы на ос нове одного и того же логического условия. Перед началом очередной вы борки в таблицу добавляются (или удаляются) строки, удовлетворяющие ло гическому условию. В результате при сканировании будет обработан иной набор данных, чем при предыдущих сканированиях. Использование различ ных наборов строк может привести к непредсказуемым последствиям.

Перечисленные проблемы характерны для любых систем управления базами данных, в которых не реализованы механизмы изоляции транзакций друг от друга. Однако в большинстве современных СУБД в той или иной мере меха низмы блокирования все же реализованы. Американским национальным инсти тутом стандартов (ANSI, American National Standard Institute) были разработаны правила на реализацию механизмов блокирования. В частности, был разработан специальный стандарт, определяющий четыре различных уровня блокирования.

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

П Level 0 — No trashing of data (запрещение "загрязнения" данных). На этом уровне решается проблема последнего изменения, т. е. обеспечивается изо лированность изменений данных транзакциями. Одни и те же данные в каж дый момент времени может изменять только одна транзакция. Если какая-то другая транзакция пытается изменить эти же данные, то она должна ожидать завершения работы первой транзакции. Только после этого разрешается из менять данные.

Глава 19. Транзакции и блокировки 85?

• Level 1 — No dirty read (запрещение "грязного" чтения). На этом уровне реша ется проблема "грязного" чтения. Когда транзакция начинает изменение дан ных, СУБД должна блокировать ресурсы, чтобы ни одна другая транзакция не смогла прочитать изменяемые данные. До тех пор, пока транзакция не бу дет зафиксирована или отменена, данные нельзя будет прочитать. Транзак ции, подавшие запрос на чтение данных, должны будут ожидать разблокиро вания ресурсов.

• Level 2 — No nonrepeatable read (запрещение неповторяемого чтения). На этом уровне решается проблема неповторяемого чтения. Когда транзакция обра щается к каким-то данным, СУБД должна организовывать блокировку таким образом, чтобы ни одна другая транзакция не могла изменить эти данные.

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

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

Система управления базами данных, удовлетворяющая последнему уровню блоки рования, является самой надежной. В принципе, СУБД может поддерживать толь ко начальные уровни блокирования. В некоторых недорогих СУБД реализован только нулевой или первый уровень блокирования, а в некоторых бесплатных простых СУБД и вовсе отсутствует понятие блокирования и даже транзакций. Mi crosoft SQL Server 2000 поддерживает все уровни блокирования, предоставляя пользователям мощный и надежный механизм управления данными.

Использование транзакций В SQL Server 2000 все операции обработки данных выполняются как транзак ции. По умолчанию каждая команда обрабатывается как отдельная транзакция.

Однако при необходимости пользователь может включить в транзакцию множе ство команд. При использовании различных механизмов доступа к данным (например, ODBC) для определения транзакций необходимо применять иные методы, чем при непосредственной работе с Transact-SQL.

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

852 Часть IV. Разработка и сопровождение баз данных Замечание В обычном режиме пользователь никак не может избежать выполнения его команд вне транзакции. Однако все же имеется возможность отменить использование тран закций и блокировок. Для этого необходимо перевести базу данных в однопользо вательский режим (single user mode). В этом режиме автоматически исключается появление различных проблем, связанных с использованием данных множеством пользователей.

В SQL Server 2000 имеется несколько различных методов определения транзак ций: автоматический, явный и подразумеваемый. Кроме того, транзакции также классифицируются по масштабу воздействия. Если транзакция обращается только к данным одной базы данных, то такая транзакция является стандарт ной. Если же в одной транзакции производится обрашение к данным, располо женным в различных базах данных, то такая транзакция называется распределен ной (distributed).

Замечание Строго говоря, в SQL Server 2000 имеется всего два типа определения начала тран закции: автоматический и неявный. Режим явного определения начала транзакции является своего рода надстройкой над этими двумя режимами.

Рассмотрим более подробно использование каждого типа транзакций.

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

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

По умолчанию SQL Server 2000 работает именно в режиме автоматического на чала транзакции (Autocommit transaction). Если SQL Server 2000 находится в ином режиме, то для переключения его в режим автоматического определения транзакции необходимо использовать следующую команду:

SET IMPLICIT TRANSACTION OFF Глава 19. Транзакции и блокировки Явное определение транзакции При использовании явных транзакций (Explicit transaction) пользователь должен четко указывать как начало, так и конец транзакции. С помощью явных тран закций можно объединять в одной транзакции множество команд. Но лишь в случае успешного выполнения всех команд SQL Server 2000 будет осуществлять фиксирование транзакции. Если же хоть одна команда завершится неудачно, то вся транзакция будет отменена. Помимо того, что транзакцией может управлять SQL Server 2000, пользователь также имеет в своем распоряжении команды Transact-SQL, с помощью которых он способен воздействовать на поведение транзакций. Рассмотрим эти команды:

П BEGIN TRAN. Эта команда предназначена для обозначения начала транзакции.

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

BEGIN TRAN [ SACTION ] [ transaction_name I @tran_name_variable [ WITH M R [ ' d e s c r i p t i o n ' ] ] ] AK С помощью аргумента transaction_name определяется имя транзакции, ко торое будет использоваться для обращения к ней. При выборе имени тран закции необходимо руководствоваться стандартными правилами именования объектов SQL Server 2000. Однако следует учесть, что длина имени транзак ции не может превышать 32 символов.

Имя транзакции также можно задать с помощью переменной, для чего суще ствует аргумент @tran_name_variable. Для задания имени транзакции могут использоваться переменные с типом данных char, varchar, nchar или nvar char. В принципе, транзакция может и не иметь имени. В этом случае работа с транзакциями осуществляется по уровням вложенности. Сначала необхо димо завершить работу с транзакциями нижнего уровня, а уже затем перейти к транзакциям более высокого уровня.

В SQL Server 2000 появилась возможность маркировать транзакции (mark transaction). Эта операция представляет собой выделение специальным обра зом в журнале транзакции помечаемой транзакции. Такая маркировка при меняется при восстановлении резервной копии журнала транзакций. Система резервного копирования SQL Server 2000 позволяет восстанавливать резерв ную копию журнала транзакций не только до конкретной точки времени, но и до конкретной транзакции. В последнем случае пользователь может указать любую из маркированных транзакций. Чтобы маркировать транзакцию дос таточно при ее объявлении указать ключевое слово WITH MARK. Дополнитель но посредством параметра ' d e s c r i p t i o n ' можно указать краткое описание транзакции. Это описание поможет выбрать нужную транзакцию при после дующем восстановлении резервной копии журнала транзакций.

• SAVE TRAN. Эта команда служит для создания точки сохранения (save point).

Внутри транзакции можно определить одно или более состояний данных, к которым при необходимости можно будет вернуться. Точки сохранения яв ляются мощным механизмом, расширяющим возможности пользователей по 854 Часть IV. Разработка и сопровождение баз данных управлению транзакциями. Без применения точек сохранения необходимо было бы использовать вложенные транзакции. Однако вложенная транзакция может быть отменена всего один раз, тогда как система может быть возвра щена в состояние, описанное с помощью точки сохранения, несколько раз.

Для восстановления транзакции в состояние, в котором она была в момент создания точки сохранения, используется команда ROLLBACK TRAN. Рассмот рим полный синтаксис команды SAVE TRAN:

SAVE TRANSACTION] {savepoint_name I @savepoint_variable) • С помощью аргумента savepoint_name задается имя точки сохранения.

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

Имя точки сохранения также можно указывать с помощью переменной типа char, varchar, nchar или nvarchar, которая назначается с помощью аргумен та @savepoint_variable.

• ROLLBACK TRAN. С помощью данной команды выполняется откат транзакции.

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

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

ROLLBACK [WORK] ИЛИ ROLLBACK [TRANSACTION] [transaction_name I @tran_name_variable I save point_name I @savepoint_variable]] Команда ROLLBACK WORK имеет то же действие, что и команда ROLLBACK TRAN (т. е. без указания параметров). Назначение и использование аргументов ана логично одноименным аргументам, описанным в предыдущих командах.

О COMMIT TRAN. Эта команда выполняет фиксирование транзакции. Если все команды внутри транзакции выполнены успешно, то SQL Server 2000 автома тически фиксирует изменения данных, осуществления транзакцией. Однако иногда бывает необходимо зафиксировать транзакцию до того, как будут вы полнены все команды транзакции. Пользователь может решить, что все не обходимые изменения сделаны и нет нужды организовывать дополнительные Глава 19. Транзакции и блокировки модификации данных. В этом случае он может зафиксировать транзакцию с помощью команды COMMIT TRAN, которая имеет синтаксис:

CM I O MT [TRAN[SACTION] [transaction_name | @tran_name_variable] ] Если при создании транзакции было указано ее имя, то с помощью аргумен тов transaction_name И @tran_name_variable М Ж О ЯВНО указать, какую ОН транзакцию следует зафиксировать. Если команда COMMIT выполняется без указания имени транзакции, то происходит фиксирование последней ини циированной транзакции. Если транзакции создаются друг из друга, то про исходит образование вложенных транзакций. Пользователь может произво дить поочередно фиксирование как каждой транзакции в отдельности, так и выполнить сразу фиксирование транзакции высокого уровня. В последнем случае обязательно указание имени транзакции.

При использовании команд управления транзакциями следует учитывать, что раз личные технологии доступа к данным могут автоматически начинать транзакции незаметно от пользователя. При написании конечных приложений необходимо учитывать эти особенности. Например, драйвер ODBC автоматически начинает запуск транзакций. Если пользователь выполнит команду ROLLBACK TRAN ИЛИ COMMIT TRAN, то транзакция будет завершена. Однако драйвер ODBC не воспри мет это и в свою очередь также попытается завершить транзакцию. В лучшем слу чае будет выдано сообщение об ошибке, а в худшем, при работе с вложенными транзакциями, может произойти фиксирование транзакции не того уровня.

Замечание При работе с технологией ODBC пользователь не имеет возможности явно опреде лить начало транзакции, т. к. эта технология поддерживает только неявное и авто матическое определение транзакции. При использовании технологий OLE DB и ADO явное определение транзакции разрешено.

Неявное определение транзакции При работе SQL Server 2000 в режиме неявного или, как его еще называют, под разумевающегося начала транзакций (Implicit transaction) система автоматически начинает новую транзакцию после того, как будет завершена предыдущая тран закция. Пользователь не должен явно указывать начало транзакции с помощью команды BEGIN TRAN, т. к. это за него делает SQL Server 2000. Если же начало транзакции будет указано явно, то станет использоваться режим явного опреде ления транзакций, описанный в предыдущем разделе.

При открытии нового соединения также создается новая транзакция. В отличие от режима автоматического определения транзакций, после выполнения очеред ной команды не происходит автоматического фиксирования транзакции. Поль зователь должен явно выполнить фиксирование или откат транзакции с помо щью команд COMMIT TRAN ИЛИ ROLLBACK TRAN. В итоге получается транзакция, состоящая из последовательно запускаемых в соединении команд, своего рода цепь непрерывных изменений данных.

856 Часть IV. Разработка и сопровождение баз данных ( Замечание ) Если пользователь не знает, что SQL Server 2000 работает в режиме неявного опре деления транзакций и выполняет множество команд, то он может блокировать боль шое количество ресурсов, тем самым не давая работать другим пользователям.

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

О изменение таблицы (ALTER TABLE);

• создание в базе данных нового объекта (CREATE);

О удаление данных из таблицы (DELETE);

f!J удаление объекта базы данных (DROP);

О выборка данных из курсора (FETCH);

П предоставление пользователям доступа к объектам базы данных (GRANT);

О вставка в таблицу новых строк (INSERT);

П открытие курсора (OPEN);

П неявное отклонение доступа пользователей к объектам базы данных (REVOKE);

• выборка данных (SELECT);

П выполнение усечения таблицы (TRUNCATE TABLE);

(1 изменение данных в таблице (UPDATE).

( Замечание ~^) При работе в режиме явного определения транзакции указанные выше команды не приводят к фиксированию транзакции. Все описанные действия могут быть как за фиксированы, так и откачены. Например, можно создать в транзакции таблицу, но выполнить откат. При этом таблица будет автоматически удалена.

Для переключения SQL Server 2000 в режим неявного начала транзакции суще ствует команда:

SET IMPLICITJTRANSACTION ON Команды, запрещенные в транзакциях В SQL Server 2000 имеется набор команд, использование которых внутри тран закций запрещено. Такие команды затрагивают важные аспекты работы сервера или баз данных и не могут быть отменены или зафиксированы как часть тран закции. Эти команды должны выполняться отдельно от других команд. Напри мер, внутри транзакции нельзя восстановить резервную копию базы данных, а позже выполнить откат транзакции и тем самым восстановить базу данных в Глава 19. Транзакции и блокировки первоначальное состояние. В SQL Server 2000 не реализован откат подобных операций.

( Замечание ) Для отката сложных операций, таких как модификация структуры объектов базы данных, изменение параметров базы данных или сервера, можно использовать ар хивирование. Перед выполнением операции создается резервная копия базы дан ных, в которой производятся изменения. Если необходимо осуществить откат опе рации, то достаточно просто восстановить базу данных из архива. При изменении параметров конфигурации сервера необходимо выполнять архивирование систем ной базы данных Master.

Рассмотрим, какие команды нельзя использовать в транзакциях:

• ALTER DATABASE — изменение физических характеристик базы данных;

П BACKUP LOG — создание резервной копии журнала транзакций;

• CREATE DATABASE — создание новой базы данных;

• DISK INIT — выполнение инициализации диска (используется только для обеспечения обратной совместимости с предыдущими версиями);

• DROP DATABASE — уничтожение базы данных;

• DUMP TRANSACTION — подготовка журнала транзакций к выполнению опера ций резервного копирования (используется только для обеспечения обратной совместимости с предыдущими версиями);

П LOAD DATABASE — восстановление базы данных из резервной копии. Эта ко манда унаследована от предыдущих версий SQL Server. В SQL Server используется команда RESTORE, поведение которой идентично команде LOAD DATABASE. В следующих версиях SQL Server команда LOAD DATABASE, ПО всей видимости, поддерживаться не будет;

d LOAD TRANSACTION — восстановление журнала транзакций из резервной ко пии. К этой команде относится все, что сказано выше при описании коман ды LOAD DATABASE;

• RECONFIGURE — применение изменений конфигурации сервера, выполненных с помощью хранимой процедуры sp_configure и не требующих перезапуска служб SQL Server 2000;

О RESTORE DATABASE — восстановление базы данных из резервной копии;

П RESTORE LOG — восстановление журнала транзакций из резервной копии;

О UPDATE STATISTICS — обновление статистики.

) ( Замечание Помимо указанных выше команд в транзакциях также не разрешено использование хранимых процедур sp_dboption и sp_conf i g u r e, а также любых других храни мых процедур, выполняющих модификации в системной базе данных Master. Такие хранимые процедуры должны выполняться отдельно.

Часть IV. Разработка и сопровождение баз данных Распределенные транзакции При работе с информацией, находящейся в одной базе данных, SQL Server использует локальные транзакции, которые являются стандартными. Локальные транзакции позволяют блокировать любые таблицы и индексы, находящиеся в пределах этой базы данных. Большая часть работы пользователей выполняется именно с применением локальных транзакций. Однако иногда пользователи выполняют запросы, которые обращаются к информации различных баз дан ных, возможно расположенных на разных серверах сети. В этом случае SQL Server 2000 автоматически использует распределенные запросы.

Распределенные запросы (distributed query) позволяют в одном запросе обращаться к различным базам данных. Как уже говорилось, все операции в SQL Server 2000 выполняются с помощью транзакций. Не являются исключением и распределенные запросы. Однако их работа связана с определенными трудно стями. В частности, стандартная транзакция SQL Server 2000 может обращаться только к данным базы данных, в контексте которой она создана. Так как рас пределенные запросы обращаются к различным базам данных, то необходим какой-то механизм, позволяющий работать в теле одной транзакции с различ ными базами данных. Таким механизмом являются распределенные транзакции.

Распределенные транзакции (distributed transaction) представляют собой совокуп ность двух или более локальных транзакций, выполняемых одновременно в раз личных базах данных. Можно сказать, что распределенные транзакции являются своего рода надстройкой над локальными транзакциями. Однако каждая из ло кальных транзакций выполняется самостоятельно и не подозревает о существова нии других транзакций и о том, что она является частью распределенной транзак ции. Необходимо каким-то образом синхронизировать все действия, выполняемые в каждой из локальных транзакций, и производить централизованную обработку всех данных, полученных из локальных транзакций. В качестве такого централь ного менеджера в SQL Server 2000 используется компонент MSDTC.

Координатор распределенных транзакций (MSDTC, Microsoft Distributed Transac tion Coordinator) контролирует всю работу по инициализации, откату и фикси рование локальных транзакций. Выполнение распределенных транзакций явля ется довольно сложным процессом, работу которого обеспечивает множество различных модулей. Сам координатор распределенных транзакций реализован в виде службы операционной системы и может запускаться отдельно от SQL Server 2000. Возможности MSDTC могут использоваться не только SQL Server 2000, но и другими приложениями, в том числе и написанными пользова телями. Например, с помощью языка программирования Microsoft Visual C++ 6.0 можно легко создавать приложения, применяющие возможности MSDTC.

В терминологии распределенных транзакций источник данных, к которому происходит обращение, называется менеджером ресурсов (Resource Manager). To есть распределенная транзакция обращается непосредственно не к данным, а лишь передает запрос на выборку этих данных менеджеру ресурсов. Последний, в свою очередь, должен обеспечивать возможность создания, фиксирования и Глава 19. Транзакции и блокировки • отката транзакций, а также позволять управлять ходом работы полученного за проса с целью синхронизации действий множества менеджеров ресурсов, задей ствованных в распределенной транзакции. Эти требования выполняются с по мощью специального модуля менеджера ресурсов, называемого обработчиком распределенных запросов.


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

Таким образом, распределенная транзакция может обращаться к любым ресур сам, которые управляются с помощью менеджеров ресурсов, соответствующих описанным требованиям. То есть область доступа распределенной транзакции не ограничивается только SQL Server 2000. При необходимости практически для любой СУБД может быть написан менеджер ресурсов, позволяющий обращать ся к его данным из распределенных транзакций. Это раскрывает широкие воз можности для пользователей SQL Server 2000.

( Замечание ^ Чтобы менеджер ресурсов мог быть использован для распределенных транзакций SQL Server 2000, его обработчик распределенных транзакций должен соответство вать спецификации X/OPEN ХА.

Завершение распределенных транзакций Как и при работе с обычными транзакциями, работа с распределенными тран закциями состоит из двух фаз: начало транзакции и ее завершение. Начало рас пределенной транзакции не вызывает особых трудностей — как только будут инициированы все локальные транзакции, MSDTC может начать выполнение распределенной транзакции.

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

При использовании стандартных механизмов фиксирования транзакций воз можна ситуация, когда часть локальных транзакций окажется принятой, а фик сирование другой части по каким-либо причинам будет невозможно. В этом случае необходимо будет выполнить откат всех локальных транзакций, исполь 860 Часть IV. Разработка и сопровождение баз данных зованных для выполнения распределенной транзакции. Однако требование ус тойчивости (Durability) ACID говорит о том, что система не может вернуться в состояние, в котором она была до начала транзакции, после того, как последняя была зафиксирована. То есть требование атомарности (Atomicity) ACID будет нарушено, что недопустимо. Поэтому необходим какой-то механизм, позво ляющий синхронизировать фиксирование всех локальных транзакций. В качест ве такого механизма выступает двухфазный протокол фиксирования транзакции.

Двухфазный протокол фиксирования (2РС, Two Phase Commit protocol) транзак ции специально предназначен для фиксирования распределенных транзакций, который разбивает операцию фиксирования на две фазы — фазу подготовки и собственно фазу фиксирования. Рассмотрим более подробно каждую из фаз:

П Фаза подготовки (Prepare Phase). На этом этапе каждому из локальных менед жеров ресурсов посылается сообщение о подготовке к фиксированию транзак ции. Менеджеры ресурсов должны выполнить проверку ограничений целост ности и соответствие данных наложенным правилам, т. е. гарантировать целостность данных. Кроме того, на этом этапе выполняется физическая за пись данных из буферов на диск и другие операции. Однако в журнале тран закций не отмечается, что транзакция завершена, и блокировки не снимаются.

• Фаза фиксирования (Commit Phase). Информация об успешном (или неус пешном) выполнении фазы подготовки от локальных менеджеров ресурсов поступает координатору распределенных транзакций, который переходит к фазе фиксирования после прохождения фазы подготовки. Если все локаль ные менеджеры ресурсов успешно выполнили фазу подготовки, то MSDTC посылает им команду с требованием выполнить окончательное фиксирова ние транзакции. После это считается, что распределенная транзакция ус пешно зафиксирована. Может получиться ситуация, когда один или более локальных менеджеров ресурсов по каким-то причинам не смогут зафикси ровать локальную транзакцию. В этом случае MSDTC посылает всем менед жерам ресурсов, участвующим в распределенной транзакции, команду с тре бованием отката локальных транзакций. Таким образом, выполняется откат распределенной транзакции. Пользователь при этом получает соответствую щее сообщение об ошибке.

Создание распределенных транзакций Для конечного пользователя работа с распределенной транзакцией практически ничем не отличается от работы с обычными локальными транзакциями. Поль зователю не нужно предпринимать никаких дополнительных действий по управлению работой локальных менеджеров ресурсов. Все необходимые дейст вия выполняет MSDTC.

Распределенная транзакция может быть начата несколькими способами.

П Технологии доступа к данным ODBC и OLE DB имеют специальные функ ции для начала распределенной транзакции. С помощью этих функций поль зователи могут инициировать выполнение распределенной транзакции.

Глава 19. Транзакции и блокировки П Локальная транзакция может быть автоматически расширена до распреде ленной транзакции, если в ней выполняются запросы, обращающиеся к таб лицам иных баз данных, чем база данных, в контексте которой была начата локальная транзакция. Действия SQL Server 2000 по созданию распределен ной транзакции незаметны для пользователя.

• При выполнении удаленных хранимых процедур из локальных транзакций SQL Server 2000 будет автоматически создавать распределенную транзакцию, если параметр REMOTE_PROC_TRANSACTION установлен в ON.

• Пользователь может явно инициировать выполнение распределенной тран закции с помощью команды BEGIN DISTRIBUTED TRANSACTION, имеющей сле дующий синтаксис:

BEGIN DISTRIBUTED TRANSACTION] [transaction_name | @tran_name_variable j ( Замечание ^ Откат или фиксирование распределенной транзакции, инициированной с помощью команды B E G I N D I S T R I B U T E D TRANSACTION, выполняется, соответственно, с по мощью команд ROLLBACK TRAN и COMMIT TRAN, приведенных ранее в этой главе.

Одна из ситуаций, когда происходит инициирование распределенной транзак ции — это выполнение удаленных хранимых процедур из локальных транзак ций. Однако, чтобы это стало возможным, необходимо установить параметр REMOTE_PROCTRANSACTION. Значение этого параметра можно определять на уровне сервера, как значение по умолчанию для всех вновь создаваемых соеди нений, или на уровне конкретного соединения. Для управления значением на уровне сервера предназначена хранимая процедура:

EXEC sp_configure 'remote proc trans', {0 | 1} Если пользователя не устраивают значения по умолчанию, или необходимо га рантировать, что приложение будет всегда работать с одними и теми же уста новками, то нужно указать параметр REMOTEPROCTRANSACTION на уровне со единения. Для этого используется следующая команда:

SET REMOTE_PROC_TRANS, {ON I OFF} ( Замечание ^ Настройки конфигурации, определенные на уровне базы данных, перекрывают зна чения, установленные на уровне сервера. В то же время настройки, определенные на уровне соединения, перекрывают как настройки на уровне сервера, так и на стройки на уровне базы данных.

Вложенные транзакции В предыдущих разделах предполагалось, что транзакция содержит только набо ры команд Transact-SQL и вызовы хранимых процедур. Однако в транзакциях могут содержаться и другие транзакции. Транзакции, которые создаются в ходе 862 Часть IV. Разработка и сопровождение баз данных работы некоторой транзакции, называются вложенными транзакциями (nested transaction).

Использование вложенных транзакций повышает наглядность кода и позволяет более эффективно реализовать логику обработки данных. В принципе, приме нение вложенных транзакций можно заменить применением точек сохранения (save point). Однако их использование не всегда удобно и связано с определен ными трудностями. Эффективность от точек сохранения существенно падает с увеличением уровня вложенности блоков обработки данных. При использова нии вложенных транзакций пользователь может легко и естественно реализовы вать алгоритмы обработки данных с большим уровнем вложенности. Кроме то го, в некоторых случаях применение точек сохранения невозможно. Лучшим примером является работа с хранимыми процедурами.

При написании хранимой процедуры необходимо обеспечить ее универсаль ность, обеспечив возможность ее нормального выполнения как из тела транзак ции, так и при непосредственном вызове. Независимо от места вызова храни мой процедуры, система должна обеспечить выполнение требований ACID применительно к этой хранимой процедуре. То есть необходимо гарантировать, что хранимая процедура либо выполнит все действия, либо не выполнит их во обще. Не должно быть такого, что часть действий будет успешно завершена, а другая, из-за ошибки, — нет.

( Замечание } SQL Server 2000 не обеспечивает автоматического соблюдения требований ACID при выполнении хранимой процедуры. То есть если одна из команд хранимой про цедуры закончится неудачно, то это не приведет к откату всей хранимой процедуры и восстановлению системы в первоначальное состояние. Реализация требований ACID полностью ложится на разработчика.


При использовании внутри хранимой процедуры транзакций можно легко и естественно обеспечить соблюдение требований ACID. Достаточно выполнить команду BEGIN TRAN первой в хранимой процедуре, а последней командой сде лать COMMIT TRAN. Однако без поддержки SQL Server 2000 вложенных транзак ций обеспечение требований ACID для хранимых процедур представляло бы определенные проблемы.

Замечание Вложенность транзакций в SQL Server 2000 ограничена. Допускается создание вло женной транзакции не более чем 32-го уровня.

Создание вложенной транзакции производится автоматически, если пользова тель выполняет команду BEGIN TRAN ДО ТОГО, как будет завершена предыдущая транзакция. Не трудно прийти к выводу, что создание вложенной транзакции возможно только при использовании режима явного определения транзакции. В режиме автоматического начала транзакции каждая команда выполняется как отдельная транзакция, которая завершается сразу же после выполнения коман Глава 19. Транзакции и блокировки ды. Таким образом, образование вложенной транзакции просто невозможно.

При работе в режиме неявного определения транзакции пользователь использу ет только команды ROLLBACK TRAN И COMMIT TRAN, тогда как команда BEGIN TRAN выполняется системой автоматически.

Замечание Строго говоря, как только пользователь явно укажет начало транзакции с помощью команды B E G I N TRAN, то система переходит в режим явного определения начала транзакции. Таким образом, создание вложенных транзакций не представляет ника ких проблем.

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

( Замечание ) Технология вложенных транзакций позволяет нарушать требование устойчивости (Durability) ACID. При фиксировании транзакции нижнего уровня должна обеспечи ваться невозможность восстановления системы в первоначальное состояние. Тем не менее, это условие действует только в пределах вложенной транзакции. То есть, внутри вложенной транзакции нельзя восстановить данные в первоначальное со стояние. Однако, можно выполнить откат всей вложенной транзакции и тем самым откатить изменения, сделанные транзакцией нижнего уровня. Откат всех изменений, выполненных внутри вложенной транзакции, производится в соответствии с требо ваниями ACID.

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

Рассмотрим поведение SQL Server 2000 при откате транзакций нижних уровней.

При использовании команды ROLLBACK TRAN без указания имени транзакции или применении команды ROLLBACK WORK происходит последовательный откат всех транзакций нижних уровней вплоть до отката всей вложенной транзакции.

Допускается указание имени транзакции при выполнении команды ROLLBACK TRAN. Однако разрешается указание имени транзакции самого высокого уровня.

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

864 Часть IV. Разработка и сопровождение баз данных При указании имени самой верхней транзакции также происходит откат всей вложенной транзакции. То есть любой вызов команды ROLLBACK TRAN приведет к откату всей вложенной транзакции.

В SQL Server 2000 имеется функция @@TRANCOUNT, С ПОМОЩЬЮ которой можно определить текущий уровень вложенности транзакций. До начала транзакции уровень вложенности равен 0. При каждом выполнении команды BEGIN TRAN происходит последовательное увеличение на единицу значения, возвращаемого функцией @@TRANCOUNT. Выполнение команды ROLLBACK TRAN имеет обратный эффект — после каждого завершения этой команды происходит уменьшение возвращаемого значения на соответствующее число. Команда COMMIT TRAN уменьшает значение функции @@TRANCOUNT строго на единицу.

Рассмотрим использование функции @@TRANCOUNT:

BEGIN TRAN trl PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) BEGIN TRAN tr PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) BEGIN TRAN tr PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) BEGIN TRAN tr PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) BEGIN TRANSACTION tr SAVE TRANSACTION aaa PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) ROLLBACK TRANSACTION aaa PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) COMMIT TRAN tr PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) COMMIT TRAN tr PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) ROLLBACK TRAN PRINT 'Текущий уровень вложенности: '+CAST(@@TRANCOUNT AS char(2)) В результате выполнения указанного кода будет получен следующий результат:

Текущий уровень вложенности: Текущий уровень вложенности: Текущий уровень вложенности: Текущий уровень вложенности: Текущий* уровень вложенности: Текущий уровень вложенности: Текущий уровень вложенности: Текущий уровень вложенности: Текущий уровень вложенности: О Использование блокировок Как уже говорилось ранее в этой главе, блокировки являются важным механиз мом, с помощью которого обеспечивается реализация требований ACID. Ис пользование того или иного уровня блокирования позволяет управлять поведе Глава 19. Транзакции и блокировки нием сервера по обработке блокировок. Основное назначение блокировок — обеспечение нормальной работы множества пользователей с одними и теми же данными. Система блокирования должна обеспечить пользователям устойчивую среду для выполнения модификации данных. Действия системы, которые обес печивают параллельную работу множества пользователей, называются управлени ем конкуренцией или управлением параллелизмом (Concurrency Control).

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

sp_configure ' l o c k s ', num_locks Аргумент num locks определяет максимальное количество блокировок, которое может быть установлено на сервере для всех баз данных. По умолчанию уста новлено значение 0, что соответствует динамическому управлению максималь ным количеством блокировок. Значение numlocks может колебаться в пределах от 5000 до 2 147 483 647.

Л Замечание На одну блокировку отводится 96 байт оперативной памяти. Первоначально для системы блокирования отводится около 2% памяти, используемой для работы SQL Server 2000. Количество памяти, необходимой для хранения блокировок, может ди намически изменяться. Максимальное количество памяти, которое может быть вы делено для хранения информации о блокировках, составляет 40%.

Для снижения общих затрат сервера на поддержание блокировок могут быть использованы различные методы управления блокированием ресурсов. Теория управления конкуренцией (Concurrency control theory) предусматривает два метода обеспечения работы пользователей:

• Оптимистическая конкуренция (Optimistic Concurrency Control). При работе в этом режиме при выполнении операций чтения из транзакции не происходит блокирования используемых ресурсов. При выполнении операции повтор ного чтения из той же транзакции система проверяет, не были ли изменены данные со времени последнего чтения. Если это произошло, то транзакция откатывается и ее необходимо начать заново. То есть происходит нарушение условия изолированности (isolation) набора требований ACID. Наградой за снижение изолированности транзакций является повышение производитель ности как выполнения отдельной транзакции, так и работы системы в целом.

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

866 Часть IV. Разработка и сопровождение баз данных П Пессимистическая конкуренция (Pessimistic Concurrency Control). В этом ре жиме система блокирования строго следует требованиям ACID. При любом обращении к ресурсам происходит их блокирование. Таким образом, до тех пор, пока транзакция не окажется завершенной, ни одна другая транзакция не сможет блокировать те же ресурсы, и тем самым создать конфликт с уже существующей блокировкой. В случае конфликта блокировок наложение второй транзакции откладывается до снятия ранее запущенной транзакции.

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

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

Замечание Интерфейсы доступа к данным (OLE DB, ADO, ODBC и т. д.) также позволяют уста навливать любой режим контроля конкуренции.

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

SQL Server 2000 спроектирован таким образом, что система сама выбирает наи более оптимальный режим блокирования, который обеспечивает не только вы полнение требований ACID, но и по возможности позволяет другим транзакци ям работать с данными. Тем не менее, в распоряжении пользователя имеются средства, позволяющие при выполнении запроса использовать конкретный ре жим блокирования.

Установкой, снятием и разрешением конфликтов блокировок в SQL Server занимается специальный модуль — менеджер блокировок (Lock Manager). Когда пользователь хочет выполнить команду, то менеджер транзакций сначала пыта ется установить блокировку на ресурсы, к которым обращается команда. Ме неджер блокировок принимает запрос на установку блокировки и проверяет, не блокированы ли уже запрашиваемые ресурсы. Если ресурсы блокированы тран закцией, отличной от той, которая подала заявку на установление блокировки, и тип устанавливаемой блокировки конфликтует с типом уже наложенной бло кировки, то выполнение блокирования откладывается до того времени, когда требуемые ресурсы будут разблокированы. Осуществление транзакции при этом также приостанавливается. Как только требуемый ресурс будет разблокирован, менеджер блокировок обрабатывает запрос на блокирование ресурса и отправ Глава 19. Транзакции и блокировки ляет менеджеру транзакций сообщение об успешном наложении блокировки.

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

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

Для установки максимального периода ожидания блокировки предназначена команда:

SET LOCK_TIMEOUT timeout^period Аргумент timeout_period задает время в миллисекундах, которое менеджер блокировок будет ожидать разблокирования требуемого ресурса. Если значение этого аргумента равно 0, то запрос на установление блокировки будет сразу же прерываться, если обнаружится, что требуемый ресурс уже блокирован. Беско нечному периоду ожидания соответствует значение — 1, которое установлено по умолчанию.

( Замечание ) Команда S E T L O C K _ T I M E O U T действует только на уровне конкретного соединения.

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

Замечание Текущее значение периода ожидания может быть получено с помощью функции @@LOCK_TIMEOUT.

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

Менеджер блокировок SQL Server 2000 поддерживает самые различные уровни блокировок: начиная с отдельной строки таблицы и заканчивая блокированием 868 Часть IV. Разработка и сопровождение баз данных целой базы данных. Диапазон ресурсов, которые охватываются в блокировке, определяет уровень блокирования. В SQL Server 2000 имеются следующие уров ни блокирования:

О RID — блокировка отдельной строки таблицы.

П Key — блокировка диапазона индекса. Используется в транзакциях для ре шения проблемы чтения фантомов (The phantom read problem). Блокировка налагается не только на сами данные, но и на вставку или удаление строк, удовлетворяющих логическому условию, используемому в операциях обра ботки данных.

О Page — блокировка отдельной страницы данных. Страница представляет со бой минимальную единицу хранения данных в файле базы данных. Страница может содержать одну и более строк.

• Extent — блокировка группы страниц (экстента). Для удобства управления страницы объединяются в группы, называемые экстентами. Каждый экстент содержит 8 страниц.

О Table — блокировка всех данных таблицы, включая связанные с ними индексы.

• DB — блокировка всех таблиц базы данных.

Замечание В SQL Server 6.x минимальным уровнем блокирования являлась страница, которая имела размер 2 Кбайта. В SQL Server 2000 минимальным уровнем блокирования является строка.

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

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

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

Со временем детальность блокирования ресурсов может меняться. При выпол нении больших транзакций, включающих множество команд, транзакция может Глава 19. Транзакции и блокировки 869_ постепенно расширять круг блокированных ресурсов. Менеджер блокировок может принять решение об уменьшении уровня детализации блокирования для повышения скорости исполнения запроса.

( Замечание ^ В одном и том же запросе могут устанавливаться различные степени детализации для самих данных и для индексов. Например, для данных может быть использована блокировка на уровне строк, тогда как для индексов может использоваться блоки ровка на уровне страниц.

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

Замечание Процесс уменьшения уровня блокирования называется эскалацией блокировок (lock escalation). Момент определения необходимости эскалации блокировок определяет ся динамически и не требует конфигурирования.

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

Уровень блокирования определяет степень изолированности транзакций друг от друга. Чем выше степень изолированности, тем в большей мере обеспечи вается надежность работы системы и ее соответствие требованию изолирован ности (isolation) набора требований ACID. Однако платой за это является по вышение требований к объему системных ресурсов, необходимых для выпол нения транзакций.

Для управления уровнями изолированности в SQL Server 2000 существует команда:

SET TRANSACTION ISOLATION LEVEL {. • READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE } 870 Часть IV. Разработка и сопровождение баз данных Рассмотрим поведение SQL Server 2000 при установке того или иного уровня изолированности:



Pages:     | 1 |   ...   | 21 | 22 || 24 | 25 |   ...   | 33 |
 





 
© 2013 www.libed.ru - «Бесплатная библиотека научно-практических конференций»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.