Transacciones SQL, WAL y 'database is locked'

Serie: curso de SQL 5/6 (avanzado). Para quienes ya escriben consultas e índices y ahora tienen código donde dos cosas tocan la base de datos al mismo tiempo: una app web con varios workers, un proceso batch junto a una API, una app de celular con sincronización en segundo plano. Al terminar esta clase vas a saber qué garantiza una transacción y qué no, cómo hacer escrituras masivas 100× más rápidas y cómo diagnosticar cada database is locked que te encuentres.

La mayoría de los bugs de transacciones son invisibles en desarrollo, donde un solo proceso usa la base de datos. Aparecen en producción como un saldo que sale mal una vez al mes, o como un error database is locked en un log que nadie logra reproducir. Si alguna vez te tocó el ticket de "a veces la app de un cliente se cuelga al guardar", probablemente era esto. En esta clase los reproducimos a propósito con dos procesos sqlite3 y unas líneas de Python, mostramos los mensajes exactos y medimos lo que las transacciones le hacen a la velocidad de escritura.

Entorno: CLI de SQLite 3.51.0 y el módulo sqlite3 de Python 3.9.6 (con SQLite incluido, también 3.51.0), Mac con Apple Silicon (M1 Pro), macOS, SSD con APFS. No teníamos MySQL ni PostgreSQL; todo lo que se diga sobre ellos lleva la etiqueta "no se ejecutó aquí".

Qué es una transacción y los primeros errores que vas a ver

Una transacción es un grupo de sentencias que la base de datos aplica todas juntas o ninguna. BEGIN abre una, COMMIT la vuelve permanente y ROLLBACK la descarta. Fuera de BEGIN, SQLite ejecuta cada sentencia en su propia transacción pequeña: es el modo autocommit.

Nuestro ejemplo es una transferencia de dinero entre dos cuentas, con la regla de que un saldo nunca puede ser negativo:

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  owner   TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50);

Los dos errores con los que todos se topan la primera semana:

sqlite> COMMIT;
Runtime error near line 1: cannot commit - no transaction is active
sqlite> BEGIN;
sqlite> BEGIN;
Runtime error near line 3: cannot start a transaction within a transaction

Las transacciones no se anidan. Para una "transacción dentro de una transacción" usa un savepoint, un punto con nombre al que puedes volver:

SAVEPOINT transfer;
UPDATE accounts SET balance = balance + 150 WHERE id = 2;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;  -- CHECK fails
ROLLBACK TO transfer;   -- undoes the first UPDATE too
RELEASE transfer;

Runtime error near line 3: CHECK constraint failed: balance >= 0 (19)
1|alice|100
2|bob|50

Error común: suponer que un error deshace la transacción

El error. Alice tiene 100 e intenta enviar 150. El código envuelve las dos actualizaciones en BEGIN … COMMIT y supone que la restricción CHECK la va a proteger:

BEGIN;
UPDATE accounts SET balance = balance + 150 WHERE id = 2;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;
COMMIT;
SELECT * FROM accounts;
SELECT 'total', sum(balance) FROM accounts;

Lo que pasa en realidad. La sentencia que falla se deshace, pero la transacción sigue abierta, y COMMIT confirma la sentencia que sí funcionó:

Runtime error near line 6: CHECK constraint failed: balance >= 0 (19)
1|alice|100
2|bob|200
total|300

El banco acaba de crear 150 de la nada. (PostgreSQL se comporta distinto: después de un error rechaza cada sentencia siguiente con "current transaction is aborted" hasta que hagas rollback. No se ejecutó aquí.)

La solución. Haz rollback tú mismo cuando falle cualquier sentencia. En un script, sqlite3 -bail se detiene en el primer error; el proceso termina con la transacción todavía abierta y SQLite la deshace:

$ sqlite3 -bail bank.db < transfer.sql
Runtime error near line 3: CHECK constraint failed: balance >= 0 (19)
$ echo $?
1
$ sqlite3 bank.db "SELECT * FROM accounts"
1|alice|100
2|bob|50

En el código de tu aplicación, usa el helper de transacciones de tu lenguaje. El with con: de Python hace commit si todo sale bien y rollback ante una excepción. El mismo archivo muestra además una trampa propia de Python:

import sqlite3
print("SQLite", sqlite3.sqlite_version)

# Trap 1: Python 3.9's sqlite3 opens a transaction for you before INSERT/UPDATE/DELETE
con = sqlite3.connect("bank.db")
con.execute("UPDATE accounts SET balance = 999 WHERE id = 1")
print("in_transaction:", con.in_transaction)
con.close()                                   # no commit() -> rolled back
print("after close:", sqlite3.connect("bank.db").execute("SELECT balance FROM accounts WHERE id=1").fetchone())

# Fix: one transfer = one transaction; `with con:` commits, or rolls back on exception
def transfer(con, src, dst, amount):
    with con:
        con.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
        con.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))

con = sqlite3.connect("bank.db")
try:
    transfer(con, 1, 2, 150)
except sqlite3.IntegrityError as e:
    print("IntegrityError:", e)
print(con.execute("SELECT id, balance FROM accounts").fetchall())
transfer(con, 1, 2, 30)
print(con.execute("SELECT id, balance FROM accounts").fetchall())

SQLite 3.51.0
in_transaction: True
after close: (100,)
IntegrityError: CHECK constraint failed: balance >= 0
[(1, 100), (2, 50)]
[(1, 70), (2, 80)]

La primera mitad es la sorpresa contraria: la actualización "desapareció" porque Python había abierto una transacción en silencio y nadie llamó a commit().

Medido: 10,000 INSERT en autocommit contra una sola transacción

Las mismas 10,000 sentencias INSERT (generadas con random.Random(42)) se pasaron por tubería a sqlite3 con distintas configuraciones. Cada configuración se ejecutó 5 veces sobre una base de datos nueva; el tiempo incluye arrancar el proceso sqlite3, que por sí solo tardó 6 ms.

Tres lecciones. Envolver el lote en una transacción lo hizo unas 195× más rápido. Desactivar la sincronización (synchronous=OFF, que arriesga corrupción si se corta la luz) solo redujo a la mitad el tiempo en autocommit, porque en modo DELETE cada commit además crea y borra un archivo -journal. Y el modo WAL con synchronous=NORMAL es la mejor configuración cuando no puedes agrupar, por ejemplo en una app web que hace commit de una fila por petición.

La dispersión de la primera fila es real: había otros trabajos corriendo en la máquina, y crear archivos es la operación más sensible a eso.

Rollback journal contra WAL: quién bloquea a quién

SQLite tiene dos formas de mantener segura una transacción. El rollback journal por defecto (journal_mode=DELETE) copia las páginas originales a un archivo -journal y escribe los cambios en el propio archivo de la base de datos. WAL (write-ahead log, journal_mode=WAL) agrega los cambios a un archivo -wal y no toca el archivo de la base de datos hasta que un checkpoint los copia de vuelta:

w.db 8192          <- after 10,000 inserts in WAL mode
w.db-wal 189552
w.db-shm 32768
(0, 0, 0)          <- PRAGMA wal_checkpoint(TRUNCATE)
w.db 188416
w.db-wal 0

La diferencia práctica es la concurrencia. En esta prueba, el proceso A abre una transacción de lectura y la mantiene 3 segundos; un segundo después, el proceso B intenta escribir:

( echo "BEGIN;"; echo "SELECT 'A sees', balance FROM accounts WHERE id = 1;"; sleep 3
  echo "SELECT 'A still sees', balance FROM accounts WHERE id = 1;"; echo "COMMIT;"
  echo "SELECT 'A after COMMIT', balance FROM accounts WHERE id = 1;" ) | sqlite3 bank.db &
sleep 1
printf "UPDATE accounts SET balance = 0 WHERE id = 1;\nSELECT 'B wrote', balance FROM accounts WHERE id = 1;\n" | sqlite3 bank.db
wait

--- delete mode
A sees|100
Runtime error near line 1: database is locked (5)
B wrote|100
A still sees|100
A after COMMIT|100
--- wal mode
A sees|100
B wrote|0
A still sees|100
A after COMMIT|0

En modo DELETE, un lector impide que un escritor haga commit. En modo WAL ambos avanzan, y A sigue viendo 100 hasta que termina su transacción: lee una instantánea (snapshot). Eso es aislamiento por snapshot, mostrado en tres líneas.

Las tres causas de "database is locked"

1. Otra conexión está escribiendo y no esperaste. El busy timeout de la CLI es 0, así que una escritura durante otra escritura falla de inmediato:

$ ( echo "BEGIN IMMEDIATE;"; echo "UPDATE accounts SET balance = balance - 10 WHERE id = 1;"; sleep 3; echo "COMMIT;" ) | sqlite3 bank.db &
$ sleep 1; sqlite3 bank.db "UPDATE accounts SET balance = balance + 10 WHERE id = 2;"; echo "exit=$?"
Error: stepping, database is locked (5)
exit=5

Solución: un busy timeout. Con .timeout 5000, B esperó 2.05 s a que A hiciera commit y funcionó. El sqlite3.connect() de Python ya configura 5 segundos (PRAGMA busy_timeout devolvió 5000), por eso el mismo código puede fallar en la CLI pero no en Python.

2. Un lector largo en modo rollback journal bloquea el commit del escritor, como vimos arriba. Solución: modo WAL, o transacciones de lectura más cortas.

3. La trampa del ascenso en modo WAL. Esta sorprende incluso a desarrolladores con experiencia, porque el timeout no ayuda. Una transacción iniciada con BEGIN simple (una transacción deferred, diferida) primero lee y luego intenta escribir después de que otra conexión ya hizo commit:

( echo ".timeout 5000"; echo "BEGIN;"; echo "SELECT 'A read', balance FROM accounts WHERE id = 1;"; sleep 2
  echo ".timer on"; echo "UPDATE accounts SET balance = balance - 30 WHERE id = 1;"; echo "COMMIT;" ) | sqlite3 bank.db &
sleep 1
sqlite3 bank.db ".timeout 5000" "UPDATE accounts SET balance = balance - 50 WHERE id = 1;"
wait

A read|100
Runtime error near line 5: database is locked (5)
Run Time: real 0.000 user 0.000044 sys 0.000030
Run Time: real 0.000 user 0.000005 sys 0.000005

(La segunda línea Run Time es el COMMIT, que solo termina la transacción de lectura de A.)

A falló en menos de un milisegundo a pesar del timeout de 5 segundos. Su snapshot es más vieja que el commit de B, así que dejar escribir a A significaría escribir encima de datos que nunca vio; esperar no lo arregla, y SQLite devuelve SQLITE_BUSY (el código extendido es SQLITE_BUSY_SNAPSHOT) de inmediato. Python reporta lo mismo como sqlite3.OperationalError: database is locked. Con BEGIN IMMEDIATE, A toma el bloqueo de escritura desde el principio; B espera a A, y las dos actualizaciones se aplican: el saldo final fue 20 (100 − 30 − 50).

Actualizaciones perdidas: la carrera en el código de tu aplicación

Ni un solo error de bloqueo y aun así se pierde dinero. Dos workers leen un saldo, calculan en la aplicación y escriben el resultado (Python, dos conexiones, orden determinista):

import sqlite3
a = sqlite3.connect("bank.db", isolation_level=None)   # two app workers, autocommit mode
b = sqlite3.connect("bank.db", isolation_level=None)
q = "SELECT balance FROM accounts WHERE id = 1"
ba = a.execute(q).fetchone()[0]      # worker A reads 100
bb = b.execute(q).fetchone()[0]      # worker B reads 100
a.execute("UPDATE accounts SET balance = ? WHERE id = 1", (ba - 30,))
b.execute("UPDATE accounts SET balance = ? WHERE id = 1", (bb - 50,))
print("read-modify-write in app:", a.execute(q).fetchone()[0], "(expected 20)")

a.execute("UPDATE accounts SET balance = 100 WHERE id = 1")
a.execute("UPDATE accounts SET balance = balance - 30 WHERE id = 1")
b.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
print("atomic UPDATE          :", a.execute(q).fetchone()[0])

read-modify-write in app: 50 (expected 20)
atomic UPDATE          : 20

Soluciones en orden de preferencia: haz la aritmética en SQL (SET balance = balance - ?); si de verdad tienes que leer primero, haz la lectura y la escritura dentro de BEGIN IMMEDIATE … COMMIT; en otras bases de datos, SELECT … FOR UPDATE (SQLite no tiene esa cláusula).

Niveles de aislamiento: SQLite frente a otras bases de datos

El aislamiento es cuánto pueden ver las transacciones concurrentes unas de otras. El estándar SQL define cuatro niveles según qué anomalías permiten. SQLite es simple: solo hay un escritor a la vez, así que las transacciones son en la práctica serializables; en modo WAL los lectores obtienen una snapshot, como vimos arriba. (PRAGMA read_uncommitted solo aplica al modo de caché compartida, que casi nadie usa.)

Las filas de las otras bases de datos vienen de su documentación, no de ejecuciones. La consecuencia práctica: la actualización perdida de arriba ocurre con Read Committed en cualquiera de ellas, así que la solución del UPDATE atómico es portable.

Qué configuración usar en cada situación

Ejercicios

1. Un proceso nocturno inserta 50,000 filas con un execute() por fila en Python 3.9 y llama a commit() al final. ¿Está corriendo en modo autocommit? ¿Qué pasa si se cae en la fila 40,000?
2. Tu app en modo WAL a veces falla con database is locked en cuestión de milisegundos, aunque busy_timeout es 5000. ¿Qué patrón es la causa probable y cuál es la solución en una palabra?

Respuestas

1. No. El sqlite3 de Python 3.9 (con isolation_level="" por defecto) abrió una transacción antes del primer INSERT, así que las 50,000 filas son una sola transacción: es rápido, y una caída en la fila 40,000 no deja nada escrito porque la transacción abierta se deshace. Lo simulamos con os._exit(1) en la fila 40,000: in_transaction era True, y después SELECT count(*) devolvió 0. Normalmente es lo que quieres, pero es lo contrario de lo que mucha gente supone.
2. Un BEGIN diferido que primero lee y después escribe, luego de que otra conexión hizo commit (la causa 3 de arriba). Usa BEGIN IMMEDIATE.

Límites honestos y notas de versión

• Solo se ejecutó en SQLite 3.51.0 (la CLI y la biblioteca incluida en Python 3.9.6). El modo WAL existe desde SQLite 3.7.0 y los savepoints desde la 3.6.8.
• Esta compilación de macOS usa synchronous=FULL por defecto y, según sus opciones de compilación, NORMAL para bases de datos WAL (DEFAULT_WAL_SYNCHRONOUS=1); una compilación estándar usa FULL para ambos. PRAGMA fullfsync era 0, así que las sincronizaciones usaron fsync() simple, que en macOS no obliga al disco a vaciar su propia caché. En Linux, o con fullfsync=ON, los números de autocommit van a ser distintos, probablemente más altos.
• Python 3.12 agregó Connection.autocommit y cambió cómo se pueden controlar las transacciones; solo ejecutamos 3.9.
• WAL necesita memoria compartida entre procesos, así que no funciona sobre sistemas de archivos de red.
• La tabla de aislamiento de PostgreSQL, MySQL, SQL Server y Oracle viene de la documentación, no de ejecuciones.

¿Necesitas repasar por qué un UPDATE sin WHERE es peligroso? Mira la clase 2. Para la ruta de aprendizaje completa, revisa la ruta de estudio de programación.

Anterior: Clase 4: índices y EXPLAIN QUERY PLAN · Siguiente: Clase 6: funciones de ventana y CTE