Skip to content

Latest commit

 

History

History
157 lines (91 loc) · 5.13 KB

transacciones.md

File metadata and controls

157 lines (91 loc) · 5.13 KB

Universidad Austral de Chile

INFO133: Base de Datos

Responsable: Matthieu Vernier, [email protected]

TP: Transacciones y Propiedades ACID

Este TP se centra sobre el concepto de Transacción en los SGBD relacionales y sus propiedades ACID. Las Transacciones son uno de los 3 conceptos pilares de todos los SGBD relacionales (el modelo relacional y el lenguaje SQL son los 2 otros conceptos pilares).

Para ilustrar el concepto de Transacción trabajaremos con una instancia de MySQL que se encuentra en un servidor remoto.

Se puede configurar MySQL para que usuarios remotos puedan conectarse a través de una red. En nuestro caso, utilizaremos una instancia de MySQL que se encuentra en un computador de la red local de la UACh con la dirección IP local siguiente: 146.83.216.219

Desde un terminal, se puede acceder al MySQL remoto con el comando siguiente: mysql -h 146.83.216.219 -P 3001 -u username -p

-h: IP de la máquina que aloja MySQL

-P: puerto que utiliza MySQL para comunicar con programas externos

-u: usuario autorizado

-p: password del usuario

1. Caso de estudio: Creación de una moneda alternativa por los estudiantes de Informática de la UACh

Supongamos que los estudiantes de Informática-UACh deciden crear su propia moneda: el Pudu.

Esta moneda sirve para facilitar pequeñas transacciones entre los miembros de la comunidad dentro del campus Miraflores.

Por el momento, el volumen de Pudu en circulación no puede variar, está fijado en 5.000 PUDU.

Al momento de su creación, 1 PUDU era igual a 1.000 pesos. 5 estudiantes decidieron ser parte de esta comunidad comprando cada uno 100 PUDU.

USE pudu;

SELECT * FROM cuentas;

2- ¿Qué es una transacción A.C.I.D?

Supongamos ahora que algunos estudiantes quieren empezar a utilizar sus PUDU:

  • el estudiante 1 quiere transferir 2 PUDU al estudiante 2 para reembolsar la compra de un sandwich.
  • el estudiante 3 quiere transferir 50 PUDU al estudiante 4 quien lo ayudó en un proyecto.
  • el estudiante 3 quiere transferir 75 PUDU al estudiante 5 quien lo ayudó en un proyecto.

Cada transferencia corresponde a una transacción distinta.

Cada transacción implica al menos 2 operaciones:

  • actualizar la cuenta del estudiante que envia los PUDU
  • actualizar la cuenta del estudiante que recibe los PUDU

Atomicidad (A): Una transacción se realiza completamente o no se realiza.

START TRANSACTION;

UPDATE cuentas SET valor=valor-2 WHERE ID=1;

UPDATE cuentas SET valor=valor+2 WHERE ID=2;

commit;

START TRANSACTION;

UPDATE cuentas SET valor=valor-2 WHERE ID=1;

UPDATE cuentas SET valor=valor+2 WHERE ID=2;

rollback;

aIslamiento (I): Una transacción se realiza completamente aislada de otras transacciones.

START TRANSACTION;

UPDATE cuentas SET valor=valor-50 WHERE ID=3;

UPDATE cuentas SET valor=valor+50 WHERE ID=4;

commit;

START TRANSACTION;

UPDATE cuentas SET valor=valor-75 WHERE ID=3;

UPDATE cuentas SET valor=valor+75 WHERE ID=5;

commit;

Consistencia (C): Una transacción debe respetar las restricciones establecidas, sino se anula la transacción. Se terminan las transacciones que no van a romper las reglas y directrices de Integridad de la base de datos. La propiedad de consistencia sostiene que cualquier transacción llevará a la base de datos desde un estado válido a otro también válido.

Ver lista de restricciones en MySQL: https://www.w3resource.com/mysql/creating-table-advance/constraint.php

MySQL no soporta totalmente la restricción CHECK: http://www.mysqltutorial.org/mysql-check-constraint/

Sin embargo se puede simular la restricción CHECK que necesitamos utilizando el concepto de Procedura almacenada y Trigger.

Procedura: check_cuentas

DELIMITER $

CREATE PROCEDURE `check_cuentas`(IN valor INT)
BEGIN
    IF valor < 0 THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'cuenta con valor negativo';
    END IF;
END$
DELIMITER ;

Trigger: comandos que se activan solos cuando pasa cierto evento. Por ejemplo cuando se insert o actualiza cierta tabla.

-- before insert
DELIMITER $
CREATE TRIGGER `cuentas_before_insert` BEFORE INSERT ON `cuentas`
FOR EACH ROW
BEGIN
    CALL check_cuentas(new.valor);
END$
DELIMITER ; 
-- before update
DELIMITER $
CREATE TRIGGER `cuentas_before_update` BEFORE UPDATE ON `cuentas`
FOR EACH ROW
BEGIN
    CALL check_cuentas(new.valor);
END$
DELIMITER ;

DURABILIDAD (C): Cuando una transacción fue confirmada por "commit", el sistema asegura que el resultado de la transacción está registrado.

Prueba con 'Exit'.

Ejercicio

  1. Agregar una restriccion que permite que un miembro no puede tener más de 50% de los PUDU.
  2. Agregar una restricción que permite asegurarse que hay siempre 5.000 PUDU en el sistema.
  3. Agregar una restricción que permite evitar transacciones de más de 50 PUDU.

Documentacion: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html