Discussion :

[rico35]

Bonjour,

Dans le trigger suivant, je vérifie le nombre de places disponibles avant d'autoriser l'inscription à une session :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
create or replace trigger trg_s_dating_eleve_session
before insert 
on s_dating_eleve_session
for each row
declare
v_nb_eleves number;
v_max_inscription s_dating_session.max_inscription%type;
 
begin
 
  -- nb d'élèves ayant choisi la session
  select count(num_eleve)
  into v_nb_eleves
  from s_dating_eleve_session
  where num_session= :new.num_session;
 
  -- nb de places max
  select max_inscription
  into v_max_inscription
  from s_dating_session
  where num_session= :new.num_session;
 
  -- La session est-elle complète ?
  if v_nb_eleves =  v_max_inscription
    then
    RAISE_APPLICATION_ERROR (-20005, 'Session complète');  
  end if;
end;
/
 
show errors

Manifestement, ce trigger ne fonctionne pas comme je voudrais car en production un utilisateur a réussi à s’inscrire alors que la session était complète : que me conseillez-vous pour gérer ce genre de problématique ?
Merci.

[skuatamad]

Déporte l'encour des inscrits dans une colonne de la table s_dating_session avec une contrainte CHECK BETWEEN 0 AND MAX_INSCRIPTION afin de sérialiser les inscriptions.
Ensuite avec un le trigger sur s_dating_eleve_session lors d'une insertion fait encour_inscrit + 1, et lors d'une suppression encour_inscri - 1.
Et pour les updates gérer les 2 cas.

Ou sinon utilise une vue materialisée (je n'arrive pas trop à savoir si l'approche VM est vraiment conseillée pour des contraintes en prod).

Un peu de lecture sur les triggers :
The Trouble with Triggers
Best way to enforce cross-row constraints

[mnitu]

Le problème avec cet algorithme est qu’il marche bien seulement en mode mono utilisateur. Le problème est lié au niveau d’isolation des transactions qui par défaut chez Oracle est read commited. Cela veut dire que vous ne voyez pas les modifications faite par des autres utilisateurs avant que ces modifications soient validées via commit.

Imaginez dans votre cas qu’il y a seulement une place pour une session. Vous avez posée la question combien des élèves se sont inscrit via votre premier select et la réponse est 0. Vous comparez 0 avec le nombre de place et comme 0 est différent de 1 vous continuez avec la création de l’enregistrement. Mais vous n’est pas le seul à travailler à saisir des inscriptions. Pendant ce temps là votre collègue qui est en train de faire la même inscription dans une autre session reçoit la même réponse 0 parce qu’il ne peut voir votre enregistrement qui à été inséré dans la table mais qui n’a pas encore été validé. Donc lui aussi peut continuer et insérer un deuxième enregistrement. En suite chacun valide et voilà vous est arrivé à violer la restriction que votre code était supposé à implémenter.

Ce qui vous manque en fait pour la bonne réussite est le verrouillage de la table s_dating_session qui devrait empêcher un deuxième utilisateur à poursuivre sa saisie pendant le déroulement de votre transaction. Et ça devrait être votre première chose à faire suivi de l’interrogation du nombre des élèves inscrit.

Une fois l’algorithme correctement défini vous devez renoncer aux triggers pour implémenter ce genre des algorithmes à cause du problème de la table (s_dating_eleve_session) mutante (restriction relaxé en quelque sort en 11g via les triggers composées).

[pachot]

Bonjour,
Une bonne solution, et qui évite de verrouiller toute la table, c'est de rajouter un compteur dans s_dating_session pour compter les inscriptions. Et une contrainte check pour vérifier qu'on ne dépasse pas le max. Ce compteur peut lui être mis à jour par trigger: +1 pour l'insert, -1 pour delete.
Une autre solution pour automatiser ce compteur (et donc éviter des erreurs): une vue matérialisée qui fait le count/group by sur s_dating_eleve_session et la jointure avec s_dating_session et une contrainte check sur la table qui matérialise cette vue.
Cordialement,
Franck.

[SQLpro]

Une façon encore plus simple est de modifier le modèle de données pour :
1) contingenter préventivement les places de manière unique (par exemple un nombre compris entre 1 et le nombre de place)
2) pour chaque place réservée attribuer un n° unique dans cette frange et faire de cette colonne, associée à la clef du domaine de réservation une contrainte d'unicité.

Dans ce cas il n'est plus nécessaire de faire une transaction explicite, ni de s'emmerder avec le pilotage d'un niveau d’isolation adéquat !

Ce sera en sus beaucoup plus performant !

La plupart des problèmes transactionnels complexes se résolvent par une modélisation adéquate...

A +

[rico35]

Bonjour et merci pour vos réponses rapides.

Mnitu, je me suis fait la même réflexion sur la cause du problème. Mais ce qui m’étonne le plus, c’est que l’application web qui permet les inscriptions utilise un seul utilisateur. Par conséquent, il peut y avoir en même temps plusieurs sessions de ce même utilisateur. J’imagine alors que nous sommes bien en mode mono utilisateur comme vous l’avez écrit dans votre première phrase et que donc ça aurait du fonctionner correctement, non ?

Quant aux solutions proposées par skuatamad et pachot, elles vont dans le même sens : c’est rassurant ;o). En revanche, ce qui est moins rassurant, c’est que je ne vois pas bien en quoi gérer un compteur va pouvoir m’aider puisque pour l’incrémenter, je vais devoir faire un select + 1 du compteur or puisqu’on part de l’idée qu’on ne peut lire que ce qui est commité , on va se retrouver avec le problème initial.
En ce qui concerne, les vues matérialisées, je ne les ai jamais manipulées : il faut un début à tout et que je potasse…
Il y a sans doute quelque chose qui m’échappe. J’en demande peut-être beaucoup, mais si vous aviez un exemple, je ne serais pas contre

Pour le moment, j’ai géré mon problème en faisant un commit juste après chaque insert : la probabilité que le problème survienne à nouveau a été abaissée mais ne me semble pas nulle, ai-je malheureusement raison ?

Merci.

[rico35]

Je n'avais pas vu la réponse de SQLPRO (je n'ai pas rafraichi ma page...) et cette solution me semble limpide et très intéressante
N'hésitez cependant pas à répondre à mon précédent message

Merci !

[skuatamad]

Non justement plusieurs sessions c'est multi utilisateurs.

Pour les triggers, pas besoin de sélectionner, il suffit d'updater.
Un rapide exemple ou je n'ai codé que l'INSERT, il faut également gérer le DELETE et l'UPDATE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
SQL> create table s_dating_session (
  2      id_session number,
  3      nom_session varchar2(10),
  4      max_inscription number(2),
  5      encour_inscrit number(2) default 0,
  6      CONSTRAINT ck_encour_inscrit_0_max CHECK (encour_inscrit between 0 and max_inscription))
  7  /
 
Table created.
 
SQL> create table s_dating_eleve_session (id_eleve number, id_session number)
  2  /
 
Table created.
 
SQL> create trigger trg_inscription
  2  before insert on s_dating_eleve_session
  3  for each row
  4  begin
  5      update s_dating_session
  6         set encour_inscrit = encour_inscrit + 1
  7       where id_session = :new.id_session;
  8  end;
  9  /
 
Trigger created.
 
SQL>
SQL> insert into s_dating_session(id_session,nom_session,max_inscription) values (1, 'test1', 2);
 
1 row created.
 
SQL> insert into s_dating_session(id_session,nom_session,max_inscription) values (2, 'test2', 1);
 
1 row created.
 
SQL>
SQL> commit;
 
Commit complete.
 
SQL>
SQL> insert into s_dating_eleve_session values (1,1);
 
1 row created.
 
SQL> insert into s_dating_eleve_session values (1,2);
 
1 row created.
 
SQL> insert into s_dating_eleve_session values (2,1);
 
1 row created.
 
SQL> insert into s_dating_eleve_session values (2,2);
insert into s_dating_eleve_session values (2,2)
            *
ERROR at line 1:
ORA-02290: check constraint (SKUATAMAD.CK_ENCOUR_INSCRIT_0_MAX) violated
ORA-06512: at "SKUATAMAD.TRG_INSCRIPTION", line 2
ORA-04088: error during execution of trigger 'SKUATAMAD.TRG_INSCRIPTION'
 
 
SQL> insert into s_dating_eleve_session values (3,2);
insert into s_dating_eleve_session values (3,2)
            *
ERROR at line 1:
ORA-02290: check constraint (SKUATAMAD.CK_ENCOUR_INSCRIT_0_MAX) violated
ORA-06512: at "SKUATAMAD.TRG_INSCRIPTION", line 2
ORA-04088: error during execution of trigger 'SKUATAMAD.TRG_INSCRIPTION'
 
 
SQL> select * from s_dating_session;
 
ID_SESSION NOM_SESSIO MAX_INSCRIPTION ENCOUR_INSCRIT
---------- ---------- --------------- --------------
         1 test1                    2              2
         2 test2                    1              1
 
SQL>

Et évidemment il faut faire les tests avec 2 sessions (sqlplus par exemple) d'ouvertes pour observer/vérifier les blocages qui interviennent.

Pour les VM c'est plus simple (moins de code) mais comme je suis sur une XE je ne peux pas, d'ailleurs il me semble que les VM ne sont dispos qu'en Entrepise Edition.

[mnitu]

@SqlPro
Je doute que ça changera grand chose au problème, pour moi cette solution ne fait que le déplacer. Mais si vous me fournissez un jeu d’essai complet (create table, insert, etc.) pour dissiper les éventuels malentendus et qu’on y voit clairement de quoi on parle je serai ravi d’apprendre quelque chose de nouveau.

[SQLpro]

Vous avez un exemple de ceci dans mon défunt livre sur SQL :
http://www.amazon.fr/SQL-Fr%C3%A9d%C.../dp/2744011843
Dans un chapitre de 200 pages, un exemple consacré à la recherche de place de théâtre.

Hélas je suis en déplacement et n'ai ni le temps adéquat pour monter un tel exemple, ni Oracle installé sur la machine ou je suis....

A +

[mnitu]

@SQLPro
Il n'y rien de spécifique à Oracle dans votre proposition c'est juste "modifier le modèle de données", n'est pas ça?
J'ai la patience nécessaire d'attendre votre retour.
Sinon pour vous, fournir un jeu d'essaie assez basique ça devrait être un jeu d'enfant (au moins c'est le cas pour moi, voir aussi Skuatamad).

[mnitu]

@Skuatamad
Une chose que je n’aime pas dans votre solution c’est que la deuxième session va rester suspendue derrière la première en attendant le commit ou le rollback.
Session 1 (pas de commit)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SQL> INSERT INTO s_dating_eleve_session VALUES (1,2);
 
1 ligne créée.

Session 2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SQL> INSERT INTO s_dating_eleve_session VALUES (2,2);

et ça reste comme ça dans l'air!
Mais comme de plus ça pourrait provoquer aussi des deadlocks, non!

[Garuda]

Une façon encore plus simple est de modifier le modèle de données pour :
1) contingenter préventivement les places de manière unique (par exemple un nombre compris entre 1 et le nombre de place)
2) pour chaque place réservée attribuer un n° unique dans cette frange et faire de cette colonne, associée à la clef du domaine de réservation une contrainte d'unicité.

J'ai rien compris
Moi aussi la solution m'intéresse. Mais pas jusqu'à dépenser 60 € !

[pachot]

Bonjour,

Une chose que je n’aime pas dans votre solution c’est que la deuxième session va rester suspendue derrière la première en attendant le commit ou le rollback.

C'est justement ça le but: on ne peut pas saisir 2 inscriptions pour la même session en même temps, afin de ne pas dépasser le seuil. Mais c'est mieux que de verrouiller toute la table et empêcher aussi les inscription sur les autres sessions.
Cordialement,
Franck.

[mnitu]

Si le but est assez clair les solutions le sont moins!
La solution qui laisse la session 2 suspendue n’est pas vraiment souhaitable, mais bon on dirait que c'est une question de goût!
Nul besoin de verrouiller toute la table !
Par contre la possibilité d’arriver en situation de deadlock disqualifie cette solution

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an applicationor from issuing incorrect ad-hoc SQL.

[rico35]

@Skuatamad

Merci : cette solution me convient parfaitement car dans mon contexte, on ne peut s'inscrire qu'à une seule session donc le risque de deadlock me semble improbable.

@mnitu @SQLpro
Si vous aviez mieux sous forme d'exemples pour gérer ce cas d'école du problème d'accès concurrent lors de l'insertion d'un nombre max de lignes dans une table, nous sommes preneurs également.

En tout cas, merci à tous pour vos réponses .

[mnitu]

...
cette solution me convient parfaitement car dans mon contexte, on ne peut s'inscrire qu'à une seule session donc le risque de deadlock me semble improbable.

Voilà maintenant l'introduction d'un nouveau concept « programmation orientée contexte » sœur bien connue de la « programmation orientée maintenance »!

Si juste avant de faire l’update vous essayez de réservez l’enregistrement via un select for update nowait vous allez avoir une solution qui ne fait jamais de deadlocks et qui ne suspende pas la deuxième session.

[skuatamad]

Si juste avant de faire l’update vous essayez de réservez l’enregistrement via un select for update nowait vous allez avoir une solution qui ne fait jamais de deadlocks et qui ne suspende pas la deuxième session.

Mais c'est bien sûr ! merci

[pachot]

Bonjour,

Je ne comprends pas la l'idée de faire un select for update nowait avant. Une exception sera levée et l'utilisateur devra re-saisir sa transaction en espérant que ça passe plus tard. C'est pire qu'un deadlock: on sort en erreur dès qu'il y a un verrou.

Ici, il n'y aura pas de deadlock: c'est fait par trigger, donc toujours le même code, les tables sont toujours vérouillées dans le même ordre.

Cordialement,
Franck.

[skuatamad]

Pachot, le deadlock est facilement observable sur sqlplus, après probablement beaucoup plus rare dans l'application, ça dépend peut être de comment l'application est codée. Mais je suis d'accord que le NO WAIT va invalider trop de transactions...

Donc la solution à base de VM présente de nombreux avantages en fait :
- pas de deadlock
- pas besoin du NO WAIT qui peut clairement annuler trop de transaction qui n'auraient pas causées de deadlock.
- pas de code, c'est seulement déclaratif.

Tom Kyte en fait d'ailleurs la promotion sur son site ou sur oracle magazine.

Par contre Laurent Schneider, n'aime pas (ici)

si la vue est invalidée, c'est la catastrophe au niveau de l'intégrité des données.

C'est vrai ce serait la catastrophe, mais qu'est ce qui provoque l'invalidation ?

Qu'en pensez vous ? Le faites vous en prod ? Avez vous régulièrement constaté des VM invalidées ?
Je n'ai pas d'expérience sur ce sujet.