Lección 8.4 · Tiempo de lectura: ~10 min

Una transacción SQL es un grupo de sentencias que la base de datos ejecuta como una sola unidad: o se aplican todas, o ninguna. En esta lección verás START TRANSACTION, COMMIT y ROLLBACK, cómo SAVEPOINT deshace parte de una transacción, qué pasa cuando hay un error y de qué problemas protegen los niveles de aislamiento cuando varios usuarios modifican los mismos datos.

Transacciones SQL: COMMIT, ROLLBACK y niveles de aislamiento

En las lecciones anteriores modificaste datos con INSERT, UPDATE y DELETE, una sentencia cada vez. Las operaciones reales suelen necesitar varias: alquilar una película en Sakila significa añadir una fila en rental y otra en payment. Si la segunda sentencia falla, la primera no debe quedarse sola.

Las bases de datos de ejemplo de SQLtest son de solo lectura, así que allí estas sentencias devuelven un error de permisos. Los ejemplos usan el esquema Sakila y se han comprobado en MySQL 8.0 y PostgreSQL 17.

¿Qué es una transacción?

Una transacción convierte varios cambios en una sola operación. La base de datos garantiza las cuatro propiedades ACID, que viste en la lección 1.3:

• Atomicidad: se aplican todas las sentencias de la transacción, o ninguna.
• Consistencia: después de la transacción, todas las restricciones se siguen cumpliendo.
• Aislamiento: las transacciones concurrentes no ven los cambios sin terminar de las demás.
• Durabilidad: una vez confirmados, los cambios sobreviven a un fallo o a un corte de luz.

El ejemplo clásico es una transferencia: restar de una cuenta y sumar a la otra. Sin transacción, un fallo entre los dos pasos hace desaparecer el dinero.

START TRANSACTION, COMMIT y ROLLBACK

START TRANSACTION;

-- statements that change data

COMMIT;    -- save all changes
-- or
ROLLBACK;  -- undo all changes

• START TRANSACTION (o BEGIN) abre una transacción.
• COMMIT hace permanentes todos sus cambios y los hace visibles para los demás usuarios.
• ROLLBACK deshace todos los cambios hechos desde que empezó la transacción.

Las palabras clave cambian un poco según la base de datos:

Ejemplo: alquilar una película y cobrar el pago

START TRANSACTION;

INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES ('2005-08-24 10:00:00', 1, 1, 1);

INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 1, LAST_INSERT_ID(), 0.99, '2005-08-24 10:00:00');

COMMIT;

Resultado: las dos filas se guardan juntas. En MySQL, LAST_INSERT_ID() devuelve el rental_id que se acaba de generar; PostgreSQL usa INSERT ... RETURNING rental_id para lo mismo.

Ejemplo: deshacer un error

START TRANSACTION;

UPDATE film
SET rental_rate = rental_rate * 2;

SELECT SUM(rental_rate) FROM film;  -- the new prices, visible inside the transaction

ROLLBACK;

Resultado: después del ROLLBACK, todos los precios vuelven a ser los de antes. Hasta el COMMIT, los demás usuarios no ven los precios duplicados (salvo en el nivel READ UNCOMMITTED, descrito más abajo).

Autocommit: cada sentencia es una transacción

Si no abres una transacción, la base de datos usa una de todos modos: cada sentencia se ejecuta en su propia transacción y se confirma automáticamente. Este modo se llama autocommit y está activado por defecto en MySQL, PostgreSQL y SQL Server.

SELECT @@autocommit;  -- MySQL: 1 means autocommit is on

Por eso un UPDATE suelto sin START TRANSACTION no se puede deshacer con ROLLBACK: se confirmó en el momento en que terminó. START TRANSACTION suspende el autocommit hasta el COMMIT o el ROLLBACK.

SAVEPOINT: deshacer parte de una transacción

Un punto de guardado (savepoint) marca un punto dentro de una transacción. ROLLBACK TO SAVEPOINT deshace solo los cambios hechos después de él; la transacción sigue abierta.

START TRANSACTION;

UPDATE film SET rental_rate = 1.99 WHERE film_id = 1;

SAVEPOINT before_second;

UPDATE film SET rental_rate = 9.99 WHERE film_id = 2;

ROLLBACK TO SAVEPOINT before_second;

COMMIT;

Resultado: la película 1 recibe el nuevo precio 1.99; el cambio de la película 2 se deshace y mantiene su precio anterior.

Los puntos de guardado son útiles en operaciones largas en las que un paso puede fallar y quieres repetirlo sin perder el trabajo ya hecho.

Qué pasa cuando una sentencia falla

Aquí las bases de datos se comportan de forma distinta, y es una fuente habitual de errores.

MySQL y MariaDB deshacen solo la sentencia que ha fallado. La transacción sigue abierta, y COMMIT guarda todo lo que se ejecutó bien:

START TRANSACTION;

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (1, 1, 2.99, '2005-08-24 10:00:00');   -- succeeds

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (999, 1, 4.99, '2005-08-24 10:00:00'); -- fails: customer 999 does not exist

COMMIT;

Resultado en MySQL: la segunda inserción falla con un error de clave foránea, pero COMMIT guarda igualmente el primer pago. Para conseguir el "todo o nada", la aplicación debe comprobar los errores y ejecutar ROLLBACK por su cuenta.

PostgreSQL marca toda la transacción como fallida tras un error. Cada sentencia siguiente se rechaza con "current transaction is aborted", y COMMIT en realidad ejecuta un rollback: no se guarda nada.

Sentencias que confirman implícitamente

En MySQL, MariaDB y Oracle, las sentencias DDL como CREATE TABLE, ALTER TABLE y DROP TABLE confirman la transacción actual antes de ejecutarse. Un ROLLBACK posterior no puede deshacer los cambios hechos antes de ellas:

START TRANSACTION;

UPDATE film SET rental_rate = 5.99 WHERE film_id = 1;

CREATE TABLE film_backup (film_id INT);  -- implicit COMMIT here

ROLLBACK;  -- too late: the UPDATE is already saved

Resultado en MySQL: el nuevo precio 5.99 se queda. PostgreSQL y SQL Server ejecutan el DDL dentro de la transacción, así que allí ROLLBACK deshace tanto el UPDATE como el CREATE TABLE.

Problemas de concurrencia: lecturas sucias, no repetibles y fantasma

Cuando varias transacciones trabajan a la vez con las mismas filas, una transacción puede ver los datos en un estado en el que no debería. El estándar SQL define tres anomalías de lectura:

Una lectura sucia paso a paso:

Niveles de aislamiento de las transacciones

El nivel de aislamiento decide de cuáles de estas anomalías está protegida una transacción. Cuanto más fuerte es el aislamiento, menos sorpresas, pero más esperan las transacciones unas a otras.

El nivel por defecto depende de la base de datos:

• MySQL y MariaDB (InnoDB): REPEATABLE READ. Un SELECT normal lee una instantánea tomada en la primera lectura de la transacción, así que en la práctica tampoco ve fantasmas.
• PostgreSQL, SQL Server, Oracle: READ COMMITTED. PostgreSQL trata READ UNCOMMITTED como READ COMMITTED, por lo que allí nunca hay lecturas sucias; Oracle solo admite READ COMMITTED y SERIALIZABLE.

Para cambiar el nivel:

-- for the next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- for all following transactions of this connection (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

En PostgreSQL, SET TRANSACTION ISOLATION LEVEL ... se escribe justo después de BEGIN. En el nivel SERIALIZABLE, la base de datos puede cancelar una de dos transacciones en conflicto con un error de serialización; la aplicación debe entonces repetirla.

Reglas prácticas

• Mantén las transacciones cortas: mientras una transacción está abierta, mantiene bloqueos que otros usuarios esperan. Nunca esperes una entrada del usuario dentro de una transacción.
• Comprueba el resultado de cada sentencia y ejecuta ROLLBACK si hay un error, sobre todo en MySQL, que no lo hace por ti.
• No mezcles DDL con transacciones de datos en MySQL: confirma implícitamente.
• Dos transacciones que bloquean las mismas filas en orden inverso pueden bloquearse mutuamente: es un interbloqueo (deadlock). La base de datos cancela una de ellas, y la aplicación debe repetirla.

Puntos clave:

• Una transacción agrupa sentencias en una unidad: COMMIT las guarda todas, ROLLBACK las deshace todas.
• Sin una transacción explícita, cada sentencia se confirma inmediatamente (autocommit).
• SAVEPOINT y ROLLBACK TO SAVEPOINT deshacen solo parte de una transacción.
• Tras un error, MySQL mantiene la transacción abierta, mientras que PostgreSQL la aborta; en MySQL, ejecuta ROLLBACK tú mismo.
• En MySQL, las sentencias DDL confirman implícitamente la transacción actual.
• Los niveles de aislamiento protegen de las lecturas sucias, no repetibles y fantasma; el nivel por defecto es REPEATABLE READ en MySQL y READ COMMITTED en PostgreSQL, SQL Server y Oracle.

Preguntas frecuentes

¿Qué es una transacción en SQL?

Una transacción es un grupo de sentencias SQL que la base de datos ejecuta como una sola unidad: o se guardan todos los cambios con COMMIT, o se deshacen todos con ROLLBACK. Las transacciones mantienen los datos consistentes cuando una operación necesita varias sentencias o cuando varios usuarios modifican los datos a la vez.

¿Qué diferencia hay entre COMMIT y ROLLBACK?

COMMIT termina la transacción y hace sus cambios permanentes y visibles para los demás usuarios. ROLLBACK termina la transacción y deshace todos sus cambios, devolviendo los datos al estado anterior a START TRANSACTION.

¿Se puede deshacer después de un COMMIT?

No. Después del COMMIT, los cambios son permanentes; para revertirlos hace falta una nueva sentencia que vuelva a cambiar los datos, o una copia de seguridad. Lo mismo ocurre con las sentencias ejecutadas en modo autocommit.

¿Cuál es el nivel de aislamiento por defecto?

REPEATABLE READ en MySQL y MariaDB con InnoDB; READ COMMITTED en PostgreSQL, SQL Server y Oracle.

Preguntas de entrevista

¿Qué significa ACID para una transacción?

Atomicidad: todo o nada. Consistencia: las restricciones se cumplen antes y después. Aislamiento: las transacciones concurrentes no ven los cambios sin confirmar de las demás. Durabilidad: los datos confirmados sobreviven a los fallos.

¿Qué es una lectura sucia y qué nivel de aislamiento la evita?

Una lectura sucia es leer un cambio sin confirmar de otra transacción, que después puede deshacerse. La evitan READ COMMITTED y todos los niveles más fuertes.

¿Qué diferencia hay entre una lectura no repetible y una lectura fantasma?

Una lectura no repetible es un valor cambiado en una fila que ya habías leído; una lectura fantasma es una fila nueva que aparece en el resultado de una consulta repetida con la misma condición.

¿Qué ocurre en MySQL si una sentencia de una transacción falla?

Solo se deshace esa sentencia; la transacción sigue abierta y COMMIT guarda las sentencias que se ejecutaron bien. Para que toda la operación sea atómica, la aplicación debe capturar el error y ejecutar ROLLBACK.