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

Version imprimable

Voir 40 message(s) de cette discussion en une page

29/11/2007, 22h26
strikerm59

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.
30/11/2007, 09h32
pifor

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.

J'ai pris connaissance de l'explication de la documentation Oracle.

Voici le test que j'effectue :

Code:

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 ?

01/12/2007, 11h53
mnitu

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
01/12/2007, 13h01
pifor

Il y a quand même eu certains changements depuis Oracle 9 d'après la doc Oracle 9.
01/12/2007, 15h17
strikerm59

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.
01/12/2007, 18h45
mnitu

Citation:

Envoyé par pifor

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 :)

Code:

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:

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.

02/12/2007, 23h25
strikerm59

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...
03/12/2007, 09h07
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.

Voir le tableau des verrous de niveau table à ce sujet dans le Concepts Guide.
03/12/2007, 17h38
mnitu

Citation:

Envoyé par strikerm59

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

Citation:

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.

???
03/12/2007, 20h07
pifor
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:

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:

1 2 3 4 5 SQL> show user; USER est "O" SQL> lock table t in row share mode; Table(s) verrouillee(s).
03/12/2007, 20h23
pifor

Par contre, RX n'est pas SSX :oops:

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

Citation:

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:

Citation:

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;
03/12/2007, 20h31
mnitu

Citation:

Envoyé par pifor

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.

Code:

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.

Citation:

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.

En utilisant le schéma initial, on a la situation suivante:

session 1:

Code:

1
2
3
dev001> lock table  t_child in exclusive mode;
 
Table(s) Locked.

et session 2 attend sur session 1:

Code:

dev001> update t_parent set field_pk = 101 where field_pk = 1;

Code:

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:

Citation:

create unique index ifk on t_child(field_fk);

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

Code:

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.

05/12/2007, 10h07
LBO72
Salut à tous

Citation:

Pifor
dev001> SELECT * FROM dba_waiters;
...

.

C'est quoi cette table dba_waiters.
Code:

1 2 3 4 5 PAVSD10)> SELECT * FROM all_waiters; * ERROR at line 1: ORA-00942: table or view does not exist
.

Merci.
LBO72.
05/12/2007, 10h15
pifor
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.
05/12/2007, 10h45
LBO72

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.

Citation:
Envoyé par pifor
En utilisant le schéma initial, on a la situation suivante:

session 1:
Code:

1 2 3 dev001> lock table t_child in exclusive mode; Table(s) Locked.
et session 2 attend sur session 1:

Code:

dev001> update t_parent set field_pk = 101 where field_pk = 1;

et
Code:

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:

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 ?

Voir 40 message(s) de cette discussion en une page