Acid en mysql: conceptos y ejemplo práctico

• Crear la base de datos y la tabla: Crea una base de datos y una tabla "cuentas" con el motor InnoDB:
  
  

  CREATE DATABASE banco;
  USE banco;
  
  CREATE TABLE cuentas (
  id INT PRIMARY KEY,
  titular VARCHAR(50),
  saldo DECIMAL(10, 2),
  CONSTRAINT saldo_positivo CHECK (saldo >= 0)
  ) ENGINE=InnoDB;
  

  La restricción "CHECK" no permite saldos negativos.
• Insertar datos:
  
  

  INSERT INTO cuentas (id, titular, saldo) VALUES
  (1, 'Ana', 1000.00),
  (2, 'Juan', 2500.00).
  (3, 'María', 1500.00),
  (4, 'Luis', 500.00).
  (5, 'Marta', 10000.00),
  (6, 'Silvia', 55000.00).
  (7, 'Gema', 1300.00),
  (8, 'Adrana', 6500.00);
  

• Ejemplo de transacción ACID: Realizar una transferencia de 200.00 de Ana a Juan usando una transacción. Esto demuestra las propiedades ACID:
  
  

  START TRANSACTION;
  
  -- Restar 200 de la cuenta de Ana
  UPDATE cuentas SET saldo = saldo - 200.00 WHERE id = 1;
  
  -- Sumar 200 a la cuenta de Juan
  UPDATE cuentas SET saldo = saldo + 200.00 WHERE id = 2;
  
  -- Verificar que el saldo de Ana no sea negativo (para consistencia)
  SET @saldo_ana = (SELECT saldo FROM cuentas WHERE id = 1);
  IF @saldo_ana >= 0 THEN
  COMMIT; -- Confirma la transacción si todo está bien
  ELSE
  ROLLBACK; -- Deshace la transacción si el saldo es negativo
  END IF;
  

• Explicación de las propiedades ACID en el ejemplo anterior:
  • Atomicidad: La transacción asegura que ambas operaciones (restar de Ana y sumar a Juan) se completen juntas. Si alguna falla (por ejemplo, un error en la base de datos), el ROLLBACK deshace todo, dejando la base de datos sin cambios.
  Consistencia: La restricción CHECK y la verificación del saldo garantizan que la base de datos permanezca en un estado válido (sin saldos negativos). Si el saldo de Ana fuera menor a 200, el ROLLBACK evitaría violar esta regla.
  • Aislamiento: Si otra transacción intenta leer o modificar las cuentas mientras esta transacción está en curso, el nivel de aislamiento de InnoDB (por defecto, REPEATABLE READ) asegura que no haya interferencias. Por ejemplo, nadie verá un estado intermedio donde Ana ya perdió 200 pero Juan aún no los recibió.
  • Durabilidad: Una vez que se ejecuta el COMMIT, los cambios (nuevo saldo de Ana: 800.00, nuevo saldo de Juan: 700.00) se guardan permanentemente en disco, incluso si el servidor falla justo después.
• Para confirmar que la transferencia se realizó correctamente ejecutar:
  
  

  SELECT * FROM cuentas;
  

  Se verá que los datos concuerdan con la transferencia realizada.
• Ejecución con fallo. Supongamos que Ana intenta transferir 20000.00 (más de lo que tiene)::
  
  

  START TRANSACTION;
  
  UPDATE cuentas SET saldo = saldo - 20000.00 WHERE id = 1;
  UPDATE cuentas SET saldo = saldo + 20000.00 WHERE id = 2;
  
  SET @saldo_ana = (SELECT saldo FROM cuentas WHERE id = 1);
  IF @saldo_ana >= 0 THEN
  COMMIT;
  ELSE
  ROLLBACK;
  END IF;
  

  En este caso, se ejecuta el ROLLBACK porque el saldo de Ana sería negativo, violando la restricción de consistencia.