Oracle et les verrous

Introduction

Dans des systèmes multi-utilisateurs, plusieurs utilisateurs peuvent mettre à jour la même information en même temps. Le verrouillage permet à un seul utilisateur de mettre à jour un bloc de données en particulier, empêchant une autre personne de modifier la même donnée.

L’idée de base du verrouillage consiste à bloquer une donnée modifiée dans une transaction jusqu’à ce que la transaction soit validée ou annulée. Le verrou est actif jusqu’à la fin de la transaction, principe mieux connu sous le terme de concurrence d’accès aux données (data concurrency).

Le deuxième objectif du verrouillage est d’assurer que tous les process peuvent toujours accéder en lecture aux données originales, c’est-à-dire telles qu’elles étaient avant la modification par la transaction non encore validée. On parle alors de consistence de lecture (read consistency).

Bien que les verrous soient indispensables pour renforcer la consistence de la base de données, ils peuvent créer des problèmes de performances. Chaque fois qu’un process engendre un verrou, un autre utilisateur peut être bloqué, voire débouté de sa demande pour verrouiller une ligne ou une table. Oracle permet de verrouiller tout type de ressources : une seule ligne, plusieurs lignes, une table entière, même plusieurs tables.

Niveaux de verrouillage sous Oracle (row-level et table level locking)

Oracle fournit deux différents niveaux de verrouillage : le verrouillage ligne (row-level lock) et le verrouillage table (table-level lock).

Verrouillage ligne (row level locking)

Avec la stratégie de verrouillage ligne, chaque ligne dans une table est verrouillée individuellement. Les lignes verrouillées ne peuvent être mises à jour que par le process bloquant. Toutes les autres lignes de la table sont toujours disponibles pour mise à jour par d’autres process.

Bien entendu, les autres sessions peuvent continuer de lire la table, y compris la ligne qui est en cours de mise à jour. Lorsque d’autres sessions lisent des lignes mises à jour, ces sessions ne peuvent que lire la version ancienne de la ligne avant mise à jour (via les segments d’annulation « rollback segments ») jusqu’à ce que les changements soient effectivement validés par la transaction bloquante. C’est le principe de la lecture consistante pour Oracle (consistent read).

Lorsqu’un process place un verrou ligne sur un enregistrement :

• 1. Premièrement, un verrou de manipulation de données (DML Lock) est placé sur la ligne. Ce verrou empêche les autres sessions de manipuler ou verrouiller cette ligne. Le verrou est relâché seulement lorsque le process bloquant annule ou valide la transaction.
• 2. Ensuite un verrou DDL (data dictionary language, DDL Lock) est placé sur la table pour empêcher les altérations de structure de la table. Ce verrou est relâché également seulement lorsque le process bloquant annule ou valide la transaction.

Verrouillage table (table level locking)

Avec le verrouillage table, la table entière est verrouillée intégralement. Une fois qu’un process a verrouillé la table, seule ce process peut mettre à jour ou verrouiller une ligne de cette table. Aucune des lignes n’est disponible pour mise à jour par d’autres sessions. Les autres sessions peuvent toujours cependant continuer à lire la table, y compris la ligne qui est en cours de mise à jour.

Comment le verrouillage table fonctionne-t-il ? La première opération DML qui a besoin de mettre à jour une ligne dans une table obtient ce que l’on appelle un verrou ligne partagé exclusif (Row Share Exclusive lock) sur la table dans son intégralité. Tous les autres process effectuant des requêtes et qui ont besoin d’accéder à la table sont informés qu’ils doivent utiliser les informations des segments d’annulation (rollback segments). Le verrou est relâché uniquement lorsque le process bloquant valide ou annule sa transaction.

Modes de verrouillage

Oracle utilise 2 modes de verrouillage :

• 1. Le mode de verrou exclusif (X - Exclusive lock mode) : ce mode empêche la ressource associée d’être partagée. Ce mode de verrou est obtenu pour modifier des données. La première transaction qui verrouille une ressource exclusivement est la seule à pouvoir altérer cette ressource (ligne ou table) jusqu’à ce que le verrou exclusif soit relaxé.
• 2. Le mode de verrou partagé (S - Share lock mode) : autorise la ressource associée à être partagée, mais tout dépend des opérations engagées. Plusieurs utilisateurs lisant les données peuvent partager les données, laissant des verrous de partage pour empêcher l’accès concurrent à un process voulant écrire (qui requiert un verrou exclusif). Plusieurs transactions peuvent acquérir des verrous de partage sur la même ressource.

Verrous exclusifs :

Verrous de partage :

Tout ce qui suit décrit chaque mode de verrouillage, du moins restrictif au plus restrictif.

Row Share Table Locks (RS)

Un verrou "row share table lock", appelé parfois "subshare table lock", indique qu’une transaction a verrouillé des lignes dans une table et a pour intention de mettre à jour ces dernières. On parle plus de verrous d’intention dans ce cas de figure. Le verrou de type RS est automatiquement acquis avec les commandes ci-dessous :

SELECT ... FROM table ... FOR UPDATE OF ... ;
LOCK TABLE table IN ROW SHARE MODE;

C’est le type de verrou le moins restrictif, offrant le plus haut degré de concurrence pour une table.

Opérations permises :

Les verrous RS tenus par une transaction permettent aux autres transactions les opérations ci-dessous :

• SELECT
• INSERT, UPDATE, DELETE ou verrouiller des lignes de manière concurrente sur la même table.

Aussi d’autres transactions peuvent obtenir simultanément des verrous RS (row share), RX (row exclusive), S (share) ou SRX (share row exclusive) sur la même table.

Opérations interdites:

Un verrou RS posé par une transaction empêche les autres transactions d’avoir un accès exclusif sur les mêmes données de la table.

La table emp dans le schéma SCOTT est prise pour les exemples :

EMPNO      ENAME      JOB
---------- ---------- ---------
7369       Smith      CLERK
7499       Allen      SALESMAN
7521       Ward       SALESMAN
7566       Jones      MANAGER
7654       Martin     SALESMAN
7698       Blake      MANAGER
7782       Clark      MANAGER
7788       Scott      ANALYST
7839       King       PRESIDENT
7844       Turner     SALESMAN
7876       Adams      CLERK
7900       James      TEST
7902       Ford       ANALYST
7934       Miller     CLERK

select job from emp
where job = 'CLERK'
for update of empno;
OK

select job from emp
where job = 'CLERK'
for update of empno;
En attente ....

select job from emp
where job = 'MANAGER'
for update of empno;
OK

lock table emp in share mode;
OK

lock table emp in exclusive mode;
En attente ....

insert into emp (empno,ename)
values (9999,'Test');
OK

delete from emp where empno = 9999;
OK

delete from emp where empno = 7876;
En attente .... (Blocked by Session 1)

update emp set job = 'CLIMBER'
where empno = 7876;
En attente .... (Blocked by Session 1)

La vue DBA_LOCKS permet de retrouver la situation de verrouillage :

SELECT SUBSTR(TO_CHAR(session_id),1,5) "SID",
       SUBSTR(lock_type,1,15) "Lock Type",
       SUBSTR(mode_held,1,15) "Mode Held",
       SUBSTR(blocking_others,1,15) "Blocking?"
FROM dba_locks
/

SID   Lock Type       Mode Held       Blocking?
----- --------------- --------------- ---------------
95    Transaction     Exclusive       Blocking     <- Session 1
95    DML             Row-S (SS)      Not Blocking
98    DML             Row-X (SX)      Not Blocking <- Session 2
98    Transaction     None            Not Blocking
110   Temp Segment    Row-X (SX)      Not Blocking
111   RS              Row-S (SS)      Not Blocking
111   Control File    Row-S (SS)      Not Blocking

Row Exclusive Table Locks (RX)

Un verrou RX indique généralement qu’une transaction a fait une ou plusieurs mises à jour sur une table. Un verrou RX est automatiquement acquis pour une table modifiée avec les commandes ci-dessous :

INSERT INTO table ... ;
UPDATE table ... ;
DELETE FROM table ... ;
LOCK TABLE table IN ROW EXCLUSIVE MODE;

Un verrou RX est plus restrictif qu’un verrou RS.

Opérations permises:

Les verrous RX tenus par une transaction permettent aux autres transactions les opérations ci-dessous :

• SELECT
• INSERT, UPDATE, DELETE ou verrouiller des lignes de manière concurrente sur la même table.

Les verrous RX autorisent de multiples transactions à obtenir des verrous exclusifs et partagés de type ligne pour la même table.

Opérations interdites :

Un verrou RX posé par une transaction empêche les autres transactions de verrouiller manuellement la table en exclusif pour écriture ou lecture. Aussi, les transactions ne peuvent pas de manière concurrente utiliser les commandes suivantes :

• LOCK TABLE table IN SHARE MODE;
• LOCK TABLE table IN EXCLUSIVE MODE;

Ce mode de verrouillage est le mode par défaut d’Oracle.

update emp
set ename = 'Zahn';
OK

lock table emp in exclusive mode;
En attente ....

Share Table Locks (S)

Un verrou de type S est acquis automatiquement pour une table avec la commande ci-dessous :

LOCK TABLE table IN SHARE MODE;

Opérations permises

Les verrous S tenus par une transaction permettent seulement aux autres transactions les opérations ci-dessous :

• SELECT
• Rechercher des lignes spécifiques avec SELECT … FOR UPDATE.
• Exécuter LOCK TABLE … IN SHARE MODE

Aucune mise à jour n’est autorisée pour les autres transactions. De multiples transactions peuvent tenir des verrous S sur la même table de manière concurrente. Dans ce cas, aucune transaction ne peut mettre à jour la table. Une transaction peut avoir un verrou de type S et peut mettre à jour la table uniquement si aucune autre transaction n’a posé de verrou de type S sur la même table.

Opérations interdites

Un verrou S posé par une transaction empêche les autres transactions de modifier la même table et d’exécuter les commandes ci-dessous :

• LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
• LOCK TABLE table IN EXCLUSIVE MODE;
• LOCK TABLE table IN ROW EXCLUSIVE MODE;

lock table emp
in share mode;
OK

update emp
set ename = 'Zahn'
where empno = 7900;
commit;
OK

lock table emp
in share mode;
OK

update emp
set ename = 'Müller'
where empno = 7900;
En attente ....

select * from emp;
OK

lock table emp
in share mode;
OK

Exclusive Table Locks (X)

Un verrou exclusif sur une table est le plus restrictif. Un verrou de type exclusif sur une table est acquis avec la commande ci-dessous :

• LOCK TABLE table IN EXCLUSIVE MODE;

Opérations permises

Une seule transaction à la fois peut acquérir un verrou exclusif X sur une table. Un verrou de type X permet seulement aux autres transactions de requêter la table.

Opérations interdites

Un verrou X posé par une transaction empêche toute autre transaction de réaliser une quelconque opération DML ou placer tout autre type de verrou sur la table.

lock table emp
in exclusive mode;
OK

update emp
set ename = 'Zahn'
where empno = 7900;
OK

lock table emp
in exclusive mode;
OK

select * from emp;
OK

lock table emp
in share mode;
En attente ....

lock table emp
in exclusive mode;
En attente ....

update emp
set ename = 'Zahn'
where empno = 7900;
En attente ....

Conversion de verrous (et escalade)

Une transaction pose des verrous RX pour toutes les lignes insérées, mises à jour ou supprimées dans une transaction. Parce que les verrous niveau ligne sont acquis au niveau le moins restrictif, aucune conversion de verrou est requis ou réalisé.

Oracle convertit automatiquement un verrou de plus faible restrictivité vers un niveau de verrouillage plus restrictif lorsque c’est nécessaire. Par exemple, c’est le cas lors de l’utilisation de la commande SELECT … for UPDATE : si la transaction met à jour une ou plusieurs autres lignes que les lignes déjà verrouillées, le verrou RS est automatiquement converti en verrou RX.

Oracle n’effectue jamais d’escalades de verrous (RX => S par exemple). Dans de nombreux autres moteurs (Sybase par ex.), une promotion de verrous est parfois réalisée pour passer du verrouillage ligne au verrouillage table au delà d’un certain seuil, réduisant ainsi le nombre de verrous mais augmentant aussi la restrictivité. Le moteur Oracle n’a pas choisi de mettre en œuvre ce comportement.

Deadlocks

Un deadlock (ou verrou mortel) peut se produire lorsque deux ou plusieurs utilisateurs attendent des données verrouillées par eux mêmes. Les deadlock empêchent les transactions de se poursuivre.

Exemple :

update emp set
sal = sal * 1.1
where empno = 7369;
1 row updated.
update emp set
sal = sal * 1.1
where empno = 7934;
ERROR at line 1:
ORA-00060: deadlock detected
while waiting for resource

update emp set
mgr = 1342
where empno =7934;
1 row updated.
update emp set
mgr = 1342
where empno = 7369;
En attente ...

Une erreur ORA-60 est retournée à la session qui a rencontré le deadlock et en fonction de la méthodologie de développement de l’outil, la transaction est entièrement annulée, et un fichier de trace du deadlock est écrit dans le répertoire /udump de l’instance Oracle. Ce fichier de trace permet de diagnostiquer les causes du deadlock.

La statistique « enqueue deadlocks » dans la vue système V$SYSSTAT enregistre le nombre de fois qu’un deadlock a été détecté :

select name, value
from v$sysstat
where name = 'enqueue deadlocks';
NAME                                                         VALUE
------------------------------------------------------------ ----------
enqueue deadlocks                                            1

Les verrous avec les intégrités référentielles (Verrous RI)

Oracle suppose qu’il existe un index sur toutes les clés étrangères pour une table.

Si un index existe sur la clé étrangère dans la table fille, aucun autre verrou DML que les verrous RX sur les lignes modifiées n’est posé.

Dans le cas où l’index n’existe pas sur la clé étrangère, un verrou S sur la table est posé le temps de la transaction => ce qui peut créer des bloquages très longs sur la table fille si la transaction est longue.

Le script ci-dessous permet de détecter les contraintes étrangères pour lesquelles des indexes ne sont pas posés :

      --- -----------------------------------------------------------------------
      --- Shows the foreign keys without appropiate index
      --- -----------------------------------------------------------------------
      ---
      
      SET echo off
      SET verify off
      --
      COLUMN OWNER noprint new_value own
      COLUMN TABLE_NAME format a24 wrap heading "Table Name"
      COLUMN CONSTRAINT_NAME format a24 wrap heading "Constraint Name"
      COLUMN CONSTRAINT_TYPE format a3 heading "Typ"
      COLUMN COLUMN_NAME format a24 wrap heading "1. Column"
      BREAK ON OWNER skip page
      --
      SET TERMOUT ON
      TTITLE CENTER 'Unindexed Foreign Keys owned by Owner: ' own SKIP 2
      PROMPT
      PROMPT Please enter Owner Name and Table Name. Wildcards allowed (DEFAULT: %)
      PROMPT
      PROMPT eg.: SCOTT, S% OR %
      PROMPT eg.: EMP, E% OR %
      PROMPT
      --
      ACCEPT vOwner prompt "Owner <%>: " DEFAULT %
      ACCEPT vTable prompt "Tables <%>: " DEFAULT %
      --
      SELECT OWNER, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
       FROM DBA_CONS_COLUMNS c
       WHERE position=1 AND
        (OWNER, TABLE_NAME, COLUMN_NAME) IN
        (SELECT c.OWNER, c.TABLE_NAME,cc.COLUMN_NAME
          FROM DBA_CONSTRAINTS c, DBA_CONS_COLUMNS cc
          WHERE c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
           AND c.TABLE_NAME = cc.TABLE_NAME
           AND c.OWNER = cc.OWNER
           AND c.CONSTRAINT_TYPE = 'R'
           AND cc.POSITION = 1
           AND c.OWNER LIKE UPPER('&vOwner')
           AND c.TABLE_NAME LIKE UPPER('&vTable')
        MINUS
         SELECT table_owner, table_name, column_name
         FROM DBA_IND_COLUMNS
         WHERE COLUMN_POSITION = 1
          AND TABLE_OWNER LIKE UPPER('&vOwner')
          AND TABLE_NAME LIKE UPPER('&vTable')
      )
      ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME;
      --
      ttitle off
      SET pause off
      COLUMN TABLE_NAME clear
      COLUMN CONSTRAINT_NAME clear
      COLUMN CONSTRAINT_TYPE clear
      COLUMN COLUMN_NAME clear
      clear breaks

Verrous bloquants

Il y a plusieurs façons de diagnostiquer les situations de verrouillage, normalement avec l’intention de tuer la session bloquante.

Les verrous bloquants sont toujours des verrous TX (transaction) ou des verrous TM (table). Lorsqu’une session attend un verrou TX, elle attend qu’une transaction valide ou annule. La raison de l’attente est que la transaction a modifié un bloc de données, et la session en attente a besoin de modifier la même partie du bloc de données. Dans de tels cas, les colonnes V$SESSION sont très utiles pour identifier l’objet, le fichier, les numéros de blocs concernées et même le numéro de ligne dans le cas d’un verrouillage ligne. V$LOCKED_OBJECT peut être utilisé pour obtenir les informations de session de la session ayant posé des verrous DML sur l’objet. Ceci est basé sur le fait que les session avec des verrous TX pose toujours des verrous DML également, à moins que les verrous DML aient été désactivés.

Il peut ne pas être adéquat d’identifier une session unique bloquante, parce qu’elle même peut également être bloquée par une autre session. Pour résoudre ceci, le script UTLLOCK.SQL donne une structure en arbre montrant la relation entre les session bloquantes et les sessions en attente. Certains DBA sont hésitants pour utiliser ce script car il crée une table temporaire. La même information peut être obtenu avec la vue DBA_WAITERS, vue créée par le script catblock.sql.

Scripts de détection de verrous :

Les scripts suivants peuvent être utilisés pour traquer et identifier les verrous bloquants. Les scripts montrent la situation de verrouillage ci-dessous :

select empno
from emp for update of empno;

update emp set ename = 'Müller'
where empno = 7369;

show_dml_locks.sql

Ce script montre les verrous DML, WAIT=YES indique que les utilisateurs attendent un verrou.

      WAI OSUSER  PROCESS  LOCKER  T_OWNER  OBJECT_NAME   PROGRAM
      --- ------- -------- ------- -------- ------------- --------------
      NO  pads    8935     SCOTT   -        Record(s)     sqlplus@darius
      YES pads    8944     SCOTT   -        Record(s)     sqlplus@darius
      NO  pads    8935     SCOTT   SCOTT    EMP           sqlplus@darius
      NO  pads    8944     SCOTT   SCOTT    EMP           sqlplus@darius

show_blocking_sessions.sql

Ce script montre les utilisateurs attendant un verrou, le bloquant et la commande SQL pour laquelle un verrou est attendu, l’OS User, le schéma et les PIDs sont également donnés.

      Current Lock-Waits

      OS_LOCKER  LOCKER_SCHEMA  LOCKER_PID OS_WAITER   WAITER_SCHEMA   WAITER_PID
      ---------- -------------- ---------- ----------- --------------- ----------
      pads       SCOTT          8935       pads        SCOTT           8944

      SQL_TEXT_WAITER
      --------------------------------------------------------------------------
      TX: update emp set ename = 'Müller' where empno = 7369

utllockt.sql

C’est le script original fourni par Oracle pour afficher les attentes de verrous dans une version structurée en arbre. Le script affiche les sessions dans le système qui attendent des verrous et les verrous qu’ils attendent. Si un ID de session est affiché immédiatement en dessous à droite d’une autre session, alors cette ID de session attend après cette session. Les Ids de session affichées à la gauche sont les sessions qui bloquent les autres sessions (la session 96 attend que la session 88 se termine et relâche ses verrous) :

      WAITING_SESSION   LOCK_TYPE    MODE_REQUESTED MODE_HELD  LOCK_ID1  LOCK_ID2
      ----------------- ------------ -------------- ---------- --------- --------
      88                None
        96              Transaction  Exclusive      Exclusive  262144    3206

L’information à droite d’une session bloquée décrit le type de verrous en attente (et non les verrous qu’il possède). Ce script n’est pas un jeu sur des vues standards car la clause CONNECT BY est utilisée, aussi une table temporaire est créée et supprimée.

Ce script a deux petits inconvénients. Premier inconvénient : une table est créée quand ce script est lancé. Pour créer cette table un nombre de verrous est acquis, ce qui fait apparaître ceux-ci dans le résultat du script. Second inconvénient : si une session attend après plusieurs sessions ayant posé des verrous en mode partagé (verrous S), le résultat n’est alors plus un arbre et la session apparaîtra plusieurs fois.

Transactions distribuées

Pour les transactions distribuées, Oracle n’est pas en mesure de faire la différence entre des verrous bloquants et des deadlocks, pour la simple et bonne raison que toutes les informations ne sont pas disponibles en local au niveau de l’instance. Pour empêcher les deadlocks dans les transactions distribuées, Oracle dispose d’un timeout dans toute transaction distribuée si aucune réponse n’est obtenue à l’issue d’un nombre de secondes spécifié par le paramètre d’initialisation DISTRIBUTED_LOCK_TIMEOUT. Ce paramètre est fixé à 60 secondes par défaut. Si une transaction distribuée rencontre le timeout, une erreur ORA-2049 est retournée à la session. Les applications robustes devraient gérer cette exception de la même manière que les deadlocks.

select name,value
from v$parameter
where name = 'distributed_lock_timeout';

NAME                          VALUE
----------------------------- ------
distributed_lock_timeout      60

Manques d’entrée ITL (ITL Entry shortages)

Il existe un ITL (interested transaction list) dans la variable d’entête de chaque bloc de données Oracle. Lorsqu’un nouveau bloc est formatté pour un segment, le nombre d’entrée initial dans l’ITL est appliqué par le paramètre INITRANS pour le segment. L’ITL peut croître dynamiquement si nécessaire, jusqu’à la limite imposée par la taille du bloc, ou le paramètre MAXTRANS pour le segment, lequel est plus petit.

Chaque transaction qui modifie un bloc de données doit enregistrer son identifiant de transaction et l’adresse du segment d’annulation (rollback segments) pour ses changements pour ce bloc dans l’entrée ITL. Oracle recherche toujours dans l’ITL une entrée libre ou réutilisable. Si toutes les entrées dans l’ITL sont occupées par des transactions non valisées, alors une nouvelle entrée est créé dynamiquement si cela est possible.

Si le bloc ne dispose pas assez d’espace interne (24 bytes) pour créer dynamiquement une entrée additionnelle ITL, la transaction doit alors attendre qu’une transaction utilisant une des entrées ITL valide ou annule sa transaction. La transaction bloquée attend en mode partage dans la queue TX après les transactions existantes, transactions choisies de manière pseudo-aléatoires. Les colonnes row wait de la vue V$SESSION montrent l’objet, le fichier, les numéros de blocs. Si la colonne ROW_WAIT_ROW# demeure inchangé, cela indique que la transaction n’attend pas après un verrou ligne, mais attend probablement une entrée libre ITL.

La cause la plus commune des manques d’entrée ITL est un paramétrage de PCTFREE à 0. Il faut réfléchir à deux fois avant de mettre le paramètre PCTFREE à 0 pour un segment qui peut être sujet à des mises à jour concurrents pour un bloc unique, même si ces mises à jour peuvent ne pas augmenter la longueur totale de la ligne. Le degré de concurrence qu’un bloc peut supporter est dépendant de la taille de son ITL. Toutefois il ne faut pas prendre peur et utiliser des larges valeurs INITRANS et PCTFEE pour autant, de larges PCTFREE compromettent en effet la densité des données et dégrade les performances des scans de tables.

Un cas pour lequel le paramètre INITRANS mérite d’être changé est celui des segments sujets à des DML parallèles. Si une transaction fille d’une transaction rencontre un manque d’entrée ITL (ITL entry shortage), celle-ci verifiera si toutes les entrées ITL sont occupées par des transactions sœur et si c’est le cas, la transaction est annulée avec une erreur ORA-12829 pour éviter le « self-deadlock ». 2 options dans ce cas de figure : diminuer le degré de parallélisme ou bien reconstruire le segment avec une valeur plus élevée pour le paramètre INITRANS.

La commande SQL plus bas montre le nombre de la statistique 'ITL-Waits' par table (Interested Transaction List). INITRANS et/ou PCTFREE pour ces tables est alors trop faible (ou encore MAXTRANS est trop petit). Pour récupérer cette statistique, STATISTICS_LEVEL doit être à TYPICAL ou ALL, par ailleurs MAXTRANS a été retiré avec Oracle 10g et est maintenant toujours à 255.

select name,value
from v$parameter
where name = 'statistics_level';

NAME                                 VALUE
------------------------------------ -----------
statistics_level                     TYPICAL

TTITLE "ITL-Waits per table (INITRANS to small)"
set pagesize 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading "NBR. ITL WAITS"

select owner,
	object_name||' '||subobject_name object_name,
	value
from v$segment_statistics
where statistic_name = 'ITL waits'
and value > 0
order by 3,1,2;

col owner clear
col object_name clear
col value clear
ttitle off
/

Annexe 1 (show_dml_locks.sql)

      --- -------------------------------------------------------------------------
      --- Shows actual DML-Locks (incl. Table-Name)
      --- WAIT = YES are users waiting for a lock
      --- -----------------------------------------------------------------------
      ---
      --
      SET PAGES 24 LINES 80 FEED ON ECHO OFF TERMOUT ON HEAD ON
      COLUMN PROGRAM FORMAT A80 TRUNC
      COLUMN LOCKER FORMAT A10 TRUNC
      COLUMN T_OWNER FORMAT A10 TRUNC
      COLUMN OBJECT_NAME FORMAT A25 TRUNC
      COLUMN WAIT FORMAT A4
      TTITLE "Actual DML-Locks (TM+TX)"
      --
      select /*+ rule */
      decode(L.REQUEST,0,'NO','YES') WAIT,
      S.OSUSER,
      S.PROCESS,
      S.USERNAME LOCKER,
      U.NAME T_OWNER,
      O.NAME OBJECT_NAME,
      ' '||S.PROGRAM PROGRAM
      from V$LOCK L,
      V$SESSION S,
      OBJ$ O,
      USER$ U
      where U.USER# = O.OWNER#
      and S.SID = L.SID
      and L.ID1 = O.OBJ#
      and L.TYPE = 'TM'
      union
      select decode(L.REQUEST,0,'NO','YES') WAIT,
      S.OSUSER,
      S.PROCESS,
      S.USERNAME LOCKER,
      '-',
      'Record(s)',
      ' '||S.PROGRAM PROGRAM
      from V$LOCK L,
      V$SESSION S
      where S.SID = L.SID
      and L.TYPE = 'TX'
      order by 7,5,1,2,6
      /
      ttitle off
      col program clear
      col locker clear
      col t_owner clear
      col object_name clear
      col wait clear

Annexe 2 (show_blocking_sessions.sql)

      --- -------------------------------------------------------------------------
      --- Show users waiting for a lock, the locker and the
      --- SQL-Command they are waiting for a lock
      --- osuser, schema and PIDs are shown
      --- -----------------------------------------------------------------------
      ---
      --
      SET PAGES 24 LINES 100 FEED ON ECHO OFF TERMOUT ON HEAD ON
      COLUMN OS_LOCKER FORMAT A15 TRUNC
      COLUMN OS_WAITER FORMAT A15 TRUNC
      COLUMN LOCKER_SCHEMA FORMAT A15 TRUNC
      COLUMN WAITER_SCHEMA FORMAT A15 TRUNC
      COLUMN WAITER_PID FORMAT A10
      COLUMN LOCKER_PID FORMAT A10
      COLUMN SQL_TEXT_WAITER FORMAT A100 WRAP
      COLUMN DATABASE NOPRINT NEW_VALUE DATABASE
      COLUMN DATUM_ZEIT NOPRINT NEW_VALUE DATUM_ZEIT
      SET TERMOUT OFF ECHO OFF FEED OFF
      SET TERMOUT ON
      TTITLE CENTER 'Current Lock-Waits' SKIP 2
      --
      select /*+ ordered
      no_merge(L_WAITER)
      no_merge(L_LOCKER) use_hash(L_LOCKER)
      no_merge(S_WAITER) use_hash(S_WAITER)
      no_merge(S_LOCKER) use_hash(S_LOCKER)
      use_nl(O)
      use_nl(U)
      */
      /* first the table-level locks (TM) and mixed TM/TX TX/TM */
      S_LOCKER.OSUSER OS_LOCKER,
      S_LOCKER.USERNAME LOCKER_SCHEMA,
      S_LOCKER.PROCESS LOCKER_PID,
      S_WAITER.OSUSER OS_WAITER,
      S_WAITER.USERNAME WAITER_SCHEMA,
      S_WAITER.PROCESS WAITER_PID,
      'Table lock (TM): '||U.NAME||'.'||O.NAME||
      ' - Mode held: '||
      decode(L_LOCKER.LMODE,
      0, 'None', /* same as Monitor */
      1, 'Null', /* N */
      2, 'Row-S (SS)', /* L */
      3, 'Row-X (SX)', /* R */
      4, 'Share', /* S */
      5, 'S/Row-X (SSX)', /* C */
      6, 'Exclusive', /* X */
      '???: '||to_char(L_LOCKER.LMODE))||
      ' / Mode requested: '||
      decode(L_WAITER.REQUEST,
      0, 'None', /* same as Monitor */
      1, 'Null', /* N */
      2, 'Row-S (SS)', /* L */
      3, 'Row-X (SX)', /* R */
      4, 'Share', /* S */
      5, 'S/Row-X (SSX)', /* C */
      6, 'Exclusive', /* X */
      '???: '||to_char(L_WAITER.REQUEST))
      SQL_TEXT_WAITER
      from
      V$LOCK L_WAITER,
      V$LOCK L_LOCKER,
      V$SESSION S_WAITER,
      V$SESSION S_LOCKER,
      OBJ$ O,
      USER$ U
      where S_WAITER.SID = L_WAITER.SID
      and L_WAITER.TYPE IN ('TM')
      and S_LOCKER.sid = L_LOCKER.sid
      and L_LOCKER.ID1 = L_WAITER.ID1
      and L_WAITER.REQUEST > 0
      and L_LOCKER.LMODE > 0
      and L_WAITER.ADDR != L_LOCKER.ADDR
      and L_WAITER.ID1 = O.OBJ#
      and U.USER# = O.OWNER#
      union
      select /*+ ordered
      no_merge(L_WAITER)
      no_merge(L_LOCKER) use_hash(L_LOCKER)
      no_merge(S_WAITER) use_hash(S_WAITER)
      no_merge(S_LOCKER) use_hash(S_LOCKER)
      no_merge(L1_WAITER) use_hash(L1_WAITER)
      no_merge(O) use_hash(O)
      */
      /* now the (usual) row-locks TX */
      S_LOCKER.OSUSER OS_LOCKER,
      S_LOCKER.USERNAME LOCKER_SCHEMA,
      S_LOCKER.PROCESS LOCK_PID,
      S_WAITER.OSUSER OS_WAITER,
      S_WAITER.USERNAME WAITER_SCHEMA,
      S_WAITER.PROCESS WAITER_PID,
      'TX: '||O.SQL_TEXT SQL_TEXT_WAITER
      from
      V$LOCK L_WAITER,
      V$LOCK L_LOCKER,
      V$SESSION S_WAITER,
      V$SESSION S_LOCKER,
      V$_LOCK1 L1_WAITER,
      V$OPEN_CURSOR O
      where S_WAITER.SID = L_WAITER.SID
      and L_WAITER.TYPE IN ('TX')
      and S_LOCKER.sid = L_LOCKER.sid
      and L_LOCKER.ID1 = L_WAITER.ID1
      and L_WAITER.REQUEST > 0
      and L_LOCKER.LMODE > 0
      and L_WAITER.ADDR != L_LOCKER.ADDR
      and L1_WAITER.LADDR = L_WAITER.ADDR
      and L1_WAITER.KADDR = L_WAITER.KADDR
      and L1_WAITER.SADDR = O.SADDR
      and O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE
      /
      TTITLE OFF
      COLUMN OS_LOCKER CLEAR
      COLUMN OS_WAITER CLEAR
      COLUMN LOCKER_SCHEMA CLEAR
      COLUMN WAITER_SCHEMA CLEAR
      COLUMN WAITER_PID CLEAR
      COLUMN LOCKER_PID CLEAR
      COLUMN SQL_TEXT_WAITER CLEAR
      COLUMN DATABASE CLEAR
      COLUMN DATUM_ZEIT CLEAR

Annexe 3 (utllockt.sql)

      /* Print out the lock wait-for graph in a tree structured fashion.
      *
      * This script prints the sessions in the system that are waiting for
      * locks, and the locks that they are waiting for. The printout is tree
      * structured. If a sessionid is printed immediately below and to the right
      * of another session, then it is waiting for that session. The session ids
      * printed at the left hand side of the page are the ones that everyone is
      * waiting for.
      *
      * For example, in the following printout session 9 is waiting for
      * session 8, 7 is waiting for 9, and 10 is waiting for 9.
      *
      * WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
      * ----------------- ---- ----------------- ----------------- -------- --------
      * 8 NONE None None 0 0
      * 9 TX Share (S) Exclusive (X) 65547 16
      * 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
      * 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
      *
      * The lock information to the right of the session id describes the lock
      * that the session is waiting for (not the lock it is holding).
      *
      * Note that this is a script and not a set of view definitions because
      * connect-by is used in the implementation and therefore a temporary table
      * is created and dropped since you cannot do a join in a connect-by.
      *
      * This script has two small disadvantages. One, a table is created when
      * this script is run. To create a table a number of locks must be
      * acquired. This might cause the session running the script to get caught
      * in the lock problem it is trying to diagnose. Two, if a session waits on
      * a lock held by more than one session (share lock) then the wait-for graph
      * is no longer a tree and the conenct-by will show the session (and any
      * sessions waiting on it) several times.
      */


      /* Select all sids waiting for a lock, the lock they are waiting on, and the
      * sid of the session that holds the lock.
      * UNION
      * The sids of all session holding locks that someone is waiting on that
      * are not themselves waiting for locks. These are included so that the roots
      * of the wait for graph (the sessions holding things up) will be displayed.
      */
      drop table lock_holders;

      create table LOCK_HOLDERS /* temporary table */
      (
      waiting_session number,
      holding_session number,
      lock_type varchar2(26),
      mode_held varchar2(14),
      mode_requested varchar2(14),
      lock_id1 varchar2(22),
      lock_id2 varchar2(22)
      );

      drop table dba_locks_temp;
      create table dba_locks_temp as select * from dba_locks;

      /* This is essentially a copy of the dba_waiters view but runs faster since
      * it caches the result of selecting from dba_locks.
      */
      insert into lock_holders
      select w.session_id,
      h.session_id,
      w.lock_type,
      h.mode_held,
      w.mode_requested,
      w.lock_id1,
      w.lock_id2
      from dba_locks_temp w, dba_locks_temp h
      where h.blocking_others = 'Blocking'
      and h.mode_held != 'None'
      and h.mode_held != 'Null'
      and w.mode_requested != 'None'
      and w.lock_type = h.lock_type
      and w.lock_id1 = h.lock_id1
      and w.lock_id2 = h.lock_id2;

      commit;

      drop table dba_locks_temp;

      insert into lock_holders
      select holding_session, null, 'None', null, null, null, null
      from lock_holders
      minus
      select waiting_session, null, 'None', null, null, null, null
      from lock_holders;
      commit;

      column waiting_session format a17;
      column lock_type format a17;
      column lock_id1 format a17;
      column lock_id2 format a17;

      /* Print out the result in a tree structured fashion */
      select lpad(' ',3*(level-1)) || waiting_session waiting_session,
      lock_type,
      mode_requested,
      mode_held,
      lock_id1,
      lock_id2
      from lock_holders
      connect by prior waiting_session = holding_session
      start with holding_session is null;

      drop table lock_holders;