Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Unidad 5:
Transacciones en SQL:
Conjunto de instrucciones SQL, agrupadas lógicamente,que o bien se ejecutan todas sobre
la base de datos o bien no se ejecuta ninguna.

Principio ACID:
● ATÓMICA (ATOMIC): Todas las operaciones que componen la transacción deben
aplicarse sobre la base de datos o no aplicarse ninguna.
● CONSISTENTE (CONSISTENT): Se mantiene la consistencia de los datos antes y
después de ejecutar la transacción.
● AISLADA (ISOLATED): Cuando muchas transacciones se pueden llevar a cabo
simultáneamente por uno o varios usuarios, la realización de una no debe afectar
jamás a las otras y por tanto no llevar a una situación de error.
● PERMANENTE (DURABLE): Una vez que la transacción ha sido confirmada
(COMMIT) y por tanto los cambios de la base de datos guardados, éstos deben
perdurar en el tiempo aun cuando el gestor de base de datos o el propio equipo
fallen.

Diferencia de usar MyISAM o InnoDB:

La capacidad transaccional de MySQL depende del sistema de almacenamiento empleado.
Los dos más importantes son el MyISAM y el InnoDB:
● MyISAM: No admite transacciones. Utilizada para aplicaciones que en su mayoría
son de sólo lectura.
● InnoDB: Es el modelo de almacenamiento de transacción segura de MySQL más
popular.

Tipos de Transacciones:
En los gestores de datos relacionales modernos disponemos de tres tipos de transacciones
según la forma de iniciarlas:
● De confirmación automática: el gestor de datos inicia una transacción
automáticamente por cada operación que actualice datos. De este modo mantiene
siempre la consistencia de la base de datos, aunque puede generar bloqueos.
● Implícitas: el gestor de datos comienza una transacción automáticamente cada vez
que se produce una actualización de datos, pero el que dicha transacción se
confirme o se deshaga, lo debe indicar el programador.
● Explícitas: son las que iniciamos nosotros "a mano" mediante instrucciones SQL.
somos nosotros, los programadores, los que indicamos qué operaciones va a
abarcar.

Niveles de Aislamiento en MySQL:

Los niveles de aislamiento que nos ofrece MySQL son los siguientes:
● Secuenciable (SERIALIZABLE): Mayor nivel de aislamiento. Las transacciones se
aíslan completamente dando la impresión de que se ejecutan secuencialmente, una
detrás de otra. Lectura repetible (REPEATABLE READ):Es el nivel por defecto. Las
lecturas repetidas de la misma fila por la misma transacción dan los mismos
resultados.
● Lectura confirmada (READ COMMITED): Sólo se permite lectura de datos que han
sido confirmados.
 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

● Lectura no confirmada (READ UNCOMMITTED): También conocido como lectura

sucia. Es el nivel más bajo. Permite que una transacción pueda leer filas que todavía
no han sido confirmadas (COMMIT).

START TRANSACTION, COMMIT y ROLLBACK:

Estas declaraciones proporcionan control sobre el uso de transacciones:
● START TRANSACTION o BEGIN: Comenzar una nueva transacción.
● COMMIT: Confirma la transacción actual, haciendo que sus cambios sean
permanentes.
● ROLLBACK: Revierte la transacción actual, cancelando sus cambios.
● SET autocommit: deshabilita o habilita el modo de confirmación automática
predeterminado para la sesión actual.

WITH CONSISTENT SNAPSHOT:

El modificador WITH CONSISTENT SNAPSHOT inicia una lectura consistente para los
motores de almacenamiento que son capaces de hacerlo. Esto se aplica solo a InnoDB. El
efecto es el mismo que emitir un START TRANSACTION seguido de un SELECT desde
cualquier tabla InnoDB.

Autocommit:
Es una variable de sesión y debe establecerse para cada sesión. Puede deshabilitar el
modo de confirmación automática para cada nueva conexión.

BEGIN /BEGIN WORK:

● BEGIN y BEGIN WORK son compatibles como alias de START TRANSACTION
para iniciar una transacción. START TRANSACTION es la sintaxis SQL estándar, es
la forma recomendada de iniciar una transacción ad-hoc y permite modificadores
que BEGIN no lo hacen.

WORK /CHAIN y RELEASE:

● La palabra clave opcional WORK es compatible con COMMIT y ROLLBACK, al igual
que las cláusulas CHAIN y RELEASE. CHAIN y RELEASE puede usarse para un
control adicional sobre la finalización de la transacción.
● La cláusula AND CHAIN hace que una nueva transacción comience tan pronto como
finalice la actual, y la nueva transacción tiene el mismo nivel de aislamiento que la
transacción que acaba de finalizar.

Transacciones y bloqueos:
Las situaciones en que se producen estos bloqueos son:
● Sentencia UPDATE: Las filas afectadas se bloquean hasta que se confirme o
deshaga la transacción.
● Sentencia INSERT: Si existe clave primaria, las filas insertadas quedan bloqueadas
para prevenir que otra transacción pueda introducir otra fila con la misma clave.
● Sentencia LOCK TABLES: Bloquea la tabla entera. No es muy eficiente pues reduce
la concurrencia.
Las situaciones en que se producen estos bloqueos son:
● Sentencia UPDATE: Las filas afectadas se bloquean hasta que se confirme o
deshaga la transacción.
 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

● Sentencia INSERT: Si existe clave primaria, las filas insertadas quedan bloqueadas
para prevenir que otra transacción pueda introducir otra fila con la misma clave.
● Sentencia LOCK TABLES: Bloquea la tabla entera. No es muy eficiente pues reduce
la concurrencia.
● Si dentro de una sentencia SELECT se utilizan las clausulas FOR UPDATE o LOCK
IN SHARE MODE, todas las filas devueltas por la sentencia SELECT serán
bloqueadas.

Deadlock:
Esta situación ocurre cuando una transacción A intenta modificar los datos que están siendo
modificados por una transacción B y a su vez esta última intenta modificar los datos que
están siendo modificados por la primera transacción A.

Estrategias de bloqueo:
● Estrategia de bloqueo pesimista: El planteamiento para este caso es que las
transacciones concurrentes son muy fácil de que ocurran y por tanto hay que estar
prevenido.
● Estrategia de bloqueo optimista: Asume que es muy poco probable que el valor de
una fila que acabamos de leer cambie.

YouTube Link: ✨ La magia de las transacciones SQL | Ejemplo en Sql Server

 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Transacción:
Una transacción es una unidad atómica de trabajo que se completa en su totalidad o no se
lleva a cabo en absoluto. Para fines de recuperación, el sistema debe hacer el seguimiento
de cuándo se inicia, termina y confirma o aborta una transacción.

Clasificación un sistema de bases de datos es el número de usuarios:

● Un criterio que sirve para clasificar un sistema de bases de datos es el número de
usuarios que lo pueden utilizar al mismo tiempo.
● Un DBMS es monousuario si sólo lo puede utilizar un usuario a la vez (computadora
personal), y es multiusuario si varios usuarios pueden utilizar el sistema (y, por tanto,
acceder a la base de datos) simultáneamente.

Sistema multiusuario:
En un sistema multiusuario la base de datos pueden acceder simultáneamente múltiples
usuarios (y utilizar los computadores) debido al concepto de multiprogramación, que permite
al computador ejecutar varios programas (o procesos) al mismo tiempo.
● Interpolación:
○ La interpolación mantiene ocupada la CPU cuando un proceso requiere una
operación de entrada o salida (E/S), como la lectura de un bloque del disco.
○ La CPU pasa a ejecutar otro proceso en lugar de permanecer ociosa durante
el tiempo de E/S. La interpolación también evita que un proceso largo retrase
otros procesos.
● Procesamiento paralelo:
○ Si el computador tiene varios procesadores (CPUs), es posible el
procesamiento paralelo de varios procesos.
○ La concurrencia en las bases de datos está desarrollada en términos de
concurrencia interpolada.

Transacciones, operaciones de lectura y escritura y búferes DBMS:

Una transacción es un programa en ejecución que constituye una unidad lógica del
procesamiento de una base de datos. Una transacción incluye una o más operaciones de
acceso a la base de datos (operaciones de inserción, eliminación, modificación o
recuperación).

Transacciones, operaciones de lectura y escritura y búferes DBMS:

Una forma de definir los límites de una transacción es especificando explícitamente las
sentencias begin transaction y end transaction en una aplicación.

Granularidad:
Una base de datos está representada básicamente como una colección de elementos de
datos con nombre. El tamaño de un elemento de datos se conoce como granularidad.

Puede ser un campo de algún registro de la base de datos, o puede ser una unidad más
grande, como un registro o incluso un bloque de disco entero.
 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Operaciones de acceso a la Base de Datos:

Las operaciones básicas de acceso a la base de datos que una transacción puede incluir
son las siguientes:
read_item (X) (leer elemento):
● Lee un elemento de base de datos denominado X y lo almacena en una variable de
programa.
● La ejecución de un comando write_item (X) abarca estos pasos:
○ Encontrar la dirección del bloque de disco que contiene el elemento X.
○ Copiar ese bloque de disco en un búfer de la memoria principal (si dicho
bloque no se encuentra ya en algún búfer de la memoria principal).
○ Copiar el elemento X desde la variable de programa X a su ubicación
correcta en el búfer.
○ Almacenar el bloque actualizado desde el búfer al disco (puede ser
inmediatamente o en un momento posterior en el tiempo).
write_item (X) (escribir elemento):
● Escribe el valor de la variable de programa X en un elemento de base de datos
denominado X.
● La ejecución de un comando read_item (X) incluye estos pasos:
○ Encontrar la dirección del bloque de disco que contiene el elemento X.
○ Copiar ese bloque de disco en un búfer de la memoria principal (si dicho
bloque no se encuentra ya en algún búfer de la memoria principal).
○ Copiar el elemento X desde el búfer a la variable de programa X.

Tipos de fallos:
● Un fallo del computador: Durante la ejecución de una transacción se produce un
error del hardware, del software o de la red.
● Un error de la transacción o del sistema: Alguna operación de la transacción puede
provocar que falle, como un desbordamiento de entero o una división por cero.
● Errores locales o condiciones de excepción detectados por la transacción: Durante la
ejecución de una transacción, se pueden dar ciertas condiciones que necesitan
cancelar la transacción.
● Control de la concurrencia: El método de control de la concurrencia puede optar por
abortar la transacción, para restablecerla más tarde, porque viola la serialización o
porque varias transacciones se encuentran en estado de bloqueo.
● Fallo del disco: Algunos bloques del disco pueden perder sus datos debido a un mal
funcionamiento de la lectura o la escritura o porque se ha caído la cabeza de
lectura/escritura del disco.
● Problemas físicos y catástrofes: Se refiere a una lista interminable de problemas que
incluye fallos de alimentación o aire acondicionado, fuego, robo, sabotaje,
sobrescritura de discos y cintas por error, y montaje de la cinta errónea por parte del
operador.

Operaciones del Gestor de Recuperación:

● BEGIN_TRANSACTION: Marca el inicio de la ejecución de una transacción.
● READ o WRITE: Especifican operaciones de lectura o escritura en los elementos de
la base de datos que se ejecutan como parte de una transacción.
● END_TRANSACTION: Especifica que las operaciones READ y WRITE de la
transacción han terminado y marca el final de la ejecución de la transacción.
 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

● COMMIT_TRANSACTION: Señala una finalización satisfactoria de la transacción,

por lo que los cambios (actualizaciones) ejecutados por la transacción se pueden
enviar con seguridad a la base de datos y no se desharán.
● ROLLBACK (o ABORT): Señala que la transacción no ha terminado
satisfactoriamente, por lo que deben deshacerse los cambios o efectos que la
transacción pudiera haber aplicado a la base de datos.

El registro del sistema (log):

Para poder recuperarse de los fallos que afectan a las transacciones, el sistema mantiene
un registro para hacer un seguimiento de todas las operaciones de las transacciones que
afectan a los valores de los elementos de una base de datos. Este registro se guarda en el
disco, por lo que no se ve afectado por ningún tipo de fallo, excepto los de disco o
catastróficos.

Listado de los tipos de entradas en el log:

Se listan los tipos de entradas que se realizan en el log y las acciones que cada una
conlleva. En estas entradas, T se refiere a un id de transacción único que el sistema genera
automáticamente y que utiliza para identificar las transacciones:
● [start_transaction, T] Indica que se ha iniciado la ejecución de la transacción T.
● [write_item, T , X , valor_antiguo , valor_nuevo] Indica que la transacción T ha
cambiado el valor del elemento X de valor_antiguo a valor_nuevo.
● [read_item, T , X ] Indica que la transacción T ha leído el valor del elemento X de la
base de datos.
● [commit, T ] Indica que la transacción T se ha completado satisfactoriamente, y
afirma que su efecto puede confirmarse (grabarse permanentemente) en la base de
datos.
● [abort, T ] Indica que la transacción T se abortó.

Propiedades deseables de las transacciones:

● Atomicidad: Una transacción es una unidad atómica de procesamiento; o se ejecuta
en su totalidad o no se ejecuta en absoluto.
● Conservación de la consistencia: Una transacción está conservando la consistencia
si su ejecución completa lleva a la base de datos de un estado consistente a otro.
● Aislamiento: Una transacción debe aparecer como si estuviera ejecutándose de
forma aislada a las demás. Es decir, la ejecución de una transacción no debe
interferir con la ejecución de ninguna otra transacción simultánea.
● Durabilidad. Los cambios aplicados a la base de datos por una transacción
confirmada deben persistir en la base de datos.

Planificaciones de transacciones:
Una planificación S de n transacciones T1, T2, ..., Tn es una ordenación de las operaciones
de esas transacciones sujeta a la restricción de que, por cada transacción Ti que participa
en S, las operaciones de Ti en S deben aparecer en el mismo orden en el que tienen lugar
en Ti.
 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Planificaciones de transacciones:
Dos operaciones de una planificación entran en conflicto si satisfacen estas tres
condiciones:
● Pertenecen a transacciones diferentes.
● Acceden al mismo elemento X.
● Al menos una de las operaciones es write_item( X ).

Planificaciones de transacciones:
Se dice que una planificación S de n transacciones T1, T2, ..., Tn es una planificación
completa si se dan las siguientes condiciones:
● Las operaciones en S son exactamente las operaciones en T1, T2, ..., Tn,
incluyendo una operación commit o abort como última operación de cada
transacción de la planificación.
● Para cualquier par de operaciones de la misma transacción Ti , su orden de
aparición en S es el mismo que su orden de aparición en Ti.
● Para cualesquiera dos operaciones en conflicto, una de las dos debe producirse
antes que la otra en la planificación.

Planificación serializable:
Una planificación S de n transacciones es serializable si es equivalente a alguna
planificación en serie de las mismas n transacciones.

Clasificación de Planificaciones serializables:

● Equivalentes por conflicto.
● Equivalencia por vista.

YouTube Link: Curso Bases de Datos en MySQL - 15: Transacciones

 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Unidad 6:
Funciones SQL:
Una función en MySQL es una rutina creada para tomar unos parámetros, procesarlos y
retornar en una salida.
Normalmente, utiliza funciones almacenadas para encapsular fórmulas comunes o reglas
comerciales que son reutilizables entre sentencias SQL o programas almacenados.

Diferencia con los procedimientos almacenados:

Se diferencian de los procedimientos almacenados en las siguientes características:
● Solamente pueden tener parámetros de entrada IN y no parámetros de salida OUT o
INOUT.
● Deben retornar en un valor con algún tipo de dato definido.
● Pueden usarse en el contexto de una sentencia SQL.
● Solo retornan un valor individual, no un conjunto de registros.

Cómo definir una función SQL:

Debes usar la sentencia CREATE FUNCTION. La sintaxis para crear una función es casi
idéntica a la de crear un procedimiento, veamos:
CREATE FUNCTION nombre_función (parametro1,parametro2,...)
RETURNS tipoDato
[atributos de la rutina]
<bloque de instruccciones>

Cómo actualizar una función:

Para actualizar una función usamos el comando ALTER FUNCTION. Con esta sentencia
podemos cambiar los atributos de la función, pero no podremos cambiar el cuerpo. Sintaxis:
ALTER FUNCTION nombre_funcion
[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT descripción ]

Cómo borrar una función:

Usando el comando DROP FUNCTION. Simplemente especificamos el nombre de la
función y esta se borrará de la base de datos. Su sintaxis está definida de la siguiente
forma:
DROP FUNCTION nombre_funcion

YouTube Link: Tutorial 15 de SqlServer - Crear Funciones (FUNCTION)

 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Unidad 7:
Procedimientos almacenados:
Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse
en el servidor. Una vez que se hace, los clientes no necesitan relanzar los comandos
individuales pero pueden en su lugar referirse al procedimiento almacenado.

¿Cuándo es adecuado usar un procedimiento almacenado?

● Si varias aplicaciones necesitan un mismo programa de base de datos, este último
se puede almacenar en el servidor e invocarlo desde esas aplicaciones.
● Reducir la duplicidad del esfuerzo y mejorar la modularidad del software.
● Cuando la seguridad es muy importante.

Sintaxis general:
Un procedimiento almacenado puede estar compuesto por comandos SQL sencillos, como
recuperaciones y actualizaciones. La forma general para declarar un procedimiento
almacenado es la siguiente:
CREATE PROCEDURE <nombre del procedimiento>
(<parámetros>)
<declaraciones locales>
<cuerpo del procedimiento> ;

Procedimientos almacenados en Mysql:

Sintaxis para crear un procedimiento almacenado:
La lista de parámetros entre paréntesis debe estar siempre presente. Si no hay parámetros,
se debe usar una lista de parámetros vacía () . Cada parámetro es un parámetro IN por
defecto. Para especificar otro tipo de parámetro, use la palabra clave OUT o INOUT antes
del nombre del parámetro.

Eliminar un procedimiento almacenado:

Sintaxis:
DROP PROCEDURE [IF EXISTS] nombre_procedimiento
La cláusula IF EXISTS es una extensión de MySQL .
Evita que ocurra un error si el procedimiento no existe.

Sentencia call:
Teniendo el siguiente procedimiento almacenado:
CREATE PROCEDURE `contar_productos` (IN codigo varchar(5), OUT nproductos
INT)
BEGIN
SELECT COUNT(*) INTO nproductos
FROM productos
WHERE cprincipal=codigo;
END
 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Para saber si la ejecución del procedimiento funciona se debe hacer la llamada al mismo
de la siguiente manera:
CALL contar_productos('001', @cantidad);
select @cantidad;

SHOW CREATE PROCEDURE

Este comando es una extensión de MySQL:
Similar a SHOW CREATE TABLE, retorna la cadena exacta que puede usarse para recrear
la rutina nombrada. Sintaxis:
SHOW CREATE {PROCEDURE |
FUNCTION} sp_name

SHOW PROCEDURE STATUS

Este comando es una extensión de MySQL . Retorna características de rutinas, como el
nombre de la base de datos, nombre, tipo, creador y fechas de creación y modificación. Si
no se especifica un patrón, le lista la información para todos los procedimientos
almacenados, en función del comando que use.

Sentencia BEGIN ... END

La sintaxis BEGIN ... END se utiliza para escribir sentencias compuestas que pueden
aparecer en el interior de procedimientos almacenados y triggers. Una sentencia compuesta
puede contener múltiples sentencias, encerradas por las palabras BEGIN y END.
lista_sentencias es una lista de una o más sentencias.

Variables en procedimientos almacenados:

Declarar variables locales con DECLARE:
Este comando se usa para declarar variables locales. Para proporcionar un valor por
defecto para la variable, incluya una cláusula DEFAULT . El valor puede especificarse como
expresión, no necesita ser una constante. Si la cláusula DEFAULT no está presente, el valor
inicial es NULL.

Sentencia SET para variables:

El comando SET en procedimientos almacenados es una versión extendida del comando
general SET. Las variables referenciadas pueden ser las declaradas dentro de una rutina, o
variables de servidor globales.

YouTube Link: Curso SQL. Procedimientos almacenados I. Vídeo 23

 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Unidad 8:
Triggers:
Un Trigger es un programa almacenado(stored program), creado para ejecutarse
automáticamente cuando ocurra un evento en nuestra base de datos. Dichos eventos son
generados por los comandos INSERT, UPDATE y DELETE, los cuales hacen parte del
DML(Data Modeling Languaje) de SQL.
Invocaremos nuestros Triggers para ejecutar un bloque de instrucciones que proteja,
restrinja o preparen la información de nuestras tablas, al momento de manipular nuestra
información.

Crear un Trigger:
La sintaxis es similar a la creación de Procedimientos y Funciones:
CREATE [DEFINER={usuario|CURRENT_USER}]
TRIGGER nombre_del_trigger {BEFORE|AFTER} {UPDATE|INSERT|
DELETE} ON nombre_de_la_tabla
FOR EACH ROW
<bloque_de_instrucciones>

● DEFINER={usuario|CURRENT_USER} Indica al gestor de bases de datos qué

usuario tiene privilegios en su cuenta, para a invocación de los triggers cuando
surjan los eventos DML.
● nombre_del_trigger: Indica el nombre de nuestro trigger.
● BEFORE|AFTER: Especifica si el Trigger se ejecuta antes o después del evento
DML.
● UPDATE|INSERT|DELETE: Aquí eliges que sentencia usarás para que se ejecute el
Trigger.
● ON nombre_de_la_tabla: En esta sección estableces el nombre de la tabla asociada.
● FOR EACH ROW: Establece que el Trigger se ejecute por cada fila en la tabla
asociada.
● <bloque_de_instrucciones>: Define el bloque de sentencias que el Trigger ejecutará
al ser invocado.

Identificadores NEW y OLD en Triggers:

Si queremos relacionar el trigger con columnas especificas de una tabla debemos usar los
identificadores OLD y NEW.
OLD indica el valor antiguo de la columna y NEW el valor nuevo que pudiese tomar.
Por ejemplo: OLD.idproducto ó NEW.idproducto.

¿Qué utilidades tienen los Triggers?

Con los Triggers podemos implementar varios casos de uso que mantengan la integridad de
la base de datos, como Validar información, Calcular atributos derivados, Seguimientos de
movimientos en la base de datos, etc.

Ver la información de un Trigger:

Usar el comando SHOW CREATE TRIGGER y rápidamente estarás viéndolas
especificaciones de tu Trigger creado.
 Resumen Parcial 2 - Base De Datos II - Birchner, Rodrigo

Eliminar un Trigger:
El comando para eliminar casi cualquier cosa en nuestra base de datos:
DROP TRIGGER [IF EXISTS] nombre_trigger

YouTube Link: Curso SQL. Triggers I (Disparadores). Vídeo 20