Avant d’expliquer ce que sont les enqueues et à quoi ils servent, voici illustré à travers un exemple de quelques lignes qu’Oracle n’est jamais aussi simple que l’on croit a priori. Pour cela, nous allons montrer l’effet d’un verrou de table (Comprenez un TM enqueue).

Pour cette exemple, il ne vous faut pas moins de 4 sessions SQL*Plus et une table que nous créerons avec le script ci-dessous :
create table gark(id number);
insert into gark values (1);
insert into gark values (2);
insert into gark values (3);
insert into gark values (4);
commit;

Nous utiliserons la 4ème session pour surveiller les locks. Exécutez un script comme celui ci-dessous pour repérer vos sessions :
SQL> select sid, program from v$session
where username='SYS'
and program like '%sql%'

SID PROGRAM
--- ----------------------------
125 sqlplus@node2 (TNS V1-V3)
128 sqlplus@node2 (TNS V1-V3)
132 sqlplus@node2 (TNS V1-V3)
133 sqlplus@node2 (TNS V1-V3)

On notera que les SID des sessions sont : (125, 128, 132, 133). modifiez cette liste avec vos propres valeurs pour la suite du test.

a°) Dans la première session SQL*Plus, verrouiller la table en mode « Row-X » en verrouillant la ligne 1 avec le script ci-dessous :
select * from gark where id=1 for update;

b°) Effectuez la même opération dans la seconde session SQL*Plus , sur la ligne 2. Vous vérifierez ainsi que 2 verrous « Row-X (SX) » peuvent être posés simultanément sur la même table :
select * from gark where id=2 /update;

c°) Vérifiez qu’il y a bien des verrous TM posés sur la table GARK à l’aide de la 4° session SQL*Plus :
SQL> select sid, type, id1, id2, lmode
from v$lock
where sid in (125, 128, 132, 133);

OBJECT_NAME
----------------------------
GARK

d°) Dans la première session SQL*Plus tentez maintenant de verrouiller la table en mode exclusif. Utilisez la commande ci-dessous :
lock table gark in exclusive mode;
La session SQL*Plus ne rend pas la main indiquant que la requête attend pour poser le verrou qu’elle y soit autorisée.

e°) Dans la seconde session SQL*Plus, vous pouvez toujours verouiller une autre ligne :
select * from gark where id=3 for update;
La requête ayant déjà posé un verrou « Row-X » sur la table n’a plus besoin de poser d’autres verrous

f°) D’ailleurs, si vous vérifiez avec la quatrième session SQL*Plus, vous constaterez que rien dans les verrous n’a changé :
SQL> select sid, type, id1, id2, lmode, request
from v$lock
where sid in (125, 128, 132, 133)

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
132 TM 11739 0 3 6
128 TM 11739 0 3 0
128 TX 655383 907 6 0
132 TX 65573 897 6 0

Ou presque… Désormais la session 142 voudrait (request) un verrou de niveau 6 (i.e. exclusive – X). Qu’est-ce que ça change ?

g°) Si une nouvelle session SQL*Plus (la troisième) demande à poser un verrou « Row-X » sur la table, elle est désormais bloquée :
select * from gark where id=4 for update;

h°) Si vous vérifiez avec la quatrième session SQL*Plus, les verrous, vous constaterez ce qui suit :
/

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
132 TM 11739 0 3 6
128 TM 11739 0 3 0
133 TM 11739 0 0 3
128 TX 655383 907 6 0
132 TX 65573 897 6 0

La dernière session aimerait avoir un verrou TM Row-X et pourtant elle a un verrou « None » (i.e. 0) seulement positionné. Même si le niveau de verrouillage actuel permettrait à notre troisième session SQL*Plus d’établir un verrou correspondant à ses attentes, le fait qu’une autre session attende d’être augmenté de niveau avant sa demande l’empêche d’obtenir ce verrou.

Nous avons illustré que la base de données Oracle, non seulement trace les niveaux des verrous (et plus généralement des enqueues) mais également les niveaux d’attente supplémentaires des sessions. Nous illustrerons ce point à travers une caractéristique particulière des structures d’enqueue dans le prochain Post.

-GarK!