1 · Los tres bloques del tema

Los tres bloques del tema

Dónde está el tema dentro de SQL

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.

Vistas, transacciones y concurrencia: qué pertenece a cada bloque

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.

Vista de alumnos con sus notas por módulo
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';
Vista de módulos con su profesor
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;
Vistas (seguridad): vista para mostrar solo los datos esenciales
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 permite insertar alumnos de Barcelona o Madrid, y sin numHermanos
-- 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 con Table_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 último COMMIT que se haya ejecutado.
La vida de una transacción, de START TRANSACTION a COMMIT o ROLLBACK

Recorre el diagrama paso a paso.

Gestión transaccional: ejemplo MySQL
-- 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 último COMMIT.
  • SAVEPOINT nombre — crea un punto de control dentro de la transacción.
  • ROLLBACK TO nombre — deshace hasta el último punto de control o SAVEPOINT.

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.

Transferimos 300 € de la cuenta 1 a la 2. ¿Y si el servidor cae justo entre los dos UPDATE?
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
Resultado de la consulta
cuentaantestras COMMIT
11000700
2500800
total15001500

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 desde START 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.
autocommit está activado por defecto: cada sentencia suelta es una transacción y se confirma sola. START TRANSACTION lo suspende hasta el COMMIT o ROLLBACK
SELECT @@autocommit;   -- 1
SET autocommit = 0;    -- hasta COMMIT

4 · 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

Los bloqueos: dos tipos y dos niveles

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 con SELECT, 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.
Shared lock (lectura)
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.
Exclusive lock (escritura)
LOCK TABLES CLIENTE WRITE;
INSERT INTO CLIENTE(Id) VALUES(666);
SELECT * FROM CLIENTE;
UNLOCK TABLES;

Problemas de no bloquear correctamente

Los 3 escenarios, con el ejemplo de la diapositiva

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 hace ROLLBACK, habrás leído datos que nunca existieron realmente. Ejemplo: A lee saldo = 500 € modificado por B; B hace ROLLBACK; 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 hace COMMIT; A vuelve a leer y obtiene 15 €.
  • Phantom Read (lectura fantasma) — repites una consulta (por ejemplo, un SELECT con 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 hace COMMIT; 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).

Usuario root · bloqueo shared lock: LOCK TABLES Alumno READ
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);
Usuario root · bloqueo exclusive: LOCK TABLES Alumno WRITE
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.

Bloqueo de tabla: LOCK TABLES, de tipo READ o WRITE
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.
Bloqueo de fila: FOR UPDATE
START TRANSACTION;
SELECT saldo FROM Cuenta
WHERE id = 1 FOR UPDATE;   -- bloquea
UPDATE Cuenta SET saldo = saldo - 300
WHERE id = 1;
COMMIT;                    -- libera

Practica

Pregunta 1 de 12 0 aciertos

¿Qué guarda una vista?

Fuente: NF2 Lenguaje SQL · pág. 19–26 · pág. 59–62