7.5.- Interbloqueos

Ejemplo de interbloqueo entre dos tablas con cancelación de bloqueo automático a nivel de transacción.

Intentamos crear dos tablas con referencias cruzadas, es decir, la primera hace referencia a la segunda que aún no está creada y viceversa. Vamos a solucionar el problema del interbloqueo considerando la transacción como DEFERRED, es decir la comprobación de las restricciones se hará en diferido.

Si creo la primera tabla y hago referencia a la segunda, se producirá un error, aún no existe la segunda tabla:

CREATE TABLE Gallina(

idGallina number(3) PRIMARY KEY,

idHuevo number(3)  REFERENCES Huevo(idHuevo));

CREATE TABLE Huevo(

idHuevo number(3)  PRIMARY KEY,

idGallina number(3) REFERENCES Gallina(idGallina));

Informe de error -,

Error SQL: ORA-00942: la tabla o vista no existe

00942. 00000 - "table or view does not exist"

*Cause:

Podemos crear ambas tablas sólo especificando cual es la PK en cada una y a continuación ALTER TABLE para añadirle la referencia a la otra tabla.

CREATE TABLE Gallina(

idGallina number(3)  PRIMARY KEY,

idHuevo  number(3) );

CREATE TABLE Huevo(

idHuevo  number(3)  PRIMARY KEY,

idGallina  number(3) );

ALTER TABLE Gallina ADD CONSTRAINT fkRefHuevo

FOREIGN KEY(idHuevo) REFERENCES Huevo(idHuevo);

ALTER TABLE Huevo ADD CONSTRAINT fkRefGallina

FOREIGN KEY(idGallina) REFERENCES Gallina(idGallina);

Intentamos insertar un registro en cada una:

INSERT INTO Gallina VALUES(4, 5);

INSERT INTO Huevo VALUES(5, 4);

COMMIT;

Da error en las órdenes de inserción, ya que al insertar en la primera tabla, aún no tenemos dato en la segunda y exactamente igual al insertar en la segunda.

Si añadimos INITIALLY DEFERRED después de  REFERENCES en la orden ALTER TABLE la comprobación se hará después de commit y aunque aún no existan datos en la segunda tabla nos permitirá insertar el registro en la primera.

ALTER TABLE Gallina ADD CONSTRAINT fkRefHuevo

FOREIGN KEY(idHuevo) REFERENCES Huevo(idHuevo) INITIALLY DEFERRED;

ALTER TABLE Huevo ADD CONSTRAINT fkRefGallina

FOREIGN KEY(idGallina) REFERENCES Gallina(idGallina) INITIALLY DEFERRED;

INSERT INTO Gallina VALUES(4, 5);

INSERT INTO Huevo VALUES(5, 4);

COMMIT;

Las sentencias  para borrar las constraints  y las tablas tras el ejemplo, son:

ALTER TABLE Huevo DROP CONSTRAINT fkRefGallina;

ALTER TABLE Gallina DROP CONSTRAINT fkRefHuevo;

DROP TABLE Huevo;

DROP TABLE Gallina;