Управление триггерами и транзакциями

Триггер – это объект базы данных, являющийся специальным типом хранимой процедуры, которую SQL Server выполняет при операциях в данной таблице. Триггеры выполняют после применения правил, значений по умолчанию и обеспечивают ограничения целостности на уровне строк, обеспечивает ссылочную целостность в базе данных. Выполнение любой команды модификации приводит к срабатыванию триггера.

Для создания триггера нужно быть владельцем базы данных. Триггеры делятся на триггеры вставки, обновления или удаления. Команда создания триггера:

Create Trigger <имя триггера>

ОN <имя таблицы>

FOR {insert, UPDATE, Delete}

AS <команды sql>

[RETURN]

При создании триггера можно задать одну, две или три операции. В случае выбора нескольких операций их следует разделить запятыми. SQL Server ограничивает типы инструкций SQL, которые могут быть выполнены при работе с триггером. Большинство ограничений связаны с тем, что в случае возвращения в начальное состояние результатов работы операций обновления, добавления и удаления, вызвавших активизацию триггера, результатов выполнения инструкций внутри триггера откатить невозможно. Триггер не может использовать: все команды Create, все инструкции удаления Drop, объектное разрешение доступа: Grand и Revoke, Update Statistics, Reconfigure, операции загрузки Load Database, Load transaction, инструкции физической модификации диска: Disk, временное создание таблиц Select into. Нельзя создавать триггер для вида, а только для базовой таблицы или таблиц. Любая правильная операция Set работает только в период существования триггера. После завершения работы триггера, все установки возвращаются в прежнее состояние. Не следует применять операцию select возвращающие результирующие наборы из триггера, для приложения клиента, требующего специального управления результирующим наборами, независимо от того, делается ли это в хранимой процедуре или нет. Тщательно проверьте, все ли операции select считывают свои значения в локально определенные переменные, доступные в триггере. Удаление триггера: Drop trigger < имя триггера>.

Триггеры могут встраиваться друг в друга. Доступно 16 уровней вложенности. Триггеры становятся вложенными, когда выполнение одного триггера модифицирует таблицу, что вызывает к работе другой триггер. Триггер может вызвать бесконечный цикл. Например таблица А имеет триггер TR_A, который выполняется при обновлении таблицы А. При выполнении триггер А вызывает обновление таблицы В. Эта таблица включает триггер В, который выполняется, когда обновляется таблица В и вызывает обновление таблицы а. Таким образом, если пользователь обновляет любую из этих 2-х таблиц, два триггера продолжают бесконечно вызывать выполнение друг друга. При возникновении такого состояния SQL Server закрывает или отменяет выполнение триггера. Если триггер вызывает дополнительную модификацию своей базовой таблицы, это не приводит к его рекурсивному выполнению. В этой версии SQL Server нет поддержки повторной (reentrant) или рекурсивной (recursive) хранимой процедуры или триггера.

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

SET TRANSACTION ISOLATION Level Read Commited

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

Этот уровень изолированности устанавливается по умолчанию. Команда

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED

устанавливает второй уровень изолированности пользователей, уровень незавершенного чтения (то же действие, что и ключевое слово Nolock в отдельной инструкции Select). Никаких разделяемых блокировок, если они имеются в запросе, не выполняется. Если пользователь удаляет целую таблицу, но еще не завершил целиком эту транзакцию, другие пользователи могут из нее читать данные без всяких ошибок, то есть можно экспериментировать с «грязными данными».

SET TRANSACTION ISOLATION REPEATABLE READ

Отсутствие повторяемого чтения – это 3-й уровень изолированности пользователей, наиболее эксклюзивный (до завершения транзакции1 транзакция2 изменяет объект А и успешно завершается, транзакция 1 повторно читает объект А и видит его измененное состояние). Этот уровень изолированности гарантирует неизменность читаемых вами данных и невозможность влияния на ваши данные со стороны любой выполняемой другим пользователем транзакции в течение времени жизни вашей. Этот уровень сильно снижает параллельность доступа к данным и используется не часто.

SET TRANSACTION ISOLATION LEVEL SERIALZABLE – обеспечивает 4-й уровень изолированности – отсутствие картежей – фантомов (транзакцию 1 выполняет оператор А выборки картежей R с условием выборки S, до завершения транзакции 1, транзакции 2. Вставляет в R запись, удовлетворяющую условию S, тогда при повторении выборки читается новая запись в транзакции 1).

В SQL Server реализован гранулированный метод синхронизационных захватов.

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

SQL Server дает возможность изменять пороговый уровень блокировок от одной страницы до таблицы. Максимальный порог блокировки – это максимальное число блокированных страниц перед переходом на табличную блокировку, даже если процентный порог расширения блокировки не пройден. Значение порога по умолчанию – 200 страниц. Есть понятие минимального порога блокировки. Табличная блокировка произойдет, только если будет достигнут минимальный порог и процентный порог расширения. Минимальный порог расширения блокировки предотвращает переход к табличной блокировке для маленьких таблиц, там, где процентный порог расширения достигается часто. Значение по умолчанию – 20 заблокированных страниц. Процентныйпорог расширения указывает процент заблокированных страниц, необходимый для перехода на табличную блокировку. Значение по умолчанию – 0, что указывает на возможность табличной блокировки только при достижении максимального порога расширения. Процедура sp_configure позволяет установить 3 типа порогов распространения блокировок. Переход к табличной блокировке называется экскалацией.

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

Управление блокировками на уровне команды Select, указывая опции оптимизатора Nolock, UpDlock, tablock, Paglock и tablockx:

Nolock –отменяет разделяемые блокировки и не принимает во внимание монопольные при исполнении команды, т.е. позволяет читать «грязные данные», аналогична установке уровня Read UNCommitted, но действует только в рамках одной команды.

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

Tablock – поднимает разделяемую блокировку на уровень таблицы.

В случае UPDATE равнозначно директиве Tablockx.

Tablockx – налагает монопольную блокировку на таблицу.

Paglock – задает страничную блокировку. В сочетании с HoldLock позволяет избежать немедленного наложения табличной блокировки при отсутствии подходящего индекса.

Rowlock – блокировка на уровне записей.

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

Select <список выбора>

FROM имя таблицы1| вида[(указание оптимизатору)],

имя таблицы n| вида [(указания оптимизатору)]

Создание транзакций реализуется командой:

Begin TRAN [SACTION]

<инструкции TR.SQL>

[ROLLBACK TRAN[SACTION]]

COMMIT TRAN[SACTION]

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

Ключевое слово COMMIT TRAN приводит к тому, что все, что было сделано в транзакции, будет реализовано в базе данных.

ROLLBACK Tran – отменяет все инcтрукции. Нельзя внутри транзакции использовать команды: Create всех видов, DROP всех видов, Alter, Crant и Revoke, Select INTO, TRUNCATE TABLE. Для работы с большими транзакциями используется именованные транзакции и точки сохранения.