Транзакции в SQLite: BEGIN, COMMIT, ROLLBACK

Транзакция — это «всё или ничего»

Транзакция в SQLite объединяет несколько SQL-запросов так, что они либо применяются все вместе, либо не применяются вовсе. Если что-то пойдёт не так посередине, достаточно откатить изменения — и база вернётся ровно в то состояние, в котором была до начала.

Классический пример — перевод денег между счетами:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

Оба UPDATE неразрывно связаны. Если база рухнет между ними, Ада останется без 2000 центов, а Борис ничего не получит. Если же обернуть их в BEGIN ... COMMIT, пара становится атомарной — либо выполняются обе операции, либо ни одна.

Автокоммит SQLite: режим по умолчанию

Каждый SQL-запрос, который вы запускали до этого момента, на самом деле был транзакцией. По умолчанию SQLite работает в режиме автокоммита: вокруг каждого запроса автоматически подставляются неявные BEGIN и COMMIT.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('первая');
INSERT INTO notes (body) VALUES ('вторая');
INSERT INTO notes (body) VALUES ('третья');

SELECT * FROM notes;

Три отдельных INSERT — это три отдельные транзакции и три похода на диск с fsync. Для разовых записей нормально, а вот для массовой загрузки уже медленно. Плюс ко всему, откатить группу запросов как единое целое не получится. BEGIN выключает автокоммит вплоть до ближайшего COMMIT или ROLLBACK.

ROLLBACK: как откатить транзакцию SQLite

ROLLBACK отменяет всё, что было сделано после соответствующего BEGIN. База возвращается ровно в то состояние, в котором была до начала транзакции.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

И UPDATE, и DELETE бесследно исчезают — таблица возвращается к тому виду, в котором была до BEGIN. Это и есть та страховочная сетка, которая позволяет приложению аккуратно прервать работу, если посреди многошаговой операции что-то пошло не так.

Кстати, нарушение ограничения внутри транзакции вовсе не откатывает её целиком автоматически. Откатывается только сам проблемный стейтмент, а транзакция остаётся открытой и ждёт вашего решения. Если нужна логика «всё или ничего», приложение само должно выполнить ROLLBACK, как только заметит ошибку.

Ускоряем массовые вставки

Поскольку при автокоммите каждый отдельный стейтмент делает свой fsync, обёртывание пачки запросов в одну транзакцию нередко даёт ускорение в 100 раз:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

Один сброс на диск при COMMIT вместо синка на каждую строку. Если вы когда-нибудь заливали тысячи строк и недоумевали, почему всё ползёт — в 99% случаев причина именно в этом.

DEFERRED, IMMEDIATE, EXCLUSIVE в SQLite

У BEGIN есть режим, который определяет, в какой момент SQLite берёт блокировки:

• BEGIN DEFERRED (по умолчанию) — никаких блокировок, пока вы не начнёте читать или писать. Блокировка на запись берётся лениво, на первом же пишущем запросе.
• BEGIN IMMEDIATE — блокировка на запись захватывается сразу. Другие соединения всё ещё могут читать, но начать запись параллельно никто уже не сможет.
• BEGIN EXCLUSIVE — то же, что IMMEDIATE, плюс другие соединения не смогут даже читать. В режиме WAL ведёт себя так же, как IMMEDIATE; разница проявляется только в старом режиме rollback journal.

BEGIN DEFERRED;     -- то же самое, что и обычный BEGIN
BEGIN IMMEDIATE;    -- сразу зарезервировать блокировку записи
BEGIN EXCLUSIVE;    -- зарезервировать всё (режим rollback-journal)

Выбор режима напрямую влияет на конкурентный доступ. С обычным BEGIN два соединения могут спокойно открыть транзакцию, обе почитать данные, а потом столкнуться лбами при попытке записи — тот, кто запросит блокировку на запись вторым, получит SQLITE_BUSY. И это ещё полбеды: к этому моменту он уже успел что-то прочитать, и все эти чтения придётся выбросить.

BEGIN IMMEDIATE решает проблему: если вы заранее знаете, что будете писать, запросите блокировку на запись сразу. Второе соединение тут же заблокируется (или быстро упадёт с ошибкой) — ещё до того, как сделает работу, которую потом пришлось бы выкидывать.

Простое правило: если транзакция будет писать — используйте BEGIN IMMEDIATE.

Чтение внутри транзакции видит снимок данных

Пока транзакция открыта, ваши SELECT-ы видят согласованный снимок базы — таким, каким он был на момент старта транзакции (в режиме WAL) или на момент первого чтения (в режиме rollback-journal). Изменения, которые в это время коммитят другие соединения, в ваших запросах внезапно не всплывут.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, ваша собственная запись видна
COMMIT;

SELECT n FROM counter;

Вы видите свои собственные незакоммиченные изменения, а другие подключения — нет. Как только вы делаете COMMIT, новое значение становится видно всем. Именно это имеют в виду, когда говорят, что SQLite работает в режиме serializable: здесь нет никакого переключателя READ COMMITTED, потому что по умолчанию уже включён самый строгий уровень изоляции.

Пример транзакции SQLite в коде приложения

В реальном коде шаблон обычно такой: try/except (или try/catch) вокруг основного блока и ROLLBACK в ветке с ошибкой:

-- Псевдокод для любой клиентской библиотеки
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

Большинство клиентских библиотек (питоновский sqlite3, better-sqlite3 и прочие) оборачивают всё это за вас — через блок with или хелпер transaction(). Но в доку своей библиотеки заглянуть всё-таки стоит: дефолты не всегда такие, как ожидаешь. У питоновского sqlite3, в частности, исторически было довольно странное поведение автокоммита; в свежих версиях наконец-то добавили нормальный параметр autocommit, который это чинит.

На чём чаще всего спотыкаются

• DDL внутри транзакции работает. CREATE TABLE, ALTER TABLE и даже DROP TABLE можно откатить. SQLite в этом смысле белая ворона — во многих СУБД DDL коммитится автоматически.
• VACUUM нельзя запускать внутри транзакции. Как и ещё пару служебных команд. Их выполняют в режиме автокоммита.
• Неудачный COMMIT — это настоящая ошибка. Если COMMIT вернул SQLITE_BUSY (редко, но бывает), транзакция не зафиксирована. Ваш код должен это обработать — обычно через повтор.
• Долгие транзакции блокируют пишущих. Если транзакция висит открытой минутами, она ровно столько же минут блокирует других писателей. Открывайте позже, коммитьте быстрее.

Дальше: точки сохранения (savepoints)

BEGIN и COMMIT работают по принципу «всё или ничего». Но иногда хочется откатить только часть транзакции — например, отказаться от одного рискованного шага, а остальное сохранить. Для этого и существуют savepoints, и о них поговорим в следующей части.