Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Транзакции — довольно интересный и полезный инструмент языка SQL и реляционных баз данных. Мы уже знаем о том, какие SQL команды позволяют запустить транзакцию, какой командой можно успешно завершить транзакцию и как можно откатить изменения, которые мы производили во время транзакции и завершить транзакцию. Но бывают такие ситуации, когда нужно отменить операции произведенные в транзакции, но не завершать ее и эта запись, как раз таки и покажет вам, как это сделать.

Команда SAVEPOINT в базах данных SQLite (оператор SAVEPOINT в SQLite3)

В базах данных SQLite для таких целей есть команда SAVEPOINT. Оператор SAVEPOINT является, с одной стороны, псевдонимом команд COMMIT и BEGIN, но в тоже время, использование оператора SAVEPOINT в базах данных SQLite3 позволяет откатить транзакцию на контрольную точку, но не завершать ее, чтобы выполнить операции повторно. В этой статье мы с вами разберем синтаксис команды SAVEPOINT, особенности ее реализации в SQLite и посмотрим несколько примеров использования SAVEPOINT в SQLite3.

Синтаксис оператора SAVEPOINT в базах данных SQLite

Рассмотрим четвертую команду управления транзакциями в SQLite3 – SAVEPOINT. Команда SAVEPOINT в базах данных SQLite – это еще один способ начать транзакцию. Команда SAVEPOINT в SQLite3 может работать, как самостоятельно, так и внутри конструкции BEGIN … COMMIT. По сути, оператор SAVEPOINT в SQLite3 создает транзакцию с именем, имя транзакции может быть не уникальным, а работает транзакция SAVEPOINT в базах данных SQLite3 в режиме DEFERRED, о котором мы говорили, когда рассматривали оператор BEGIN. Давайте рассмотрим синтаксис команды SAVEPOINT в SQLite.

Синтаксис команды SAVEPOINT в базах данных SQLite3 при завершении транзакции с подтверждением изменений

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

Синтаксис оператора SAVEPOINT в базах данных SQLite с откатом внесенных изменений

Если нам необходимо отменить изменения транзакции в SQLite3 предусмотрен второй вариант использования SAVEPOINT: начинаем транзакцию с ключевого слова SAVEPOINT, за которым идет имя транзакции, далее следует набор запросов, после которого для отмены транзакции идет ключевое слово ROLLBACK TRANSACTION TO SAVEPOINT и имя транзакции. SQLite позволяет опускать слово TRANSACTION и в этом случае. Давайте рассмотрим примеры использования команды SAVEPOINT в SQLite3.

Примеры использования команды SAVEPOINT в базах данных SQLite3

Для примера работы SAVEPOINT в SQLite будем использовать базу данных World.db3 из предыдущих записей. Если вы помните, мы уже удалили первую строку из таблицы City. Совет: если вы самостоятельно будете повторять пример SAVEPOINT в SQLite3, то выполняйте все команды по очереди, а не копируйте весь листинг целиком.

[php]

— Создаем транзакцию с именем при помощи команды 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

— вы не увидите

[/php]

В принципе, ничего хитрого внутри транзакции мы не сделали: воспользовавшись оператором SELECT мы посмотрели первых 10 строк из таблицы и удалили строку с id = 2 при помощи оператора DELETE. Эти действия можно было бы сделать не используя транзакцию и оператор SAVEPOINT. Убедиться в том, что изменения были подтверждены можно, опять же, воспользовавшись командой SELECT:

[php]

SELECT * FROM city LIMIT 10;

[/php]

Так работает оператор SAVEPOINT в SQLite3 при успешном завершении транзакции. Давайте теперь посмотрим, как работает SAVEPOINT в SQLite3 в том случае, если транзакцию нужно откатить и завершить, как это было, когда мы рассматривали команду ROLLBACK TRANSACTION.

[php]

— Начинаем транзакцию с именем 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;

[/php]

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

[php]

— Посмотрим на первых 10 записей после подтверждения транзакции, вы увидите

— записи с 3 по 12

SELECT * FROM city LIMIT 10;

[/php]

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

[php]

— Начинаем транзакцию с именем 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;

[/php]

Хочу обратить ваше внимание на то, что последний запрос SELECT будет выполняться в рамках транзакции transact1, так как мы сделали откат до метки transact1, то есть отменили все запросы, начиная с SAVEPOINT transact1, но транзакцию не завершили. Так же хочу обратить ваше внимание на следующий момент, когда я писал о SAVEPOINT я использовал два термина: первый — команда, второй -оператор. Первый термин правильный с формальной точки зрения, второй более популярный среди русскоязычных разработчиков. Про SQL операторы на моем блоге есть отдельная статья.