IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Foreign Key : lock sur la table référencée ?


Sujet :

SQL Oracle

  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    42
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 42
    Par défaut Foreign Key : lock sur la table référencée ?
    Bonsoir à tous,

    Grosse interogation sur le comportement Oracle.
    Je suis en 10g.

    J'ai une table principale sur laquelle j'ai une clé primaire.

    J'ai une autre table qui référence ma table principale avec une clé étrangère (sur la clé primaire de ma table principale).

    Que se passe-t-il lorsque j'effectue un INSERT ou un UPDATE sur ma table référencant ma table principale ?

    Est-ce que le moteur d'Oracle effectue un lock sur ma principale ?

    Selon mes tests non.
    Le seul cas où j'ai un verous qui est posé :
    j'effectue un update sur ma table référencant ma table principale dans une session 001 et si j'essaie de faire un delete sur une ligne de ma table principale dans une autre session 002.
    Ma session 002 est bloquée tant que je n'ai pas effectué de commit sur ma session 001.


    Mais on m'a affirmé le contraire.

    D'avance merci pour votre aide.

  2. #2
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Voir ce qui dit le Concepts Guide à ce sujet pour la 10g. Je crois aussi que dans les anciennes versions, le comportement était différent.

  3. #3
    Membre averti
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    42
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 42
    Par défaut
    J'ai pris connaissance de l'explication de la documentation Oracle.

    Voici le test que j'effectue :

    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
     
    create table T_PARENT (
      field_pk    number(5),
      field_name  varchar2(50),
      primary key (field_pk)
    );
     
    create table T_CHILD (
      field_pk  number(5),
      field_fk  number(5),
      field_name  varchar2(50),
      primary key (field_pk, field_fk),
      constraint t_child_fk foreign key (field_fk) references T_PARENT (field_pk)
    );
     
    -- Remplissage des tables T_PARENT et T_CHILD
    begin
      for i in 1..100 loop
        insert into t_parent (field_pk, field_name) values (i, 'Field name '||i);
      end loop;
      for i in 50..100 loop
        insert into t_child (field_pk, field_fk, field_name) values (i, i, 'Field name child '||i);
      end loop;
      commit;
    end;
    /
     
    -- T_CHILD est ROW LOCK RX DML T_CHILD
    update t_child set field_name = 'Field name child 88 (modified)' where field_pk = 88;
     
    commit;
     
    -- T_PARENT est ROW LOCK RX DML T_PARENT
    update t_parent set field_name = 'Field name 88 (modified)' where field_pk = 88;
     
    commit;
     
    -- Echec : violation de contrainte enregistrement enfant trouvée
    delete t_parent where field_pk = 88;
     
    commit;
     
    -- T_CHILD est ROW LOCK RX DML T_CHILD
    -- T_PARENT est ROW LOCK RS DML T_PARENT
    delete t_child where field_pk = 88;
    J'ai testé sur une Oracle XE, voici la version :

    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product


    Je n'arrive pas à reproduire le comportement décrit.

    Est-ce dû au fait que j'effectue mon test sur une version XE ?

  4. #4
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Je n'arrive pas à comprendre votre problème. Quel est exactement le comportement que vous n'arrivez pas à reproduire ? Avez remarqué que la modification de la clé primaire sur la table parent n'est pas le même chose que la modification d'une autre colonne dans la même table ?

    Le comportement est le même depuis Oracle 7.3

  5. #5
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Il y a quand même eu certains changements depuis Oracle 9 d'après la doc Oracle 9.

  6. #6
    Membre averti
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    42
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 42
    Par défaut
    Dans mon code exemple, j'essaie d'arriver à ce qui est écrit dans la doc Oracle :

    Si je mets jour la table T_PARENT, la table T_CHILD doit être lockée.
    Car il n'y a pas d'index sur le champ foreign key.

    Or, d'après mes tests, la table T_CHILD n'est pas lockée sauf pour un delete.

  7. #7
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Citation Envoyé par pifor Voir le message
    Il y a quand même eu certains changements depuis Oracle 9 d'après la doc Oracle 9.
    D'accord vous avez raison c'est pas pareil

  8. #8
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    Session 1
    SQL> lock table t_child in exclusive mode nowait;
    
    Table(s) verrouillÚe(s).
    
    Session 2
    SQL> update t_parent set field_pk=3 where field_pk=2;
    et la session 2 ne peut pas obtenir et ensuite relâcher son verrou et reste bloquée

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    session 1
    SQL> rollback;
    
    Annulation (rollback) effectuÚe.
    
    Session 2
    1 ligne mise à jour.
    jusque quand la session1 commit ou rollback.

  9. #9
    Membre averti
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    42
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 42
    Par défaut
    Ok, mais vous posez le lock explicitement.

    Normalement, si l'on en croit la doc, la table T_CHILD devrait être lockée.

    Donc, soit mon code exemple est faux, soit il y a quelque chose qui m'échappe...

  10. #10
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Les verrous SSX=RX et SS=RS posés par Oracle lors de l'exécution de certaines instructions SQL sur des tables liées par des contraintes clé étrangères ne sont pas des verrous exclusifs au niveau de la table: ce ne sont pas des verrous qui vont bloquer toute écriture sur la table.

    Voir le tableau des verrous de niveau table à ce sujet dans le Concepts Guide.

  11. #11
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Citation Envoyé par strikerm59 Voir le message
    Ok, mais vous posez le lock explicitement.

    ...
    Je pose un lock exclusive et explicite pour vous montrer que Oracle essaye d'obtenir le lock. Sinon il n'y aurait pas eu de blocage.

    Pour pifor

    Les verrous SSX=RX et SS=RS posés par Oracle lors de l'exécution de certaines instructions SQL sur des tables liées par des contraintes clé étrangères ne sont pas des verrous exclusifs au niveau de la table: ce ne sont pas des verrous qui vont bloquer toute écriture sur la table.
    ???

  12. #12
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Un verrou RS est un verrou au niveau table qui signifie "j'ai verrouillé dans la table une ou plusieurs ligne(s) en mode exclusif"; ce n'est pas un verrou exclusif sur la table:

    Plusieurs sessions peuvent poser un verrou RS sur la même table sans être en attente:

    Session 1:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SQL> show user; 
    USER est "O"
    SQL> lock table t in row share mode;
     
    Table(s) verrouillee(s).

    Session 2:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SQL> show user;
    USER est "O"
    SQL> lock table t in row share mode;
     
    Table(s) verrouillee(s).

  13. #13
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Par contre, RX n'est pas SSX

    RX qui n'est pas cité dans la doc. sur les FKs:

    Row Exclusive Table Locks (RX)

    A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements:

    INSERT INTO table ... ;

    UPDATE table ... ;

    DELETE FROM table ... ;

    LOCK TABLE table IN ROW EXCLUSIVE MODE;


    A row exclusive table lock is slightly more restrictive than a row share table lock.

    Permitted Operations: A row exclusive table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.

    Prohibited Operations: A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:

    LOCK TABLE table IN SHARE MODE;

    LOCK TABLE table IN SHARE EXCLUSIVE MODE;

    LOCK TABLE table IN EXCLUSIVE MODE;
    et SSX qui peut être utilisé par les FKs:

    Share Row Exclusive Table Locks (SRX)

    A share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) is more restrictive than a share table lock. A share row exclusive table lock is acquired for a table as follows:

    LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;


    Permitted Operations: Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows using SELECT with the FOR UPDATE clause, but not to update the table.

    Prohibited Operations: A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive table locks and modifying the same table. A share row exclusive table lock also prohibits other transactions from obtaining share, share row exclusive, and exclusive table locks, which prevents other transactions from executing the following statements:

    LOCK TABLE table IN SHARE MODE;

    LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

    LOCK TABLE table IN ROW EXCLUSIVE MODE;

    LOCK TABLE table IN EXCLUSIVE MODE;

  14. #14
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Citation Envoyé par pifor Voir le message
    Un verrou RS est un verrou au niveau table qui signifie "j'ai verrouillé dans la table une ou plusieurs ligne(s) en mode exclusif"; ce n'est pas un verrou exclusif sur la table ...
    Bon ,d'accord et ... ce n'est pas ça que j'ai proposé, ni affirmé (relisez !). Tout simplement je n'arrive pas à voir votre point de vue et non pas les définitions.

  15. #15
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Session 1 
    
    SQL> lock table t_child in exclusive mode nowait;
    
    Table(s) verrouillÚe(s).
    
    Session 2
    
    SQL> update t_parent set field_name = 'TOTO' where field_name = 'UN';
    
    1 ligne mise Ó jour.
    Pas de blocage donc pas de SSX.
    Unindexed foreign keys cause DML on the primary key to get a share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) on the foreign key table. This prevents DML on the table by other transactions. The SSX lock is released immediately after it is obtained. If multiple primary keys are updated or deleted, the lock is obtained and released once for each row.
    Ca c'est tout.

  16. #16
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    En utilisant le schéma initial, on a la situation suivante:

    session 1:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    dev001> lock table  t_child in exclusive mode;
     
    Table(s) Locked.
    et session 2 attend sur session 1:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    dev001> update t_parent set field_pk = 101 where field_pk = 1;
    et

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    dev001> select * from dba_waiters;
     
    WAITING_SESSION HOLDING_SESSION LOCK_TYPE
    --------------- --------------- --------------------------
    MODE_HELD
    ----------------------------------------
    MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
    ---------------------------------------- ---------- ----------
                306             304 DML
    Exclusive
    Share                                         12558          0
    Si on rajoute dans le schéma:
    create unique index ifk on t_child(field_fk);
    On a la même situation d'attente mais avec:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    dev001> select * from dba_waiters;
     
    WAITING_SESSION HOLDING_SESSION LOCK_TYPE
    --------------- --------------- --------------------------
    MODE_HELD
    ----------------------------------------
    MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
    ---------------------------------------- ---------- ----------
                306             304 DML
    Exclusive
    Row-S (SS)                                    12576          0
     
     
    dev001>
    Pas d'index sur la clé étrangère et MAJ clé primaire table parent => verrou "share" (RS ?) sur la table enfant.
    index sur la clé étrangère et MAJ clé primaire table parent => verrou ROW-S "(SSX ?)" sur la table enfant.

  17. #17
    Membre éclairé Avatar de LBO72
    Profil pro
    Inscrit en
    Mai 2007
    Messages
    406
    Détails du profil
    Informations personnelles :
    Âge : 56
    Localisation : France

    Informations forums :
    Inscription : Mai 2007
    Messages : 406
    Par défaut
    Salut à tous

    Pifor
    dev001> SELECT * FROM dba_waiters;
    ...
    .

    C'est quoi cette table dba_waiters.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    PAVSD10)> SELECT * FROM all_waiters;
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    .

    Merci.
    LBO72.

  18. #18
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    DBA_WAITERS et DBA_BLOCKERS sont 2 vues liées à V$SESSION et V$LOCK:
    • DBA_WAITERS affiche les sessions en attente sur un verrou détenu par une autre session
    • DBA_BLOCKERS affiche les sessions qui détiennent un verrou démandé par une autre session (et qui donc bloquent une autre session).


    Ces vues n'ont pas d'équivalent avec USER_xxx ou ALL_xxx car elles ne sont pas liées directement à des objets stockés dans la base (comme les tables, les index, etc.) mais à l'activité des sessions et des transactions dans l'instance.

  19. #19
    Membre éclairé Avatar de LBO72
    Profil pro
    Inscrit en
    Mai 2007
    Messages
    406
    Détails du profil
    Informations personnelles :
    Âge : 56
    Localisation : France

    Informations forums :
    Inscription : Mai 2007
    Messages : 406
    Par défaut
    Ok, je te remercie, c'est très claire.
    Je n'arrivais pas à les voir en faisant un "desc dab_waiters", je pense c'est parce qu'elles n'ont pas de synonym. Je suis obligé de spécifier le possesseur de schéma devant : "desc sys.dab_waiters".

    Encore merci.
    LBO72.

  20. #20
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Citation Envoyé par pifor Voir le message
    En utilisant le schéma initial, on a la situation suivante:

    session 1:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    dev001> lock table  t_child in exclusive mode;
     
    Table(s) Locked.
    et session 2 attend sur session 1:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    dev001> update t_parent set field_pk = 101 where field_pk = 1;
    et

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    dev001> select * from dba_waiters;
     
    WAITING_SESSION HOLDING_SESSION LOCK_TYPE
    --------------- --------------- --------------------------
    MODE_HELD
    ----------------------------------------
    MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
    ---------------------------------------- ---------- ----------
                306             304 DML
    Exclusive
    Share                                         12558          0
    Si on rajoute dans le schéma:


    On a la même situation d'attente mais avec:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    dev001> select * from dba_waiters;
     
    WAITING_SESSION HOLDING_SESSION LOCK_TYPE
    --------------- --------------- --------------------------
    MODE_HELD
    ----------------------------------------
    MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
    ---------------------------------------- ---------- ----------
                306             304 DML
    Exclusive
    Row-S (SS)                                    12576          0
     
     
    dev001>
    Pas d'index sur la clé étrangère et MAJ clé primaire table parent => verrou "share" (RS ?) sur la table enfant.
    index sur la clé étrangère et MAJ clé primaire table parent => verrou ROW-S "(SSX ?)" sur la table enfant.
    Puis-je comprendre que vous est d’accord finalement avec le test que j’ai proposé, ou j’ai encore une fois raté quelque chose ?

Discussions similaires

  1. lock sur ma table
    Par kaking dans le forum Administration
    Réponses: 6
    Dernier message: 02/07/2008, 14h02
  2. Désactivation de toutes les Foreign Key pointant sur une table.
    Par sybaris dans le forum Développement
    Réponses: 3
    Dernier message: 29/06/2008, 20h57
  3. foreign key multiple (vers 2 tables ou plus)
    Par Bombastus dans le forum Langage SQL
    Réponses: 2
    Dernier message: 25/07/2006, 16h26
  4. comment gérer plusieurs locks sur une table?
    Par charluber dans le forum Oracle
    Réponses: 4
    Dernier message: 18/04/2006, 21h28
  5. Faire un Lock sur une table pendant l'exec d'un DTS
    Par Pete dans le forum MS SQL Server
    Réponses: 5
    Dernier message: 14/03/2005, 14h17

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo