Transacciones en SQLite: BEGIN, COMMIT y ROLLBACK

Una transacción es un paquete de todo-o-nada

Una transacción agrupa varias sentencias para que se apliquen todas o no se aplique ninguna. Si algo falla a mitad de camino, puedes hacer rollback y la base de datos queda exactamente como estaba al principio.

El ejemplo de toda la vida es una transferencia de dinero:

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;

Los dos UPDATE van de la mano. Si la base de datos se cayera entre uno y otro, Ada se quedaría sin 2000 céntimos y Boris no recibiría nada. Al envolverlos en BEGIN ... COMMIT, el par se vuelve atómico: o se ejecutan ambos, o no se ejecuta ninguno.

Autocommit en SQLite: el modo por defecto que ya estás usando

Cada sentencia SQL que has ejecutado hasta ahora ha sido, en realidad, una transacción. SQLite funciona en modo autocommit por defecto: cada sentencia lleva su propio BEGIN y COMMIT implícitos.

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

INSERT INTO notes (body) VALUES ('primero');
INSERT INTO notes (body) VALUES ('segundo');
INSERT INTO notes (body) VALUES ('tercero');

SELECT * FROM notes;

Tres INSERT, tres transacciones independientes y tres viajes al disco para hacer fsync de cada cambio. Vale para escrituras puntuales, pero se vuelve lento en cargas masivas, y además no puedes deshacer un grupo de sentencias como una unidad. Aquí entra BEGIN: desactiva el autocommit hasta el siguiente COMMIT o ROLLBACK.

ROLLBACK: como si nunca hubiera pasado

ROLLBACK descarta todo lo que se haya hecho desde el BEGIN correspondiente. La base de datos vuelve al estado previo a la transacción.

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;

Tanto el UPDATE como el DELETE se esfuman: la tabla queda igual que estaba antes del BEGIN. Esa es la red de seguridad que le permite al código de la aplicación abortar limpiamente cuando se topa con un error a medio camino de una operación de varias sentencias.

Por cierto, una violación de restricción dentro de una transacción no provoca un rollback completo de forma automática. Lo que hace SQLite es deshacer la sentencia que falló y dejar la transacción abierta, esperando tu decisión. Si quieres un comportamiento de todo o nada, la aplicación tiene que emitir un ROLLBACK en cuanto detecte el error.

Acelerar inserciones masivas con transacciones

Como cada sentencia en modo autocommit hace su propio fsync, envolver un lote dentro de una sola transacción suele ser 100 veces más rápido:

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;

Una sola sincronización en disco al hacer COMMIT, en vez de una por cada fila. Si alguna vez importas miles de filas y te preguntas por qué va lentísimo, casi siempre la respuesta es esta.

DEFERRED, IMMEDIATE y EXCLUSIVE en SQLite

BEGIN acepta un modo que controla cuándo adquiere los bloqueos SQLite:

• BEGIN DEFERRED (el modo por defecto): no se toma ningún bloqueo hasta que leas o escribas. El bloqueo de escritura se adquiere de forma perezosa, en la primera sentencia de escritura.
• BEGIN IMMEDIATE: toma el bloqueo de escritura de inmediato. Otras conexiones todavía pueden leer, pero ninguna otra puede empezar a escribir.
• BEGIN EXCLUSIVE: igual que IMMEDIATE, pero además ninguna otra conexión podrá leer. En modo WAL se comporta igual que IMMEDIATE; la diferencia solo importa con el antiguo modo de rollback journal.

BEGIN DEFERRED;     -- igual que un BEGIN normal
BEGIN IMMEDIATE;    -- reserva el bloqueo de escritura ahora
BEGIN EXCLUSIVE;    -- reserva todo (modo rollback-journal)

La elección importa, y mucho, cuando hay concurrencia. Con un BEGIN a secas, dos conexiones pueden abrir su transacción a la vez, leer tan tranquilas y, al intentar escribir, chocar de frente: la segunda en pedir el bloqueo de escritura recibe SQLITE_BUSY y, para colmo, ya hizo lecturas que ahora tiene que tirar a la basura.

BEGIN IMMEDIATE resuelve esto: si sabes que vas a escribir, pide el bloqueo de escritura desde el principio. La segunda conexión se queda esperando (o falla rápido) de inmediato, antes de hacer ningún trabajo que luego tendría que descartar.

Regla práctica: si tu transacción va a escribir, usa BEGIN IMMEDIATE.

Leer dentro de una transacción es ver una foto fija

Mientras una transacción está abierta, tus lecturas ven una instantánea coherente de la base de datos: el estado que tenía al iniciar la transacción (en modo WAL) o en el momento de tu primera lectura (en modo rollback-journal). Los cambios que confirmen otras conexiones no van a aparecer de repente en tus consultas.

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, tu propia escritura es visible
COMMIT;

SELECT n FROM counter;

Solo tú ves tus propias escrituras sin confirmar; el resto de conexiones, no. En cuanto haces COMMIT, el nuevo valor pasa a ser visible para todo el mundo. A esto se refiere la gente cuando dice que SQLite es serializable: no hay ningún interruptor de READ COMMITTED que tocar, porque el nivel por defecto ya es el más estricto.

Una transacción dentro del código de la aplicación

En un programa real, el patrón habitual es envolver el cuerpo en un try/except (o try/catch), con un ROLLBACK en la rama de error:

-- Pseudocódigo para cualquier biblioteca cliente
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;

La mayoría de las librerías cliente (sqlite3 de Python, better-sqlite3, etc.) ya envuelven todo esto por ti con un bloque with o un helper tipo transaction(). Vale la pena revisar la documentación de tu librería, porque los valores por defecto no siempre son los que esperarías. El módulo sqlite3 de Python, en particular, ha tenido históricamente un comportamiento bastante peculiar con el autocommit; las versiones recientes añadieron un parámetro autocommit en condiciones para arreglarlo.

Detalles que pillan a más de uno

• El DDL dentro de transacciones funciona. CREATE TABLE, ALTER TABLE e incluso DROP TABLE se pueden revertir con rollback. SQLite es raro en esto: muchas bases de datos hacen commit automático del DDL.
• VACUUM no se puede ejecutar dentro de una transacción. Tampoco unos cuantos comandos de mantenimiento. Ejecútalos en modo autocommit.
• Un COMMIT que falla es un fallo real. Si COMMIT devuelve SQLITE_BUSY (poco común, pero posible), la transacción no queda confirmada. Tu código tiene que contemplar ese caso, normalmente reintentando.
• Las transacciones largas bloquean a los escritores. Una transacción que se queda abierta durante minutos bloqueará a los demás escritores durante esos mismos minutos. Ábrelas tarde y ciérralas rápido.

Lo que viene: savepoints

BEGIN y COMMIT funcionan en plan todo o nada. A veces quieres deshacer solo una parte de la transacción, por ejemplo, descartar un paso arriesgado pero conservar el resto. Para eso existen los savepoints en SQLite, y los vemos a continuación.