Управление и автоматизация БД Практическая работа №4

Практическое занятие № 4

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

Цель работы: научиться осуществлять управление транзакциями с помощью SQL

Приобретаемые умения и навыки: умение работать с транзакциями в SQL Server Management Studio.

Норма времени: 2 часа.

Оборудование: Компьютер с установленным программным обеспечением и подключенный к Internet.

Методические указания по выполнению практической работы

Задание 1. Применяем транзакции c автофиксацией

Запустите SQL Server Management Studio, в своей БД на сервере создайте таблицу, которую мы будем использовать в следующем опыте для изучения поведения транзакций: 

Теперь давайте вставим в таблицу table 1 три новых строки. Для этого введите следующие инструкции в окне запроса и выполните все три инструкции вместе.

Вы получите сообщение, которое информирует вас о том, что SQL Server не разрешает вставку значения NULL в столбец col1, потому что для этого столбца задано условие NOT NULL. Введите и выполните следующую инструкцию SELECT, чтобы проверить, были ли записи успешно вставлены в таблицу.

SELECT i,col1,col2 FROM table1;

Как видите, вставка второй строки не выполнена, но первая и третья строки успешно вставлены. Когда SQL Server использует транзакции с автофиксацией, каждая инструкция рассматривается как транзакция. Если одна инструкция генерирует ошибку, соответствующая ей транзакция автоматически подвергается откату. Если инструкция успешно и без ошибок выполняется, то транзакция автоматически фиксируется. Следовательно, инструкции 1 и 3 были зафиксированы, а инструкция 2, вызвавшая ошибку, была отменена. Обратите внимание на то, что такое поведение имеет место даже в том случае, если три инструкции передаются вместе в виде пакета. Пакетное выполнение не определяет, следует ли обрабатывать все инструкции в пакете как единую транзакцию.

Явные транзакции

Для явной транзакции разработчик определяет начало транзакции и момент, в который она должна быть зафиксирована или подвергнута откату. Это достигается при помощи инструкций T-SQL BEGIN TRANSACTION, COMMIT TRANSACTION и ROLLBACK TRANSACTION. Явные транзакции независимы от пакета. Явная транзакция может объединять несколько пакетов; в одном пакете может быть задано несколько явных транзакций.

Задание 2. Применяем явные транзакции

В SQL Server Management Studio введите и выполните следующую инструкцию, чтобы выполнить усечение таблицы table1.

TRUNCATE TABLE table1;

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

Вы получите такое же сообщение, как и раньше, в котором сообщается, что SQL Server не разрешает вставку значения NULL в столбец col1, потому что для этого столбца задано условие NOT NULL.

Введите и выполните следующую инструкцию SELECT, чтобы проверить, были ли вставлены записи.

SELECT i,col1,col2 FROM table1;

Вы видите, что результат тот же, что и в режиме автофиксации. Две из трех записей вставлены в таблицу, а одна, нарушающая ограничение NULL, не вставлена. Что произошло? Как отмечалось ранее, обязанностью разработчика является не только определение длины транзакции, но и то, должен ли выполняться откат. Поэтому в транзакцию необходимо добавить обработчик ошибок. Без обработчика ошибок SQL Server после ошибки просто обработает следующую инструкцию, потому что пакет не отменяется. В предыдущем пакете SQL Server просто обрабатывает каждую инструкцию INSERT и после этого обрабатывает инструкцию COMMIT TRAN. Следовательно, у нас тот же результат, что и в режиме автофиксации.

Чтобы добавить обработчик ошибок, можно использовать TRY и CATCH. Снова выполните усечение таблицы, а затем запустите транзакцию с обработчиком ошибок, как показано ниже.

- выполняем усечение таблицы

TRUNCATE TABLE table1

- транзакция с обработчиком ошибок

В этом случае вы не получите сообщения об ошибке, поскольку ошибка была захвачена блоком CATCH.

Введите и выполните следующую инструкцию SELECT, чтобы проверить, был ли выполнен откат транзакции.

SELECT i,col1,col2 FROM table1;

Эта инструкция не возвратила ни одной записи. Как видите, произошел откат всей транзакции. Когда во второй инструкции INSERT произошло нарушение, SQL Server перешел к блоку CATCH и выполнил откат транзакции.

Остается только одна проблема – это код не возвращает каких-либо сообщений, которые информировали бы о том, что произошла ошибка. Это поведение управляется в блоке CATCH, в котором можно использовать особые функции для возвращения ошибок; можно также использовать функцию RAISERROR для задания пользовательского текста сообщения об ошибке. Измените блок CATCH как показано ниже.

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

Неявные транзакции

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

ALTER TABLE GRANT FETCH DELETE

CREATE REVOKE INSERT SELECT

DROP OPEN UPDATE TRUNCATE TABLE

Задание 3. Применяем неявные транзакции

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

SET IMPLICIT_TRANSACTIONS ON;

GO

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

CREATE TABLE T1 (i int PRIMARY KEY);

Чтобы проверить, открыта ли транзакция, можно использовать функцию @@TRANCOUNT; выполните следующую инструкцию SELECT:

SELECT @@TRANCOUNT AS [Transaction Count];

Результат равен 1; это означает, что соединение имеет открытую транзакцию. Значение 0 означало бы, что в данный момент не открыто ни одной транзакции, а число больше 1 – что имеют место вложенные транзакции (о которых речь пойдет позже). Теперь вставьте в таблицу запись и снова проверьте значение @@TRANCOUNT, выполнив следующие инструкции:

INSERT INTO T1 VALUES(5);

GO

SELECT @@TRANCOUNT AS [Transaction Count];

Значение функции @@TRANCOUNT, как и прежде, равно 1. SQL Server не запустил новой транзакции, потому что уже существует одна открытая транзакция.

Теперь выполним откат транзакции и снова проверим значение функции @@TRANCOUNT, выполнив следующий код. Вы увидите, что значение @@TRANCOUNT будет равно 0 после выполнения инструкции ROLLBACK TRAN.

ROLLBACK TRAN

GO

SELECT @@TRANCOUNT AS [Transaction Count];

Попробуем сделать выборку из таблицы T1.

SELECT * FROM T1;

Мы получим сообщение об ошибке, потому что таблицы больше не существует. Неявная транзакция была запущена при помощи инструкции CREATE TABLE, а инструкция ROLLBACK TRAN отменила результаты работы, выполненной первой инструкцией.

Отключим неявный режим, выполнив следующий код:

SET IMPLICIT_TRANSACTIONS OFF;

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

Вложенные транзакции

Явные транзакции могут быть вложенными; это означает, что можно запускать явные транзакции в других явных транзакциях. Одна из основных причин поддержки этого механизма – это разрешение транзакций внутри хранимых процедур, независимо от того, была ли сама процедура вызвана из транзакции. Как же вложенные транзакции обрабатываются в SQL Server? Давайте изучим вложенные транзакции на двух простых примерах.

Задание 5. Изучаем вложенные транзакции

Воспользуйтесь функцией @@TRANCOUNT, чтобы узнать, как SQL Server обрабатывает вложенные транзакции. Введите и выполните следующий пакет.

Из результата видно, что каждая инструкция BEGIN TRAN увеличивает значение @@TRANCOUNT на 1, а каждая инструкция COMMIT TRAN уменьшает значение на 1. Как мы уже наблюдали ранее, значение 0 означает, что не открыто ни одной транзакции. Следовательно, транзакция завершается, когда значение функции @@TRANCOUNT уменьшается от 1 до 0, что происходит при фиксации самой внешней транзакции. Таким образом, каждая внутренняя транзакция требует фиксации. Самая внешняя транзакция определяет, будут ли внутренние транзакции полностью фиксироваться, поскольку эта транзакция запускается первой инструкцией BEGIN TRAN и фиксируется только последней инструкцией COMMIT TRAN. Если эта самая внешняя транзакция не зафиксирована, то вложенные в нее транзакции также не будут зафиксированы. Введите и выполните следующий пакет, чтобы проверить, что произойдет в случае отката транзакции. 

В этом примере город студента обновляется в процессе вложенной транзакции, которая сразу же фиксируется. Затем выполняется инструкция ROLLBACK TRAN. Инструкция ROLLBACK TRAN уменьшает значение функции @@TRANCOUNT с 0 до 1, и выполняет откат всей транзакции вместе со всеми вложенными транзакциями, независимо от того, были ли они до этого зафиксированы. Следовательно, обновление данных, выполненное в течение вложенной транзакции, подвергается откату, и данные не изменяются.

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

Задания для самостоятельного выполнения

Указание. Оформите решение следующих задач в виде хранимых процедур.

1. Определите время года для заданной даты: осень, зима, весна или лето. Замечания. Для извлечения года, месяца и дня из даты, используйте функции YEAR(), MONTH() и DAY(). Для управления преобразованием даты из строки во внутренний формат используется следующая команда: SET DATEFORMAT Формат.

Опция Формат указывает формат ввода даты и может принимать следующие значения – mdy, dmy, ymd, myd и dym (где m, d и y – день, месяц и год соответственно).

2. Известна дата рождения пользователя. Проверить, исполнилось ему или нет полных 16 лет. Можно использовать функции DATEADD, DATEDIFF.

Контрольные вопросы:

1. Что такое транзакция в контексте баз данных?

2. В чем разница между явной и неявной транзакцией?