Discussion :

[mnitu]

...
Dans ton exemple, un delete sur t_mere ou un update de id_mere, même s'ils ne touchent aucune ligne, aurait été bloqué et aurait en outre bloqué toute nouvelle modification sur la table t_fils, jusqu'à ce que la transaction initiale se termine.
...

Merci de votre intervention mais, je connaît comment ça fonctionne sinon je n'aurais pas choisi ces exemples, n'est pas vrai ? Par ailleurs, les applications qui possèdent un peu de bon sens évitent de modifier la clé primaire vu qu'elle est immuable, donc ...

En conclusion il faut réfléchir un peu avant de se précipiter sur l'indexation des clés étrangères.

[pachot]

Merci de votre intervention mais, je connaît comment ça fonctionne sinon je n'aurais pas choisi ces exemples, n'est pas vrai ? Par ailleurs, les applications qui possèdent un peu de bon sens évitent de modifier la clé primaire vu qu'elle est immuable, donc ...

En conclusion il faut rafraichir un peu avant de se précipiter sur l'indexation des clés étrangères.

Oui... je voulais juste aller dans le même sens: n'indexer que quand c'est nécessaire, et se souvenir (i.e. documenter) la raison de la création de l'index.
Cordialement,
Franck.

[Mohamed.Houri]

Il faut nuancer un peu ça sinon il y aura des indexes par tout!

Il devient nécessaire d’indexer les contraintes d’intégrité du type FK uniquement lorsqu’on delete/update/merge la table mère dans un environnement OLTP multiutilisateurs et donc concurrent. Cette indexation est dictée par les soucis d’éviter des verrous (lock) et des inters blocages (deadlocks). Et, même si on est amené à indexer une FK pour lesdits soucis, il faut quand même s’assurer qu’il n’existe pas déjà un index qui couvre ces FK.

Ce problème de verrous et d’inters-blocage écarté, il n’y aura alors aucune nécessité d’indexer une FK autre que celle éventuellement soulevée par un souci de performance. Mais là, on rejoint le cortège classique de l’indexation.

Pour en revenir à votre exemple, pour provoquer un inter blocage lors d’une opération sur une table mère ayant une table fille dotée d’une FK non indexée, il faut qu’il y ait une tentative de transformation d’un verrou du type TM (DML) d’un mode 3(SX) à un mode 5 (SSX). Dans votre exemple la première session acquiert un verrou TM d’un mode 6 (X) et dans le déroulement de vos opérations il n’y a jamais eu une tentative de transformation d’un verrou du type TM du mode SX ou mode SSX. Par contre, en faisant les opérations suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
session1 > delete from t_fils where id_mere = 1;
2 rows deleted.
 
session1 > delete from t_mere where id_mere = 1;
1 row deleted.
 
session1 > start getlocks2;
 
       SID       WSID LOCK_TYPE            MODE_HELD       MODE_REQUESTED                                               
---------- ---------- -------------------- --------------- --------------                                              
       252        334 Transaction          EXCLUSIVE       SHARE

Et dans la session 2 ceci:

Code :

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

1
2
 
session2 > insert into t_fils values (100, 1, 'test mohamed');

A ce niveau, la session 2 bloque. Je reviens à la session 1 et je fais ceci :

Code :

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

1
2
3
 
session1 > delete from t_mere where id_mere = 200;
0 rows deleted.

En revenant à la session 2 j'observe ceci

Code :

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

1
2
3
4
5
6
 
session2 > insert into t_fils values (100, 1, 'test mohamed');
insert into t_fils values (100, 1, 'test mohamed')
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

L’inter blocage se produit toutes les 3 secondes, et j’ai été assez rapide pour obtenir l’image suivante avant le time out interne d’oracle (qui se réveille toutes les 3 secondes)

Code :

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

1
2
3
4
5
6
7
 
session3> start getlocks2
 
       SID       WSID LOCK_TYPE            MODE_HELD       MODE_REQUESTED       
---------- ---------- -------------------- --------------- ---------------      
       252        334 Transaction          EXCLUSIVE       SHARE                
       334        252 DML                  ROW-X (SX)      S/ROW-X (SSX)

Où l’on voit bien qu’une tentative de transformation d’un verrou du type DML (TM) du mode SX vers le mode SSX sur une table fille ayant une FK non indexée a produit un inter-blocage. Si, j'indexe la FK cet inter blocage ne se produira pas

Et enfin, la séquence des opérations ci-dessous a, à mon sens, plus de chance de se produire dans une application réelle que l’opération d’un lock d’une table fille explicite via la commande lock

[mnitu]

Il devient nécessaire d’indexer les contraintes d’intégrité du type FK uniquement lorsqu’on delete/update/merge la table mère dans un environnement OLTP multiutilisateurs et donc concurrent. ...

Déjà le select for update à disparu ce qui est déjà bien!
Il vous reste de faire le pas pour éliminer l’update qui ne modifié pas la clé primaire, ce qui devrait être la règle.
Après éliminer parfois le delete pour les cases où tout simplement, fonctionnellement la suppression ne se fait pas.