Comandos transaccionales y de bloqueo de tablas
Las transacciones permiten agrupar operaciones como una única unidad lógica. La atomicidad evita cambios parciales; la consistencia preserva las reglas definidas; y el aislamiento controla cómo interactúan las transacciones concurrentes. El aislamiento no implica que todas las operaciones sean siempre invisibles: el comportamiento depende del nivel de aislamiento y del motor.
Los comandos transaccionales típicos son COMMIT (para confirmar una transacción) y ROLLBACK (para deshacer una transacción). El uso adecuado de las transacciones puede mejorar la consistencia y la integridad de los datos de la base de datos y prevenir situaciones en las que se producen cambios parciales e inconsistentes.
Por otro lado, el bloqueo de tablas es una forma de controlar el acceso concurrente a los datos de una tabla. El bloqueo de tablas se utiliza para evitar situaciones en las que dos o más transacciones intentan modificar la misma fila al mismo tiempo. Cuando una transacción bloquea una tabla, impide que otras transacciones accedan o modifiquen los datos de la tabla hasta que se libere el bloqueo. Los bloqueos pueden ser explícitos (adquiridos mediante comandos LOCK TABLES) o implícitos (adquiridos automáticamente por la base de datos en función de la operación que se está ejecutando).
Sintaxis de START TRANSACTION, COMMIT y ROLLBACK
START TRANSACTION inicia una nueva transacción. Este comando indica a la base de datos que todas las operaciones que se ejecuten a partir de ese momento deben ser agrupadas como parte de la transacción actual.
EJEMPLO
Supongamos que tienes una tabla llamada "ventas" con dos columnas: "id" y "monto". Quieres actualizar el monto de la venta con el ID 1234 y agregar un registro a otra tabla llamada "registros" con información sobre la transacción.
START TRANSACTION;
UPDATE ventas
SET monto = 100
WHERE id = 1234;
INSERT INTO registros (fecha, monto, descripcion)
VALUES (NOW(), 100, 'Venta actualizada');
COMMIT;
En este ejemplo, se inicia una transacción con el comando START TRANSACTION. A continuación, se actualiza el monto de la venta con ID 1234 y se inserta un nuevo registro en la tabla "registros". Si ambas operaciones tienen éxito, se confirman con el comando COMMIT y se hacen permanentes en la base de datos. Si algo sale mal, se puede deshacer la transacción con el comando ROLLBACK para revertir todos los cambios realizados en la base de datos.
COMMIT se utiliza para confirmar una transacción y hacer que todos los cambios realizados durante la transacción se hagan permanentes en la base de datos. Cuando se ejecuta un COMMIT, se confirma que todas las operaciones de la transacción se han completado correctamente y que se pueden aplicar los cambios de forma permanente. Por defecto está habilitado.
EJEMPLO
Supongamos que tienes una tabla llamada "empleados" con las columnas "id", "nombre", "salario" y "departamento". Quieres actualizar el salario de un empleado y confirmar los cambios en la base de datos.
UPDATE empleados
SET salario = salario * 1.1
WHERE id = 123;
COMMIT;
En este ejemplo, se actualiza el salario del empleado con el ID 123 multiplicándolo por 1.1. Luego, se confirman los cambios en la base de datos con el comando COMMIT, lo que hace que los cambios sean permanentes y no reversibles. A partir de ese momento, el salario del empleado con el ID 123 será un 10% mayor que antes de la actualización.
Deshabilitar / habilitar autocommit
Por defecto, el valor de esta variable es "1" (encendido), lo que significa que cada sentencia SQL es confirmada automáticamente como una transacción independiente. Si lo estableces en "0" (apagado), las sentencias SQL se agruparán en transacciones que deben confirmarse manualmente mediante el comando COMMIT o revertirse mediante el comando ROLLBACK.
SET AUTOCOMMIT = 0;
ROLLBACK se utiliza para deshacer una transacción y volver a un estado anterior. Cuando se ejecuta un ROLLBACK, todas las operaciones realizadas desde el inicio de la transacción se deshacen y se vuelve a un estado anterior a la transacción. Esto significa que se revierten todos los cambios realizados durante la transacción y que la base de datos vuelve a estar en el estado en el que se encontraba antes de la transacción.
EJEMPLO
Supongamos que estás insertando datos en dos tablas, "clientes" y "pedidos", dentro de una transacción. Si la inserción en la tabla "pedidos" falla por algún motivo (por ejemplo, por una violación de clave foránea)
START TRANSACTION;
INSERT INTO clientes (id, nombre, email)
VALUES (1, 'Juan', 'juan@email.com');
INSERT INTO pedidos (id_cliente, fecha, cantidad)
VALUES (1, '2023-04-17', 5); -- Esta inserción fallará por una violación de clave foránea
ROLLBACK;
Cuando esto suceda, el comando ROLLBACK deshará todas las operaciones realizadas dentro de la transacción, incluyendo la inserción en la tabla "clientes". Después de ejecutar el comando ROLLBACK, la base de datos volverá al estado en que estaba antes de que comenzara la transacción, sin haber realizado ningún cambio permanente.
Sentencias que no se pueden deshacer
En MySQL y MariaDB muchas sentencias DDL provocan una confirmación implícita y no pueden revertirse con ROLLBACK. Este comportamiento no es universal: PostgreSQL permite incluir muchas operaciones DDL dentro de una transacción. Debe consultarse la documentación del motor y de la sentencia concreta.
Ejemplo
DROP TABLE mi_tabla;
Sentencias que causan una ejecución (commit) implícita
Las sentencias que causan una ejecución (commit) implícita son aquellas que, además de realizar una acción específica en la base de datos, también confirman cualquier transacción que se esté ejecutando actualmente. Esto significa que no es necesario ejecutar explícitamente un comando COMMIT para confirmar la transacción después de ejecutar una de estas sentencias.
La siguiente tabla contiene ejemplos habituales de sentencias que provocan confirmaciones implícitas en MySQL. No es una regla de SQL general ni debe aplicarse a otros motores:
| ALTER TABLE | BEGIN | CREATE INDEX |
|---|---|---|
| CREATE TABLE | - | CREATE DATABASE |
| DROP DATABASE | DROP INDEX | DROP TABLE |
| LOAD MASTER DATA | LOCK TABLES | RENAME TABLE |
| SET AUTOCOMMIT | START TRANSACTION | TRUNCATE TABLE |
Ejemplo
TRUNCATE TABLE mi_tabla;
Sintaxis de SAVEPOINT y ROLLBACK TO SAVEPOINT
Se utilizan en MySQL para crear puntos de guardado de transacciones llamados SAVEPOINT, lo que permite deshacer solo una parte de una transacción en lugar de deshacer toda la transacción.
SAVEPOINT savepoint_name;
Para revertir la transacción a un SAVEPOINT
ROLLBACK TO SAVEPOINT savepoint_name;
Esto restaura la transacción al SAVEPOINT indicado, revirtiendo cualquier cambio realizado a partir de ese punto en adelante.
:pencil:NOTA es importante tener en cuenta que los SAVEPOINTS solo se pueden utilizar dentro de una transacción, y que no se pueden utilizar después de que se haya realizado un COMMIT en la transacción. Además, no se pueden utilizar fuera de la sesión de conexión actual.
Bloqueo de tablas
Es un mecanismo que se utiliza para controlar el acceso concurrente a las tablas de una base de datos. Cuando una transacción necesita realizar operaciones en una tabla, puede solicitar un bloqueo de esa tabla para garantizar que ningún otro proceso pueda modificar los datos de esa tabla mientras se realiza la transacción.
- Bloqueo de lectura: Permite que múltiples transacciones lean los datos de la tabla simultáneamente, pero impide que cualquier transacción realice modificaciones en la tabla.
- Bloqueo de escritura: Permite que una sola transacción realice modificaciones en una tabla, impidiendo que cualquier otra transacción lea o escriba en la misma tabla mientras el bloqueo está activo.
Sintaxis de LOCK TABLES
LOCK TABLES nombre_tabla WRITE;
Sintaxis de UNLOCK TABLES
Desbloqueo de todas la tablas
UNLOCK TABLES;
MySQL libera con UNLOCK TABLES todos los bloqueos de tabla adquiridos explícitamente por la sesión; la sentencia no recibe el nombre de una tabla.
Sintaxis de SET TRANSACTION
Se utiliza para establecer el nivel de aislamiento de la transacción actual en una base de datos en particular. El nivel de aislamiento determina la forma en que una transacción interactúa con otras transacciones que pueden estar intentando acceder a los mismos datos en la misma base de datos.
:pencil: NOTA las reglas cambian según el motor. En MySQL,
SET TRANSACTIONsin ámbito configura la siguiente transacción y debe ejecutarse fuera de una transacción activa.SET SESSION TRANSACTIONconfigura las transacciones posteriores de la sesión. PostgreSQL permiteSET TRANSACTIONal inicio de la transacción, antes de ejecutar consultas o sentencias de modificación relevantes.
SET TRANSACTION [ READ WRITE | READ ONLY ] [ , ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } ];
Las opciones READ WRITE y READ ONLY se utilizan para especificar si la transacción va a leer y escribir datos o solo leer datos. La opción ISOLATION LEVEL se utiliza para especificar el nivel de aislamiento de la transacción, que puede ser uno de los cuatro siguientes:
- SERIALIZABLE: exige un resultado equivalente a alguna ejecución en serie de las transacciones; el motor puede conseguirlo mediante bloqueos, detección de conflictos u otras técnicas.
- REPEATABLE READ: evita que una misma fila leída vuelva a observarse con valores distintos dentro de la transacción. El tratamiento de otros fenómenos concurrentes depende del motor.
- READ COMMITTED: cada sentencia ve únicamente datos confirmados, aunque dos lecturas sucesivas dentro de la misma transacción pueden observar estados confirmados diferentes.
- READ UNCOMMITTED: es el nivel estándar más débil, pero su comportamiento real depende del motor. Por ejemplo, InnoDB trata este nivel de forma distinta a otros sistemas y PostgreSQL lo eleva a
READ COMMITTED.
Ejemplo
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- Aquí puedes ejecutar tus operaciones de base de datos
COMMIT;