Download Desarrollo de Bases de Datos Oscar Díaz

Desarrollo de Bases de Datos
SQLJ
Motivación
Motivación
?Dos tipos de usuario:
? ocasionales:
? desarrolladores:
utilizan SQL interactivo
utilizan SQL embebido
para recuperar datos
para tratamiento de datos
Utilización de variables compartidas
Compatibilidad de tipos
Recuperación de datos
Tratamiento de excepciones
Contexto de ejecución
Contexto de conexión
Ejecución de una aplicación SQLJ
SQL embebido
?Estándar para la inclusión de instrucción SQL dentro de
un lenguaje de programación
?Se “extiende” el leng. prog. mediante la pre-compilación
?Requisitos del tratamiento de datos
? modificar/recuperar tuplas ? SQL
? capacidad de cálculo + instrucciones de control ? COBOL, C
?Por tanto, el tratamiento de datos requiere utilizar
SQL dentro de un lenguaje de programación
SQLJ
?Interfaz de alto nivel: consultas directamente en SQL
?Análisis sintáctico y semántico en tiempo de
(pre)compilación
?Interoperabilidad: estándar ANSI (diciembre, 1998)
?Toda instrucción SQL aparece precedida por una palabra
clave reconocida durante la pre-compilación
?Para el caso de JAVA
?estándar SQLJ
Pasos para la ejecución de una
aplicación .sqlj
Código fuente: Java + SQLJ
.....
Pre-compilador SQLJ
• comprobación de sintaxis
• comprobación de tipos
• comprobación de esquema
#sql [contexto conexión] {select ...};
>>sqlj mifichero.sqlj
Código fuente: Java + JDBC
Compilador Java
>>javac mifichero.java
Código byte: Java + llamadas al controlador JDBC
>>java mifichero.class
Controlador JDBC
Sistema
Base de Datos
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Utilización de variables compartidas
?Variables de Java que aparecen en sentencias SQL
void ejemplo() throws Exception {
String cuentaA = null;
String cuentaB = null;
Integer elSaldo = null;
DefaultContext contexto1 = new DefaultContext (
“jdbc:oracle:oci8:@midb”, “unusuario”, “secreto ”, false );
#sql [contexto1] {select saldo INTO :elSaldo
from cuentaCorriente where codigo = :cuentaA};
#sql [contexto1] {update cuentaCorriente
set saldo = saldo + :elSaldo where codigo = :cuentaB};
Compatibilidad de tipo SQL ? Java
#sql [ctx] {update Alumno set nota= :la_nota where dni = :el_dni}
? A la variable de SQL “nota” se le asigna la variable JAVA
“la_nota” ? sus tipos tienen que ser compatibles
Tipos Java
(escalares)
Tipos SQL
boolean
byte
short
int
long
float
double
BIT
TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT, DOUBLE
cont.
Cont.
?OJO: Identificadores de SQL no son sensibles.
Identificadores de Java sí son sensibles
#sql [ctx] {update Alumno
set nota= :la_nota where dni = :el_dni}
instrucción
SQLJ
contexto en el que se
ejecuta la sentencia SQL
Las variables compartidas
precedidas por “ :”
#sql [ctx1] {select nombre into :el_nombre from Alumno
where dni = :el_dni}
El valor null
Los tipos de datos de Java NO soportan el valor null.
? SQLJ define para cada clase
escalar de Java, una clase
“wrapper” donde
? se soporta el valor “null”
? existe un método xxxValue que
permite la conversión al tipo
primitivo (p.ej intValue pasa a
int)
? otros métodos: toString(int),
valueOf(String), shortValue()
? Nótese que si el tipo es
wrapper empieza por
mayúscula
Tipos Java
(clases wrapper)
java.lang.Boolean
java.lang.Byte
java.lang.Short
java.lang.Integer
java.lang.Long
java.lang.Float
java.lang.Double
java.lang.String
java.math.BigDecimal
byte[ ]
java.sql.Date
java.sql.Time
java.sql.Timestamp
Tipos SQL
BIT
TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT, DOUBLE
CHAR, VARCHAR,
LONGVARCHAR
NUMERIC, DECIMAL
BINARY, VARBINARY,
LONGBINARY
DATE
TIME
TIMESTAMP
Recuperación de datos
?Pasar null de Java a SQL
String elNombre = null;
Short laEdad = null;
#sql {insert into Alumno (dni, nombre, edad)
values (444, :elNombre, :laEdad)};
•Recuperar null de SQL en Java
Short elDni;
Short laEdad;
#sql {select edad into :laEdad
from Alumno where dni = :elDni}
short auxEdad = laEdad.shortValue(); error
?Si SELECT devuelve un solo valor.
?El resultado se guarda en una variable del lenguaje anfitrión
#sql {SELECT edad INTO :laEdad
FROM Alumno where dni = :elDni}
?Si SELECT devuelve un conjunto de valores.
?El resultado se guarda en un CURSOR
• Si una variable de tipo primitivo recibe null se
genera el error SQLNullException
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Cursores identificados por posición
? Un cursor es un objeto de la clase “iterator”
? El nº de columnas es igual al de la pregunta
? Se especifica el tipo de los atributos de la pregunta
#sql public iterator iteAlumno (Integer, String); // 1.- define el tipo “iteAlumno”
iteAlumno unCursor;
// 2.- se introduce variable de este tipo
Integer elDni;
String elNombre;
#sql unCursor = {select dni, nombre from Alumno}; // 3.- se abre el cursor
while (true) {
// 4.- se itera sobre el cursor
#sql {fetch :unCursor into :elDni, :elNombre};
if (un_cursor.endFetch()) break; // true si no hay más tuplas
System.out.println (elDni, elNombre); }
un_cursor.close();
// 4.- se cierra el cursor
Tratamiento de excepciones
?Excepción
? es un objeto de tipo SQLException
? se crea cuando se produce una excepción
? métodos: getMessage, getErrorCode, getSQLState
try{ #sql {select nombre into :elNombre from Alumno where dni = :elDni};}
catch( SQLNullException ne ){
Cursores identificados por el nombre
(recomendado)
? Un iterador especifica el nombre y tipo de los atributos de la pregunta
? Los valores de los atributos del registro se obtienen con métodos con
nombre el del atributo
#sql public iterator iteAlumno (Integer el_dni, String el_nombre);
iteAlumno unCursor;
// variable de tipo la clase anterior
Integer elDni;
String elNombre;
#sql unCursor = {select nombre as el_nombre,
dni as el_dni from Alumno}; // asignación cjto. tuplas
while (unCursor.next()) {
// avanza a la sgte. tupla. ( false, si no hay más)
elDni = unCursor.el_dni();
// obtiene el valor del atributo de la tupla
elNombre = unCursor.el_nombre();
System.out.println (elDni, elNombre);}
unCursor.close();
// liberación del recurso
Contexto de conexión
? Una instrucción SQL se ejecuta en un contexto de conexión
? #sql [contexto] { update Alumno set nota= :la_notawhere dni = :el_dni}
? Este contexto indica
? el SGBD,
? la base de datos
? usuario y password
? modo “autocommit”
con el que se ejecutará la instrucción SQL
System.out.println(“ Se ha encontrado un valor null: “ + ne. getErrorCode());}
catch( SQLException e ){
System.out.println(“ Error: ” + e.getMessage() + e.getErrorCode());}
Clase DefaultContext
? static DefaultContext DefaultContext(java.lang.String url,
java.lang.String user, java.lang.String password, boolean
autoCommit)
? Crea el contexto por defecto
? static DefaultContext getDefaultContext()
? Devuelve el contexto por defecto
? Este contexto se define como una instancia de la clase
sqlj.runtime.DefaultContext
Ejemplo
DefaultContext miEjemplo = new DefaultContext (
“jdbc:oracle:oci8:@midb”,
“unusuario”, “secreto”, false );
DefaultContext.setDefaultContext ( miEjemplo );
#sql [miEjemplo] {update ...}; //no sería necesario poner el contexto
? static void setDefaultContext(DefaultContext ctx)
? Establece el contexto por defecto
miEjemplo.close();
? public void close()
? Cierra el actual contexto
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Varios contextos de conexión simultáneos
? Necesidad de usar en la aplicación SGBDs distintos
? Necesidad de usar BD diferentes
? Necesidad de añadir más funcionalidades a la clase contexto de conexión
DefaultContext miEjemplo = new DefaultContext (
“ jdbc:oracle:oci8:@midb ”, “ unusuario ”, “ secreto ”, false );
DefaultContext otroEjemplo = new DefaultContext (
“ jdbc:oracle:oci8:@midb ”, “ otrousuario”, “ misecreto”, false );
DefaultContext.setDefaultContext ( miEjemplo );
#sql {select ...};
// utiliza el contexto por defecto
#sql [otroEjemplo] {update ...};
Contexto de ejecución
? Una instrucción SQL se ejecuta en un contexto de ejecución:
#sql [unCtxCon, unCtxEje] {update Alumno set nota = nota + 1 where curso = 5};
? Este contexto es una zona compartida por el runtime del SGBD y
el programa, para intercambiar información sobre
? MaxRows: máximo nº de tuplas que puede guardar un cursor
? MaxFieldSize: máximo nº de bytes de un atributo
? QueryTimeout: segundos de espera a una sentencia SQL
? UpdateCount: nº tuplas afectadas con última sentencia SQL
? SQLWarnings : texto error generado en la ejecución
? Estos atributos se guardan como una instancia de la clase
sqlj.runtime.ExecutionContext
miEjemplo.close(); otroEjemplo.close( );
Ejemplo
// Crea un contexto de ejecución donde guarda las incidencias
ExecutionContext unCtxEje = new ExecutionContext();
Tratamiento
fichero .sqlj
Código fuente: Java + SQLJ
.....
Pre-compilador SQLJ
• comprobación de sintaxis
• comprobación de tipos
• comprobación de esquema
#sql [contexto conexión] {select ...};
>>sqlj mifichero.sqlj
Código fuente: Java + JDBC
#sql [unCtxCon, unCtxEje] {delete from Alumno where curso = 5};
Compilador Java
System.out.println(“Se han borrado” + unCtxEje.getUpdateCount() + “ alumnos”);
>>javac mifichero.java
Código byte: Java + llamadas al controlador JDBC
>>java mifichero.class
// Espera 3 segundos a que se ejecute la sentencia SQL
unCtxEje.setQueryTimeout(3);
Controlador JDBC
#sql [unCtxCon, unCtxEje] {update Alumno set curso = 5};
Sistema
Base de Datos
Configuración de la pre-compilación
Conexión durante la pre-compilación
?Algunos parámetros de configuración
SQLJ
mifichero.java
Compilador
Java
miE
jem
plo
mifichero.sqlj
?Pueden indicarse
?explícitamente:
mifichero.class
plo
Ejem
otro
? dir : directorio de los ficheros .java generados
? user y password : nombre de usuario, y su clave, para conectarse a
la bd. (si no es null, el sistema realiza comprobación online)
? url: indica dónde establecer la conexión
? driver: controladores JDBC
? offline/online: comprobación sin/con conexión a la bd.
>> sqlj -user=unusuario -password=secreto mifichero.sqlj
?en el fichero sqlj.properties, consultado al invocar a sqlj
sqlj.user =unusuario
sqlj.password=secreto
Conexiones en la compilación
Conexiones en la ejecución
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
public class top5 {
#sql static iterator top5It (String unAlumno, Integer notaMedia);
Ejercicio
void listarTop5() throws Exception {
top5It losTop5;
String elAlumno = null;
Integer laNotaMedia = null;
int contador = 0;
#sql losTop5 = { SELECT elAlumno AS unAlumno, avg(nota) AS notaMedia
FROM AluAsi
GROUP BY elAlumno
ORDER BY 2 DESC};
System.out.println("ALUMNO
" + "SU NOTA MEDIA");
while (losTop5.next() && contador < 5) {
elAlumno = losTop5.unAlumno();
laNotaMedia = losTop5.notaMedia();
System.out.println(elAlumno + " " + laNotaMedia);
contador = contador + 1;
}
losTop5.close();}
? La casa discográfica VIRGIN desea crear una base de datos
sobre las canciones, los autores y los cantantes que hacen
distintas versiones de dichas canciones. Un autor puede
componer distintas canciones y una canción puede tener
distintos compositores (tabla Aut_Can).
? Por otro lado, los cantantes pueden realizar distintas
versiones de una determinada canción. Las canciones están
agrupadas en álbumes (tabla Can_Can_Al). En un álbum una
misma canción puede ser cantada por distintos cantantes,
pero un cantante no puede tener diferentes versiones de la
misma canción en el mismo álbum. El diagrama E/R y las
tablas correspondientes son estas:
top5() {ConnectionManager.initContext();}}
Esquema
Esquema
Autor
Cantante
M
N
AUT_CAN
Fecha
CAN_CAN_AL
Duración
N
Canción
M
P
?AUTOR (NomAut, Tfno, Cuota)
?CANCION (Título, Estilo)
?CANTANTE (NomCan, Tfno, País)
?ALBUM (Código, DiscosPlatino)
?AUT_CAN (ElAutor, LaCanción)
?CAN_CAN_AL (LaCanción, ElCantante, ElAlbum,
Fecha, Duración)
Álbum
Ejercicio 1
Ejercicio 1
?Expresar en SQLJ un programa que cree un nuevo
album recopilatorio con las canciones 1º) de
cualquier estilo 2º) existan más de dos versiones de
las mismas, y 3º) cuyos autores hayan compuesto
alguna canción de estilo “Jazz”.
?Expresar en SQLJ un programa que cree el album
“XXX The BEST” que contenga para cada autor sus
5 canciones de más éxito, es decir, aquellas que
aparezcan en más álbumes.
?Conseguir todas las canciones
?Para cada canción
?Comprobar que si es de algún autor de Jazz
?Comprobar número de versiones
?Si se cumplen las dos anteriores
?Insertar en la BD (número de álbum y cantante fijos que
deberíamos saber)
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Ejercicio 1
Otras opciones del lenguaje
?Conseguir todas las canciones
?Borrar o modificar la tupla actual
?Select titulo from cancion
?Comprobar que si es de algún autor de Jazz
?Select 2
FROM AUT_CAN AS AC1 NJ Autor NJ AUT_CAN AS
AC2 NJ Canción
WHERE AC1.título=:título AND C2.Estilo=‘Jazz’
?(si devuelve tuplas es que sí tiene autor de Jazz)
#sql [ctx] {delete from tabla
WHERE CURRENT OF :variableIteradoranfitrión};
#sql [ctx] {update tabla set atributo = valor
WHERE CURRENT OF : variableIteradoranfitrión};
#sql iterator UnIterador(String, String);
UnIterador iter; …
#sql iter = { select nombre, dir from cliente where ciudad= ‘Donostia’};
while (true) {
?Comprobar número de versiones
#sql {fetch :iter into :nombre, :dir};
if (iter.endFetch()) break;
System.out.println (nombre+ " " + dir + " ¿Borrar (S/N)?");
entrada= new BufferedReader (new InputStreamReader (System.in));
?Select COUNT (Distinct Album) INTO :numversiones
from CAN-CAN-AL WHERE título=:titulo
?(si >2, entonces insertar en la BD)
tecla = entrada.readLine();
if (tecla.equals(“S"))
#sql {delete from cliente where current of :iter };
}
Procedimientos almacenados
Transacción
?Los definen los usuarios.
?Son objetos del esquema
?Se ejecutan en el SGBD
?La sintaxis de las llamadas depende del SGBD. Por
ejemplo, en Oracle
?Comienzo de la transacción:
?Al realizar una operación SQL sobre tuplas de la BD
?El control de concurrencia se basa en reservas
?A nivel de tupla
?Reserva de tupla: lo hace el sistema al escribir
?Para leer no se hace reserva
#sql [ctx] { CALL procedimiento(:arg1, :arg2) };
#sql [ctx] var= { VALUES funcion(:arg1, :arg2) };
?Liberar la reserva:
?Automáticamente
?Manualmente
AutoCommit
#sql {commit};
#sql {rollback};
AutoCommit
Autocommit
?Se puede definir cuando se crea el contexto de
conexión:
?También se puede cambiar a
través del contexto de conexión:
?Autocommit=false
DefaultContext ctx1= new DefaultContext (
“jdbc:oracle:oci8:@mibd”, “Jon”, “secreto”, false);
?Las transacciones se deben terminar manualmente
ctx.getConnection().setAutoCommit (true);
Tiene que ser la primera operación de la transacción
?Autocommit=true
DefaultContext ctx2= new DefaultContext (
“jdbc:oracle:oci8:@mibd”, “Nerea”, “asaber”, true);
?Las transacciones se terminan automáticamente en cuanto
terminan sus operaciones
?NO hay posibilidad de hacer ROLLBACK
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Recomendaciones para el diseño
de transacciones
Reducir el tráfico de red
#sql public iterator IteBDNotas(int nota);
IteBDNotas bdNotas;
...
#sql bdNotas = {select nota from Matricula where codAlum = 787};
#sql media = {select avg(nota) from Matricula where codAlum = 787};
while (bdNotas.next()) {
nota = bdNotas.nota();
...
if (nota > media) { System.out.println(... );}
}
#sql public iterator iteBDNotas (int nota);
iteBDNotas bdNotas;
...
// Solo se obtienen las notas que son superiores a la media
#sql bdNotas = {select nota from Matricula
where codAlum = 787 and nota > ( select avg(nota) from Matricula where codAlum = 787
)};
while (bdNotas.next()) {
nota = bdNotas.nota();
...
System.out.println(... );
}
Reducir el tráfico de red.
Row Prefetching
?El estándar JDBC trae las tuplas del resultado de una
consulta de una en una
?Para cada tupla hace falta un acceso a la BD
?Con la opción DefaultRowPrefetch las tuplas traidas se
pueden agrupar
?Se ahorran accesos a la BD
?El valor por defecto es 10 (Oracle)
?Se debe indicar el valor a cada instancia del contexto de
conexión
Reducir el tráfico de red
Update batching
?Las operaciones UPDATE, DELETE, INSERT se
pueden agrupar (en un proceso batch) para que la
ejecución sea más rápida (ahorrando accesos a la
BD)
?Las instrucciones en modo batch deben ser del mismo
tipo
?Esta característica se aplica a cada contexto de
ejecución
Reducir el tráfico de red
Row prefetching
?Cambiar el valor por defecto a 20 tuplas en un
contexto concreto (ctx)
((OracleConnection)ctx.getConnection()).getDefaultRowPrefetch()
((OracleConnection)ctx.getConnection()).setDefaultRowPrefetch(20)
?Cambiarlo en el contexto por defecto
((OracleConnection)DefaultContext.getDefaultContext().getConnection()).get
DefaultRowPrefetch()
((OracleConnection)DefaultContext.getDefaultContext().getConnection()).set
DefaultRowPrefetch(20)
Reducir el tráfico de red
Update batching
ExecutionContext ctxEje = new ExecutionContext();
ctxEje.setBatching(true);
ctxEje.setBatchLimit(limite);
for (int j=1; i<=limite; i++) {
// meter las instrucciones en un “lote”
#sql [ctxEje ] { INSERT INTO Tabla VALUES(:i) };
}
//Ejecución explícita de todas las inserciones a la vez
int[ ] kop = ctxEje.executeBatch();
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Reducir el tráfico de red
Update batching
Reducir el tráfico de red
Update batching
?las instrucciones de un lote se ejecutan de manera
implícita:
ctxEje.setBatching(true);
?Antes de ejecutar una instrucción de otro tipo
?Cuando se ejecuta commit
?Cuando se llega al límite de instrucciones batch
?Otras instrucciones
ExecutionContext ctxEje = new ExecutionContext ();
ctxEje.setBatchLimit( 3);
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where dni=1};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where dni=2};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where dni=3};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where dni=4};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where dni=5};
?ctxEje.isBatching()
?ctxEje.getBatchLimit()
?ctxEje.getBatchUpdateCounts()
?ctxEje.cancel()
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where categoria=10};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where categoria=20};
#sql [ctxEje] {commit};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where categoria=20};
ctxEje.executeBatch
¿Cuántos lotes hay y cuándo se ejecutan?
Reducir el tráfico de red
Update batching
ExecutionContext ctxEje = new ExecutionContext ();
ctxEje.setBatching(true);
ctxEje.setBatchLimit( 3);
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where na=1};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where na=2};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where na=3}; // llegado al límite
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where na=4};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where na=5};
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where categoria=10}; // límite =3
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where categoria=20};
#sql [ctxEje] {commit}; // se ha llegado ha terminado la transacción
#sql [ctxEje] {update Empleado set sueldo=sueldo+100 where categoria=20};
Niveles de aislamiento en transac.
?Priorizar transacciones
?Es posible hacer operaciones de lectura y escritura
?Cuando se compromete una transacción se liberan las
reservas (protocolo estricto de dos fases)
?Garantizar la serialización empeora el nivel de
concurrencia ? Para mejorar la concurrencia se
propone relajar las reglas definiendo niveles de
aislamiento
?Se pueden ejecutar concurrentemente las transacciones
que tienen distintos niveles de aislamiento
ctxEje.executeBatch(); // petición de ejecución explícita
Niveles de aislamiento en transac.
?Para definir las características de la transacción se
debe utilizar como primera instrucción:
set transaction
[read only | read write]
[isolation level [read uncommitted | read committed |
| repeatable read | serializable]]
[diagnostics size <valor>]
Read uncommited
?Solo operaciones de lectura
?Recomendable solo para transacciones que tienen
funciones estadísticas
?Las transacciones (Tj) pueden leer datos sin
comprometer
?Puede aparecer el problema de la lectura sucia (dirty
read)
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Read commited
Read uncommitted (dirty read)
Transacción T1
Transacción T2
...
#sql {update cuenta
#sql iterator MiIter(int numcta);
#sql {set transaction
set saldo= saldo- :cant
where numcta= :ctax};
Transacción T2
...
#sql {update cuenta
isolation level read uncommitted};
MiIter iter;
?
#sql iter = { select numcta from cuenta
where saldo<0};
while (iter.next( )) {
set saldo = saldo+ :cant
where numcta= :ctay };
?La transacción (Tj) no puede leer ni escribir un dato
modificado por otra transacción (Ti) hasta que éste
se comprometa
?Evita la lectura sucia (dirty read)
?La transacción Tk puede leer o modificar un dato
leido antes por Tj antes de comprometerlo
?Puede ocurrir el problema de la lectura irrepetible
(unrepeatable read)
ctax = iter.numcta( );
mandar_carta(ctax);}
#sql {commit};
...
if (...) # sql {rollback};
Read committed
(unrepeatable read)
Repeatable read
?Se aplica el protocolo de reserva de dos fases con
todo
?Se evita la lectura sucia (dirty read)
?Se evita la lectura irrepetible (unrepeatable read)
?Puede aparecer el problema de la modificación fantasma
cuando hay transacciones que introducen nuevas tuplas. El
predicado no se tiene en cuenta
Transacción T1
#sql iterator MiIter(int dni);
#sql {set transaction
isolation level read committed};
MiIter iter;
#sql iter = { select dni from cuenta
where saldo> 10.000.000};
...
a = iter.dni( );
Transacción T2
...
#sql iter = { select dni from cuenta
where saldo> 10.000.000};
...
Transacción T2
?
#sql {update cuenta
set saldo= saldo - :cant
where numcta= A2345};
#sql {commit};
Repeatable read (ghost update)
Transacción T1
#sql iterator MiIter(int dni);
#sql {set transaction
isolation level repeatable read};
MiIter iter;
#sql iter = { select dni from cuenta
where saldo> 10.000.000};
...
a = iter.dni( );
Transacción T2
...
#sql iter = { select dni from cuenta
where saldo> 10.000.000};
...
Transacción T2
#sql {insert into cuenta values
(A2565, 37,12.000.000, ‘98-4-20’, 2, ‘Matia’)};
#sql {commit};
Serializable
?Se evita la lectura sucia
?Se evita la lectura irrepetible
?Se evita la modificación fantasma
?En el grafo de prioridad, también se almacena
información acerca de los predicados utilizados
?En oracle se usan rollback segments y marcas de tiempo
?Consistencia a nivel de transacción: los datos
utilizados a lo largo de la transacción, son los
comprometidos al comienzo de la misma.
?
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Serializable
Niveles de aislamiento. Resumen
Reserva de
exclusi ón larga
sobre las tuplas
Transacción T1
update cuenta set saldo= saldo * 2 where numcta= 2
Transacción T2
set transaction isolation level serializable
update cuenta set saldo = saldo + 20 where numcta= 1
Reserva
compartida larga
sobre las tuplas
Reservas de exc.,
compart. y sobre
predicados largas
Read uncommited
NO
NO
Read commited
SI
NO
NO
select * from cuenta
Repeatable read
SI
SI
NO
commit
Serializable
SI
SI
NO
insert into cuenta values (4, …)
update cuenta set saldo = saldo + 30 where numcta = 3
select * from cuenta
update cuenta set saldo = saldo + 30 where numcta = 2
Reserva larga = al comprometerse la transacción se libera la reserva (vs se
libera la reserva nada m ás terminar de usar el dato)
Error de pérdida de la serialización:
ORA-08177
No todos los SGBD ofrecen todos los niveles de aislamiento
Oracle: Read committed, Serializable
DB2: Read uncommited, Read commited, repeatable
read & serializable
Reservas explícitas
LOCK TABLE
Reservas explícitas
SELECT…FOR UPDATE
? Sintaxis:
?Sintaxis:
LOCK TABLE Tabla/Vista IN modo -reserva MODE [NOWAIT]
?Reserva explícita de una tabla completa
?Sin NOWAIT la transacción se queda en espera
hasta lograr la reserva
SELECT … FOR UPDATE [OF tabla] [NOWAIT]
? Nivel de consistencia de transacciones
? Las tuplas encontradas se reservan una a una, pero todas a la vez y
en modo exclusivo
? Si una tupla ya está reservada
? Con NOWAIT ? Se envía un mensaje de error al momento
? Sin NOWAIT? La transacción se queda en espera hasta que se libera la reserva
? Si es en un iterador al llenarlo se reservan las tuplas (y no cuando se hace
next)
Clave
Otras reservas
compatibles
Consistencia nivel de
transacción
ROW SHARE
IS
IS, IX, S, SIX
?
ROW EXCLUSIVE
IX
IS, IX
?
SHARE
S
IS, S
?
SIX
IS
?
X
--
?
Modos de reserva
SHARE ROW EXCLUSIVE
EXCLUSIVE
SELECT ... FOR UPDATE.
Ejemplo
#sql libres = {select asientos from espectaculo where cod = 34
System.out.println (libres + “ asientos disponibles. “+
”¿Cuántas entradas quiere?”);
cant = entrada.readLine( );
if (cant<= libres)
#sql {update espectáculo
set asientos = asientos - :cant
where cod = 34};
else { … }
#sql {commit};
select nombre, saldo
from cuenta natural join cliente
where ciudad= ‘Durango’
for update of Cuenta
for update
}
? Para liberar la reserva, se espera al commit o al rollback
de trabajar con el iterador)
(y no cuando se acaba
? No se pueden utilizar: funciones agregadas, la cláusula group by, ni
distinct
? Se pueden reservar varias tablas de una consulta con Join
Definición y verificación de
restricciones
CONSTRAINT nombre-restricción
...
[NOT] DEFERRABLE [INITIALLY [DEFERRED | IMMEDIATE] ]
? Not Deferrable (No diferible)
? La comprobación de la restricción se realiza cada vez que se ejecuta una
sentencia SQL en la transacci ón.
? Deferrable (Diferible)
? La comprobación de la restricción se puede diferir hasta el punto en el
que se compromete la transacción
? initially deferred, initially immediate:
? Se indica cuándo se realizará la comprobación de las restricciones
diferibles
? Initially immediate
? Se comprueba cada vez que se ejecuta una sentencia SQL
Reserva las cuentas de los clientes de Durango
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
Desarrollo de Bases de Datos
Cambio de las definiciones de
restricciones
SET CONSTRAINTS [ALL | murriztapen_izena {, urriztapen_izena}]
[DEFERRED | IMMEDIATE]
? En general las restricciones serán diferibles
? La comprobación de las restricciones diferidas se retrasa
hasta que se haga explícito #sql {set ... immediate} o hasta
comprometer la transacción.
? Al comprometer una transacción que tiene las
restricciones diferidas, el sistema las pasa al estado
immediate y se hace la comprobación.
?Si se viola una restricción, se termina la transacción (rollback) y
se envía un mensaje de error
Restricciones. Ejemplos
#sql {insert into alumno values (23, …)};
...
#sql {commit};
create table alumno(…
constraint matriculado check dni in (select dni from matricula)
not deferrable)
insert ...
commit
create table alumno(…
constraint matriculado check dni in (select dni from matricula)
deferrable initially immediate )
insert ...
Restricciones. Ejemplos
#sql {insert into alumno values (23, …)};
...
#sql {commit};
create table alumno(…
constraint matriculado check dni in (select dni from matricula)
deferrable initially deferred )
insert ...
commit
insert ...
set constraint …
immediate
commit
Oscar Díaz - Facultad de Informática - Universidad del País Vasco / Euskal Herriko
Unibertsitatea
commit