База SQLite и основы SQL. Часть 11 (транзакции)
18-11-2021Время чтения ~ 9 мин.SQL 3194
Существует ряд операций, которые имеют критическую важность. Например перевод денег с одного счёта на другой. Если это делать через SQL, то первым запросом нужно добавить деньги на новый счёт, а вторым запросом удалить деньги с первого. Но, что если между этими двумя операциями произойдет какой-то сбой? Очевидно, что это приведёт к проблеме целостности данных и некорректному отображению состояния счётов. Таким образом операция будет успешной только в том случае, если оба запроса прошли без ошибок.
Транзакция — это группа операторов, которые успешно выполнятся все вместе. Если на любой операции произошла ошибка, то транзакция считается незавершённой и дальше произойдёт откат до начального состояния.
Для задания начала блока транзакции используется ключевое слово BEGIN (или BEGIN TRANSACTION). В конце блока указывается END или COMMIT (или END TRANSACTION / COMMIT TRANSACTION).
В общем слово TRANSACTION во всех случаях можно опускать.
В целом это выглядит так:
BEGIN; любые sql-запросы COMMIT;
Когда встретится команда COMMIT, то все изменения в блоке транзакции будут внесены в базу данных.
Здесь важный момент в том, что транзакции бывают нескольких типов блокировки, которые позволяют разрешать конфликты множественных сессий подключений к БД.
Например к базе подключено несколько пользователей. Один из них выполняет вставку данных в базу (несколько INSERT). Другой — производит чтение данных (SELECT). Если вставку данных делать вне блока BEGIN ... COMMIT, то SQLite придётся решать вопрос как правильно обновлять файл базы, чтобы не нарушить целостность данных. А что будет, если сразу несколько пользователей обновляют данные?
Вот поэтому такие блоки и нужно оформлять как транзакции. Конечно, это актуально только для случаев, когда происходит множественное обновление данных. Для одиночных запросов не нужно указывать транзакции, поскольку SQLite делает это автоматически.
Команда ROLLBACK
С помощью этой команды можно сделать откат изменений.
BEGIN; любые sql-запросы ROLLBACK;
Как только встретится ROLLBACK, то SQLite сделает откат всех изменений в этом блоке. Но, если транзакция была уже завершена через COMMIT, то откат уже не получится, поскольку все изменения уже были зафиксированы.
Типы блокировок
Есть три вида блокировок:
- DEFERRED — отложенная — используется по умолчанию
- IMMEDIATE — немедленная
- EXCLUSIVE — эксклюзивная
Тип блокировки указывается сразу после слова BEGIN.
BEGIN IMMEDIATE; ... COMMIT;
Блокировка DEFERRED в реальности не блокирует базу, а изменения фиксируются тогда, когда это станет реально необходимо. Например, если два пользователя одновременно изменяют базу, то SQLite попытается самостоятельно разделить этот конфликт. Если это не получится, что для одного из пользователей возникнет ошибка о невозможности внести изменения (SQLITE_BUSY).
Блокировка IMMEDIATE сразу блокирует базу на внесение изменений. Здесь всё будет зависеть от того, сколько в этот момент читающих сессий и SQLite дождется завершения этих операций и начнет вносить изменения в базу. Если же в один момент будет несколько сессий на изменение данных, то все они будут происходить по очереди (ожидающая транзакция). Этот режим хорош тем, что фактически гарантирует одновременно чтение данных и изменение. Конечно тут многое будет зависеть от скорости работы сервера.
Блокировка EXCLUSIVE полностью блокирует другие сессии на чтение и запись. Пока она не завершится, то другие пользователи не смогут получить данные из базы.
Использование блокировок имеет смысл только для многопользовательского режима. И здесь важно определиться с типом блокировки. Оптимальный вариант — это IMMEDIATE и главное, чтобы все пользователи работали с этим же типом блокировки.
Транзакции ещё имеют смысл для случаев, когда необходимо выполнить множественное изменение базы, например вставка в несколько таблиц. Когда начинается транзакция, то SQLite используется специальный кэш, который работает очень быстро, а когда транзакция фиксируется, то SQLite одним скопом вносит изменения в базу. То есть, если вам нужно вносить или изменять данные в нескольких таблицах и это будет состоять из нескольких INSERT/UPDATE подряд, то нужно заключить все эти запросы в один блок транзакции.
INSERT с подзапросом SELECT
У INSERT есть интересная возможность, которая очень часто используется в SQLite для изменения таблиц. Как мы уже знаем команда ALTER TABLE в SQLite не очень мощная, поэтому во многих случаях используется небольшая хитрость, позволяющая обходить её ограничения.
Например в процессе развития программы вдруг выясняется, что нужно изменить структуру таблицы или ещё хуже — поменять тип или ограничения для какого-то поля. Поскольку у нас нет ALTER TABLE MODIFY, то используют другой алгоритм:
- Вначале исходная таблица переименовывается.
- Создаётся новая с нужными полями.
- В неё добавляются все данные из старой таблицы.
- Старая таблица удаляется.
Естественно всё это происходит в одном блоке транзакции. Рассмотрим как этот происходит на примере.
Сделаем простую таблицу и наполним её данными:
CREATE TABLE t3 ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name INTEGER ); INSERT INTO t3 (name) VALUES (10), (20), (30), (40), (50);
Через какое-то время выясняется, что поле name нужно поменять на TEXT и добавить условие NOT NULL и DEFAULT. Делаем через новую таблицу.
-- начинаем транзакцию BEGIN IMMEDIATE; -- переименовываем старую таблицу ALTER TABLE t3 RENAME TO t3_old; -- создаём новую CREATE TABLE t3 ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL DEFAULT '' ); -- вставка всех данных из старой INSERT INTO t3 SELECT * FROM t3_old; -- удаляем старую DROP TABLE t3_old; -- фиксируем изменения COMMIT;
Подобным способом можно произвольно изменять таблицы. В основном это благодаря INSERT, которая позволяет вставлять данные из подзапроса SELECT. В нашем случае поля старой и новой таблицы совпадают (по названию и расположению), поэтому здесь применяется короткий синтаксис. Если же нужно изменить таблицу более серьёзно, то используют подробный синтаксис, где указываются все поля.
Удалите таблицу t3 и создайте её заново в исходном виде. Теперь выполним её изменение:
BEGIN IMMEDIATE; ALTER TABLE t3 RENAME TO t3_old; CREATE TABLE t3 ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL DEFAULT '', slug TEXT NOT NULL DEFAULT '', level INTEGER NOT NULL DEFAULT 0 ); INSERT INTO t3 (id, name) SELECT id, name FROM t3_old; DROP TABLE t3_old; COMMIT;
Новая таблица будет иметь ещё и новые поля, которых нет в старой базе. Поэтому в INSERT мы указываем только те поля, которые есть в старой базе, а новые будут иметь значение по умолчанию.
Поскольку это обычный SELECT, то можно дополнительно указать фильтрацию во WHERE. В этом случае в новую таблицу попадут только избранные записи.
Подобный приём по изменению таблиц очень часто встречается в SQLite.
UPSERT — вставка или обновление
В практике часто будет встречаться задача, когда нужно будет решать нужно ли вставлять новую запись через INSERT или обновлять текущую через UPDATE. В программах обычно это решается так:
- Например есть значение id, по которому будет происходить обновление или добавление данных.
- Вначале выполняем запрос к базе
SELECT count(*) FROM таблица WHERE id = $id;и получаем число строк. - Если это 0, то нужно выполнять INSERT, если 1, то нужно делать UPDATE.
В SQLite начиная с версии 3.24.0 можно использовать специальную конструкцию INSERT, которая в случае возникновения конфликта, автоматом выполнит UPDATE. Эта фишка и называется UPSERT и заимствована из PostgreSQL.
Разберемся как это работает.
Сделаем таблицу
CREATE TABLE t4 ( id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL DEFAULT '' );
Добавим данные таким образом и посмотрим что получится:
INSERT INTO t4 (id, name) VALUES (1, 'first (insert)') ON CONFLICT (id) DO UPDATE SET name = 'first (update)'; SELECT * FROM t4; id name -------- 1 first (insert)
То есть произошла вставка данных через INSERT.
Теперь ещё раз добавим данные с тем же id:
INSERT INTO t4 (id, name) VALUES (1, 'first (insert)') ON CONFLICT (id) DO UPDATE SET name = 'first (update)'; SELECT * FROM t4; id name -------- 1 first (update)
В данном случае уже сработал UPDATE и, теперь, сколько бы мы не выполняли этот запрос данные будет только обновляться.
Особенность здесь в ключевом слове ON CONFLICT, где нужно указать уникальное поле которое проверяется на конфликт. Когда работает INSERT, то в первый раз поле id = 1 не существовало, поэтому INSERT и сработал. Второй раз INSERT вернул ошибку, поскольку id уникальный и нельзя его ещё раз добавить. Поэтому сработала ветка ON CONFLICT, которая запустила UPDATE.
Стоит отметить, что UPSERT нестандартная возможность SQL, поэтому пользоваться ей нужно осознано. Но нельзя отрицать, что это удобная возможность SQLite.
CHECK — проверка на ограничения
Помните, когда мы делали таблицу с полем status, то рассматривали вариант использования либо простого текстового поля, либо создания отдельной таблицы? Всё из-за того, что поле может иметь только два значения: «draft» или «publish». В SQLite нет типа данных для перечисления, но можно добавить проверку для поля, чтобы ограничить его значения.
CREATE TABLE t5 (
id INTEGER PRIMARY KEY NOT NULL,
status TEXT NOT NULL DEFAULT 'draft'
CHECK (status = 'draft' OR status = 'publish')
);
INSERT INTO t5 (status) VALUES ('draft'); -- Ok
INSERT INTO t5 (status) VALUES ('publish'); -- Ok
INSERT INTO t5 (status) VALUES ('private');
-- Ошибка CHECK constraint failed: status = 'draft' OR status = 'publish'
Команда CHECK используется при создании таблицы. Здесь указываются условия для текущего поля в виде логических операций. В данном примере мы делаем так, чтобы поле status было либо «draft», либо «publish». Если условие CHECK вернёт false, то база сгенерирует ошибку.
Подзапросы
Подзапрос это SELECT внутри другого SELECT (или другой команды). Что-то вроде такого:
SELECT * FROM (SELECT name FROM t1 WHERE id>2);
В данном случае вначале выполнится вложенный SELECT и его результат (а это набор данных) будет использоваться внешним запросом в качестве источника данных. С моей точки зрения подзапросы имеют смысл только для сложных задач, где много данных и нужна сложная фильтрация. На начальном этапе изучения SQL можно ограничиться основными возможностями.
Составные запросы
Есть ещё такая вещь, как объединение запросов UNION. Она по смыслу похожа на операции JOIN, только происходит объединение результатов нескольких запросов.
SELECT name FROM t1 UNION SELECT name FROM t3;
Главное в составных запросах — это то, чтобы возвращаемые поля (столбцы) были одного типа данных. В этом случае SQLite их объединит в одной итоговой таблице.
Команда UNION объединяет результаты, но при этом исключает дубли. Если их нужно оставить, то используется UNION ALL:
SELECT name FROM t1 UNION ALL SELECT name FROM t3;
Если нам нужно полностью вывести первый запрос, а второй только те записи, которые совпадают, то используется EXCEPT:
SELECT name FROM t1 EXCEPT SELECT name FROM t3;
Ну и если нужно получить пересечение: когда вывести только совпадающие данные, то используется INTERSECT:
SELECT name FROM t1 INTERSECT SELECT name FROM t3;
В заключении
В мире есть профессии, где требуется выполнять анализ данных. Такие специалисты используют сложные sql-запросы, формируют большие таблицы и применяют такие вещи, о которых большинство просто не в курсе (вроде оконных функций). Я с такими никогда не встречался, поскольку мои задачи намного скромнее и проще. В этом цикле я остановился на таких вещах, которые пригодятся в работе обычному разработчику. Поскольку SQL я использую не так часто, то мне нет необходимости запоминать все тонкости и нюансы: вполне хватает небольшого cheatsheet, чтобы быстро найти нужную команду. Достаточно один раз разобраться как она работает, чтобы понимать как её использовать в своей программе. :-)
На этом пока всё. Дальше поговорим об использовании SQLite в PHP, но это уже немного из другой «оперы». :-)