BDSI - RafaC - 2009/2010

Práctica 2: Transacciones en Oracle. Solución

1 Tablas de la práctica

2 Modos de consistencia 

3 Niveles de aislamiento

4 Bloqueos

5 Deadlock (interbloqueo)

6 Rollback y Savepoints


1 Tablas de la práctica


Entrar en Hoja de Trabajo de SQLPlus con nuestro grupo, y copiar las siguientes instrucciones que usaremos en el resto de la práctica:
create table taviones(tipo varchar(15) primary key, capacidad int);
create table tvuelos(id varchar(10) primary key, tipoavion references taviones, pasajeros int, destino varchar(20) );
create table tpasajeros(nombre varchar(20), vuelo references tvuelos, primary key(nombre,vuelo));
insert into taviones values ('Airbus A300',120);
insert into taviones values ('Boeing 707',210);
insert into taviones values ('Douglas DC-9',190);
insert into tvuelos values ('LHE 100','Airbus A300',110,'Roma-Fiumicino');
insert into tvuelos values ('IBE 398','Boeing 707',209,'Barcelona-El Prat');
commit;

Las tablas sirven para reflejar vuelos realizados por tipos de aviones. Se supone que el número de pasajeros siempre debe ser menor que la capacidad del avión.

Ahora vamos a abrir una segunda hoja de trabajo de SQL*Plust en el mismo ordenador, usando nuestro mismo usuario. Cada consola representa un terminal de una agencia de viajes, que podemos imaginar situados en sitios diferentes. Para simplificar llamaremos a las dos agencias A1 y A2: la A1 es en la que hemos creado las tablas e  insertado las filas, la A2 la que hemos creado a continuación

2 Modos de consistencia: Read Write/Read Only

El modo read/write es que se tiene por defecto en Oracle. Vamos a ver cómo funciona. Suponemos que en el terminal A1 se quiere saber en primer lugar el número total de vuelos que hay y luego desglosar este total por clases. (Al comienzo de cada secuencia de comandos se indica el terminal en el que se debe introducir).

  1. A1:  select tipoavion,count(*) from tvuelos group by tipoavion;
  2. A2:  insert into tvuelos values ('TWA','Airbus A300',89,'Madrid-Barajas');
  3. A1: select tipoavion,count(*) from tvuelos group by tipoavion;
  4. A2: commit;
  5. A1: select tipoavion,count(*) from tvuelos group by tipoavion;

Pregunta ¿Qué diferencias observas entre los resultados en 3 y 5? ¿Por qué?
Respuesta: A1 sólo ve los cambios que hace A2 cuando A2 hace commit, es decir cuando finaliza su transacción.

Probamos ahora con Read Only:

  1. A1:  commit;
    set transaction read only;
    select tipoavion,count(*) from tvuelos group by tipoavion;
  2. A2:  insert into tvuelos values ('KLM','Douglas DC-9',21,'Las Palmas');
    commit;
  3. A1: select tipoavion,count(*) from tvuelos group by tipoavion;

Pregunta ¿Qué cambios se producen con respecto al caso anterior?
Respuesta:A1 no ve los cambios que hace A2 incluso aunque haga A2 hace commit

Probamos ahora en A1 (que está en modo Read Only):

insert into tvuelos values ('KLM','Douglas DC-9',21,'Las Palmas');

Pregunta ¿Qué resultado se obtiene?
Respuesta:
En el modo read/Only no se permite hacer modificaciones


Acabamos dejando la opción por defecto:

A1:  commit;
set transaction read write;
select tipoavion,count(*) from tvuelos group by tipoavion;

3 Niveles de aislamiento: Serializable/Read Committed

El comando set autocommit indica si se realizará commit (grabación física en la base de datos) tras cada instrucción (si está a ON) o no (cuando está a OFF). Aunque a menudo se pone  a ON, por defecto en Oracle está a OFF, tal y como necesitamos para poder controlar las transacciones de forma manual. Para comprobarlo escribir en cualquiera de los dos terminales.

show autocommit;

que debe mostrarnos OFF

Nosotros siempre vamos a trabajar con el valor autommit a off, para poder indicar manualmente el final de las transacciones.

Otro parámetro de configuración que nos va a interesar cuando trabajamos con sesiones concurrentes es el nivel de aislamiento de la sesión. A mayor nivel de aislamiento menos posibilidad de que se produzcan interacciones, por tanto mayor seguridad pero también menor grado de concurrencia. Oracle incluye dos niveles de aislamiento:

Para ver el efecto del nivel de aislamiento vamos a realizar los siguientes pasos:

  1. Escribir (el A2: del principio indica que se refiere al terminal 2 y no debe teclearse):
     A2: commit;
    alter session set isolation_level = SERIALIZABLE;
    Nota: la instrucción commit finaliza una transacción escribiendo su resultado en disco y comienza automáticamente una transacción nueva. Aquí se utiliza para quealter session sea la primera instrucción de una transacción, condición requerida por Oracle para poder cambiar el valor de isolation_level.
  2. Escribir en el terminal A1:
    A1: update tvuelos set pasajeros=50 where id='LHE 100';
    select * from tvuelos;
  3. A2: update tvuelos set pasajeros=0 where id='LHE 100';
    select * from tvuelos;
    Pregunta: ¿qué ocurre? ¿se debe al modo serializable?
    Respuesta:A2 queda bloqueado. Esto se debe a que update bloquea filas. Cuando A1 hace update bloquea, A2 intenta bloquear lo mismo y queda en espera de que A1 desbloquee. Este comportamiento no está relacionado con serializable.

  4. Grabamos los cambios en A1::
    A1: commit 
    Pregunta: ¿qué ocurre en A2? ¿se debe al modo serializable?
    Respuesta:Ahora A2 se desbloquea, y da un error al intentar hacer su update porque la operación se ha visto interrumpida por el update de A1 y por tanto la transacción no puede verse como 'serie', es decir como operaciones consecutivas no influidas por otras sesiones.

  5. Escribir::
    A2: commit;
    alter session set isolation_level = READ COMMITTED;
    update tvuelos set pasajeros=0 where id='LHE 100';
    commit;
  6. Repetir los pasos 2,3,4 y 5
     Pregunta: ¿qué cambios ocurren y por qué?
    Respuesta:Ahora A1 puede hacer su update tras el de A2 porque no exige que se hagan de forma serializable.

Como se ve el nivel SERIALIZABLE no es interesante cuando se quiere trabajar en sistemas en los que se prentende que más de un usuario realice cambios en la base de datos.

4 Bloqueos

Supongamos que ahora llegan 2 clientes, uno a cada agencia, y que ambos quieren reservar plaza en el vuelo "IBE 398". El empleado de cada agencia mira a ver si el vuelo aún está libre y si lo está indica que ha llegado un nuevo pasajero, incrementando por tanto el número de pasajeros en el vuelo. Suponemos además que en la segunda agencia se van realizando las acciones un poco más tarde.

Simulamos esta situación escribiendo cada instrucción en el terminal correspondiente:

  A1   A2

Pregunta:  escribir una consulta para ver si quedan sitios libres en el vuelo IBE 398 (sugerencia: usar tvuelos y taviones). La consulta debe mostrar por pantalla la capacidad del avión y el número de plazas ocupadas

Respuesta:select capacidad,pasajeros
from tvuelos,taviones
where tvuelos.tipoavion=taviones.tipo and ID='IBE 398';

La consulta nos indica que queda una plaza

-- de momento aquí no se hace nada

 Aquí se está informando al viajero de que queda una plaza

 

-- teclear la misma consulta para ver si quedan vuelos libres en el vuelo IBE 398

 

Respuesta:select capacidad,pasajeros
from tvuelos,taviones
where tvuelos.tipoavion=taviones.tipo and ID='IBE 398';

La consulta nos indica que queda una plaza

-- si hay sitio libre incrementar con updateel número de plazas en el vuelo

Respuesta : update tvuelos set pasajeros=pasajeros+1
where  ID='IBE 398';

 

 Aquí se está informando al viajero de que hay sitios libres

 

 

-- Ejecutar el mismo update que en el caso anterior
update tvuelos set pasajeros=pasajeros+1
where  ID='IBE 398';

 


El update en A2 se queda bloqueado por el primer update de A1, ya que ambos intentan acceder a la misma fila y update es una sentencia que bloquea para evitar inconsistencias. Para que la sesión A2 continue es necesario hacer commit en A1. Finalmente hacer commit en A2. La segunda operación también se ha realizado con éxito, pero sólo había una plaza en el avión. ¡¡Acabamos de vender un billete de avión de más!!

El problema viene porque entre el select y el commit de una sesión, la otra no debería poder acceder a los valores. Esto se consigue con la variante de select select .... for update nowait que indica que hay que bloquear las filas seleccionadas hasta que se haga commit. El nowait es opcional y sirve para que en caso de que alguna de las filas ya esté bloqueada se devuelva un error. De otra forma lo que hace es reintentarlo y podemos llegar a un interbloqueo.

Antes de probarlo devolvemos el valor de pasajeros a su estado inicial, tecleando en cualquiera de las 2 sesiones:

update tvuelos set pasajeros=209 where id='IBE 398';
commit;

Ahora probamos la misma secuencia de comandos, pero añadiendo for update:

  A1   A2

-- consulta de la pregunta 2, pero incluyendo update al final

select capacidad,pasajeros
from tvuelos,taviones
where tvuelos.tipoavion=taviones.tipo and ID='IBE 398'
for update nowait;

La consulta nos indica que queda una plaza
-- de momento aquí no se hace nada

 

 

-- teclear la misma consulta para ver si quedan vuelos libres en el vuelo IBE 398

-- no olvidar incluir el for update no wait
select capacidad,pasajeros
from tvuelos,taviones
where tvuelos.tipoavion=taviones.tipo and ID='IBE 398'

Oracle nos da error: ORA-00054: recurso ocupado y obtenido con NOWAIT especificado

 Por tanto aquí no podemos informar al usuario de que hay una plaza

-- si hay sitio libre indicar con update que va un pasajero más

update tvuelos set pasajeros=pasajeros+1
where  ID='IBE 398';
commit;

 

 Si repetimos la consulta con for update no wait obtendremos el mismo error

 

 

Si repetimos la consulta tendremos ahora que vemos que no hay plazas libres.

Observamos que ahora no nos deja ejecutar el segundo select. La razón es que la fila está bloqueda por A1 y A2 no puede acceder a ella. En la agencia A2 tendrán que esperar a que la agencia A1 acabe su operación, y así se evitarán inconsistencias en la base de datos.

 

Pregunta. : Estamos en la agencia A1. Llega un cliente (de nombre Bertoldo, por ejemplo), que quiere un billete para el vuelo LHE 100, si no está completo. Escribir las sentencias que usaríamos para comprobar que hay sitio, que Bertoldo no tiene ya un billete para ese vuelo (la compañía impide comprar varios billetes a la misma persona para el mismo vuelo) y para, suponiendo que se cumplen estas condiciones apuntar que queda un sitio menos y grabar en tpasajeros que Bertoldo va a volar en ese vuelo. Queremos asegurarnos de que no se venden billetes de más.

-- vemos si hay plazas libres, y de paso bloqueamos la venta de plazas.
select capacidad,pasajeros
from tvuelos,taviones
where tvuelos.tipoavion=taviones.tipo and ID='LHE 100'
for update nowait;

-- vemos si Bertoldo ya tiene plaza :

select * from tpasajeros
where nombre='BERTOLDO' and vuelo='LHE 100' for update nowait;

-- como vemos que hay plazas y que ya está le vendemos una plaza
insert into tpasajeros values ('BERTOLDO', 'LHE 100');
update tvuelos set pasajeros = pasajeros +1 where id='LHE 100';
commit;

OJO: con el for update no wait no nos aseguramos de que no se pueda insertar a Bertoldo desde otro terminal, pero eso no importaría porque en todo caso hay que insertarlo y la tabla no admite duplicados

5 Deadlocks

Se cononce como Deadlock o interbloqueo el proceso en el que dos operaciones concurrentes quedan esperando cada una a la otra. Probar la secuencia siguiente en los terminales A y B

-- A
CREATE TABLE deadlock (
id NUMBER, fld VARCHAR2(1));

INSERT INTO deadlock VALUES (1,'A');
INSERT INTO deadlock values (2,'B');
COMMIT;

SELECT * FROM deadlock;

UPDATE deadlock
SET fld = 'M'
WHERE id = 1;

-- B
UPDATE deadlock
SET fld = 'N'
WHERE id = 2;

-- A
UPDATE deadlock
SET fld = 'X'
WHERE id = 2;

-- B
UPDATE deadlock
SET fld = 'Y'
WHERE id = 1;

Pregunta:  ¿Qué ocurre?  ¿Por qué?

6 Rollbacks y Savepoints

La instrucción rollback permite al usuario de Oracle acabar la transacción sin éxito. De esta forma ninguno de los cambios realizados tras el último commit se llegará a grabar físicamente en la base de datos. Veamos un ejemplo (esta sección se puede probar en cualquiera de los dos terminales):

Supongamos que estamos actualizando la base de datos porque tenemos un nuevo pasajero:

update tvuelos set pasajeros=pasajeros +1 where id='LHE 100';
insert into tpasajeros values('Herminia','LHE 100');
select * from tvuelos;
select * from tpasajeros;

Pero en último momento Herminia se arrepiente. ¿Cómo deshacemos los cambios? Como aún no hemos hecho commit, escribimos simplemente:

rollback;
select * from tvuelos;
select * from tpasajeros;

Nota: Toda transacción debe acabar con commit o rollback. Si se sale del sistema el SGBD ejecutará commit automáticamente para acabar la última transacción.

rollback resulta especialmente útil para acabar con un problema que ya nos hemos encontrado: el que los scripts se ejecuten a medias debido a un error en una de sus instrucciones. Cuando esto ocurre nos encontramos con el problema de que parte de las instrucciones sí han tenido éxito, por lo que despúes de corregir el error nos da nuevos errores debidos afilas duplicadas, etc. Esto se puede evitar simplemente usando rollback cuando vemos que el script ha fallado.

Ejemplo: supongamos que ejecutamos todas estos insert:

insert into taviones values('BAE HARRIER', 2);
insert into taviones values('Concorde', 240);
insert into tvuelos values('UNA 100', 'Concorde',10, 'Barajas');
insert into tvuelos values('UTA 600', 'Boeinj', 20,'Pekín');
insert into tpasajeros values('Nicasio','LHE 100');

---- vemos en la salida de oracle que hay un error ... no queremos que se apliquen los cambios

rollback;

Dentro de una transacción se pueden además definir puntos intermedios de rollback. Esto se hace introduciendo instrucciones de la forma:

savepoint nombre;

dentro del script. Posteriormente si sabemos que las intrucciones anteriores al punto XXXX estaban bien y no queremos repetirlas podemos escribir:

rollback to XXX;

commit;

Pregunta: Introducir un savepoint en la transacción formada por :

 
insert into taviones values('BAE HARRIER', 2);
insert into taviones values('Concorde', 240);
insert into tvuelos values('UNA 100', 'Concorde',10, 'Barajas');
insert into tvuelos values('UTA 600', 'Boeinj', 20,'Pekín');
insert into tpasajeros values('Nicasio','LHE 100');

 de forma que si hay un error al insertar en tvuelos pero no en taviones se puedan eliminar los cambios en tvuelos y tpasajeros pero se puedan grabar los de taviones. Comprobar que se obtiene el resultado deseado.
Respuesta:
insert into taviones values('BAE HARRIER', 2);
insert into taviones values('Concorde', 240);
savepoint tururu;
insert into tvuelos values('UNA 100', 'Concorde',10, 'Barajas');
insert into tvuelos values('UTA 600', 'Boeinj', 20,'Pekín');
insert into tpasajeros values('Nicasio','LHE 100');

Pregunta:  Probar a hacer:

delete from tpasajeros;
drop table tpasajeros;
rollback;
select * from tpasajeros;

 ¿Qué ocurre?  ¿Cómo puedes explicarlo?

Respuesta:
Rollback no puede deshacer drop table, solo insert, delete y update. Por eso la tabla tpasajeros queda eliminada a pesar del rollback.


Resumen de la política de bloqueos por defecto de ORACLE

Una sentencia SELECT normal no bloquea filas.

Las sentencias INSERT, UPDATE o DELETE realiza un bloqueo ROW EXCLUSIVE de las filas afectadas por el WHERE.

La sentencia SELECT ... FOR UPDATE NOWAIT realiza un bloqueo ROW EXCLUSIVE de las filas afectadas por el WHERE.

Las sentencias COMMIT y ROLLBACK desbloquean las filas bloqueadas anteriormente dentro de la transacción actual.

También se pueden bloquear tablas completas con la instrucción LOCK TABLE

Aunque una fila este bloqueada (por otra transacción), siempre podemos hacer una SELECT sobre esa fila. Los valores retornados son los anteriores al bloqueo.

Las sentencias UPDATE Y DELETE pueden provocar o sufrir esperas si hay conflictos con otra transacción.



2009-2010 Rafael Caballero