unidad 4 concurrencia
TRANSCRIPT
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 1
Unidad 4
Concurrencia
4.1 Conceptos, propiedades de las transacciones y COMMIT.
4.2 ROLLBACK.
4.3 Niveles de aislamiento.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 2
Cada vez que se ejecuta INSERT, UPDATE,
DELETE, los datos se escriben a las tablas de
la Base de Datos inmediatamente.
Se dice que se entregan los datos (commit).
4.1 Propiedades de las transacciones
Al español algunos la traducen
incorrectamente como comprometer
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 3
Este proceso de entrega automática, es
conveniente en unos casos pero en otros
no.
Analizaremos algunas situaciones para
comprobar que el automatic commit no
siempre produce buenos resultados.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 4
Ejemplo A11. Crear una base de datos llamada LaConsentida
en MySQL o SQLServer.
create database LaConsentida
use LaConsentida
2. Crear una tabla:
create table CtasBanc
(idCuenta int auto_increment / identity primary key,
tipo char(10),
saldo numeric(10,2))
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 5
3. Añadir 2 tuplas:
insert into CtasBanc values (0,’Chequera’,1000)
insert into CtasBanc values (0,’Inversión’,9000)
Recuerde: en SQL Server se omite el cero.
4. Consulte el contenido de la tabla:
select * from CtasBanc
La miscelánea “La Consentida” posee doscuentas bancarias, en la primera hay $1,000.00y en la segunda $9,000.00.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 6
En este momento la BD está en un estadoconsistente porque el dueño de la misceláneaposee $10,000, una parte en la chequera y otra enla inversión, pero 10,000 en total mientras no retirede ninguna de las cuentas o haga un depósito auna de ellas.
El dueño de la miscelánea podrá hacer traspasosentre ambas cuentas, pero el dinero que posee entotal siempre será $10,000.00 (mientras no se denlos supuestos mencionados arriba).
4.1 Propiedades de las transacciones
7
6. Conéctese a la BD mediante el Cliente de Líneade Comandos de MySQL y por otro ladomediante la interfase Workbench, ya que lossiguientes pasos requieren una simulación dedos usuarios simultaneos.
4.1 Propiedades de las transacciones
8
En SQL Server puede hacerlo con una sesión de consolay SSMS. Para la sesión de consola se puede usar unacceso directo como este:"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE" -SING-ALANIS\SQLEXPRESS -E
4.1 Propiedades de las transacciones
9
Si en SQL Server Management Studio lo hace con dosconexiones, se sugiere que las ventanas las ajuste adiferente tamaño para distinguir y ponga un comentario
(--Sesion 1 p.ej) para indicar de cual sesión se trata.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 10
Haga un retiro de $2,000 de la inversión paradepositarse en la cuenta de cheques
update CtasBanc set saldo=saldo-2000
where idCuenta=2;
update CtasBanc set saldo=saldo+2000
where idCuenta=1;
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 11
Lo anterior hágalo desde el Cliente Línea de
Comandos (Sesión 1 SSMS) y observe el
resultado desde Workbench (Sesión 2 SSMS).
select * from CtasBanc
Como verá, los datos son entregados
inmediatamente a la BD, por esa razón el
Usuario de la Sesión 2 puede ver el resultado.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 12
7. Haga otro traspaso (retiro y depósito) enLínea de Comandos (Sesión 1) y consultedesde Workbench (Sesión 2) después decada update.
update CtasBanc set saldo=saldo-500
where idCuenta=2;
select * from CtasBanc
update CtasBanc set saldo=saldo+500
where idCuenta=1;
select * from CtasBanc
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 13
Observe que cuando el Cliente Línea deComandos (Sesión 1) hace el retiro yWorkbench (Sesión 2) hace la consulta, laBD está inconsistente.
El dinero total que aparentemente hay, no escorrecto.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 14
8. Un traspaso más.
update CtasBanc set saldo=saldo-500
where idCuenta=2;
¡Alto! … No haga la segunda parte del traspaso.Se simulará que en este momento se presentauna falla (por ejemplo una caída de Windows) yse interrumpe el proceso.
Cierre abruptamente la Sesión 1 con el Administrador deTareas o en el caso de SQL Server, detenga el servidorde Bases de datos con SQL Server ConfigurationManager o Servicios de Windows.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 15
Una falla como la que acabamos de simular,puede ser causada por el hardware, falta deenergía o por una excepción de software(controlada o no).
9. Reinicie el servidor SQL SERVER e ingresede nuevo a la Sesión 1.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 16
10.Consulte los saldos de las cuentas, en
ambas sesiones:
select * from CtasBanc
select * from CtasBanc
¿Es consistente el estado de la Base de Datos?
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 17
Ejemplo A2(trabajaremos solo con una sesión)
1. Partamos de los saldos iniciales:
update CtasBanc set saldo=1000
where idCuenta=1;
update CtasBanc set saldo=9000
where idCuenta=2;
2. Consulte saldos:
select * from CtasBanc
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 18
3. Inicie un traspaso de la forma siguiente:
start transaction/ begin transaction;
update CtasBanc set saldo=saldo-2000
where idCuenta=2;
(Cierre la interfaz abruptamente)
4. Ingrese de nuevo a la interfaz y consulte
saldos.
¿Es consistente el estado de la Base de Datos?
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 19
Ejemplo A3(solo una sesión)
1. Partamos de los saldos iniciales:
update CtasBanc set saldo=1000
where idCuenta=1;
update CtasBanc set saldo=9000
where idCuenta=2;
2. Consulte saldos:
select * from CtasBanc
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 20
3. Realice un traspaso de la forma siguiente:
start transaction/ begin transaction;
update CtasBanc set saldo=saldo-3000
where idCuenta=2;
update CtasBanc set saldo=saldo+3000
where idCuenta=1;
commit;
{cierre la interfaz abruptamente}
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 21
4. Ingrese de nuevo a la interfaz y consulte
saldos.
¿Es consistente el estado de la Base de Datos?
¿Se llevó a cabo el traspaso?
Si hubiéramos omitido commit, ¿cúal sería el resultado?
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 22
Los ejemplos con que hemos trabajado soncasos clásicos que ilustran algunos de losaspectos útiles de las transacciones.
Sin embargo, para no dar pie a confusiones ypensar que las transacciones son siempreoperaciones complementarias en una solatabla, en las diapositivas siguientestrabajamos con ejemplos similaresinvolucrando varias tablas.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 23
Ejemplo B1
1. Crear una Base de Datos llamada CBTIS999.
create database CBTIS999
2. Crear las siguientes tablas:
create table Alumnos
( IdAlumno int
auto_increment / identity primary key,
Nombre char(40),
NumMatCursadas int );
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 24
Ejemplo B1
create table Materias
( IdMateria int
auto_increment / identity primary key,
Nombre char(20) );
create table MatCursadas
( IdAlumno int, IdMateria int );
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 25
3. Añadir a las tablas siguientes las tuplas suficientespara que queden como se muestra (la tercera tablainicialmente no llevará tuplas):
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 26
En este momento la BD está en un estadoconsistente porque los datos en la tablaMatCursadas y en el atributo NumMatCursadasde la tabla Alumnos son coherentes y correctosdesde cualquier punto de vista.
En este esquema hay redundancia porque elnúmero de materias cursadas se podría obteneren cualquier momento mediante una consulta sinnecesidad de conservar el atributoNumMatCursadas.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 27
A veces la redundancia es deseable cuandola complejidad de los esquemas es grande ocuando se busca aumentar la eficienciaeliminando tiempo de procesamiento deciertas consultas.
Para mantener la consistencia cuando haydatos redundantes, es indispensable usartransacciones.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 28
6. Conéctese a la BD mediante el Cliente deLínea de Comandos y por otro lado medianteWorkbench, ya que los siguientes pasosrequieren una simulación de dos usuarios.Realice las operaciones que se indican acontinuación (desde Línea de Comandos):
insert into MatCursadas values (3,2);
update Alumnos set
NumMatCursadas=NumMatCursadas+1
where IdAlumno=3;
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 29
Observe el resultado desde Workbench.
select * from MatCursadas
select * from Alumnos
Como verá, los datos son entregados
inmediatamente a la BD, por esa razón el
Cliente Workbench puede ver el resultado.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 30
7. Ejecute otras dos instrucciones en Línea de
Comandos y consulte desde Workbench
después de cada una de ellas.
insert into MatCursadas values (1,1);
select * from MatCursadas
select * from Alumnos
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 31
update Alumnos set
NumMatCursadas=NumMatCursadas+1
where IdAlumno=1;
select * from MatCursadas
select * from Alumnos
Observe que cuando el Cliente Línea de Comandoshace la primera operación y Workbench hace lasconsultas, la BD está inconsistente.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 32
8. Otra materia cursada:
insert into MatCursadas values (3,4);
(cierre la interfaz abruptamente mediante el
administrador de tareas, simulando una falla)
9. Encienda el equipo y conéctese a la BD.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 33
10.Consulte el estado de las tablas:
select * from MatCursadas
select * from Alumnos
¿Es consistente el estado de la Base de Datos?
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 34
Ejemplo B2
1. Partamos del estado inicial de las tablas:
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 35
2. Inicie las operaciones de la forma siguiente:
start transaction/ begin transaction;
insert into MatCursadas values (3,2);
(cierre abruptamente la interfaz Línea de Comandosmediante el Administrador de tareas).
3. Conéctese a la BD mediante Workbench yconsulte el contenido de las tablas.
select * from MatCursadas
select * from Alumnos
¿Es consistente el estado de la Base de Datos?
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 36
Ejemplo B3
1. Partamos del estado inicial:
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 37
2. Inicie las operaciones de la forma siguiente:
start transaction/ begin transaction;
insert into MatCursadas values (2,4);
update Alumnos set
NumMatCursadas=NumMatCursadas+1
where IdAlumno=2;
commit;
(cierre abruptamente la interfaz Línea de Comandos).
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 38
3. Conectese de nuevo a la BD con la interfaz
línea de Comandos y consulte el estado de
las tablas.
¿Es consistente el estado de la Base de Datos?
¿Se efectuaron las operaciones?
Si hubiéramos omitido commit, ¿cúal sería el resultado?
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 39
Propiedades de las transacciones:
❑Atomicidad
❑Consistencia
❑Aislamiento (Isolation en inglés)
❑Durabilidad
Conocidas como propiedades ACID.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 40
1. Atomicidad.
Se requiere que todas lasoperaciones de unatransacción sean entregadas(commited) a la base de datos.
En caso de que una de ellas nosea entregada por cualquierrazón, ninguna debe hacerse.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 41
2. Consistencia.
Se requiere que si antes de
ejecutar una transacción, la BD
es consistente, permanezca
igualmente consistente una vez
terminada la transacción.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 42
3. Aislamiento.
Si se presenta la ejecuciónconcurrente de transacciones,cada una de ellas debe tenerla impresión de que las demásno existen, como si seejecutaran en serie (unadespués de la otra).
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 43
4. Durabilidad.
Es indispensable que una vez
finalizada la transacción y los
datos entregados a la BD,
estos no se pierdan incluso
aunque se produzca una falla.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 44
Con la excepción de aislamiento,
en los ejercicios anteriores
hemos comprobado esas
propiedades.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 45
Ejemplo A4
1. Abra una sesión en MySQL o SQL Server,para simular el retroceso de transacciones.
2. A partir de los saldos iniciales:
update CtasBanc set saldo=1000
where idCuenta=1;
update CtasBanc set saldo=9000
where idCuenta=2;
select * from CtasBanc;
4.2 ROLLBACK
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 46
3. Lleve a cabo las siguientes operaciones:
start/begin transaction;
select * from CtasBanc;
update CtasBanc set saldo=saldo-15000
where idCuenta=2;
update CtasBanc set saldo=saldo+15000
where idCuenta=1;
select * from CtasBanc;
rollback;
select * from CtasBanc;
4.2 ROLLBACK
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 47
Rollback termina la transación sin entregar los datos a la BD. Commit termina la transacción y entrega los datos a la BD.
El uso de Rollback en el ejemplo anterior es una alternativa para impedir que el usuario trate de retirar más dinero del que posee en una de las cuentas, siempre y cuando esa sea una restricción establecida por el dueño de los datos.
4.2 ROLLBACK
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 48
4.2 ROLLBACK
-- Transacción que evita retiros mayores al saldo de la inversión-- (ejecútela varias veces para comprobar su utilidad)begin transactionupdate CtasBanc set saldo=saldo-1000 where idCuenta=2;update CtasBanc set saldo=saldo+1000 where idCuenta=1;declare @vSaldo numeric(10,2)select @vSaldo=saldo from CtasBanc where idCuenta=2if @vSaldo<0
beginrollbackselect 'Recursos insuficientes para retirar de la Inversión’end
elsebegincommitselect * from CtasBancend
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 49
El Nivel de Aislamiento describe el grado en elcual los datos que están siendo manipuladospor una sesión son accesibles a otras:
4.3 Niveles de Aislamiento
• Serializable (En serie).
• Repeatable Read (Lectura Repetible)
• Read Committed (Leer Entregados).
• Read Uncommitted (Leer NoEntregados).
50
Ejemplo A5-1 MySQLConéctese a la BD mediante el Cliente de Línea deComandos de MySQL y por otro lado mediante lainterfaz Workbench, para simular dos usuarios.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 51
Ejemplo A5-1
(MySQL)
1. Abra 2 sesiones, una usando Línea de
Comandos y otra Workbench.
2. Asegúrese que el nivel de aislamiento en la
sesión de Workbench sea READ
COMMITTED:
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 52
3. Si lo requiere, cambie el nivel deaislamiento de la siguiente forma:
set session transaction isolation levelread committed;
Compruebe el resultado:
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 53
4. A partir de los saldos iniciales:
use LaConsentida;
update CtasBanc set saldo=1000
where idCuenta=1;
update CtasBanc set saldo=9000
where idCuenta=2;
Consulte saldos:
select * from CtasBanc;
select * from CtasBanc;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 54
5. Haga un traspaso en Línea de Comandos:
start transaction;
update CtasBanc set saldo=saldo-5000
where idCuenta=2;
update CtasBanc set saldo=saldo+5000
where idCuenta=1;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 55
6. En Workbench consulte los saldosantes y después de terminar latransacción (en Línea deComandos):
select * from CtasBanc;
commit;
select * from CtasBanc;
Tome nota de sus observaciones.
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 56
• MySQL hace un bloqueo compartido parapermitir hacer select basados en la versiónanterior de la tupla (antes de ser modificadapor la primera transacción).
• Sin embargo, debe quedar claro que unatupla que está siendo modificada por unatransacción (bloqueo compartido o no), nopuede ser modificada por otratransacción (en todos los niveles deaislamiento, excepto READ UNCOMMITED)hasta que la primera transacción termine.
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 57
El ejercicio siguiente, es equivalente al ejercicioA5-1 pero con SQL Server.
Se podrá comprobar que las tuplasmodificadas por una transacción no estánaccesibles para ninguna otra hasta que laprimera termine.
SQL Server hace un bloqueo exclusivo quesubsiste hasta el final de la transacción de lastuplas modificadas, excepto en el nivel READUNCOMMITTED.
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 58
4.3 Niveles de Aislamiento
Bloqueo Compartido.
• Evita que otras transacciones modifiquen losdatos leídos por la primera transacción (peropueden leerlos por lo que hay que sercuidadosos al escribir los algoritmos).
Bloqueo Exclusivo.
• Impide que otras transacciones lean omodifiquen tuplas modificadas por la primeratransacción.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 59
4.3 Niveles de Aislamiento
• Los bloqueos exclusivos se adquierenautomáticamente durante INSERT, UPDATE oDELETE.
• Solo un proceso a la vez puede contener un bloqueo exclusivo (o de cualquier otro tipo) para el mismo grupo de datos.
• Los bloqueos exclusivos se mantienen hasta el final de la transacción (commit o rollback).
60
Ejemplo A5-2
1. Abra 2 sesiones, Sesión 1 en Consola de SQL Server ySesión 2, en SSMS (o en 2 ventanas de SSMS).
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 61
2. Asegúrese que el nivel de aislamiento en la
Sesión 2 sea cualquiera excepto READ
UNCOMMITTED:
DBCC useroptions
3. Si lo requiere, cambie el nivel de aislamientode la siguiente forma:set transaction isolation level
read committedCompruebe el resultado:
DBCC useroptions
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 62
4. A partir de los saldos iniciales:
use LaConsentida
update CtasBanc set saldo=1000
where idCuenta=1;
update CtasBanc set saldo=9000
where idCuenta=2;
Consulte saldos:
select * from CtasBanc;
select * from CtasBanc;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 63
5. Haga un traspaso en Sesión 1:
begin transaction;
update CtasBanc set saldo=saldo-5000
where idCuenta=2;
update CtasBanc set saldo=saldo+5000
where idCuenta=1;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 64
6. En Sesión 2 consulte los saldos antesy después de terminar la transacción(en Sesión 1):
select * from CtasBanc; -- Espere 15 seg
commit;
Registre sus observaciones.
4.3 Niveles de Aislamiento
65
Para el siguiente ejemplo, puede usar MySQL o SQL Server (eneste ultimo caso puede abrir 2 conexiones, (Sesión 1, Sesión 2)para distinguirlas, o una sesión con la interfaz de consola y una deSSMS.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 66
Ejemplo A6-1
1. Asegúrese que el nivel de aislamiento en la
sesión de Workbench o en la Sesión 2
sea READ UNCOMMITTED (Leer No
Entregados):
select @@transaction_isolation;
DBCC useroptions; -- Sql Server
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 67
3. Si lo requiere, cambie el nivel deaislamiento de la siguiente forma:
set session transaction isolation levelread uncommitted;
set transaction isolation level readuncommitted
Compruebe el resultado:
select @@transaction_isolation;
DBCC useroptions
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 68
4. A partir de los saldos iniciales:
update CtasBanc set saldo=1000
where idCuenta=1;
update CtasBanc set saldo=9000
where idCuenta=2;
Consulte saldos:
select * from CtasBanc;
select * from CtasBanc;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 69
5. Haga un traspaso en Línea de
Comandos/Sesión 1:
start transaction/begin transaction;
update CtasBanc set saldo=saldo-6000
where idCuenta=2;
update CtasBanc set saldo=saldo+6000
where idCuenta=1;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 70
6. En Workbench/Sesión 2 consulte lossaldos antes y después de terminar latransacción (en Línea deComandos):
select * from CtasBanc;
commit;
select * from CtasBanc;
Registre sus observaciones.
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 71
Ejemplo A6-2
1. Asegúrese que el nivel de aislamiento en la
Sesión 2 / Workbench sea READ
UNCOMMITTED (Leer No Entregados):
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 72
3. Si lo requiere, cambie el nivel deaislamiento de la siguiente forma:
set session transaction isolation levelread uncommitted;
Compruebe el resultado:
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 73
4. A partir de los saldos iniciales:
update CtasBanc set saldo=1000
where idCuenta=1;
update CtasBanc set saldo=9000
where idCuenta=2;
Consulte saldos:
select * from CtasBanc;
select * from CtasBanc;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 74
5. Haga un traspaso en Línea de Comandos:
start transaction/begin transaction;
update CtasBanc set saldo=saldo-6200
where idCuenta=2;
update CtasBanc set saldo=saldo+6200
where idCuenta=1;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 75
6. En Workbench consulte los saldosantes y después de terminar latransacción (en Línea deComandos):
select * from CtasBanc;
rollback;
select * from CtasBanc;
Registre sus observaciones.
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 76
Read Uncommitted es el nivel de abstracción
menos estricto.
Permite ver datos de otras transacciones
concurrentes antes de que sean entregados a
la Base de Datos.
Puede ocasionar Lecturas Sucias, ya que los
datos no entregados de una transacción serán
visibles durante un tiempo para otras
transacciones y luego pueden ser desechados.
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 77
Ejemplo A71. Abra 2 sesiones, Sesión 1 y Sesión 2 en el
DBMS de su preferencia.
2. Parta de los saldos iniciales (1000,9000) y
siga estrictamente el orden que se indica.
3. No importa el nivel de aislamiento.
4. Se van a ejecutar 2 diferentes traspasos en
forma concurrente. Una persona hará un
traspaso de 1,000 y otra de 2,000 en forma
simultanea.
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 78
5. Tome en consideración que el nivel de
aislamiento en las transacciones concurrentes
debe ser el mismo, porque se trata de los
mismos comandos ejecutados por diferentes
usuarios, por lo que no tiene sentido que el nivel
de aislamiento en las diferentes sesiones, sea
distinto.
6. Ejecute las instrucciones una a una en el orden
indicado y en la sesión correspondiente. Cuando
llegue al paso 6, no se detenga mucho tiempo,
solo observe lo que sucede y continúe.
4.3 Niveles de Aislamiento
79
1. use LaConsentida;
2. use LaConsentida
3. start/begin transaction
4. start/begin transaction
5. update CtasBanc set saldo=saldo-1000 where idCuenta=2;
6. update CtasBanc set saldo=saldo-2000 where idCuenta=2;
7. update CtasBanc set saldo=saldo+1000 where idCuenta=1;
8. commit;
9. update CtasBanc set saldo=saldo+2000 where idCuenta=1;
10. commit
11. select * from CtasBanc
12. select * from CtasBanc
4.3 Niveles de Aislamiento
Fund. Bases de Datos Ing. Felipe Alanís González - ITD -
En el ejercicio anterior observe que el paso # 6 (sesión 2) entra en espera porque no puede obtener un bloqueo sobre la misma tupla sobre la que sesión 1 ya lo obtuvo al hacer el update.
El bloqueo de la sesión 1 permanecerá hasta que termine la transacción lo que permitirá que ambas transacciones se ejecuten correctamente.
En las diapositivas siguientes, se plantea una situación en la que se producen inconsistencias si no se considera el acceso concurrente.
81
3 Transacciones concurrentes1. use LaConsentida;
2. use LaConsentida
3. use LaConsentida
4. begin transaction
5. begin transaction
6. begin transaction
7. update CtasBanc set saldo=saldo-500 where idCuenta=2;
8. update CtasBanc set saldo=saldo-600 where idCuenta=2;
9. update CtasBanc set saldo=saldo-700 where idCuenta=2;
10. update CtasBanc set saldo=saldo+500 where idCuenta=1;
11. commit;
12. update CtasBanc set saldo=saldo+600 where idCuenta=1;
13. commit
14. update CtasBanc set saldo=saldo+700 where idCuenta=1;
15. commit
16. select * from CtasBanc
17. select * from CtasBanc
18. select * from CtasBanc
4.3 Niveles de Aislamiento
Fund. Bases de Datos Ing. Felipe Alanís González - ITD -
Considere la siguiente situación:
Cada vez que se realiza la ceremonia de titulación de un estudiante, el profesor que lo asesoró, se hace acreedor a un pago.
El importe del pago es de $200.00 para las titulaciones por Tesis; para cualquiera otra opción, el importe es de $100.00.
¿Por que son necesarias las transacciones?
Respuesta: para evitar anomalías
Fund. Bases de Datos Ing. Felipe Alanís González - ITD -
En las siguientes tablas se conservan los datos:
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 84
Proceso para registrar las tuplas en las tablas de la página anterior:
1. Mediante una formulario de captura, el usuario elige al maestro asesor.
2. Una vez elegido, el programa lee el total de pagos acumulados correspondientes de la tabla PagosMaestroTitulacion y lo guarda en una variable.
3. Se muestra otro formulario para que el usuario elija al alumno, Fecha y Opción de titulación correspondientes.
4. Se Añade una nueva tupla en la tabla TitulacionesAlumnos.
5. Se calcula el nuevo total de acuerdo a la opción de titulación y se añade o actualiza la tupla correspondiente en la tabla PagosMaestroTitulacion.
Fund. Bases de Datos Ing. Felipe Alanís González - ITD -
Podría presentarse acceso concurrente a los mismos datos, bajo las siguientes condiciones hipotéticas:
✓ Se realizan una buena cantidad de procesos de titulación de alumnos al final de cada semestre y se asigna a varias personas a capturar la información que se produce.
✓ Una vez en ejecución el sistema, en forma casi simultanea, a dos personas le corresponde registrar exámenes de titulación de un mismo profesor.
Capturista 110:30 am. • Elige al Maestro Asesor.• El programa lee el total de pagos
al maestro ($300)• Se levanta a preparar un café.
Capturista 210:34 am. • Elige al Maestro Asesor.• El programa lee el total de pagos
al maestro ($300)
• ¿El importe total que se conservará es correcto?• El ejemplo está diseñado de tal manera que haya una anomalía al
haber acceso concurrente pero aunque el intervalo entre la lectura del total anterior y la escritura del nuevo total fuera una fracción de segundo, el riesgo existe cuando se presenta una gran cantidad de procesos simultáneos de la misma transacción.
10:35 am.
•Regresa a su lugar.• Elige al Alumno, Fecha y Opción
(en este caso es Promedio)• Se guarda el nuevo total ($400)
10:36 am.
• Elige al Alumno, Fecha y Opción (Tesis)
• Se guarda el nuevo total ($500.00)
Probable ejecución del algoritmo en un caso concurrente:
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 87
4.3 Niveles de Aislamiento
Ejercicio para evaluación:
• Añada al esquema de la BD del ITD las tablas correspondientesal manejo de los pagos a los profesores por el concepto detitulación.
• Diseñe la transacción requerida para llevar a cabo el proceso deacumular el importe de pago a los profesores cada vez que seregistra la titulación de un alumno.
• Escriba una aplicación para poner en practica este proceso. Useel algoritmo descrito en la página 84, para demostrar que hayriesgo de inconsistencia cuando se presentan accesosconcurrentes. Experimente con los niveles de abstracción READCOMMITED y READ UNCOMMITED y tome nota detallada desus observaciones.
88
Ejemplo A8-1 MySQLConéctese a la BD mediante el Cliente de Línea deComandos de MySQL y por otro lado mediante lainterfaz Workbench, para simular dos usuarios.
4.1 Propiedades de las transacciones
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 89
Ejemplo A8-1
1. Abra 2 sesiones, una usando Línea de
Comandos y otra Workbench.
2. Asegúrese que el nivel de aislamiento en las
dos sesiones sea REPEATABLE READ:
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 90
3. Si lo requiere, cambie el nivel deaislamiento de la siguiente forma:
set session transaction isolation levelrepeatable read;
Compruebe el resultado:
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 91
4.3 Niveles de Aislamiento
En la página siguiente se muestra la mismatransacción ejecutándose concurrentemente2 ocasiones.
Observe que el nivel de aislamientoLecturas Repetibles hace que aunque losdatos estén entregados, no sean legiblesen la 2ª transacción sino hasta que éstaúltima transacción modifica los mismosdatos (observe que no hay inconsistencia).
92
start transaction;
select * from CtasBanc;
start transaction;
select * from CtasBanc;
select * from CtasBanc;
update CtasBanc set saldo=saldo-1000 where idCuenta=2;
update CtasBanc set saldo=saldo+1000 where idCuenta=1;
select * from CtasBanc;
commit;
select * from CtasBanc;
update CtasBanc set saldo=saldo-2000 where idCuenta=2;
update CtasBanc set saldo=saldo+2000 where idCuenta=1;
select * from CtasBanc;
commit;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 93
Ejemplo A8-2
1. Abra 2 sesiones, una usando Línea de
Comandos y otra Workbench.
2. Asegúrese que el nivel de aislamiento en las
dos sesiones sea REPEATABLE READ:
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 94
3. Si lo requiere, cambie el nivel deaislamiento de la siguiente forma:
set session transaction isolation levelrepeatable read;
Compruebe el resultado:
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 95
4.3 Niveles de Aislamiento
Es un ejemplo similar al A8-1, ladiferencia estriba en que no se hace unalectura iniciando la transacción por loque la primera lectura se hace cuando laprimera transacción ya entregó los datos ala BD.
96
start transaction;
select * from CtasBanc;
start transaction;
select * from CtasBanc;
select * from CtasBanc;
update CtasBanc set saldo=saldo-1000 where idCuenta=2;
update CtasBanc set saldo=saldo+1000 where idCuenta=1;
select * from CtasBanc;
commit;
select * from CtasBanc;
update CtasBanc set saldo=saldo-2000 where idCuenta=2;
update CtasBanc set saldo=saldo+2000 where idCuenta=1;
select * from CtasBanc;
commit;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 97
Ejemplo A8-3 MySQL
1. Abra 2 sesiones, una usando Línea de
Comandos y otra Workbench.
2. Asegúrese que el nivel de aislamiento en las
dos sesiones sea READ COMMITTED.set session transaction isolation level
read committed;
select @@transaction_isolation;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 98
4.3 Niveles de Aislamiento
Ejecute de nuevo la transacción (es la misma
que se ejecutó en el ejercicio A8-1 pero se deja
por claridad).
Observe que si hace una lectura de los datos en
mas de una ocasión, estos pueden cambiar si
otra transacción los modificó.
99
start transaction;
select * from CtasBanc;
start transaction
select * from CtasBanc
select * from CtasBanc;
update CtasBanc set saldo=saldo-1000 where idCuenta=2;
update CtasBanc set saldo=saldo+1000 where idCuenta=1;
select * from CtasBanc;
commit;
select * from CtasBanc;
update CtasBanc set saldo=saldo-2000 where idCuenta=2;
update CtasBanc set saldo=saldo+2000 where idCuenta=1;
select * from CtasBanc;
commit;
4.3 Niveles de Aislamiento
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 100
4.3 Niveles de Aislamiento
Bloqueos fundamentales
• Bloqueo Compartido.
• Evita que otras transacciones modifiquen losdatos leídos por la primera transacción (peropueden leerlos por lo que hay que sercuidadosos al escribir los algoritmos.
• Los bloqueos compartidos se emiten cuando los procesos leen datos.
• Puede tratarse de una tupla, un grupo de tuplas o una tabla de índices.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 101
4.3 Niveles de Aislamiento
• Diversos procesos pueden poseer bloqueos compartidos en los mismos datos, pero ningún proceso puede adquirir un bloqueo exclusivo si ya se tiene un bloqueo compartido en él (a menos que se trate del mismo proceso que posee el bloqueo compartido).
• Los niveles de aislamiento determinan el tiempo que se retiene un bloqueo compartido dentro de una transacción. Si no hay transacción de por medio, los bloqueos compartidos se liberan al terminar la consulta.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 102
4.3 Niveles de Aislamiento
• Bloqueo Exclusivo.
• Impide que otras transacciones lean o modifiquentuplas modificadas por la primera transacción.
• Los bloqueos exclusivos se adquierenautomáticamente durante INSERT, UPDATE oDELETE.
• Solo un proceso a la vez puede contener un bloqueo exclusivo (o de cualquier otro tipo) para el mismo grupo de datos.
• Los bloqueos exclusivos se mantienen hasta el final de la transacción (commit o rollback).
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 103
4.3 Niveles de Aislamiento
Obervaciones importantes
• Una transacción siempre emite un bloqueoexclusivo de los datos que modifica ymantiene ese bloqueo hasta que secompleta (la transacción), esto esindependiente del nivel de aislamientoestablecido para la transacción.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 104
4.3 Niveles de Aislamiento
• Una forma de asegurarse de que no hayamodificaciones perdidas (lost updates) esrealizarlas mediante un solo comandoUPDATE y no basarlas en valores obtenidospreviamente por una consulta (en elejemplo de la página 86 se muestran lasinconsistencias que se producen).
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 105
4.3 Niveles de Aislamiento
• Para las operaciones de lectura, los nivelesde aislamiento definen principalmente elnivel de protección contra los efectos de lasmodificaciones realizadas por otrastransacciones.
• Un nivel de aislamiento más bajo aumenta lacapacidad de muchos usuarios para accedera los datos al mismo tiempo, pero tienealgunos efectos como lecturas sucias ofantasmas con los que hay que sercuidadoso al procesar los datos.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 106
4.3 Niveles de Aislamiento
Read Uncommitted.
• Hay poco aislamiento presente porquepueden leerse datos no entregados (dirtyreads) y que tal vez no se entreguen.
• Una aplicación difícilmente puede confiar endirty reads.
• Se podría decir que este no es un nivel deaislamiento, sin embargo lo es porque lastuplas modificadas por una sesión readuncommitted son bloqueadas igual quecualquier otro nivel de aislamiento.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 107
4.3 Niveles de Aislamiento
Read Committed.
• Se evitan las lecturas sucias ya que los cambios noentregados por otras transacciones no son visibles.
• Garantiza que cualquier dato leído está efectivamenteentregado a la BD al momento que se lee.
• Restringe lecturas intermedias no entregadas, pero sivuelve a hacerse una lectura dentro de la mismatransacción, se pueden obtener datos diferentes porqueel bloqueo compartido (de lectura) se libera una vezterminado el select. Este fenómeno se conoce comoLECTURAS NO REPETIBLES.
• Nivel de aislamiento predeterminado de SQL Server.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 108
4.3 Niveles de Aislamiento
Repeatable Read.• Se evita el fenómeno de Lecturas no repetibles porque al
ejecutarse un SELECT se hace un SNAPSHOT y se utilizaen toda la transacción cuando se ejecuta el mismo SELECT.
• Ignora los cambios realizados por otras transacciones, esténentregados o no, para asegurar lecturas REPETIBLES.
• Nivel de aislamiento predeterminado para MySQL-InnoDB.
• Puede haber lecturas fantasmas de nuevas tuplas de otrassesiones porque no hace bloqueo de rangos de tuplas deSELECT.
• En SQL Server ninguna transacción puede leer datosmodificados por otra transacción porque de manerapredeterminada no se toma al inicio de la transacción unSNAPSHOT como en MySQL-InnoDB.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 109
4.3 Niveles de Aislamiento
Serializable.
• Se evita un fenómeno llamado lecturasfantasma porque se colocan bloqueos entodas las tuplas a las que se accede con unSELECT y se bloquean los índices paraevitar que tuplas añadidas en otra sesión yque cumplan con la misma condiciónaparezcan como lecturas fantasmas.
• Este nivel de aislamiento es el más fuerteposible.