2.19. Транзакции

Язык запросов Transact-SQL взял свое название от слова транзакция. Я думаю, что Microsoft не зря сконцентрировало на этом понятии особое внимание, ведь транзакции действительно являются очень мощным средством управления базой данных.

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

Рассмотрим классическую задачу – банковскую проводку. Допустим, что у нас есть таблица из двух полей – номер счета в банке и сумма денег на этом счету. Нам необходимо перевести деньги с одного счета на другой. Для этого нужно выполнить запрос UPDATE, чтобы уменьшить сумму первого счета на нужную сумму. После этого выполняем UPDATE, чтобы увеличить значение второго счета. Все вроде бы нормально. А что, если после уменьшения первого счета выключат свет и сервер не успеет пополнить другой счет? Деньги уже сняты, но никуда не записаны, а значит, они пропали.

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

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

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

Существует две разновидности транзакций в SQL Server:

• Скрытые транзакции, каждый оператор, такой как INSERT, UPDATE или DELETE выполняется в транзакции. Неявными транзакциями можно управлять и об этом мы поговорим в разделе 4.1.2;
• Явные транзакции объявленные пользователем – операторы, сгруппированные в BEGIN TRANSACTION и COMMIT TRANSACTION.

Очень важно понимать, что транзакции необходимы только при модификации данных, т.е. использовании операторов INSERT, UPDATE или DELETE. Простая выборка SELECT не изменяет данных, и запоминать или откатывать нечего. Нет, выполнять операции выборки в транзакции можно, но если транзакция не изменяет данные, то незачем ее вообще начинать.

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

• ALTER DATABASE
• BUCKUP LOG
• CREATE DATABASE
• DROP DATABASE
• RECONFIGURE
• RESTORE DATABASE
• RESTORE LOG
• UPDATE STATISTICS

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

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

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

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

Начало транзакции в MS SQL Server имеет следующий синтаксис:

BEGIN TRAN[SACTION] 
 [transaction name | @transaction name variable 
 [WITH MARK[‘description]]]

Опция Transaction name указывает имя транзакции определенное пользователем. Опция WITH MARK указывает, что транзакция маркирована в журнале транзакций.

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

COMMIT [ TRAN [ SACTION ] 
[ transaction_name | @tran_name_variable ] ]

Отмена транзакции и всех изменений производиться командой ROLLBACK, которая в общем виде выглядит следующим образом:

ROLLBACK [ TRAN [ SACTION ] 
    [ transaction_name | @tran_name_variable 
    | savepoint_name | @savepoint_variable ] ] 

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

-- Начинаем транзакцию
BEGIN TRANSACTION

-- Очищаем таблицу товаров
DELETE Товары

Теперь откройте еще одну копию программы или установите новое соединение, выбрав меню File/Connect (Файл/Соединиться). В новом окне напишем и выполним следующий запрос:

SELECT * 
FROM Товары

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

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

ROLLBACK TRANSACTION

Вот теперь запрос завершит свое выполнение, и при этом все данные останутся на месте, не смотря на то, что мы выполняли команду удаления всех записей. Данные не удалились, потому что команда выполнялась внутри транзакции, которую мы откатили.

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

Теперь откатываем транзакцию, выполняя команду ROLLBACK TRANSACTION. Снова выполняем запрос SELECT и видим, что данные вернулись на родину. Транзакция удачно отклонена и физического удаления из базы данных не произошло. Почему мы в этот раз без проблем смогли просмотреть таблицу, а из другой сессии просмотр изменяемой в транзакции таблицы не доступен? Блокировки происходит для всех сессий кроме той, которая выполняет транзакцию. В листинге 2.8 показан весь код эксперимента с подробными комментариями.

Листинг 2.8. Пример эксперимента с удалением данных в транзакции

-- Начинаем транзакцию
BEGIN TRANSACTION

-- Очищаем таблицу товаров
DELETE Товары

-- Проверяем и убеждаемся, что таблица пуста
SELECT * FROM Товары

-- Откатываем транзакцию
ROLLBACK TRANSACTION

-- Можете убедиться, что товары на месте
SELECT * FROM Товары

Все эти команды нужно выполнять в одном и том же окне. К тому же, если в одном окне (сессии) вы начали транзакцию, то именно в этом окне вы должны ее завершить (COMMIT) или откатить (ROLLBACK).

Если в листинге 2.8 заменить вызов команды ROLLBACK TRANSACTION на COMMIT TRANSACTION, то произойдет физическое удаление всех записей из таблицы товаров. Теперь удаленные строки вернуть уже невозможно.

Теперь посмотрим еще один пример в листинге 2.9.

Листинг 2.9. Пример работы с транзакциями

-- Начинаем транзакцию
BEGIN TRANSACTION

-- Вставляем строку данных в 
-- таблицу товаров
INSERT INTO Товары (Дата, [Название товара], Цена, Количество)
VALUES ('3.3.2005', 'КАРТОФЕЛЬ', 12.50, 10)

-- Обновить данные в последней строке
UPDATE Товары
SET Цена = 15
WHERE [Название товара] LIKE 'КАРТОФЕЛЬ'

COMMIT TRANSACTION

-- Обновить данные в последней строке
UPDATE Товары
SET Цена = 17
WHERE [Название товара] LIKE 'КАРТОФЕЛЬ'

-- Откатить транзакцию
ROLLBACK TRANSACTION

-- Выбрать все данные из таблицы
SELECT * FROM Товары

Тут достаточно много действий, поэтому давайте их рассмотрим поэтапно:

1. Начинаем транзакцию;
2. Добавляем запись о покупке товара с названием Картофель;
3. Обновить цену картофеля, увеличив ее до 15 рублей;
4. Завершаем транзакцию, запоминая изменения;
5. Обновляем цену до 17 руб.;
6. Откатываем транзакцию;
7. Просматриваем содержимое таблицы.

Что произошло с содержимым таблицы? Запись о картофеле добавлена, а значит, все что было до запоминания изменений (шаг 4) выполнено удачно. А вот цена равна 17-ти рублям. Почему? Неужели на шаге 6 мы не откатили изменение цены? Да, отката не произошло, потому что новая транзакция не начиналась. На шаге 1 мы начали транзакцию, а на шаге 4 завершили. Новая транзакция не начиналась, а значит откатывать нечего и шаг 6 завершиться ошибкой:

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Запрос ROLLBACK TRANSACTION не имеет соответствующего BEGIN TRANSACTION.

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

Для каждого оператора BEGIN TRANSACTION должен быть только один оператор COMMIT TRANSACTION или ROLLBACK TRANSACTION.

А что если внутри транзакции начать новую транзакцию? Результат неожиданный и давайте его увидим на примере (см. листинг 2.10).

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

-- Начинаем транзакцию 1
BEGIN TRANSACTION

-- Вставляем строку данных в таблицу товаров
INSERT INTO Товары 
   (Дата, [Название товара], Цена, Количество)
VALUES ('4.3.2005', 'МОРКОВЬ', 11.30, 1)

-- Начинаем транзакцию 2
BEGIN TRANSACTION

-- Обновить данные в последней строке
UPDATE Товары
SET Цена = 14
WHERE [Название товара] LIKE 'МОРКОВЬ'

ROLLBACK TRANSACTION

-- Запоминаем изменения
COMMIT TRANSACTION

-- Выбрать все данные из таблицы
SELECT * FROM Товары

Логика запроса следующая:

• Начать транзакцию;
• Вставить строку;
• Начать транзакцию;
• Обновить таблицу;
• Откатить транзакцию;
• Запомнить изменения.

По логике вещей, на шаге 5 мы должны были откатить вторую транзакцию (т.е. изменение таблицы), а на шаге 6 запоминаем транзакцию 1, в которой происходит добавление записи. Посмотрите содержимое таблицы. Ни добавления, ни тем более изменения. Почему? Если посмотреть сообщения, которые выдал сервер, то вы увидите, что на шаге 6 произошла ошибка о том, что нет соответствующего начала транзакции и нечего начинать. Получается, что оператор ROLLBACK TRANSACTION откатывает все начатые транзакции.

Но это не значит, что невозможно использовать вложенные транзакции. Просто откатывать транзакции нельзя на один шаг назад. Если заменить оператор ROLLBACK TRANSACTION на COMMIT, то ошибки не будет.

Посмотрим на листинг 2.11. В нем показан такой же пример, но с именованными транзакциями и без отката.

Листинг 2.11. Использование вложенных транзакций

BEGIN TRANSACTION T1

-- Вставляем строку данных в таблицу товаров
INSERT INTO Товары 
   (Дата, [Название товара], Цена, Количество)
VALUES ('4.3.2005', 'МОРКОВЬ', 11.30, 1)

-- Начинаем транзакцию 2
BEGIN TRANSACTION T2 

-- Обновить данные в последней строке
UPDATE Товары
SET Цена = 14
WHERE [Название товара] LIKE 'МОРКОВЬ'

COMMIT TRANSACTION T2

-- Запоминаем изменения
COMMIT TRANSACTION T1

-- Выбрать все данные из таблицы
SELECT * FROM Товары

В данном примере после операторов BEGIN TRANSACTION и COMMIT TRANSACTION указывается имя T1 и T2. Таким образом, мы идентифицируем транзакции и завершаем их в обратном порядке объявлению.

Но как же тогда можно откатывать транзакции до определенной точки? Достаточно просто и вложенные транзакции тут не причем. Для этого нужно просто поставить точку сохранения с помощью оператора SAVE TRAN, который имеет следующий вид:

SAVE TRAN [ SACTION ] { savepoint_name | @savepoint_variable }

Минимум, что необходимо указать – это сам оператор и имя точки сохранения. Например, следующий оператор создает точку сохранения с именем point1:

SAVE TRAN point1

В листинге 2.12 показан пример, в котором наконец-то создаем строку, изменяем ее и откатываем только изменение, а не всю транзакцию, вместе с добавлением строки.

Листинг 2.12. Откат до определенной точки

-- Начинаем транзакцию 1
BEGIN TRANSACTION T1

-- Вставляем строку данных в 
-- таблицу товаров
INSERT INTO Товары 
   (Дата, [Название товара], Цена, Количество)
VALUES ('4.3.2005', 'МОРКОВЬ', 11.30, 1)

-- Сохраняем транзакцию
SAVE TRAN ins_complete

-- Обновить данные в последней строке
UPDATE Товары
SET Цена = 14
WHERE [Название товара] LIKE 'МОРКОВЬ'

-- Откатываем транзакцию
ROLLBACK TRANSACTION ins_complete

-- Запоминаем изменения
COMMIT TRANSACTION T1

-- Выбрать все данные из таблицы
SELECT * FROM Товары

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

1. Начинаем транзакцию;
2. Добавляем строку;
3. С помощью оператора SAVE TRAN сохраняем состояние таблицы. Точнее сказать, ставим точку в журнале, ведь пока все изменения происходят только в журнале транзакций;
4. Обновляем цену последней добавленной строки;
5. Восстанавливаем состояние таблицы на точку сохранения, установленную на третьем шаге. В этот момент из журнала транзакций удаляется запись о необходимости обновить цену, а остается только запись о необходимости добавить строку;
6. Запоминаем изменения, а в журнале транзакций находиться только добавление строки и именно это сохраняется в таблице товаров.

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

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

Следующие рекомендации позволят вам сделать транзакции как можно короче:

• Без особой надобности не выполняйте запросы SELECT. Выборка данных должна происходить вне транзакции;
• Для уменьшения времени транзакции, будьте внимательны, когда используете долго выполняемые операторы Transact-SQL, такие как циклы и создание объектов базы данных;
• Не требуйте от пользователя ввода данных во время выполнения транзакции. Делайте ввод данных до начала выполнения транзакции.
• Операторы INSERT, UPDATE и DELETE должны быть главными в транзакции и они должны быть написаны так, чтобы получать минимальный набор строк, что позволяет повысить скорость работы любого запроса.
• Все проверки данных и подготовительные расчеты необходимо произвести до начала транзакции. После оператора BEGIN TRANSACTION должно выполняться только изменение данных.