Tema 4
Vistas, transacciones y concurrencia
Tablas virtuales, operaciones seguras y acceso simultáneo
1 · Los tres bloques del tema
Los tres bloques del tema
Recorre el diagrama paso a paso.
Las vistas se crean con CREATE VIEW: CREATE es una orden del DDL. COMMIT y ROLLBACK, las órdenes de las transacciones, son el TCL.
Recorre el diagrama paso a paso.
-
1. Vistas— tablas virtuales basadas en una consulta. Simplifican consultas complejas, mejoran la seguridad al limitar el acceso a los datos y permiten reutilizar código. No almacenan datos, solo la consulta. -
2. Control de concurrencia— gestiona el acceso simultáneo a los datos para evitar conflictos. Asegura que múltiples transacciones se ejecuten de manera segura sin comprometer la integridad de los datos. -
3. Transacciones— bloques de operaciones SQL que se ejecutan como una unidad. Garantizan las propiedades ACID (Atomicidad, Consistencia, Aislamiento, Durabilidad) para preservar la integridad de los datos. -
4. Bloqueo de tablas— mecanismo que impide el acceso simultáneo a una tabla para evitar conflictos. Puede ser a nivel de tabla o de fila, según el motor de almacenamiento. Asegura que una transacción tenga acceso exclusivo mientras modifica datos. -
5. Motores de almacenamiento— sistemas que gestionan cómo se almacenan y recuperan los datos. En MySQL: InnoDB (transacciones y bloqueo a nivel de fila) y MyISAM (sin transacciones, bloqueo a nivel de tabla).
2 · Vistas
Vistas (VIEW)
Una vista es un objeto de la base de datos que muestra información a partir de una consulta SELECT. Funciona como una tabla virtual: muestra datos de una o varias tablas, pero no los almacena físicamente. La información que aparece en una vista proviene de las tablas originales.
Las vistas permiten simplificar consultas complejas, mostrar solo parte de la información y controlar el acceso a los datos. En otras palabras: permiten consultar datos de una o varias tablas como si fueran una sola tabla virtual.
CREATE VIEW vw_alumnoNotas AS
SELECT A.nombre, A.apellidos, M.nombre AS Modulo, C.convocatoria, C.nota FROM Alumno A
INNER JOIN Calificacion C ON A.codigo = C.alumnoCodigo
INNER JOIN Modulo M ON M.codigo = C.moduloCodigo;
SELECT * FROM vw_alumnoNotas
WHERE nota > 5 and modulo = 'Programación en Java';CREATE VIEW vw_modulosProfesor AS
SELECT M.codigo, M.nombre AS Modulo, M.aula, M.horas, P.nombre, P.apellidos, P.especialidad FROM modulo M
INNER JOIN profesor P ON M.profesorDNI = P.dni;
SELECT * FROM vw_modulosProfesor;SELECT * FROM Alumno;
CREATE VIEW vw_InfoAlumnoBasico AS
SELECT nombre, apellidos, email FROM Alumno;
SELECT * FROM vw_InfoAlumnoBasico;Vistas actualizables
Una vista actualizable permite modificar los datos de la tabla original a través de la vista, usándola como si fuera una tabla. Se pueden utilizar instrucciones como:
-
INSERT -
UPDATE -
DELETE
Sin embargo, para ser actualizable una vista debe cumplir ciertas condiciones:
- debe basarse normalmente en una sola tabla,
- no debe contener
GROUP BY, - no debe contener funciones agregadas (
SUM,AVG, etc.), - no debe utilizar
DISTINCT.
-- Vista que solo se permita insertar alumnos de Barcelona o Madrid y no se puedan insertar numHermano
ALTER VIEW vw_AlumnosBCNMADRID AS
SELECT codigo, nombre, apellidos, ciudad, fechaNacimiento, email FROM Alumno
WHERE ciudad IN ('Barcelona','Madrid')
WITH CHECK OPTION;
INSERT INTO vw_AlumnosBCNMADRID VALUES ('AL000025','David','Sánchez García','Barcelona','2004-12-15','dsanchez@gmail.com');Vistas: tablas virtuales
Una vista no guarda datos: guarda la consulta. Cada vez que se usa se ejecuta sobre las tablas reales, y se consulta como una tabla (el usuario no necesita saber que hay un JOIN detrás). Las vistas también permiten seguridad.
CREATE VIEW vLibroAutor AS
SELECT A.nombre, L.titulo, L.precio
FROM Libro L
INNER JOIN Autor A ON A.id = L.autorId;
SELECT * FROM vLibroAutor WHERE precio < 25;-
CREATE OR REPLACE VIEW— crear o modificar. -
DROP VIEW vLibroAutor;— eliminarla (los datos no se tocan). -
SHOW FULL TABLES;— las vistas aparecen conTable_type = VIEW. -
SHOW CREATE VIEW v;— ver su definición.
-
Materializada— almacena el resultado de la vista físicamente. -
No materializada— no almacena el resultado físicamente, sino la definición.
Vistas · Parte 1
Crea una vista llamada Vista_Rendimiento_Embalses que muestre por nombre del embalse: capacidad máxima, caudal medio registrado y porcentaje de uso (caudal medio / capacidad * 100). Después consulta los embalses con caudal medio mayor que 50.
Otro ejemplo
Crea una vista infoGlobalRegistros con la fecha, el operario (nombre y apellidos juntos), el río, el caudal, el embalse y su estado. Después consulta los registros del río Tajo.
La parte 2 del ejercicio, «uso de la vista (nivel alto)», no tiene solución en los apuntes: usando la vista, actualiza el estado a "SATURADO" de los embalses con uso > 15 % y elimina los embalses con uso = 0 (nunca usados).
3 · Transacciones
Qué es una transacción y sus propiedades ACID
Una transacción es un conjunto de operaciones SQL que se ejecutan como una única unidad de trabajo, de manera atómica o indivisible: o se realizan todas correctamente, o no se aplica ninguna. Se utilizan para mantener la integridad de los datos cuando se realizan varias modificaciones relacionadas, y permiten asegurar que un conjunto de operaciones se realice de forma segura y consistente.
Toda transacción debe cumplir cuatro propiedades, cuyas iniciales forman ACID:
-
Atomicidad— todas las operaciones se completan o ninguna. No puede quedar a medias. -
Consistencia— la BD pasa de un estado válido a otro. Solo se confirma lo que puede completarse. -
Aislamiento— las transacciones paralelas no se interfieren entre sí durante su ejecución. -
Durabilidad— una vez confirmada, la transacción persiste aunque haya un fallo del sistema.
Las instrucciones de una transacción
En una transacción se utilizan principalmente tres instrucciones:
-
START TRANSACTION— inicia la transacción. -
COMMIT— guarda definitivamente los cambios: confirma la transacción y la guarda en disco. -
ROLLBACK— cancela los cambios realizados: permite ir al últimoCOMMITque se haya ejecutado.
Recorre el diagrama paso a paso.
-- Desactivamos el modo de confirmación automática
SET AUTOCOMMIT= 0;
-- Iniciamos una transacción
START TRANSACTION;
-- Ejecutamos una serie de operaciones
UPDATE CUENTA SET saldo=saldo- 100 WHERE Id= 1;
UPDATE CUENTA SET saldo=saldo+ 100 WHERE Id= 2;
-- Confirmamos la transacción
COMMIT;
-- O podríamos deshacer la transacción...
-- ROLLBACK;
-- Volvemos a activar el modo de confirmación automática
SET AUTOCOMMIT= 1;Ejercicio 1
Se quiere actualizar la nota de un alumno en un módulo.
Ejercicio 2 · Inserción de alumno y calificación
Se quiere añadir un nuevo alumno y asignarle una calificación en un módulo. Si alguna operación falla, se deben cancelar todos los cambios.
Puntos de control: SAVEPOINT y ROLLBACK TO
Dentro de una transacción se puede crear un punto de control (SAVEPOINT) y deshacer solo hasta él, en lugar de deshacerlo todo.
-
ROLLBACK— deshace hasta el últimoCOMMIT. -
SAVEPOINT nombre— crea un punto de control dentro de la transacción. -
ROLLBACK TO nombre— deshace hasta el último punto de control oSAVEPOINT.
Ejercicio 3
Se quiere realizar varias operaciones en la base de datos relacionadas con un alumno. Realiza una transacción que: inicie una transacción; actualice la nota de un alumno en el módulo 'PROG'; cree un SAVEPOINT llamado punto1; inserte una nueva calificación para el mismo alumno en el módulo 'BBDD'; si esta segunda operación no es correcta, vuelva al SAVEPOINT creado; finalmente confirme los cambios realizados.
Transacciones: todo o nada
Una transacción es un conjunto de instrucciones que queremos que se ejecuten todas o se puedan revertir. No solo sirve para deshacer: si algo falla, se revierte todo lo ejecutado y así se puede corregir y seguir.
START TRANSACTION;
UPDATE Cuenta SET saldo = saldo - 300 WHERE id = 1;
UPDATE Cuenta SET saldo = saldo + 300 WHERE id = 2;
COMMIT; -- o ROLLBACK; si algo ha fallado| cuenta | antes | tras COMMIT |
|---|---|---|
| 1 | 1000 | 700 |
| 2 | 500 | 800 |
| total | 1500 | 1500 |
El total se mantiene: la BD sigue siendo coherente.
-
COMMIT— confirma: los cambios se guardan de forma permanente y los demás usuarios ya los ven. -
ROLLBACK— deshace: anula todo lo hecho desdeSTART TRANSACTION(los saldos vuelven a 1000 y 500). -
SAVEPOINT p1 / ROLLBACK TO p1— punto intermedio: deshace solo una parte de la transacción.
Propiedades ACID: todas las bases de datos tienen que cumplir estas 4, y el gestor de base de datos se encarga de verificarlo.
-
Atomicidad— todo o nada: se ejecutan todas las operaciones o ninguna. -
Consistencia— la BD pasa de un estado válido a otro válido (PK, FK, CHECK…). -
Aislamiento— las transacciones simultáneas no ven los cambios a medias de las demás. -
Durabilidad— tras el COMMIT, los cambios sobreviven a un apagón o un fallo.
SELECT @@autocommit; -- 1
SET autocommit = 0; -- hasta COMMIT4 · Concurrencia y bloqueos
Acceso concurrente y control de concurrencia
-
Acceso concurrente (concurrencia operacional)— varios usuarios o procesos acceden a la base de datos, o modifican los datos, al mismo tiempo. -
Bloqueo (locking)— mecanismo que usa la base de datos para evitar conflictos entre esos accesos simultáneos, por ejemplo impedir que dos usuarios modifiquen el mismo dato a la vez.
En entornos multiusuario, la concurrencia operacional puede generar inconsistencias en los datos o en los resultados devueltos. Ejemplo: estamos editando nuestra base de datos y otra persona está leyendo los datos, ahora incorrectos porque se están modificando. Para eso se crean los bloqueos.
El SGBD gestiona la concurrencia a través de los bloqueos, que pueden ejecutarse a nivel de tabla (bloqueando toda la tabla) o de registro (bloqueando solo las filas que se van a modificar o leer), y darse en lecturas, escrituras o en ambas:
-
Lectura— bloquea los datos para evitar que otras operaciones los modifiquen mientras una operación de lectura los está procesando, evitando la lectura de datos inconsistentes. -
Escritura— bloquea los datos para evitar que otras operaciones los lean o modifiquen mientras se realiza una actualización, asegurando que la actualización se realice de forma segura.
Tipos de bloqueo: shared lock y exclusive lock
Recorre el diagrama paso a paso.
-
SHARED LOCK (lectura)— los propietarios y los usuarios pueden ver la misma tabla al mismo tiempo, pero no pueden modificarla hasta que quede liberada. Protege la tabla: se puede seguir leyendo conSELECT, pero no editar hasta que el propietario la libere. -
EXCLUSIVE LOCK (escritura)— el propietario del bloqueo puede leer y editar. Los otros usuarios no pueden ni leer ni escribir.
La diapositiva del profesor los compara así. Shared lock:
- Múltiples sesiones pueden leer la misma tabla simultáneamente.
- Ninguna sesión puede escribir mientras el bloqueo está activo.
- Las escrituras quedan en cola hasta que se libera.
- Se libera automáticamente al cerrar la sesión o con
UNLOCK TABLES.
LOCK TABLES CLIENTE READ;
SELECT * FROM CLIENTE;
UNLOCK TABLES;Exclusive lock:
- Solo una sesión puede leer y escribir el registro bloqueado.
- El resto de sesiones no pueden ni leer ni escribir.
- Garantiza consistencia durante operaciones de escritura.
- Se libera al cerrar la sesión o con
UNLOCK TABLES.
LOCK TABLES CLIENTE WRITE;
INSERT INTO CLIENTE(Id) VALUES(666);
SELECT * FROM CLIENTE;
UNLOCK TABLES;Problemas de no bloquear correctamente
Recorre el diagrama paso a paso.
-
Dirty Read (lectura sucia)— lees datos que aún no han sido confirmados (COMMIT) por otra transacción. Si esa transacción haceROLLBACK, habrás leído datos que nunca existieron realmente. Ejemplo: A lee saldo = 500 € modificado por B; B haceROLLBACK; A usó un saldo que nunca existió. -
Non-Repeatable Read (lectura no repetible)— lees un dato dos veces dentro de la misma transacción y cambia entre lecturas: otro usuario lo ha modificado y confirmado entre medias. Ejemplo: A lee precio = 10 €; B actualiza precio = 15 € y haceCOMMIT; A vuelve a leer y obtiene 15 €. -
Phantom Read (lectura fantasma)— repites una consulta (por ejemplo, unSELECTcon condición) y aparecen o desaparecen filas: otro usuario ha insertado o eliminado registros que cumplen esa condición. Ejemplo: A cuenta 10 pedidos; B inserta 2 pedidos y haceCOMMIT; A vuelve a contar y obtiene 12.
Demostración en MySQL: LOCK TABLES
Para la demostración, el profesor creó un usuario y le dio permisos. Así hay dos sesiones a la vez: la del usuario root, que pone el bloqueo, y la del otro usuario (dporti).
SELECT CURRENT_USER();
LOCK TABLES Alumno READ;
SELECT AVG(numHermanos) FROM Alumno;
UNLOCK TABLES;
INSERT INTO Alumno VALUES ('A0000150', 'María', 'Gómez Pérez', 'Sevilla', '2002-11-05', 'maria.gomez@example.com', 2);
INSERT INTO Alumno VALUES ('AL000151', 'Javier', 'López Sánchez', 'Bilbao', '2005-02-18', 'javier.lopez@example.com', 0);SELECT CURRENT_USER();
LOCK TABLES Alumno WRITE;
SELECT AVG(numHermanos) FROM Alumno;
INSERT INTO Alumno VALUES ('A0000150', 'María', 'Gómez Pérez', 'Sevilla', '2002-11-05', 'maria.gomez@example.com', 2);
UNLOCK TABLES;
INSERT INTO Alumno VALUES ('AL000151', 'Javier', 'López Sánchez', 'Bilbao', '2005-02-18', 'javier.lopez@example.com', 0);Concurrencia y bloqueos
Dos usuarios modifican a la vez la cuenta 1 (saldo = 1000 €): A retira 300 € y B ingresa 200 €. Se esperaba 900 €, pero queda 1200 €: actualización perdida (los −300 € de A se han perdido).
-- t1 A: SELECT saldo -> 1000
-- t2 B: SELECT saldo -> 1000
-- t3 A: UPDATE saldo = 1000 - 300 (700)
-- t4 B: UPDATE saldo = 1000 + 200 (1200)Las anomalías clásicas de la concurrencia son tres:
-
Lectura sucia (dirty read)— leo datos que otra transacción aún no ha confirmado… y que luego deshace con ROLLBACK. -
Lectura no repetible (non repeatable read)— leo la misma fila dos veces en mi transacción y obtengo valores distintos. -
Lectura fantasma (phantom read)— repito una consulta y aparecen (o desaparecen) filas que otro ha insertado o borrado.
Soluciones: bloqueos (LOCK TABLES, SELECT … FOR UPDATE) y niveles de aislamiento.
LOCK TABLES Cuenta WRITE;
UPDATE Cuenta SET saldo = saldo - 300
WHERE id = 1;
UNLOCK TABLES;-
READ— quien bloquea solo lee; los demás leen y para escribir esperan. -
WRITE— quien bloquea lee y escribe; los demás esperan para todo.
START TRANSACTION;
SELECT saldo FROM Cuenta
WHERE id = 1 FOR UPDATE; -- bloquea
UPDATE Cuenta SET saldo = saldo - 300
WHERE id = 1;
COMMIT; -- libera