Discussion :

[mnitu]

...
Mais je suis d'accord que le NO WAIT va invalider trop de transactions...

C’est quoi le trop ? 1, 100, 1000 ou quoi ? Avez-vous des retours réels ? Demandez a un utilisateur ce qu’il préfère : appuyer sur un bouton et rester comme ça dans l’air sans aucune information de ce qui se passe ou avoir un message qui lui dit que sa transaction ne peut pas aboutir pour l’instant et de réessayer plus tard.

@pachot
Vous faite confusion entre ré-saisir une transaction et ré-valider une transaction.

C’est pire que le deadlock

Oui ça doit être ça ! C’est la raison qui a conduit Oracle à générer une alerte un cas de deadlock mais pas pour une pauvre ORA-000054.

[Waldar]

J'ai peur que nous perdions un peu le fil pour rico35 avec tous ces messages et toutes ces possibilités, alors que le sujet est "simple" et "classique".

Y a-t-il un volontaire pour faire une synthèse objective des différentes solutions évoquées dans le sujet (trigger, modélisation, vue matérialisée, select for update) avec les pour et les contre ?

[mnitu]

...une synthèse objective des différentes solutions...

Ca va être assez difficile.
Entre temps j’ai préparé l’exemple qui utilise les vues matérialisées pour qu’on y voie mieux comment ça pourrait tourner et quel est le comportement de l’application avec cette solution.
Voilà la partie mise en place d’abord

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> create table s_dating_session (
  2    id_session                  number primary key,
  3    nom_session                 varchar2(10),
  4    max_inscription             number(2)
  5  )
  6  /
 
Table created
SQL> create table s_dating_eleve_session (
  2    id_eleve                    number,
  3    id_session                  number references s_dating_session,
  4    Constraint Pk_s_dating_eleve_session Primary Key (id_session, id_eleve)
  5  )
  6  /
 
Table created
SQL> create materialized view log on s_dating_session with rowid, sequence (id_session, max_inscription) including new values
  2  /
 
Materialized view log created
SQL> create materialized view log on s_dating_eleve_session with rowid, sequence (id_session)  including new values
  2  /
 
Materialized view log created
SQL> create materialized view mv_dating
  2    refresh fast on commit
  3    as
  4    select ds.id_session, ds.max_inscription, count(*) as encour_inscrit
  5      from s_dating_session ds, s_dating_eleve_session des
  6     where ds.id_session = des.id_session
  7     group by ds.id_session, ds.max_inscription
  8  /
 
Materialized view created
SQL> alter table mv_dating add primary key (id_session)
  2  /
 
Table altered
SQL> alter table mv_dating add constraint ck_encour_inscrit_0_max check (encour_inscrit between 0 and max_inscription)
  2  /
 
Table altered
SQL> insert into s_dating_session values (1, 'TEST1', 2)
  2  /
 
1 row inserted
SQL> insert into s_dating_eleve_session values (10, 1)
  2  /
 
1 row inserted
SQL> commit
  2  /
 
Commit complete
 
SQL>

Nous avons donc déjà une inscription faite.
Session 1

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> insert into s_dating_eleve_session values (20, 1)
  2  /
 
1 row inserted
 
SQL> REM des requêtes à exécuter plus bas avant commit;

Session 2

Code :

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

1
2
3
4
5
 
SQL> insert into s_dating_eleve_session values (30, 1)
  2  /
 
1 row inserted

Déjà on peut constater qu’il n’y a pas de blocage de la deuxième session comme dans l’exemple utilisant les triggers. Ca devrait être un bon signe pour le deadlock aussi.
On commit dans la première session et ensuite dans la deuxième
Session 2

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 
SQL> commit;
 
commit
 
ORA-12008: erreur dans le chemin de régénération de la vue matérialisée
ORA-02290: violation de contraintes (MNI.CK_ENCOUR_INSCRIT_0_MAX) de vérification
 
SQL>

Maintenant juste une petit remarque regardez le résultat de ces requêtes après avoir insère la ligne dans la première session mais avant de faire le commit :
Session 1

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> insert into s_dating_eleve_session values (20, 1)
  2  /
 
1 row inserted
 
SQL> Select * from s_dating_eleve_session
  2  /
 
  ID_ELEVE ID_SESSION
---------- ----------
        10          1
        20          1
 
SQL> Select * from mv_dating
  2  /
 
ID_SESSION MAX_INSCRIPTION ENCOUR_INSCRIT
---------- --------------- --------------
         1               2              1
 
 
SQL>

La vue matérialisée ne donne pas l’information correcte ni pour la session qui est en train de la modifier. C’est au commit seulement que les choses s’arrangent.

[mnitu]

Voilà aussi la solution applicative. La partie mise en place (normalement ça devrait être une procédure dans un package):

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
 
create table s_dating_session (
  id_session                  number primary key,
  nom_session                 varchar2(10),
  max_inscription             number(2)
)
/
create table s_dating_eleve_session (
  id_eleve                    number, 
  id_session                  number references s_dating_session,
  Constraint Pk_s_dating_eleve_session Primary Key (id_session, id_eleve)
)
/
insert into s_dating_session values (1, 'TEST1', 2)
/
insert into s_dating_eleve_session values (10, 1)
/
commit
/
Create Or Replace Procedure Inscription (
  id_eleve        In  s_dating_eleve_session.id_eleve%Type,
  id_session      In  s_dating_eleve_session.id_session%Type
) Is
  max_inscription     s_dating_session.max_inscription%Type;
  encour_inscrit      Pls_Integer;
  --
  RESERVE             Exception;
  Pragma Exception_Init(RESERVE, -00054);
Begin
  Select Nvl(sd.max_inscription,0),
         Nvl((Select Count(*)
                From s_dating_eleve_session des
               Where des.id_session = sd.id_session 
         ),0) encour_inscrit
    Into Inscription.max_inscription,
         Inscription.encour_inscrit         
    From s_dating_session sd
   Where sd.id_session = Inscription.id_session
     For Update Nowait;
   --
   If max_inscription < encour_inscrit + 1
   Then
     Raise_Application_Error(-20000,'Inscription impossible le nombre de place disponible a été épuisé.');
   End If; 
   --
   Insert Into s_dating_eleve_session 
          (id_eleve,
           id_session
          )  
   Values (Inscription.id_eleve,
           Inscription.id_session           
          );            
Exception
  When NO_DATA_FOUND Then
    Raise_Application_Error(-20000,'L''identifiant de la session est inexistant.');
  When RESERVE Then
    Raise_Application_Error(-20000, 'L''enregistrement est actuelement verrouilé par un autre utilisateur. Réessayer plus tard.');
End;
/

Comme l’autre fois une inscription a été déjà saisie.
Session 1

Code :

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

1
2
3
4
5
6
7
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> exec Inscription(20,1)
 
PL/SQL procedure successfully completed

Session 2

Code :

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

1
2
3
4
5
6
7
8
 
SQL> exec Inscription(30,1)
 
begin Inscription(30,1); end;
 
ORA-20000: L'enregistrement est actuelement verrouilé par un autre utilisateur. Réessayer plus tard.
ORA-06512: à "MNI.INSCRIPTION", ligne 38
ORA-06512: à ligne 2

Commit dans la session 1 suivi de la revalidation de la session 2

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SQL> r
 
begin Inscription(30,1); end;
 
ORA-20000: Inscription impossible le nombre de place disponible a été épuisé.
ORA-06512: à "MNI.INSCRIPTION", ligne 24
ORA-06512: à ligne 2
 
SQL>

Remarque : il ne faut pas oublier que la modification de la zone max_inscription implique aussi de contrôler la règle de gestion.

[mnitu]

Pour ceux qui sont tentés par la simplicité de la solution des vues matérialisées : faite une trace SQL étendue pour avoir un aperçu du formidable boulot qui s’exécute derrière la scène surtout après le commit.

Code :

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

1
2
3
4
5
6
7
 
exec dbms_session.session_trace_enable(true, true)
INSERT INTO s_dating_eleve_session VALUES (20, 1)
/
commit
/
exec dbms_session.session_trace_disable

1 session in tracefile.
17 user SQL statements in trace file.
102 internal SQL statements in trace file.
119 SQL statements in trace file.

[Waldar]

Merci mnitu pour ces deux solutions !
J'essaierai de jeter un œil à la solution de SQLPro, mais ce ne sera pas avant deux ou trois jours.

[skuatamad]

Mnitu, je pense que le mieux c'est FOR UPDATE WAIT X :
- pas de deadlock
- l'utilisateur n'est pas suspendu indéfiniment

Et au moins quand l'utilisateur se prend une ORA-30006 (resource busy; acquire with WAIT timeout expired), c'est pas juste parce qu'il est arrivé 0.5sec trop tôt.

[mnitu]

Tom Kyte dans Oracle magazine November/December 2012

Here is another situation. Suppose you have a business rule that says, “The total number of employees in a department cannot exceed 100.” How do you enforce that? You have to make sure that when you make an insert into the EMP table, no one else is inserting into that same department. You have to serialize. You might use a DBMS_LOCK (user-defined lock) to do this, you might use the LOCK TABLE command, you might use a select * from dept where deptno=x for update nowait to serialize at the department level, and so on. But you need a manual lock. Automatic locking covers 80 percent of everything you need. You need manual locking for the rest to ensure logical data consistency.