MySQL Realizar Transacciones ACID e Integridad Referencial

Toda aplicación que incluya una base de datos debe cumplir con características ACID, en el las aplicaciones y gestores de bases de datos profesionales el concepto ACID hace referencia a las características o propiedades que garantizan que las transacciones en las bases de datos se realicen de forma segura y confiable. En concreto ACID significa Atomicity (Atomicidad), Consistency (Consistencia), Isolation (Aislamiento) y Durability (Durabilidad).

Una transacción dentro de una base de datos es por ejemplo insertar un registro o si lo vemos como modelo de negocios Añadir nuevo cliente, Modificar producto. Las transacciones siempre producen un cambio, insertar, modificar, eliminar, una consulta no es una transacción ya que no produce cambios.

Atomicidad

Es la propiedad que garantiza y verifica si la transacción se realizo o no se realizo y esta propiedad indica que si un operación no se realiza completa entonces se cancela, por ejemplo supongamos que estamos insertando un registro y se cae el servidor, un registro se grabo a la mitad, entonces por atomicidad este registro no se grabara.

Otro ejemplo si se esta realizando un pago online y se descuenta el importe de nuestra cuenta pero el pago falla o se cae el sistema, entonces se canela la transacción y la base de datos no lo graba.

Consistencia

Es la propiedad que garantiza que se ejecutaran las transacciones que puedan finalizar sin problemas. Este concepto tiene que ver con la integridad de la base de datos. Impide que los datos se cambien y pierdan el sentido o queden sin ninguna referencia por ejemplo no se podrá eliminar un cliente mientras haya realizado alguna compra alguna vez. Si se quiere eliminar el cliente habrá que eliminar primero todas las factura y los datos relacionados con ese cliente.

Aislamiento

es la propiedad que garantiza que si se producen dos o mas transacciones al mismo tiempo estas se ejecutaran una atrás de otra y si se ejecutan en paralelo cada una lo hara en forma independiente de la otra para evitar posibles errores.

Durabilidad

Es la propiedad que se encarga de garantizar una transacción se ha realizado y los cambios que haya realizado la transacción son permanentes, incluso ante cualquier problema como falta de electricidad, o fallos de sistema.

MySQL soportar el formato InnoDB, que es una forma de almacenamiento de datos para MySQL, incluido como formato de tabla estándar en todas las distribuciones de MySQL. Este formato soporta transacciones de tipo ACID e integridad referencial.

Vamos a realizar algunos ejemplos de implementación de ACID con Mysql, las consultas luego podrían implementarse en cualquier lenguaje de programación. En esta base de datos cada usuarios tendrá un nivel de privilegios y acciones que podrá ejecutar en el sistema de la empresa. Solo nos centraremos en esa funcionalidad.

Comenzaremos creando una base de datos EmpresaDB desde phpmyadmin, luego vamos a crear una tabla usuarios y seleccionaremos el motor de almacenamiento InnoDB.

-- Estructura de tabla para la tabla `usuarios`

CREATE TABLE IF NOT EXISTS `usuarios` (
  `idusuario` int(10) NOT NULL,
  `nombre` varchar(150) 
DEFAULT NULL) 
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Añadimos unos datos a la tabla usuarios

INSERT INTO `usuarios` (`idusuario`, `nombre`) VALUES
(1, 'Carlos Alberte'),
(2, 'Pablo Callejos'),
(3, 'Ana Bolena');

En este caso creamos la clave primaria de la tabla usuarios que sera idusuario

-- Indices de la tabla `usuarios`
ALTER TABLE `usuarios`  ADD PRIMARY KEY (`idusuario`);

A continuación crearemos la tabla niveles

-- Estructura de tabla para la tabla `niveles`

CREATE TABLE IF NOT EXISTS `niveles` ( 
 `idnivel` int(11) NOT NULL, 
 `nivel` varchar(50) DEFAULT NULL) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Añadimos algunos datos a la tabla niveles

INSERT INTO `niveles` (`idnivel`, `nivel`) VALUES
(1, 'Básico'),
(2, 'Intermedio'),
(3, 'Avanzado');

Creamos a continuación la tabla privilegios, donde indicaremos el nivel de permisos de cada usuario

-- Estructura de tabla para la tabla `privilegios`
CREATE TABLE IF NOT EXISTS `privilegios` ( 
`idprivilegio` int(11) NOT NULL,  
`idnivel` int(11) NOT NULL DEFAULT '0', 
`idusuario` int(11) NOT NULL)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Añadimos algunos datos a la tabla privilegios

INSERT INTO `privilegios` (`idprivilegio`, `idnivel`, `idusuario`) VALUES
(1, 1, 1),
(2, 2, 3),
(3, 1, 2);

Añadimos a continuación las relaciones desde le editor SQL, asigno una clave foránea que relaciona la tabla privilegios y usuarios a través de idusuario y la clave foránea que relación privilegios con niveles a través de idnivel.

-- Filtros para la tabla `privilegios`
ALTER TABLE `privilegios`  
ADD CONSTRAINT `nivelfk` FOREIGN KEY (`idnivel`) REFERENCES `niveles` (`idnivel`),  
ADD CONSTRAINT `privilegiosfk` FOREIGN KEY (`idusuario`) REFERENCES `usuarios` (`idusuario`);

A continuación consultamos la base de datos para ver si los datos están bien

SELECT usuarios.nombre, niveles.nivel
FROM usuarios, niveles, privilegios
WHERE privilegios.idnivel=niveles.idnivel
ANDusuarios.idusuario=privilegios.idusuario

Veamos a continuación como funciona intentamos insertar los privilegios a un usuario y nivel que no existen.
La sentencia será la siguiente INSERT INTO privilegios VALUES (idprivilegio, idusuario,idnivel); por lo tanto

INSERT INTO privilegios VALUES (6, 8,10);

Esto da error ya que la clave foránea de idusuario en la tabla privilegios crearía un inconsistencia si añadimos un usuario que no existe en la tabla usuarios, aquí evitamos el error, con el formato MySam el dato se hubiese guardado sin problemas.

A continuación intentaremos borrar un usuarios de la tabla usuarios:

DELETE FROM `usuarios` WHERE idusuario=3

Al ejecutar la sentencia SQL nos dará un error, debido a que no se puede borrar el cliente porque tiene datos en otras tablas en este caso el cliente con id 3 esta en la tabla privilegios, si queremos borrarlo deberemos primero eliminarlo de todas las tablas y luego de la tabla cliente.

Para eliminar este tipo de registros con claves foráneas se utiliza el que se denomina eliminar en CASCADA, en la que todos los registros relacionados a una consulta en particular se eliminaran en todas las tablas donde tengan relaciones de claves foráneas. Para llevar a cabo esta transacción utilizamos la función ON DELETE CASCADE.

Lo primero sera dar permiso de eliminación en cascada, recordemos que al iniciar por defecto el tipo de referencia es RESTRICT que indica que los datos de ese campo de la tabla no pueden ser actualizados o borrados, esto es por seguridad cualquier sentencia que se ejecute no podrá realizar ninguna transacción, por ello cambiamos los permisos de modificación y de eliminación.

ALTER TABLE `privilegios`
ADD CONSTRAINT `privilegiosfk` FOREIGN KEY (`idusuario`) 
REFERENCES `usuarios` (`idusuario`) ON DELETE CASCADE ON UPDATE CASCADE;

Realizamos nuevamente la consulta SQL

DELETE FROM `usuarios` WHERE idusuario=3

Luego podremos realizar la consulta sql para verificar que ya no está en ninguna tabla:

SELECT usuarios.nombre, niveles.nivel
FROM usuarios, niveles, privilegios
WHERE privilegios.idnivel=niveles.idnivel
AND usuarios.idusuario=privilegios.idusuario

El usuario 3 se ha eliminado de la tabla usuarios y de la tabla privilegios también, pues ahí tenia una clave foránea el idusuario.

Esto es lo mismo para los Update al modificar, se puede modificar en cascada para así mantener la integridad referencial en Mysql y la relación entre tablas.

Veamos que ocurre si añadimos un dato incorrecto en la inserción en cadena, por ejemplo añadimos un usuario, pero al añadirle privilegio nos equivocamos de id

INSERT INTO usuarios VALUES(5,'Julia Montaña');
INSERT INTO privilegios (`idprivilegio`, `idnivel`, `idusuario`) VALUES (6, 2, 6);

En este caso se guardara el usuario pero no sus privilegios debido a que el ID 6 no existe en la tabla usuarios.