Часть 9: Транзакции в базах данных. BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT
Напомню, что команда BEGIN TRANSACTION говорит СУБД о том, что клиент хочет начать транзакцию и при этом СУБД должна изолировать данные, с которыми будет работать пользователь в транзакции. В этой статье мы ознакомимся с синтаксисом команды BEGIN TRANSACTION и некоторыми ее особенностями в SQLite3.
Синтаксис и использование оператора BEGIN TRANSACTION в SQLite
Рассмотрим первую команду из группы управления транзакциями в SQLite3. Команда BEGIN TRANSACTION в SQLite позволяет начать транзакцию. Давайте рассмотрим синтаксис команды BEGIN TRANSACTION в SQLite3.
Синтаксис команды BEGIN TRANSACTION в базах SQLite3 довольно прост: сначала идут два ключевых слово BEGIN TRANSACTION, а далее уже идут команды транзакции. Этот синтаксис СУБД SQLite3 позволяет упростить еще сильнее, мы можем не писать ключевое слово TRANSACTION.
SQLite3 позволяет выбрать режим блокировки при выполнении транзакции, для этого используются ключевые слова: DEFERRED, IMMEDIATE, EXCLUSIVE.
DEFERRED - База данных блокируется на запись при выполнение первого оператора чтения или записи, который находится внутри тела транзакции (после команды BEGIN). При такой блокировки база данных будет доступна другим пользователям только для чтения, но не для записи. Режим DEFERRED является стандартным в SQLite3 и его можно не указывать.
IMMEDIATE - Выполнит блокировку базы данных после ключевого слова BEGIN.
EXCLUSIVE - Заблокирует базу данных для других пользователей не только на запись, но и на чтение. Блокировка происходит после ключевого слова BEGIN.
Команда COMMIT в базах данных SQLite (оператор COMMIT в SQLite3)
Рассмотрим вторую команду управления транзакциями в базах данных SQLite. COMMIT TRANSACTION в SQLite3 позволяет подтвердить операции, выполненные внутри транзакции. Хочу обратить ваше внимание, что SQL предложение, содержащее команду BEGIN и предложение, содержащее команду COMMIT – это две разных команды для СУБД, между которыми может быть множество запросов к базе данных распределенных во времени. Оператор COMMIT в базах данных SQLite подтверждает изменения, внесенные транзакцией. Давайте взглянем на синтаксис команды COMMIT в SQLite3. Хотя я и использую термин оператор, вместо команда, позволю напомнить, что SQL операторы и команды — это разные языковые конструкции.
Синтаксис оператора COMMIT TRANSACTION в SQLite3 очень прост: ключевое слово COMMIT, которое можно заменить словом END, за которым идет ключевое слово TRANSACTION. SQLite3 позволяет не писать ключевое слово TRANSACTION. Давайте посмотрим на примере работу команды COMMIT TRANSACTION в базах данных SQLite3.
Пример использования оператора COMMIT в базах данных SQLite
--Начинаем транзакцию BEGIN; -- Выберем первых 10 записей из таблицы City SELECT * FROM city LIMIT 10; -- Удаляем первую строку из таблицы city DELETE FROM city WHERE id = 1; -- Посмотрим на первых 10 записей SELECT * FROM city LIMIT 10; /* * Пока вы не выполните команду COMMIT, * строка не будет удалена, так как транзакция еще не подтверждена, * в этом можно убедиться, подключившись к базе данных World * другим клиентом, выполнив SELECT */ COMMIT;
На самом деле это пример ради примера, удалить первую строку из таблицы командой DELETE мы могли бы и не используя транзакции. Так же хочу заметить, что если вы попробуете установить второе соединение с базой данных World в тот момент, когда транзакция не завершена, то таблица City будет доступна только для чтения из-за свойства изоляции транзакции. А теперь, воспользовавшись командой SELECT, убедимся, что внесенные изменения вступили в силу.
-- Посмотрим на первых 10 записей после подтверждения транзакции SELECT * FROM city LIMIT 10;
По сути, пока вы не выполните команду COMMIT, SQLite3 не внесет ни одного изменения в базу данных (не выполнит ни одну команду после оператора BEGIN). Как только вы сказали SQLite COMMIT, все изменения будут выполнены.
Команда ROLLBACK в базах данных SQLite (оператор ROLLBACK в SQLite3)
Рассмотрим третью команду из группы управления транзакциями – команду ROLLBACK TRANSACTION. Команда ROLLBACK TRANSACTION в базах данных SQLite используется для отмены запросов, введенных после команды BEGIN. Если команда COMMIT подтверждает изменения, то оператор ROLLBACK TRANSACTION их откатывает и завершает транзакцию. Давайте посмотрим на синтаксис команды ROLLBACK в базах данных SQLite3.
Синтаксис оператора ROLLBACK в SQLite3 очень прост: сначала идет ключевое слово ROLLBACK, а затем идет ключевое слово TRANSACTION, которое можно не писать. Сейчас не обращайте внимание на SAVEPOINT, на мой взгляд это отдельная конструкция, которую мы рассмотрим в следующей записи. Давайте рассмотрим пример использования команды ROLLBACK TRANSACTION в SQLite.
Для примера использования оператора ROLLBACK TRANSACTION в SQLite3 будем использовать демонстрационную базу данных World.db3. Совет: если вы самостоятельно будете повторять пример ROLLBACK TRANSACTION в SQLite, то выполняйте все команды по очереди, а не копируйте весь листинг целиком.
--Начинаем транзакцию BEGIN; /* * Выберем первых 10 записей из таблицы City * (в результате мы получим записи со 2 * по 11, т.к. ранее мы уже удалили запись с id =1) */ SELECT * FROM city LIMIT 10; -- Удаляем вторую строку из таблицы city DELETE FROM city WHERE id = 2; -- Посмотрим на первых 10 записей и увидим, что вывелись записи с 3 по 12 SELECT * FROM city LIMIT 10; /* * Пока вы не выполните команду ROLLBACK, * транзакция не будет завершена,а вы * будете видеть все изменения */ ROLLBACK;
А теперь давайте убедимся в том, что оператор ROLLBACK TRANSACTION сработал и мы не внесли никаких изменений в таблицу базы данных SQLite3. Для этого мы выведем записи со второй по одиннадцатую командой SELECT:
/* * Посмотрим на первых 10 записей после подтверждения транзакции, * вы увидите записи со 2 по 11 */ SELECT * FROM city LIMIT 10;
Обратите внимание: иногда я использую слово оператор, вместо слова команда, это не совсем правильно с формальной точки зрения, так как SQL операторы не являются командами, так же часто вы можете услышать словосочетание SQL запрос, когда на самом деле человек говорит про SQL предложение. Пока вы не выполните команду ROLLBACK, SQLite3 не завершит транзакцию и не отменит изменения, внесенные данной транзакцией. Как только вы сказали SQLite ROLLBACK, все изменения будут отменены, а транзакция завершена.
Не важно какие команды выполнялись внутри транзакции, команда ROLLBACK TRANSACTION отменит любые. Вы создали базу данных командой CREATE или вы добавили несколько строк в таблицу командой INSERT, да даже если вы удалили таблицу командой DROP... ROLLBACK TRANSACTION вернет базу данных к тому состоянию, в каком она была до транзакции.
Так же важно понимать, что если вы завершите транзакцию командой COMMIT, то оператор ROLLBACK вам уже не поможет, так как изменения были подтверждены. Обычно приложения, использующие базы данных и транзакции, принимают решения о том, как завершить транзакцию на основе действий пользователя или каких-либо внешних факторов, например, вы совершаете платеж картой в магазине, и тут неожиданно гаснет свет и терминал отключается: в этом случае деньги магазину переведены не будут, а вы не сможете забрать товар, так как его не оплатили.
Команда SAVEPOINT в базах данных SQLite (оператор SAVEPOINT в SQLite3)
Бывают такие ситуации, когда нужно отменить операции произведенные в транзакции, но не завершать ее и эта запись, как раз таки и покажет вам, как это сделать.
В базах данных SQLite для таких целей есть команда SAVEPOINT. Оператор SAVEPOINT является, с одной стороны, псевдонимом команд COMMIT и BEGIN, но в тоже время, использование оператора SAVEPOINT в базах данных SQLite3 позволяет откатить транзакцию на контрольную точку, но не завершать ее, чтобы выполнить операции повторно. В этой статье мы с вами разберем синтаксис команды SAVEPOINT, особенности ее реализации в SQLite и посмотрим несколько примеров использования SAVEPOINT в SQLite3.
Команда SAVEPOINT в базах данных SQLite – это еще один способ начать транзакцию. Команда SAVEPOINT в SQLite3 может работать, как самостоятельно, так и внутри конструкции BEGIN … COMMIT. По сути, оператор SAVEPOINT в SQLite3 создает транзакцию с именем, имя транзакции может быть не уникальным, а работает транзакция SAVEPOINT в базах данных SQLite3 в режиме DEFERRED, о котором мы говорили, когда рассматривали оператор BEGIN. Давайте рассмотрим синтаксис команды SAVEPOINT в SQLite.
Синтаксис команды SAVEPOINT в SQLite3 при успешном завершении транзакции выглядит следующим образом: начинаем транзакцию командой SAVEPOINT, далее даем имя нашей транзакции, после чего следует набор SQL запросов внутри транзакции, а подтверждение транзакции происходит при помощи команды RELEASE SAVEPOINT и название метки. SQLite3 позволяет SAVEPOINT не писать, когда мы хотим завершить транзакцию. Рассмотрим синтаксис оператора SAVEPOINT в базах данных SQLite3 в том случае, когда нам необходимо отменить изменения.
Если нам необходимо отменить изменения транзакции в SQLite3 предусмотрен второй вариант использования SAVEPOINT: начинаем транзакцию с ключевого слова SAVEPOINT, за которым идет имя транзакции, далее следует набор запросов, после которого для отмены транзакции идет ключевое слово ROLLBACK TRANSACTION TO SAVEPOINT и имя транзакции. SQLite позволяет опускать слово TRANSACTION и в этом случае. Давайте рассмотрим примеры использования команды SAVEPOINT в SQLite3.
Для примера работы SAVEPOINT в SQLite будем использовать базу данных World.db3 из предыдущих записей. Если вы помните, мы уже удалили первую строку из таблицы City. Совет: если вы самостоятельно будете повторять пример SAVEPOINT в SQLite3, то выполняйте все команды по очереди, а не копируйте весь листинг целиком.
-- Создаем транзакцию с именем при помощи команды SAVEPOINT SAVEPOINT transact1; -- Посмотрим первых 10 записей из таблицы City SELECT * FROM City LIMIT 10; -- Удалим запись с id = 2 DELETE FROM city WHERE id = 2; -- Посмотрим на первых 10 записей SELECT * FROM city LIMIT 10; /* * Пока вы не выполните команду RELEASE SAVEPOINT, * строка не будет удалена, так * как транзакция еще не подтверждена, * в этом можно убедиться, подключившись к * базе данных World другим клиентом, выполнив SELECT */ RELEASE transact1; /* * Посмотрим на первых 10 записей после подтверждения транзакции, * строки с id = 2 вы не увидите */
В принципе, ничего хитрого внутри транзакции мы не сделали: воспользовавшись оператором SELECT мы посмотрели первых 10 строк из таблицы и удалили строку с id = 2 при помощи оператора DELETE. Эти действия можно было бы сделать не используя транзакцию и оператор SAVEPOINT. Убедиться в том, что изменения были подтверждены можно, опять же, воспользовавшись командой SELECT:
SELECT * FROM city LIMIT 10;
Так работает оператор SAVEPOINT в SQLite3 при успешном завершении транзакции. Давайте теперь посмотрим, как работает SAVEPOINT в SQLite3 в том случае, если транзакцию нужно откатить и завершить, как это было, когда мы рассматривали команду ROLLBACK TRANSACTION.
--Начинаем транзакцию с именем transact1 SAVEPOINT transact1; /* * Выберем первых 10 записей из таблицы City * (в результате мы получим записи с 3 * по 12, т.к. ранее мы уже удалили запись с id =2) */ SELECT * FROM city LIMIT 10; -- Удаляем третью строку из таблицы city DELETE FROM city WHERE id = 3; -- Посмотрим на первых 10 записей и увидим, что вывелись записи с 4 по 13 SELECT * FROM city LIMIT 10; /* * Пока вы не выполните команду ROLLBACK, * транзакция не будет завершена, а вы * будете видеть все изменения */ ROLLBACK;
А теперь давайте посмотрим, что произошло с нашей базой данных после завершения транзакции:
/* * Посмотрим на первых 10 записей после подтверждения транзакции, * вы увидите записи с 3 по 12 */ SELECT * FROM city LIMIT 10;
Так работает команда SAVEPOINT в базах данных SQLite3 при завершении транзакции с откатом. Давайте теперь посмотрим, как работает оператор SAVEPOINT в SQLite3 в том случае, если транзакцию нужно откатить, но не завершить.
--Начинаем транзакцию с именем transact1 SAVEPOINT transact1; /* * Выберем первых 10 записей из таблицы City * (в результате мы получим записи с 3 * по 12, т.к. ранее мы уже удалили запись с id =2) */ SELECT * FROM city LIMIT 10; -- Удаляем третью строку из таблицы city DELETE FROM city WHERE id = 3; -- Посмотрим на первых 10 записей и увидим, что вывелись записи с 4 по 13 SELECT * FROM city LIMIT 10; /* * Пока вы не выполните команду ROLLBACK TRANSACTION TO SAVEPOINT, * отката изменений не произойдет, и вы будете видеть все изменения */ ROLLBACK TRANSACTION TO SAVEPOINT transact1; /* * Посмотрим на первых 10 записей после подтверждения транзакции, * вы увидите записи с 3 по 12 */ SELECT * FROM city LIMIT 10;
Хочу обратить ваше внимание на то, что последний запрос SELECT будет выполняться в рамках транзакции transact1, так как мы сделали откат до метки transact1, то есть отменили все запросы, начиная с SAVEPOINT transact1, но транзакцию не завершили. Так же хочу обратить ваше внимание на следующий момент, когда я писал о SAVEPOINT я использовал два термина: первый — команда, второй -оператор. Первый термин правильный с формальной точки зрения, второй более популярный среди русскоязычных разработчиков. Про SQL операторы на моем блоге есть отдельная статья.