Transacciones en SQL

Hay operaciones que no son una sola instrucción, sino varias que tienen que ir juntas: o se hacen todas, o no se hace ninguna. Para eso existen las transacciones. En esta lección aprenderás a abrirlas, confirmarlas y deshacerlas, qué significa que una base de datos sea ACID y qué pasa cuando varios usuarios cambian los mismos datos a la vez.

El problema: la transferencia bancaria

Es el ejemplo clásico, y por algo lo es. Crea una tabla de cuentas:

CREATE TABLE cuentas (
  id INT PRIMARY KEY,
  titular VARCHAR(100) NOT NULL,
  saldo DECIMAL(10, 2) NOT NULL CHECK (saldo >= 0)
);

INSERT INTO cuentas VALUES
  (1, 'Ana García', 500.00),
  (2, 'Luis Pérez', 200.00);

Ana quiere pasarle 100 € a Luis. Son dos pasos:

UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;  -- quitar a Ana
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;  -- dar a Luis

¿Qué pasa si el servidor se apaga justo entre las dos instrucciones? A Ana le han quitado 100 €, pero Luis no los ha recibido. El dinero ha desaparecido. Da igual que el fallo sea poco probable: con miles de operaciones al día, acabará pasando.

Lo que necesitas es decirle a la base de datos: “estas dos instrucciones son una sola unidad”.

BEGIN, COMMIT y ROLLBACK

Una transacción es un grupo de instrucciones que la base de datos trata como una sola:

START TRANSACTION;   -- o BEGIN;

UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;

COMMIT;

• START TRANSACTION (o BEGIN) abre la transacción.
• COMMIT confirma todos los cambios. A partir de aquí son definitivos y los ven los demás usuarios.
• ROLLBACK deshace todos los cambios desde el START TRANSACTION, como si nunca hubieran ocurrido.

Si el servidor se cae antes del COMMIT, al arrancar de nuevo la base de datos deshace la transacción a medias. Nunca verás a Ana con 400 € y a Luis con 200 €.

id	titular	saldo
1	Ana García	400.00
2	Luis Pérez	300.00

Nota: START TRANSACTION es la forma estándar y funciona en MySQL y PostgreSQL. BEGIN funciona en MySQL, PostgreSQL y SQLite. Elige una y sé coherente.

Deshacer con ROLLBACK

Dentro de una transacción puedes mirar el resultado antes de decidir:

START TRANSACTION;

UPDATE cuentas SET saldo = saldo - 50 WHERE id = 1;
SELECT * FROM cuentas;  -- Ana aparece con 350.00

ROLLBACK;
SELECT * FROM cuentas;  -- Ana vuelve a tener 400.00

Esto es muy útil cuando haces cambios a mano en una base de datos real: ejecutas el UPDATE o DELETE, compruebas con un SELECT que ha tocado lo que querías y solo entonces haces COMMIT.

Autocommit

Si nunca has escrito START TRANSACTION, ¿tus cambios no se guardaban? Sí se guardaban, gracias al autocommit: por defecto, MySQL, PostgreSQL y SQLite tratan cada instrucción suelta como una transacción y hacen COMMIT automáticamente al terminarla.

Al escribir START TRANSACTION, el autocommit se desactiva hasta el siguiente COMMIT o ROLLBACK. En MySQL también puedes desactivarlo para toda la sesión:

SELECT @@autocommit;   -- 1 = activado
SET autocommit = 0;    -- a partir de aquí, nada se guarda sin COMMIT

Cuidado: con autocommit = 0, si cierras la conexión sin hacer COMMIT, pierdes los cambios. Y mientras no confirmas, puedes estar bloqueando filas que otros usuarios necesitan.

Las propiedades ACID

Las bases de datos relacionales garantizan cuatro propiedades en sus transacciones, conocidas por sus iniciales en inglés: ACID.

Atomicidad (Atomicity)

La transacción es indivisible: se hace entera o no se hace. Es lo que has visto con la transferencia. Nunca queda “a medias”.

Consistencia (Consistency)

Una transacción lleva la base de datos de un estado válido a otro estado válido. Si algo viola una regla (restricciones como CHECK, NOT NULL o claves foráneas), la instrucción falla. Por ejemplo, Ana no puede enviar 1000 €:

UPDATE cuentas SET saldo = saldo - 1000 WHERE id = 1;
-- Error: CHECK constraint failed (el saldo quedaría negativo)

Aislamiento (Isolation)

Las transacciones que se ejecutan a la vez no se pisan. Mientras Ana hace su transferencia, otro usuario que consulta las cuentas no ve el estado intermedio (100 € quitados pero no sumados). Verás los detalles en el apartado de niveles de aislamiento.

Durabilidad (Durability)

Una vez que la base de datos responde “COMMIT hecho”, los cambios sobreviven a un apagón o a un reinicio. Para lograrlo, la base de datos escribe primero en un registro en disco (el log) antes de dar la confirmación.

Nota: en MySQL, solo el motor InnoDB (el que se usa por defecto) admite transacciones. El antiguo MyISAM las ignora: cada instrucción se guarda al momento y ROLLBACK no deshace nada.

Puntos de guardado: SAVEPOINT

A veces quieres deshacer solo una parte de la transacción. Para eso sirven los SAVEPOINT, como los puntos de control de un videojuego:

START TRANSACTION;

UPDATE cuentas SET saldo = saldo - 30 WHERE id = 1;
SAVEPOINT paso2;

UPDATE cuentas SET saldo = saldo + 30 WHERE id = 2;
ROLLBACK TO SAVEPOINT paso2;  -- deshace solo lo posterior a paso2

COMMIT;

id	titular	saldo
1	Ana García	370.00
2	Luis Pérez	300.00

El primer UPDATE se ha guardado y el segundo no. (En este ejemplo, desde luego, sería un error: los 30 € han desaparecido. Los SAVEPOINT son útiles en procesos largos con pasos opcionales, no para partir una transferencia.) Con RELEASE SAVEPOINT paso2; eliminas el punto de guardado sin deshacer nada.

Niveles de aislamiento

Cuando muchas transacciones se ejecutan a la vez, pueden aparecer tres problemas clásicos:

• Lectura sucia (dirty read): lees datos que otra transacción ha cambiado pero aún no ha confirmado. Si esa transacción hace ROLLBACK, has leído algo que nunca existió.
• Lectura no repetible (non-repeatable read): lees una fila, otra transacción la cambia y confirma, y al volver a leerla en tu misma transacción obtienes otro valor.
• Lectura fantasma (phantom read): repites una consulta con WHERE y aparecen filas nuevas que otra transacción ha insertado entretanto.

Evitar todos estos problemas tiene un coste: la base de datos tiene que bloquear más cosas y las transacciones esperan más. Por eso el estándar SQL define cuatro niveles de aislamiento, de menos a más estricto:

Nivel	Lectura sucia	Lectura no repetible	Lectura fantasma
READ UNCOMMITTED	Posible	Posible	Posible
READ COMMITTED	No	Posible	Posible
REPEATABLE READ	No	No	Posible
SERIALIZABLE	No	No	No

En MySQL con InnoDB el nivel por defecto es REPEATABLE READ. Además, InnoDB evita en la práctica casi todas las lecturas fantasma en ese nivel, porque cada transacción trabaja sobre una “foto” de los datos tomada en su primera lectura.

SELECT @@transaction_isolation;  -- REPEATABLE-READ

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Nota: PostgreSQL usa READ COMMITTED por defecto. SQLite solo permite una escritura a la vez sobre toda la base de datos, así que sus transacciones se comportan como SERIALIZABLE.

Para la mayoría de aplicaciones, el nivel por defecto es suficiente. Súbelo a SERIALIZABLE solo en operaciones delicadas donde lo necesites de verdad.

Nota sobre bloqueos: para aislar las transacciones, la base de datos bloquea las filas que modificas hasta el COMMIT o ROLLBACK. Si otra transacción quiere cambiar esas filas, espera. Un deadlock (interbloqueo) ocurre cuando dos transacciones se esperan mutuamente: la A bloquea la cuenta 1 y quiere la 2, y la B bloquea la 2 y quiere la 1. La base de datos lo detecta, cancela una de ellas con un error y la otra continúa. Para reducirlos, mantén las transacciones cortas y accede a las filas siempre en el mismo orden.

Transacciones desde una aplicación

En una aplicación real no escribes START TRANSACTION a mano: lo gestiona tu código. La idea es siempre la misma: desactivar el autocommit, ejecutar las instrucciones, hacer commit si todo va bien y rollback si hay una excepción.

En Java con JDBC:

static void transferir(Connection conexion, int origen, int destino, BigDecimal cantidad) throws SQLException {
    String restar = "UPDATE cuentas SET saldo = saldo - ? WHERE id = ?";
    String sumar = "UPDATE cuentas SET saldo = saldo + ? WHERE id = ?";
    conexion.setAutoCommit(false); // empieza la transacción
    try (PreparedStatement ps1 = conexion.prepareStatement(restar);
         PreparedStatement ps2 = conexion.prepareStatement(sumar)) {
        ps1.setBigDecimal(1, cantidad);
        ps1.setInt(2, origen);
        ps1.executeUpdate();
        ps2.setBigDecimal(1, cantidad);
        ps2.setInt(2, destino);
        ps2.executeUpdate();
        conexion.commit();   // todo bien: se guarda
    } catch (SQLException e) {
        conexion.rollback(); // algo falló: se deshace todo
        throw e;
    } finally {
        conexion.setAutoCommit(true);
    }
}

En Python, con el módulo sqlite3, la conexión se usa en un bloque with: hace commit al salir si todo fue bien y rollback si salta una excepción:

import sqlite3

def transferir(conexion, origen, destino, cantidad):
    try:
        with conexion:  # COMMIT si todo va bien, ROLLBACK si hay excepción
            conexion.execute("UPDATE cuentas SET saldo = saldo - ? WHERE id = ?", (cantidad, origen))
            conexion.execute("UPDATE cuentas SET saldo = saldo + ? WHERE id = ?", (cantidad, destino))
        print(f"Transferencia de {cantidad} € hecha")
    except sqlite3.IntegrityError as error:
        print("Transferencia cancelada:", error)

# Con Ana en 400 y Luis en 300:
transferir(conexion, 1, 2, 50)    # Transferencia de 50 € hecha
transferir(conexion, 1, 2, 1000)  # Transferencia cancelada: CHECK constraint failed: saldo >= 0
# Saldos finales: Ana 350, Luis 350

Cuidado con…

• Olvidar el COMMIT. Los cambios se quedan sin guardar y las filas bloqueadas. Al cerrar la conexión se pierden.
• Transacciones largas. Si abres una transacción y te vas a comer, bloqueas a los demás. Haz transacciones cortas y nunca esperes al usuario dentro de una.
• Un UPDATE que no toca ninguna fila no es un error. Si escribes WHERE id = 3 y esa cuenta no existe, la instrucción “funciona” con 0 filas afectadas y la transacción seguiría adelante. Tu aplicación debe comprobar cuántas filas ha cambiado.
• Un error no deshace la transacción entera en MySQL. Si una instrucción falla, MySQL deshace solo esa instrucción; las anteriores siguen pendientes. Tienes que hacer tú el ROLLBACK. (PostgreSQL, en cambio, marca la transacción como fallida y solo te deja hacer ROLLBACK.)
• CREATE, ALTER y DROP en MySQL hacen COMMIT automáticamente. No puedes deshacer un DROP TABLE con ROLLBACK, y además confirman lo que tuvieras pendiente. PostgreSQL sí permite deshacerlos.

Resumen

Instrucción	Qué hace
START TRANSACTION / BEGIN	Abre una transacción
COMMIT	Confirma los cambios de forma definitiva
ROLLBACK	Deshace todos los cambios de la transacción
SAVEPOINT nombre	Marca un punto intermedio
ROLLBACK TO SAVEPOINT nombre	Deshace solo hasta ese punto
SET autocommit = 0	Desactiva el autocommit en la sesión (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL ...	Cambia el nivel de aislamiento

• ACID: atomicidad (todo o nada), consistencia (se cumplen las reglas), aislamiento (no se pisan) y durabilidad (lo confirmado no se pierde).
• MySQL InnoDB usa REPEATABLE READ por defecto; PostgreSQL, READ COMMITTED.
• Mantén las transacciones cortas para evitar esperas y deadlocks.
• Siguiente paso: aprender a pensar la estructura de tus tablas con el diseño y la normalización.

-- La cuenta 1 empieza con 100.00
BEGIN;
UPDATE cuentas SET saldo = saldo - 20 WHERE id = 1;
SAVEPOINT s1;
UPDATE cuentas SET saldo = saldo - 50 WHERE id = 1;
ROLLBACK TO SAVEPOINT s1;
COMMIT;

SELECT saldo FROM cuentas WHERE id = 1;