Mod BD UD6 Tratamiento Datos

Introducción

• Todos los ejemplos y ejercicios propuestos están basados en una base de datos de nombre CIRCO probada sobre un gestor MYSQL versión 5.7 o superior:

• Fichero .sql: Archivo:CIRCO.zip

• SQL (Structured Quere Language) es un lenguaje completo de manipulación de datos que se utiliza no solamente para consultas, sino también para modificar y actualizar los datos de la base de datos. En comparación con la 'complejidad' que puede ter la sentencia SELECT, este tipo de sentencias son más sencillas.

Las tres sentencias SQL que se emplean para modificar los contenidos de una base de datos son:

• INSERT, que añade nuevas filas de datos a una tabla,
• DELETE, que elimina filas de datos de una tabla, y
• UPDATE, que modifica datos existentes en la base de datos

• El SGBD debe proteger la integridad de los datos almacenados durante los cambios, asegurándose que sólo se introduzcan datos válidos y que la base de datos permanezca autoconsistente, incluso en caso de fallos del sistema o errores a la hora de introducir los datos. Uno de estos mecanismos será el uso de transacciones, que veremos posteriormente en esta unidad.

• El SGBD debe coordinar también las actualizaciones simultáneas por parte de múltiples usuarios, asegurándose que los usuarios y sus modificaciones no interfieran unos con otros empleando un mecanismo de bloqueo de registros que también veremos posteriormente.

Añadir/actualizar/borrar datos

Sentencia INSERT

• Más información:

• Sintaxis para SQLSERVER: https://docs.microsoft.com/es-es/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15
• Sintaxis para Mysql: https://dev.mysql.com/doc/refman/8.0/en/insert.html

• Añade una nueva fila a una tabla.

La sintaxis de Mysql es bastante más compleja de leer que si solamente nos fijamos en los aspectos más básicos.

La sintaxis básica seria la siguiente:

Orden: Introduce en la tabla ARTISTAS una nueva fila de valores.

• Para realizar esta inserción llegaría la siguiente orden SQL:

SQL:

INSERT INTO ARTISTAS (nif, apellidos, nombre, nif_jefe)
VALUES ('55555555E', 'Rodriguez', 'Sam', NULL);

• Veamos algunos aspectos a destacar:

• Primero: Los datos de tipo carácter (normalmente van incluidos los datos de tipo fecha), van entre comillas (que pueden ser dobles o simples dependiendo del SGBD utilizado).
• Segundo: En el caso de que se inserten valores en todas las columna de la tabla, no es necesario indicar el nombre de las mismas en la sentencia INSERT. Es decir, la orden anterior es equivalente a:

SQL:

INSERT INTO ARTISTAS
VALUES ('55555555E', 'Rodriguez', 'Sam', NULL)

Supongamos que no sabemos el nombre y apellidos del artista. En este caso sólo estaríamos insertando el nif del artista.

Valdría la siguiente orden:

Nota: Dará un error ya que la tabla ARTISTAS no acepta valores nulos para los campos Apellidos y Nombre, pero la sintaxis es correcta.

SQL:

INSERT INTO ARTISTAS (nif)
VALUES ('55555555E');

El resto de columnas para esa fila (todas menos el nif) van a tener un valor NULL (desconocido) o un valor por defecto se este está establecido en la definición de la tabla. Lógicamente si alguna de ellas no acepta el valor null o no tiene valor por defecto establecido, la orden anterior dará un error.

• Tercera: Es posible insertar para una columna determinada un valor desconocido, poniendo la constante NULL en vez de un valor.

Siguiendo nuestro ejemplo anterior, las siguientes sentencias SQL son equivalentes.

Nota: Dará un error ya que la tabla ARTISTAS no acepta valores nulos para los campos Apellidos y Nombre, pero la sintaxis es correcta.

SQL:

INSERT INTO ARTISTAS
VALUES ('75555555E',null,null,null);

SQL:

INSERT INTO ARTISTAS (nif)
VALUES ('55555555E')

Transacciones

Introducción

Veamos un ejemplo para entender qué es y para qué sirve una transacción.

Supongamos que estamos trabajando con la siguiente base de datos basada en el modelo relacional que se muestra a continuación. Cuando damos de alta a un nuevo participante, añadimos entre los datos, su nombre, dirección, teléfono, tipo (árbitro o jugador) y el país al que pertenece.

Para nosotros, una transacción va a ser una tarea atómica e indivisible. Quiero esto decir, que se va a ejecutar de forma completa o no se ejecuta. Cada transacción puede estar conformada por una o más operaciones sobre la base de datos.

En el ejemplo anterior, dar de alta va a suponer añadir una nueva fila a la tabla PARTICIPANTE, pero también hay que darlo de alta en la tabla JUGADOR / ARBITRO según el tipo al que pertenezca.

Imaginemos que queremos dar de alta al siguiente jugador:

'Pedro Guiti', que vive en C/ De la Tierra Nº 1, con teléfono 981212121, es un jugador que lo envía España y tiene un nivel de 5.

Este llevaría consigo dos operaciones de INSERT, Una sobre la tabla PARTICIPANTE y otra sobre la tabla JUGADOR.

¿ Pero qué pasaría si falla el segundo INSERT o el sistema se cae ? Pues que tendríamos un dato añadido a la primera tabla (PARTICIPANTE), pero ninguno a la tabla de JUGADOR, por lo que la base de datos quedaría en un estado inconsistente.

Para solucionar este problema nacen las transacciones. Con una transacción nos aseguraremos que o bien se hace todo el conjunto de operaciones o no se hace nada.

Nota: Recordar que las tablas en Mysql están creadas haciendo uso de un motor de base de datos. Dependiendo del motor, este tendrá soporte para usar transacciones. InnoDB tiene soporte. Podéis consultar la lista de motores y sus características en este enlace: https://wiki.cifprodolfoucha.es/index.php?title=Mysql_Motores_de_bases_de_datos

Creación de una transacción

Los pasos para hacer uso de una transacción siempre son los mismos en cualquier gestor de bases de datos relacional:

• Iniciar la transacción (a partir de este punto todas las operaciones que se hagan sobre la base de datos estarán dentro de la transacción)
• Si todo va bien, confirmar la transacción.
• Si hubo algún error, deshacer la transacción y dejar la base de datos como estaba antes de iniciar la transacción.

Vamos a ver como se implementan estos tres pasos en MYSQL. INICIAR TRANSACCIÓN: Para ello podemos utilizar una de las tres formas siguientes (la opción en negrilla es la forma más habitual en otros gestores):

• SET AUTOCOMMIT = 0;
• START TRANSACTION;
• BEGIN WORK;

CONFIRMAR TRANSACCIÓN: Para confirmar la transacción, es decir, para que todas las operaciones que se hayan hecho desde el comienzo de la transacción se acepten, debemos de poner: COMMIT [WORK]; (recordar que [ ] significa optativo, por lo tanto podemos poner COMMIT o COMMIT WORK)

DESHACER TRANSACCIÓN: En caso de que comprobemos que haya habido algún error, podremos deshacer los cambios que se produjeran en la base de datos desde el comienzo de la transacción con la orden: ROLLBACK [WORK];

Lo que es importante tener claro es que desde que marcamos el inicio de una transacción, todas las operaciones (INSERT, UPDATE, DELETE) sobre la base de datos no se harán efectivas hasta encontrar la orden COMMIT.

Nota: Mysql, por defecto, tiene configurada la opción AUTOCOMMIT a true (valor 1 u ON). Esto quiere decir que cualquier instrucción individual es tratada como una transacción y, o bien se ejecuta correctamente o si aparece cualquier error, se deshacen todas las modificaciones. Por ejemplo: DELETE FROM ARTISTAS;

Esta instrucción borra todas las filas de la tabla ARTISTAS. Si alguna de ellas provoca un error, se dejará la tabla en su estado original.

Si ponemos dos transacciones una a continuación de otra, la segunda hará implícitamente un COMMIT de la primera:

1 START TRANSACTION
2 
3 -- Operación de delete
4 
5 START TRASACTION		-- Implica un COMMIT de la anterior

Niveles de aislamiento

• Como comenté anteriormente, una de las características que tienen que tener las transacciones, es impedir que un usuario pueda acceder a información que está siendo modificada por otro usuario y que todavía no está confirmada (lecturas sucias).

I (Isolation en inglés) Aislamiento, el gestor de la base de datos debe aislar los datos 'sucios'

para evitar que otros usuarios usen información no confirmada o validada.

El nivel de aislamiento va a ser la forma que nos proporcionan los gestores de bases de datos de determinar como el resto de usuarios tienen acceso a información que está siendo modificada por una transacción en curso (como veis está relacionado con la concurrencia).

• En Mysql disponemos de los siguientes modos de aislamiento:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

En Mysql el modo por defecto es REPETEABLE READ y en SQLSERVERE es REPEATABLE READ.

Normalmente este debe ser el modo que se debe mantener.

Cada nivel de aislamiento va a solucionar un tipo de problema (menos el primero que no soluciona nada).

Cada nivel superior solucionará los problemas de los niveles anteriores más el suyo propio.

Hay que tener cuidado con el nivel de aislamiento seleccionado ya que cuanto más alto es el nivel más cantidad de recursos bloquea afectando al tiempo de respuesta del resto de usuarios.

En Mysql podemos cambiar el nivel de aislamiento con la orden: SET TRASACTION ISOLATION LEVEL modo_de_aislamiento;

Podéis encontrar la sintaxis completa en esta página: https://dev.mysql.com/doc/refman/8.0/en/set�transaction.html

• Voy a explicar en qué consiste cada uno de los niveles de aislamiento….

READ UNCOMMITTED

• Lecturas no confirmadas, nivel más bajo de aislamiento solamente protege de lecturas de datos físicamente dañados. Es el que produce mayor nivel de concurrencia (nunca hay un bloqueo), pero también el que no garantiza en absoluto la coherencia de los datos.

Este nivel de aislamiento no soluciona el problema de lecturas sucias, que son datos que se leen y que están siendo modificados por una transacción sin confirmar.

Veamos un ejemplo aplicado a nuestra base de datos CIRCO.

READ COMMITED

• Lecturas confirmadas. En SQLSERVER es el nivel de aislamiento por defecto. Es este nivel de aislamiento, el problema anterior no tendría lugar.

Este nivel de aislamiento soluciona el problema anterior, el de lecturas sucias, que son datos que se leen y que están siendo modificados por una transacción sin confirmar.

Aquí el comportamiento es diferente según el gestor.

• En MYSQL el usuario2 lee el mismo dato las dos veces. El dato que lee es el dato sin modificar por el usuario1 (ya que no fue confirmado el cambio cuando el usuario2 lee el dato por primera vez). Lo que hace MYSQL es crear un SNAPSHOT de los datos leídos y trabaja sobre esa copia durante toda la transacción del usuario2.
• En SQLSERVER-ORACLE, el funcionamiento es diferente y el usuario2 se queda bloqueado hasta que el usuario1 termine la transacción.

• Este nivel de aislamiento no soluciona el problema de lecturas no repitibles (repeatable read)

Este problema se da cuando un usuario, dentro de una transacción, lee un dato y antes de que vuelva a leer el mismo dato, otro usuario ha realizado una modificación sobre el mismo dato. Al volver a leer el usuario1 ese dato tendrá un valor diferente.

REPEATABLE READ

• Lecturas repetibles. En MYSQL es el nivel de aislamiento por defecto.

Este nivel de aislamiento soluciona el problema de lecturas no repitibles (repeatable read)

Aquí el comportamiento es diferente según el gestor.

• En MYSQL el usuario2 puede modificar el dato que está leyendo el usuario1, pero el usuario1 no leerá el dato modificado. Al igual que en el caso anterior, Mysql crea un SNAPSHOT cuando el usuario1 lee el dato. Cuando vuelve a leer el dato este sigue valiendo lo mismo a pesar de que fuera modificado por el usuario2.
• En SQLSERVER-ORACLE, el funcionamiento es diferente y el usuario2 se queda bloqueado hasta que el usuario1 termine la transacción.

• Este nivel de aislamiento no soluciona el problema de datos fantasma (phantom read)

Este problema se da cuando un usuario, dentro de una transacción, lee un dato y ese dato no existe. Otro usuario añade ese dato y el usuario1 al volver a leer el dato lo encuentra (lo mismo con la operación de borrado, que sería que un usuario busca un dato y lo encuentra, otro usuario borra ese datos, y el usuario1 al volver a buscarlo ya no está, dentro de la misma transacción).

• En MYSQL los datos añadidos no aparecen por el comportamiento de crear SNAPSHOT de MYSQL. Hay un 'truco' que consiste en modificar el dato de la fila añadida.

SERIALIZABLE

Este nivel de aislamiento soluciona el problema de datos fantasma (phantom read)

• Lo que hace el gestor es bloquear todos los recursos empleados por la transacción.

Puedo dar lugar a un problema denominado DeadLock o abrazo mortal que se produce cuando dos transacciones están esperando por recursos que están siendo bloqueados por ellas.

Enlace a la página principal del curso

-- Ángel D. Fernández González -- (2020).