Problème de verrous [Résolu]

[Alain Defrance]

Bonjour,

J'ai un problème de verrous que je ne parviens pas a résoudre (certainement par manque d'expérience avec Oracle).

Tout d'abord j'ai une table PARTICIPATION :

Code :

1
2
3
4
5
6
7
CREATE TABLE PARTICIPATION
(
  COURSE_ID   NUMBER                            NOT NULL,
  SPORTIF_ID  NUMBER                            NOT NULL,
  DOSSARD     NUMBER                            NOT NULL,
  TEMPS       NUMBER(5,2)
);

Cette table sert a définir quel sportif participe a quelle course ainsi que sont temps réalisé (NULL si la course n'as pas encore eu lieu).

Par ailleurs j'ai une table PODIUM :

Code :

1
2
3
4
5
6
CREATE TABLE PODIUM
(
  COURSE_ID    NUMBER                           NOT NULL,
  SPORTIF_ID   NUMBER                           NOT NULL,
  MEDAILLE_ID  VARCHAR2(1 BYTE)                 NOT NULL
);

La table Podium permet de définir une médaille d'or, d'argent ou de bronze.

J'ai une procédure stockée qui permet de vérifier si tout les temps sont enregistré pour une course (et donc savoir si il est possible de faire le podium).

Cette procédure est :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
FUNCTION CHECK_VALID_COURSE (P_COURSE_ID IN NUMBER) RETURN BOOLEAN
	IS
	NB_PARTICIPANT NUMBER;
	NB_TEMPS_SAISI NUMBER;
	BEGIN
		SELECT COUNT(*), COUNT(TEMPS)
		INTO NB_PARTICIPANT, NB_TEMPS_SAISI
		FROM PARTICIPATION
		WHERE COURSE_ID = P_COURSE_ID;
 
		RETURN (NB_PARTICIPANT = NB_TEMPS_SAISI);
	END;

Une autre procédure permet de générer le podium en fonction des temps :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
PROCEDURE GEN_PODIUM_COURSE (P_COURSE_ID IN NUMBER)
	IS
	UN_SPORTIF_ID NUMBER;
	UN_TEMPS NUMBER;
	CPT NUMBER;
	ID_MEDAILLE VARCHAR2(1);
 
	CURSOR LIST_PARTICIPATIONS_BY_TEMPS
		IS
		SELECT SPORTIF_ID, TEMPS
		FROM PARTICIPATION
		WHERE COURSE_ID = P_COURSE_ID
		ORDER BY TEMPS DESC;
 
	BEGIN
		CLEAR_PODIUM_COURSE(P_COURSE_ID);
		CPT := 0;
		OPEN LIST_PARTICIPATIONS_BY_TEMPS;
 
		FETCH LIST_PARTICIPATIONS_BY_TEMPS
		INTO UN_SPORTIF_ID, UN_TEMPS;
 
		WHILE (LIST_PARTICIPATIONS_BY_TEMPS%FOUND AND CPT < 3)
		LOOP
 
			CASE CPT
				WHEN 0 THEN ID_MEDAILLE := 'O';
				WHEN 1 THEN ID_MEDAILLE := 'A';
				WHEN 2 THEN ID_MEDAILLE := 'B';
			END CASE;
 
			INSERT INTO PODIUM (COURSE_ID, SPORTIF_ID, MEDAILLE_ID)
			VALUES (P_COURSE_ID, UN_SPORTIF_ID, ID_MEDAILLE);
 
			FETCH LIST_PARTICIPATIONS_BY_TEMPS
			INTO UN_SPORTIF_ID, UN_TEMPS;
			CPT := CPT + 1;
		END LOOP;
 
		CLOSE LIST_PARTICIPATIONS_BY_TEMPS;
	END;

Tout ceci est testé et sûr.

Maintenant abordons le problème.

Je souhaiterais que la génération des podium soit automatique a la saisie du temps.
Donc qu'un trigger a l'insert et a l'update d'un temps génère le podium.

voici le trigger :

Code :

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE TRIGGER T_CREATE_PODIUM
AFTER INSERT OR UPDATE
ON PARTICIPATION
FOR EACH ROW
BEGIN
	IF (PKG_PODIUM.CHECK_VALID_COURSE(:NEW.COURSE_ID)) THEN
		PKG_PODIUM.GEN_PODIUM_COURSE(:NEW.COURSE_ID);
	END IF;
END;

Le problème est que PKG_PODIUM.CHECK_VALID_COURSE réalise un SELECT sur PARTICIPATION, et que cette dernière est exécuté à l'insert ou mise a jour sur PARTICIPATION.
Oracle me refuse donc son exécution puisque apparemment la table PARTICIPATION est vérouillée.

Comment ferez vous pour contourner ce problème ?
Ça fait un moment que je cherche une solution propre mais je ne trouve rien.

Merci par avance.

[plalm]

En premier lieu, je vérifierai la présence d'un commit après la suppression des lignes au sein de la table PODIUM, delete réalisé par la procedure CLEAR_PODIUM_COURSE appelée par GEN_PODIUM_COURSE.

Mais sans le code de cette procedure CLEAR_PODIUM_COURSE, cest toi qui devra le faire

[Alain Defrance]

Envoyé par plalm

En premier lieu, je vérifierai la présence d'un commit après la suppression des lignes au sein de la table PODIUM, delete réalisé par la procedure CLEAR_PODIUM_COURSE appelée par GEN_PODIUM_COURSE.

Mais sans le code de cette procedure CLEAR_PODIUM_COURSE, cest toi qui devra le faire

Théoriquement les modifications peuvent s'enchainer au cours de la transaction (a moins qu'elle soit remplit, ce qui est loin d'être le cas).
Donc que je commit en début ou en fin ça changera je pense rien.

je n'ai pas détaillé la procédure CLEAR_PODIUM_COURSE parce qu'elle n'apporte rien dans la résolution du problème.

Enfin je peu me tromper, surtout que je me retrouve bloqué

Je tacherais de poster la procédure en question dans la journée (la je l'ai pas sous la main).

Edit : Voila la procédure CLEAR_PODIUM_COURSE

Code :

1
2
3
4
5
6
	PROCEDURE CLEAR_PODIUM_COURSE (P_COURSE_ID IN NUMBER)
	IS
	BEGIN
		DELETE FROM PODIUM
		WHERE COURSE_ID = P_COURSE_ID;
	END;

[mnitu]

Tu n'a pas un problème de verrous (en Oracle le select ne bloque pas), tu a le problème de la table en mutation il me semble, n’est pas vrai ?

[kervoaz]

Bonjour,

Effectivement tu tentes de lire des données en cours de modification.
Pourquoi la procedure CHECK_VALID_COURSE est appellé for each row?
Il ne suffirait pas de verifier si :NEW.SPORTIF_ID is not null :NEW.TEMPS is not null dans le trigger?
Pour pallier en fait à la possibilté de mettre null dans TEMPS.

Cdt

[Alain Defrance]

Bonjour,

Envoyé par kervoaz

Pourquoi la procedure CHECK_VALID_COURSE est appellé for each row?

Parce que pour vérifier que la course soit valide je dois vérifier que toutes les inscriptions sont un temps saisie.

Envoyé par kervoaz

Il ne suffirait pas de verifier si :NEW.SPORTIF_ID=:NEW.TEMPS dans le trigger?

En fait j'ai plus ou moins une solution que je n'ai pas encore eu le temps de tenter mais ça ne saurait tarder.
Cela consisterais a faire une fonction d'insertion et cette dernier exécuterais la vérification ainsi que la génération juste après l'insertion.
Cela présente un désavantage :
Si l'utilisateur de la base de donnée n'utilise pas la fonction, la base de donnée devient sémantiquement incohérente.

Envoyé par kervoaz

Pour pallier en fait à la possibilité de mettre null dans TEMPS.

C'est déjà le cas, mais je souhaite justement que le fait de remplir tout les champs temps d'une course donnée déclenche tout un ensemble de procédure procédant au remplissage du podium.

Merci beaucoup de t'être penché sur mon problème.

[mnitu]

Envoyé par kazou

Bonjour,


Parce que pour vérifier que la course soit valide je dois vérifier que toutes les inscriptions sont un temps saisie.

En fait j'ai plus ou moins une solution que je n'ai pas encore eu le temps de tenter mais ça ne saurait tarder.
Cela consisterais a faire une fonction d'insertion et cette dernier exécuterais la vérification ainsi que la génération juste après l'insertion.
Cela présente un désavantage :
Si l'utilisateur de la base de donnée n'utilise pas la fonction, la base de donnée devient sémantiquement incohérente.

C'est déjà le cas, mais je souhaite justement que le fait de remplir tout les champs temps d'une course donnée déclenche tout un ensemble de procédure procédant au remplissage du podium.

Merci beaucoup de t'être penché sur mon problème.

Et si quelqu’un remet le temps à NULL ta base reste cohérente ?

[Alain Defrance]

Envoyé par mnitu

Et si quelqu’un remet le temps à NULL ta base reste cohérente ?

Non, mais la base de donnée est sensé être saisie au travers de fonction, et aucune fonction est prévu a cet effet.

Le système de procédure / fonction stocké garanti une intégrité respecté.

[jmguiche]

La première idée qui me vient est l'utilisation de dbms_scheduler.
A chaque insert, tu lances un batch (dbms_scheduler) pour un pouilleme de temps plus tard.

Je n'ai jamais testé le lancement de batch dans un trigger.

Pour faire "sérieux" et gérer des interactions massive avec la base, à chaque insert tu tests s'il y a un batch en attente, si non, tu en mets un.

[kervoaz]

La fonction d'insertion est une bonne chose du point de vue architecture.
La modification du modèle se fait via des fonctions de la couches supérieures.
De plus les triggers peuvent vite avoir des effets pervers lors de traitement de masse.

[Alain Defrance]

Envoyé par kervoaz

La fonction d'insertion est une bonne chose du point de vue architecture.
La modification du modèle se fait via des fonctions de la couches supérieures.
De plus les triggers peuvent vite avoir des effets pervers lors de traitement de masse.

Je pense que je vais tester cette solution dans la soirée de demain soir, je pense que c'est la solution qui m'économisera le plus de nuit de sommeil

Je vous tiens au courant

En tout cas merci beaucoup pour vos avis.

[Alain Defrance]

La solution de l'appel dans la procédure a porté ses fruits.

Merci a tous d'avoir contribué a solutionner le problème.