3.6 Transacciones

3.6.1 Introducción a las transacciones

Una transacción es una unidad de trabajo en una base de datos. En una transacción, varias operaciones SQL pueden ejecutarse como un bloque. Las transacciones son importantes porque permiten que las operaciones sean atómicas, lo que significa que todas las operaciones dentro de la transacción deben completarse correctamente, o ninguna de ellas se aplica.

Las transacciones se basan en las características ACID:

  • Atomicidad: Todo o nada. O se realizan todas las operaciones de la transacción, o ninguna.
  • Consistencia: La base de datos pasa de un estado válido a otro estado válido.
  • Aislamiento: Las operaciones de una transacción no son visibles para otras transacciones hasta que se completen.
  • Durabilidad: Una vez que se confirma una transacción, sus efectos son permanentes.

3.6.2 Sintaxis de transacciones en SQL

  • START TRANSACTION o BEGIN: Comienza una nueva transacción.
  • COMMIT: Confirma la transacción y hace permanentes los cambios realizados.
  • ROLLBACK: Revierte la transacción y deshace todos los cambios realizados.

Estas instrucciones son amplamente utilizadas en la declaración de procedimientos y funciones almacenadas. Cuano estas instrucciones tienen que realizar cambios en la base de datos, ejecutando diferentes sentencias SQL, es importante que todas las operaciones se realicen correctamente. Si alguna de ellas falla, es necesario revertir todos los cambios realizados hasta ese momento.

Vamos a describir por ejemplo el caso de la actualización de una cuenta bancaria. Imaginemos que tenemos una tabla cuentas con los siguientes campos:

  • id: Identificador de la cuenta.
  • saldo: Saldo actual de la cuenta.
  • estado: Estado de la cuenta (activa, bloqueada, etc.).

Supongamos que queremos transferir dinero de una cuenta a otra. Para ello, necesitamos realizar dos operaciones:

  1. Restar el dinero de la cuenta de origen.
  2. Sumar el dinero a la cuenta de destino.

Si alguna de estas operaciones falla, no podemos dejar la base de datos en un estado inconsistente. Por lo tanto, utilizamos una transacción para asegurarnos de que ambas operaciones se realicen correctamente o ninguna de ellas.

1
2
3
4
START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;
Si alguna de las operaciones falla, podemos revertir la transacción utilizando ROLLBACK:

ROLLBACK;
Esto deshará todos los cambios realizados en la transacción, dejando la base de datos en su estado original. Llegados a este punto podemos hacer un ejercicio práctico utiliando nuestro editor de SQL favorito.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- Crear la tabla cuentas
CREATE TABLE cuentas (
    id INT PRIMARY KEY,
    saldo DECIMAL(10, 2) CHECK (saldo >= 0),
    estado ENUM('activa', 'bloqueada')
);
-- Insertar datos de ejemplo
INSERT INTO cuentas (id, saldo, estado) VALUES
(1, 1000.00, 'activa'),
(2, 500.00, 'activa');
-- Iniciar la transacción
START TRANSACTION;
-- Restar 100 de la cuenta 1
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
-- Sumar 100 a la cuenta 2
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;
-- Confirmar la transacción
COMMIT;
-- Verificar los resultados
SELECT * FROM cuentas;
En este ejemplo, hemos creado una tabla cuentas y hemos realizado una transferencia de 100 unidades de una cuenta a otra. Si todo sale bien, confirmamos la transacción. Si algo falla, podemos revertirla. Vamos a generar un error para ver cómo funciona el ROLLBACK.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- Iniciar la transacción
START TRANSACTION;
-- Restar 500 a la cuenta 1 
UPDATE cuentas SET saldo = saldo - 500 WHERE id = 1;
-- Intentar sumar los 500 a la cuenta 3. Esto generará un error porque la cuenta 3 no existe.
UPDATE cuentas SET saldo = saldo + 500 WHERE id = 3;
Realizar un rollback
ROLLBACK;
-- Verificar que las cuentas no han cambiado
SELECT * FROM cuentas;

3.6.3 Transacciones con MySQLi y PDO

En MySQLi y PDO, podemos manejar transacciones utilizando funciones específicas para iniciar, confirmar y revertir las transacciones.

MySQLi (procedimental y orientado a objetos):
  1. Iniciar una transacción: Utilizamos begin_transaction() o START TRANSACTION.
  2. Confirmar una transacción: Usamos commit().
  3. Revertir una transacción: Usamos rollback().
PDO:
  1. Iniciar una transacción: Usamos $conexion->beginTransaction().
  2. Confirmar una transacción: Usamos $conexion->commit().
  3. Revertir una transacción: Usamos $conexion->rollBack().

3.6.4 Ejemplo práctico de transacciones

Vamos a realizar una operación que implique una transacción: Vamos a actualizar el salario de un empleado y registrar ese cambio en una tabla salary_changes.

1. Crear la tabla salary_changes

Primero, necesitamos crear una tabla salary_changes para almacenar el historial de cambios de salario. Esta tabla tendrá los campos emp_no, old_salary, new_salary, change_date.

1
2
3
4
5
6
7
CREATE TABLE salary_changes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_no INT NOT NULL,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    change_date DATETIME
);

2. Procedimiento para actualizar el salario y registrar el cambio en salary_changes

Vamos a crear una transacción que primero registre el cambio de salario en la tabla salary_changes y luego actualice el salario del empleado en la tabla salaries.

Ejemplo de transacción con MySQLi (orientado a objetos)

Ejemplo de transacción con MySQLi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<?php
$conexion = new mysqli("localhost", "root", "", "employees");

if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Comienza la transacción
$conexion->begin_transaction();

try {
    // Parámetros de ejemplo
    $emp_id = 1001;
    $nuevo_salario = 65000;

    // Obtener el salario actual
    $query = "SELECT salary FROM salaries WHERE emp_no = ? AND to_date = '9999-01-01'";
    $stmt = $conexion->prepare($query);
    $stmt->bind_param("i", $emp_id);
    $stmt->execute();
    $stmt->bind_result($salario_actual);
    $stmt->fetch();
    $stmt->close();

    // Registrar el cambio de salario en salary_changes
    $query = "INSERT INTO salary_changes (emp_no, old_salary, new_salary, change_date) 
            VALUES (?, ?, ?, NOW())";
    $stmt = $conexion->prepare($query);
    $stmt->bind_param("idd", $emp_id, $salario_actual, $nuevo_salario);
    $stmt->execute();
    $stmt->close();

    // Actualizar el salario en salaries
    $query = "UPDATE salaries SET salary = ?, from_date = NOW() WHERE emp_no = ? AND to_date = '9999-01-01'";
    $stmt = $conexion->prepare($query);
    $stmt->bind_param("di", $nuevo_salario, $emp_id);
    $stmt->execute();
    $stmt->close();

    // Confirmar la transacción
    $conexion->commit();
    echo "Salario actualizado correctamente.";
} catch (Exception $e) {
    // Si hay un error, revertir la transacción
    $conexion->rollback();
    echo "Error: " . $e->getMessage();
}

$conexion->close();
?>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<?php
// Crear conexión
$conexion = mysqli_connect("localhost", "root", "", "employees");

if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}

// Comienza la transacción
mysqli_begin_transaction($conexion);

try {
    // Parámetros de ejemplo
    $emp_id = 1001;
    $nuevo_salario = 65000;

    // Obtener el salario actual
    $query = "SELECT salary FROM salaries WHERE emp_no = $emp_id AND to_date = '9999-01-01'";
    $resultado = mysqli_query($conexion, $query);
    if (!$resultado) {
        throw new Exception("Error al obtener el salario: " . mysqli_error($conexion));
    }
    $row = mysqli_fetch_assoc($resultado);
    $salario_actual = $row['salary'];

    // Registrar el cambio de salario en salary_changes
    $query = "INSERT INTO salary_changes (emp_no, old_salary, new_salary, change_date) 
            VALUES ($emp_id, $salario_actual, $nuevo_salario, NOW())";
    $resultado = mysqli_query($conexion, $query);
    if (!$resultado) {
        throw new Exception("Error al insertar el cambio de salario: " . mysqli_error($conexion));
    }

    // Actualizar el salario en salaries
    $query = "UPDATE salaries SET salary = $nuevo_salario, from_date = NOW() WHERE emp_no = $emp_id AND to_date = '9999-01-01'";
    $resultado = mysqli_query($conexion, $query);
    if (!$resultado) {
        throw new Exception("Error al actualizar el salario: " . mysqli_error($conexion));
    }

    // Confirmar la transacción
    mysqli_commit($conexion);
    echo "Salario actualizado correctamente.";
} catch (Exception $e) {
    // Si hay un error, revertir la transacción
    mysqli_rollBack($conexion);
    echo "Error: " . $e->getMessage();
}

// Cerrar la conexión
mysqli_close($conexion);
?>

Ejemplo de transacción con PDO

Ejemplo de transacción con PDO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Comienza la transacción
    $conexion->beginTransaction();

    // Parámetros de ejemplo
    $emp_id = 1001;
    $nuevo_salario = 65000;

    // Obtener el salario actual
    $query = "SELECT salary FROM salaries WHERE emp_no = :emp_no AND to_date = '9999-01-01'";
    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':emp_no', $emp_id, PDO::PARAM_INT);
    $stmt->execute();
    $stmt->bindColumn(1, $salario_actual);
    $stmt->fetch(PDO::FETCH_ASSOC);

    // Registrar el cambio de salario en salary_changes
    $query = "INSERT INTO salary_changes (emp_no, old_salary, new_salary, change_date) 
            VALUES (:emp_no, :old_salary, :new_salary, NOW())";
    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':emp_no', $emp_id, PDO::PARAM_INT);
    $stmt->bindParam(':old_salary', $salario_actual, PDO::PARAM_STR);
    $stmt->bindParam(':new_salary', $nuevo_salario, PDO::PARAM_STR);
    $stmt->execute();

    // Actualizar el salario en salaries
    $query = "UPDATE salaries SET salary = ?, from_date = NOW() WHERE emp_no = ? AND to_date = '9999-01-01'";
    $stmt = $conexion->prepare($query);
    $stmt->bindParam(1, $nuevo_salario, PDO::PARAM_STR);
    $stmt->bindParam(2, $emp_id, PDO::PARAM_INT);
    $stmt->execute();

    // Confirmar la transacción
    $conexion->commit();
    echo "Salario actualizado correctamente.";
} catch (PDOException $e) {
    // Si hay un error, revertir la transacción
    $conexion->rollBack();
    echo "Error: " . $e->getMessage();
}

$conexion = null;
?>

3.6.5 Ejercicios prácticos

  1. Ejercicio 1: Crea una transacción en PHP que cambien a un empleado de departamento, debe actualizar el departamento actual y crear un registro en dept_emp. Crealo con MySQLi y con PDO. Asegurate de que si to va bien la transacción se confirme, y si no, se revierta.
  2. Ejercicio 2: Crea una transacción que canbiel el jefe de un departamento. Debe actualizar el jefe actual y crear un registro en dept_manager. Asegurate de que si to va bien la transacción se confirme, y si no, se revierta.

Resumen de la sección 3.6

  • Transacciones: Aprendimos cómo gestionar las transacciones en bases de datos para asegurar la atomicidad, consistencia y durabilidad de las operaciones.
  • Procedimientos: Vimos cómo crear procedimientos almacenados para realizar tareas como actualizar el salario de un empleado y registrar el cambio.
  • Funciones almacenadas: Aprendimos cómo trabajar con funciones que devuelven un valor, como calcular el salario promedio.
  • Errores y rollback: Aseguramos que las transacciones se manejen correctamente, utilizando commit() y rollback().