Tema 3 · 6 · Transacciones ACID y rigidez de esquema

Presentación del Tema 3: «Propiedades de las bases de datos: ACID» y «Ejemplos ACID». Duración: 15 a 20 minutos. Coste: ninguno propio (usas la RDS y Adminer que ya tienes).

Qué vas a hacer

Vas a ver en la práctica qué garantiza una transacción: una transferencia bancaria entre cuentas que se completa entera o no se hace (A, C, I, D de ACID), qué pasa con COMMIT, ROLLBACK y sin transacción, y cómo el esquema rígido de una base relacional te protege (saldos no negativos) pero también te obliga a actualizar las aplicaciones cuando cambias una tabla.

ejemplos_acid.txt ──(SQL command)──> base ejemplo_ACID: tabla banco (5 clientes) + procedimiento transferir()
ejercicios.txt ───(bloque a bloque)──> ROLLBACK / COMMIT / autocommit / CHECK / rigidez de esquema

Qué se crea en AWS: nada. Dentro de tu RDS se crean las bases ejemplo_ACID y (temporalmente) rigidez.

Qué contiene esta carpeta

Fichero	Para qué sirve	En qué paso se usa
`ejemplos_acid.txt`	Borra y vuelve a crear la base `ejemplo_ACID`, crea la tabla `banco` con cinco clientes (`A` a `E`) y el procedimiento `transferir(origen, destino, importe)`. Contiene líneas `DELIMITER //`; Adminer las interpreta	Paso 1 (y para reiniciar saldos en los pasos 3 a 5)
`ejercicios.txt`	SQL de apoyo con el resultado esperado en comentarios: sección ACID (`CALL transferir`, bloques 1 a 4 de «ACID paso a paso») y sección «Rigidez de esquema»	Pasos 2 a 6
`README.md`	Esta guía	Siempre

Antes de empezar

[ ] RDS MariaDB Available (03.01) y Adminer conectado (03.02).
[ ] Para la parte de rigidez (paso 6): la base universidad con la tabla estudiantes cargada (03.03). La parte ACID no la necesita.
[ ] Los ficheros están en tu PC; ábrelos con un editor de texto.

Cómo llevar los ficheros a donde toque

No hace falta CloudShell: copia el contenido de cada fichero desde tu PC y pégalo en Adminer (SQL command). Importante: cada bloque de ejercicios.txt debe ir en una sola ejecución de Adminer (cada Execute abre una conexión nueva, y una transacción abierta no sobrevive entre ejecuciones).

Paso a paso

Paso 1. Crear la base y el procedimiento. En Adminer: SQL command > pega el contenido COMPLETO de ejemplos_acid.txt > Execute.

Qué verás: la ejecución termina sin errores. Se ha creado la base ejemplo_ACID con la tabla banco (cinco clientes de A a E) y el procedimiento transferir. Si ejecutas el fichero otra vez, vuelve a empezar desde los saldos iniciales (empieza con DROP DATABASE IF EXISTS ejemplo_ACID).

Paso 2. Probar transferir. Cada CALL en su propia ejecución:

CALL transferir('A','C',150);
CALL transferir('A','C',2000);

Qué verás: la primera devuelve una fila con origen, destino, importe, saldo_origen y saldo_destino (A 350, C 300) y deja los saldos en A 350, B 300, C 300, D 0, E 50 (la tabla; la suma sigue siendo 1000). La segunda da el error 1644 con el mensaje Saldo insuficiente y no cambia ningún saldo: eso es lo esperado y es lo que muestra la presentación (no es un fallo del entorno). Para ver los saldos: SELECT * FROM ejemplo_ACID.banco;. Si ejecutas dos veces la primera orden, A queda en 200 y C en 450.

Paso 3. ACID paso a paso, sin el procedimiento. Usa los cuatro bloques de la sección ACID de ejercicios.txt. Reinicia los saldos ejecutando de nuevo ejemplos_acid.txt ANTES del bloque 1 (si ya hiciste los CALL de arriba, los saldos son A 350 y C 300, no A 500 y C 150) y también entre bloques. Cada bloque va en UNA sola ejecución.

Qué verás:

Bloque	Qué hace	Resultado esperado
1) `ROLLBACK`	`START TRANSACTION`, resta 100 a A y suma 100 a C, `ROLLBACK`	Dentro: A 400, C 250. Fuera: A 500, C 150 (se deshizo)
2) `COMMIT`	Lo mismo con `COMMIT`	A 400, C 250 (guardado)
3) Sin transacción (autocommit)	Resta 100 a A y suma 100 a un cliente `Z` que no existe	0 filas y ningún error; `SELECT SUM(saldo)` da 900 en vez de 1000: el dinero desaparece
4) Consistencia	`UPDATE banco SET saldo = saldo - 10 WHERE cliente = 'D';`	Error 1264 `Out of range value` (lo provoca `UNSIGNED`); D sigue en 0

En el bloque 4: si quitas UNSIGNED de la columna saldo en ejemplos_acid.txt, la misma sentencia da el error 4025 CONSTRAINT chk_saldo_no_negativo failed: ese es el que muestra el CHECK. En los dos casos el UPDATE se rechaza y D sigue en 0.

Paso 4. (Reflexión) ¿Qué letra de ACID rompe el bloque 3? (Atomicidad: sin transacción cada UPDATE se confirma solo.) ¿Y cuál protege el bloque 4? (Consistencia: el esquema impide saldos negativos.)

Paso 5. Rigidez de esquema. Bloque «Rigidez de esquema» de ejercicios.txt. Hace una copia de la tabla en una base de prueba (rigidez), de modo que universidad no se toca.

Crea la base y copia la tabla (CREATE TABLE alumnos LIKE universidad.estudiantes; + INSERT ... SELECT). Qué verás: SELECT COUNT(*) FROM alumnos; da 48 (50 si ya hiciste el ejercicio 3 de 03.04).
Cambiar el modelo: ALTER TABLE alumnos ADD id_matricula int NULL; Qué verás: la columna nueva aparece en TODAS las filas, con NULL: 48 filas y 0 con matrícula.
Rellenarla: UPDATE alumnos SET id_matricula = 100 + id; Qué verás: id 1 Elena 101, id 2 Raul 102.
Una aplicación antigua que inserta SIN nombrar las columnas deja de funcionar: INSERT INTO alumnos VALUES (60, 'Ana', 'Perez', 'A', 1, 1); Qué verás: ERROR 1136: Column count doesn't match value count at row 1. Con la lista de columnas sí funciona (la columna nueva queda a NULL).
Limpieza: DROP DATABASE rigidez;

Esto es la idea de la diapositiva: en una base relacional, si cambia el esquema hay que actualizar las aplicaciones que lo usan (el mismo mensaje que estudiantes-nota.php en 03.04); en DynamoDB (03.07) cada ítem puede traer atributos distintos.

Errores frecuentes

Síntoma	Causa	Solución
`Error 1644 Saldo insuficiente` en `CALL transferir(...)`	Es el resultado esperado de la presentación	No es un fallo; los saldos no cambian
Los saldos de los bloques 1 a 4 no son los esperados (A 500 y C 150)	Ya habías ejecutado los `CALL` o un bloque anterior	Ejecuta de nuevo `ejemplos_acid.txt` para reiniciar saldos (antes del bloque 1 y entre bloques)
El `ROLLBACK` del bloque 1 no deshace nada (sin probar)	Pegaste las sentencias en ejecuciones distintas (cada Execute abre una conexión nueva)	Cada bloque, en una sola ejecución
Error al pegar dos sentencias seguidas (`Error 1064`)	Falta algún `;` entre ellas	Termina cada sentencia con `;` o lánzalas de una en una
`Error 1146 Table 'universidad.estudiantes' doesn't exist` (sin probar) al hacer `CREATE TABLE alumnos LIKE ...`	No cargaste `universidad`	Haz primero 03.03
Error al pegar un bloque desde el PowerPoint	PowerPoint mete caracteres no imprimibles	Copia el bloque de `ejercicios.txt`

Limpieza

Nada que borrar en AWS. Dentro de la RDS: DROP DATABASE rigidez; (ya en el paso 5) y, si quieres, DROP DATABASE ejemplo_ACID;. Las dos bases desaparecen igualmente al borrar la RDS.

La RDS y Adminer no se borran aquí: Adminer en la limpieza de 03.02 y la RDS en la de 03.01, después de la última actividad que las use (03.09).

Cómo sabes que has terminado

[ ] CALL transferir('A','C',150) deja A 350, B 300, C 300, D 0, E 50 y CALL transferir('A','C',2000) da Saldo insuficiente sin cambiar saldos.
[ ] Has reproducido los cuatro bloques de «ACID paso a paso» y sabes explicar el resultado de cada uno (el bloque 3 deja la suma en 900).
[ ] Si hiciste la rigidez de esquema: universidad.estudiantes sigue con 48 filas (50 si ya hiciste el ejercicio 3) y sin columnas nuevas, y la base rigidez ya no existe.

Anterior: 03.05 · Ejercicios 4 y 5 · Siguiente: 03.07 · DynamoDB con la CLI y Python