MySQL Transacciones

MySQL Transaction se utiliza principalmente para manejar datos con operaciones de gran volumen y alta complejidad. Por ejemplo, en un sistema de gestión de personal, si eliminas a una persona, necesitas eliminar tanto la información básica de la persona como la información relacionada con ella, como el correo, artículos, etc. ¡De esta manera, estas sentencias de operación de base de datos constituyen una transacción!

En MySQL, una transacción es la ejecución de un grupo de sentencias SQL que se consideran como una única unidad de trabajo.

  • En MySQL, solo las bases de datos o tablas que utilizan el motor de base de datos InnoDB admiten transacciones.
  • El procesamiento de transacciones se puede utilizar para mantener la integridad de la base de datos, garantizando que un lote de sentencias SQL se ejecute por completo o no se ejecute en absoluto.
  • Las transacciones se utilizan para gestionarinsert、update、deletesentencias

En general, una transacción debe cumplir 4 condiciones (ACID):: atomicidad (Atomicity, o indivisibilidad), consistencia (Consistency), aislamiento (Isolation, también llamado independencia), durabilidad (Durability)。

  • Atomicidad:Todas las operaciones en una transacción se completan por completo o no se completan en absoluto, sin terminar en un punto intermedio. Si ocurre un error durante la ejecución de la transacción, se revierte (Rollback) al estado anterior al inicio de la transacción, como si esta nunca se hubiera ejecutado.

  • Consistencia:Antes de que comience la transacción y después de que termine, la integridad de la base de datos no se ve afectada. Esto significa que los datos escritos deben cumplir completamente con todas las reglas predefinidas, incluyendo la precisión de los datos, la concatenación y la capacidad de la base de datos para completar automáticamente el trabajo predeterminado posteriormente.

  • Aislamiento:Es la capacidad de la base de datos para permitir que múltiples transacciones concurrentes lean, escriban y modifiquen sus datos al mismo tiempo. El aislamiento evita la inconsistencia de los datos causada por la ejecución cruzada de múltiples transacciones concurrentes. El aislamiento de transacciones se divide en diferentes niveles, incluidos lectura no confirmada (Read uncommitted), lectura confirmada (read committed), lectura repetible (repeatable read) y serialización (Serializable).

  • Durabilidad:Después de que se completa el procesamiento de la transacción, las modificaciones a los datos son permanentes y no se perderán incluso si ocurre una falla del sistema.

En la configuración predeterminada de la línea de comandos de MySQL, las transacciones se confirman automáticamente, es decir, después de ejecutar una sentencia SQL, se ejecuta inmediatamente la operación COMMIT. Por lo tanto, para iniciar explícitamente una transacción, se debe usar el comando BEGIN o START TRANSACTION, o ejecutar el comando SET AUTOCOMMIT=0 para deshabilitar la confirmación automática de la sesión actual.

Sentencias de control de transacciones:

  • BEGIN o START TRANSACTION inician explícitamente una transacción;

  • COMMIT también puede usar COMMIT WORK, aunque ambos son equivalentes. COMMIT confirma la transacción y hace permanentes todas las modificaciones realizadas en la base de datos;

  • ROLLBACK también puede usar ROLLBACK WORK, aunque ambos son equivalentes. El rollback finaliza la transacción del usuario y deshace todas las modificaciones no confirmadas que están en curso;

  • SAVEPOINT identifier: SAVEPOINT permite crear un punto de guardado dentro de una transacción; una transacción puede tener múltiples SAVEPOINT;

  • RELEASE SAVEPOINT identifier elimina un punto de guardado de una transacción. Cuando no existe el punto de guardado especificado, ejecutar esta sentencia lanza una excepción;

  • ROLLBACK TO identifier revierte la transacción al punto marcado;

  • SET TRANSACTION se utiliza para establecer el nivel de aislamiento de la transacción. El motor de almacenamiento InnoDB ofrece los niveles de aislamiento de transacciones READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ y SERIALIZABLE.

MySQL tiene principalmente dos métodos para el manejo de transacciones:

1. Implementarlo con BEGIN, ROLLBACK, COMMIT

  • BEGIN o START TRANSACTION: se utiliza para iniciar una transacción.
  • ROLLBACKRollback de la transacción, cancela los cambios anteriores.
  • COMMIT: confirmación de la transacción, confirma la transacción, haciendo que los cambios sean permanentes.

2. Usar directamente SET para cambiar el modo de confirmación automática de MySQL:

  • SET AUTOCOMMIT=0Deshabilitar la confirmación automática
  • SET AUTOCOMMIT=1Habilitar la confirmación automática

BEGIN o START TRANSACTION-- Se utiliza para iniciar una transacción:

BEGIN; -- 或者使用 START TRANSACTION;

COMMIT-- Se utiliza para confirmar la transacción, guardando permanentemente todas las modificaciones en la base de datos:

COMMIT;

ROLLBACK-- Se utiliza para revertir la transacción, deshaciendo todos los cambios realizados desde la última confirmación:

ROLLBACK;

SAVEPOINT-- Se utiliza para establecer un punto de guardado en la transacción, para poder revertir a ese punto más tarde:

SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT-- Se utiliza para revertir a un punto de guardado establecido anteriormente:

ROLLBACK TO SAVEPOINT savepoint_name;

A continuación se muestra un ejemplo sencillo de una transacción MySQL:

Ejemplo

-- Iniciar transacción
START TRANSACTION;

-- Ejecutar algunas sentencias SQL
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- Determinar si se debe confirmar o revertir
IF (Condición) THEN
    COMMIT; -- Confirmar transacción
ELSE
    ROLLBACK; -- Revertir transacción
END IF;

Ejemplo

Un ejemplo sencillo de transacción:

Prueba de transacciones

mysql> use EJEMPLO; Database changed mysql> CREATE TABLE ejemplo_transaction_test( id int(5)) engine=innodb; # Crear tabla de datos Query OK, 0 rows affected (0.04 sec) mysql> select * from ejemplo_transaction_test; Empty set (0.01 sec) mysql> begin; # Iniciar transacción Query OK, 0 rows affected (0.00 sec) mysql> insert into ejemplo_transaction_test value(5); Query OK, 1 rows affected (0.01 sec) mysql> insert into ejemplo_transaction_test value(6); Query OK, 1 rows affected (0.00 sec) mysql> commit; # Confirmar transacción Query OK, 0 rows affected (0.01 sec) mysql> select * from ejemplo_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; # Iniciar transacción Query OK, 0 rows affected (0.00 sec) mysql> insert into ejemplo_transaction_test values(7); Query OK, 1 rows affected (0.00 sec) mysql> rollback; # Revertir Query OK, 0 rows affected (0.00 sec) mysql> select * from ejemplo_transaction_test; # Debido al rollback, los datos no se insertaron +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql>

Ejemplo de uso de transacciones en PHP

Prueba de MySQL ORDER BY:

<?php $dbhost = 'localhost'; //Dirección del host del servidor MySQL $dbuser = 'root'; //Nombre de usuario de MySQL $dbpass = '123456'; //Contraseña del usuario de MySQL $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Error de conexión:' . mysqli_error($conn)); } //Establecer la codificación para evitar caracteres chinos ilegibles mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, 'EJEMPLO' ); mysqli_query($conn, "SET AUTOCOMMIT=0"); //Configurar para que no se confirme automáticamente, porque MySQL se ejecuta inmediatamente por defecto mysqli_begin_transaction($conn); //Iniciar definición de transacción if(!mysqli_query($conn, "insert into ejemplo_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); //Determinar si se debe revertir cuando la ejecución falla } if(!mysqli_query($conn, "insert into ejemplo_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); //Determinar si se debe revertir cuando la ejecución falla } mysqli_commit($conn); //Ejecutar transacción mysqli_close($conn); ?>
Otras extensiones