locks sur 11g

**Guigui2** · 20/10/2009, 11h13

Bonjour,

Nous avons note une différence curieuse dans le comportement de oracle database 11g et oracle 9i. Il s’agit de locks. Nous avons créé deux tables comme le montre le SQL joint, dont une parent et une enfant. Nous avons réalisé un insert dans la table enfant qui a généré des locks dans la table parent, mais des locks différents selon la version de la base de données ou nous avons fait l’expérience.

En 11g nous avons eu des locks de type Row-X(SX) (« mode held ») et en 9i des locks de type Row-S(SS) (« mode held »).

Malheureusement pour nous, nous avons une base de production en 11g et de gros problèmes de locks qui vraisemblablement proviennent de ce qui précède.

Cependant, merci de noter que lorsque nous avons fait le test sur la base 11g, nous avons mis 2 tables d’essai dans notre base de données de production ; alors que le test en 9i a été effectué sur une nouvelle base de données.

L’explication peut donc venir d’un bug de la 11g, auquel cas, existe-t-il un patch, ou bien d’un mauvais paramétrage de la base, mais lequel ?

J’ai pensé au mode d’isolation des données mais je ne sais pas ou cela se lit.

Pour information nous sommes sous AIX 5.3 en 64 bits.

Merci de vos suggestions.

**petitfrere** · 20/10/2009, 11h26

C'est un souvenir lointain mais ca peut peut etre t'aider...

Ya longtemp j'ai eu le meme symptome apres une migration d'une base de la 9i vers la 10g, le comportement des locks avez changer...

a priori cela n'était pas vue comme un bug mais une evolution d'oracle

tu dois pouvoir changer ce mode mode de fonctionnement pour revenir a ton ancien mode via un alter system mais je me souviens plus de la commande exact

attention je suis pas sure a 100% ma memoire me fait defaut....

**fatsora** · 20/10/2009, 15h07

Bonjour,

Il y a une différence de comportement en effet mais Row-X(SX) existe aussi en 10G R2 patch 4 pour les memes instructions ....

Quelle version exacte de oracle 11G tu as ?

Sinon as tu mesure le temps d'execution des insert sur les 2 bases ?

Sinon , en Prod est ce qu'il y a seulement des insert ?
pas de delete ou update ?

A vérifier :

- Meme clé unique écrite pas differentes sessions
- Clés étrangeres non indexés lors de modifications des tables parentes sur les clés primaires
- Présence d' indexes Bitmap

**petitfrere** · 20/10/2009, 15h14

Whoua j'ai retrouvé mon probleme que j'avais posté en 2006....

http://www.developpez.net/forums/d24...-lock-3-10gr2/

With this fix enabled:
alter session set "_fix_control"='4969880:ON'; -- 11g
alter session set events '38084 trace name context forever, level 1'; -- 9.2/10g
select * from test where f1 is null for update;
select lmode from v$lock where type = 'TM';

Description

The fix for bug 3646162 changed the behaviour of
SELECT FOR UPDATE operations such that with that fix
they correctly take a sub-exclusive mode TM lock on
the affected tables. This change in behaviour introduced
by that fix led to problems for some client code so this
fix introduces a backout method to disable the fix and
revert to the old (incorrect) TM lock level.

To enable this fix in >= 10.2.0.2 set "_fix_control"='4969880:ON'

To enable this fix in 9.2 / 10.1 / 10.2.0.1 set event 38084 to any level.

eg:
create table test( f1 varchar2(10));
select * from test where f1 is null for update;
select lmode from v$lock where type = 'TM';
^
LMODE is 3 (SX mode)

With this fix enabled:
alter session set "_fix_control"='4969880:ON'; -- 11g
alter session set events '38084 trace name context forever, level 1'; -- 9.2/10g
select * from test where f1 is null for update;
select lmode from v$lock where type = 'TM';
^
LMODE is 2 (SS mode)

The full bug text (if published) can be seen at Bug 4969880 (This link will not work for UNPUBLISHED bugs)
You can search for any interim patches for this bug here Patch 4969880 (This link will Error if no interim patches exist)

**Guigui2** · 20/10/2009, 15h53

Bonjour,

Merci pour votre aide. Je vais transmettre ces informations à mon administrateur. Donc merci de patienter pour en connaître le résultat car il n'est pas là en ce moment.

Evidemment toute autre idée est bonne à prendre, vu que cela fait quelque mois que nous ramons (après être passé par des histoires d'initrans, mais ça ne venait pas de là...)

Pour information la version de la bdd est 11.1.0.7.0.

Par ailleurs il semble que nous ayons des problèmes similaires à ceux de l'INSERT sur des UPDATE et probablement sur des DELETE.

Cordialement

**fatsora** · 20/10/2009, 16h25

Envoyé par Guigui2

Bonjour,

Par ailleurs il semble que nous ayons des problèmes similaires à ceux de l'INSERT sur des UPDATE et probablement sur des DELETE.

Cordialement

Verifier

- Clés étrangeres non indexés lors de modifications des tables parentes sur les clés primaires
- Présence d' indexes Bitmap
- ITL( Interested Transaction List) lors d'update

**Guigui2** · 22/10/2009, 11h02

Bonjour,

Au fait, dites moi avez vous pu tester le code SQL que j'ai mis sur le fichier joint sur une utre base 11g. J'aimerai savoir si vous avez fait le même constat dans le type de vérouillage.

Cordialement

**fatsora** · 22/10/2009, 12h02

Envoyé par Guigui2

Bonjour,

Au fait, dites moi avez vous pu tester le code SQL que j'ai mis sur le fichier joint sur une utre base 11g. J'aimerai savoir si vous avez fait le même constat dans le type de vérouillage.

Cordialement

J'ai le meme resultat en 11G patch 07 linux et 12G R2 patch 4 windows ....

Mais bon il faut relire les posts au dessus ... le pb ne vient pas uniquement de 11G !

**Guigui2** · 22/10/2009, 12h16

Effectivement... autant pour moi.
Et sans indiscrétion ça ne vous pose pas de souci. Car j'ai bon espoir que cela soit l'explication des nombreux blocages que nous rencontrons... mais je m'étonnerait d'être le seul avec qui ces vérouillages génèrent autant de lock. Il est vrai que notre applicatif est vieux et que nous avons fait en une fois une migration d'une bdd 8i vers une 11g...

Cordialement

**Guigui2** · 26/10/2009, 11h59

Bonjour,

J'ai transmis notre discussion à un pretstaire qui m'a suggéré de faire :
alter system set "_fix_control"='4969880:ON' scope=memory;

Cela correspond à ce que proposait Petitfrere. Pour l'instant je vérifie que tout fonctionne correctement sur un table créée à l'occasion. Puis je fais mon alter system. Le problème c'est que j'ai l'impression que pour le nouveau paramètre soit pris en compte il faut supprimer la table. Sortir et revenir dans la session n'a semble-t-il pas suffit en tout cas. Qu'en pensez vous ?

Cordialement,

Guigui2

**Guigui2** · 28/10/2009, 15h22

Bonjour,

Je reviens vers vous pour une question/ précision qui me semble importante.
En effet j'ai constaté que si la plupart des sessions bloquantes avaient les caractéristiques suivantes :
Lock type = DML ; Mode Held = Row-X(SX) ; Mode Requested = Nonce ; Object type = TABLE A,
je constate également que la plupart des sessions bloquées ont les caractéristiques suivantes :
Lock type = DML ; Mode Held = None ; Mode reuqested = Share ; Object type = TABLE A.
Donc plutôt que de regarder côté BLOQUANT, je m'intéresse au BLOQUE et je vois ce mode requested = Share.
Pouvez-vous me dire si c'est normal qu'une session en Row-X(SX) sur une table A bloque une session en mode requested = Share sur la même table A.
Dans l'affirmative, pouvez-vous me dire quel sont les types de requêtes qui génèrent des mode requested = Share ? et à quoi sert ce mode ?

Espérant que vous aurez réponse à ces nouvelles questions.

Merci

**pachot** · 28/10/2009, 16h30

Bonjour,

Envoyé par Guigui2

Pouvez-vous me dire si c'est normal qu'une session en Row-X(SX) sur une table A bloque une session en mode requested = Share sur la même table A.

Oui, l'info devrait se trouver là:
http://download.oracle.com/docs/cd/B...htm#sthref2078

Envoyé par Guigui2

Dans l'affirmative, pouvez-vous me dire quel sont les types de requêtes qui génèrent des mode requested = Share ? et à quoi sert ce mode ?

Lorsque la foreign key n'est pas indexée dans la table fille, alors la suppréssion d'un enregistrement de la table parent (ou l'update de la clé) va poser un Table Share lock sur la table fille pour vérifier qu'il n'y a pas d'insert en cours.

Par contre si la foreign key est indexée, pas besoin de verrou au niveau table. Oracle va aller voir la première entrée d'index correspondant à ce parent, et:
- soit il n'y a pas d'entrée -> il peut supprimer le parent
- soit il y a une entrée commitée: il y a violation d'intégrité référentielle
- soit il y a une entrée non commitée: il attends (lock) pour voir ce qu'il en est.

Si tu veux un exemple, j'avais fait cç sur un forum en anglais:
http://www.dba-village.com/village/d...A=34366#120848

Cordialement,
Franck.

**Guigui2** · 28/10/2009, 16h50

Bonjour,

Dacodac.
Si je comprends bien il faut que je vérifie toutes mes foreigns keys qu'elles soient bien indexées et que je vérifie aussi tout simplement que toutes mes tables soient bien indexées. Il faut éviter les indexes en bitmap en général.
Un prestataire va venir vérifier toutes mes clefs prochainement.

Par ailleurs, y'a t-il d'autres types de requêtes qui génèrent du verrouillage en mode Share, que je face une recherche dans mon application (Forms 10g). J'ai vu que c'était semble-t-il le cas de CREATE INDEX, mais je doute fortement que notre applicatif utilise cela.

Enfin, le fait que les clefs étrangères et les clefs principales soient "uniques" et non pas "primaires" peut-il avoir un impact sur les verrous...?

Merci encore.

**pachot** · 28/10/2009, 17h30

il faut que je vérifie toutes mes foreigns keys qu'elles soient bien indexées

Oui, sauf pour celles dont le parent ne sera jamais supprimé.

Par exemple: un site de vente en europe a une table COMMANDE qui a une foreign key sur une table PAYS
-> vu qu'il y a peu de pays, un index sur COMMANDE ( PAYS ) n'est pas assez selectif pour être interressant lors des requêtes
-> on ne va jamais supprimer un PAYS (ou alors c'est une opération exceptionelle qui peut être faite offline)
Il est inutile de créer cet index

Il faut éviter les indexes en bitmap en général

En transactionnel, c'est plutôt vrai. En terme de lock, un lock qui sera posé sur une entrée d'index va vérouiller plusieurs enregistrements.

y'a t-il d'autres types de requêtes qui génèrent du verrouillage en mode Share

Je ne connais que 'LOCK TABLE table IN SHARE MODE;' et le pb des foreign key non indexes lors d'un delete du parent (mais le lock est relaché tout de suite depuis la 9i).
Mais il y en a peut-être d'autres.

J'ai vu que c'était semble-t-il le cas de CREATE INDEX

Ca me parait logique, create index doit s'assurer qu'il n'y a pas de modifications en cours dans la table. Par contre il autorise d'autres create index.

Enfin, le fait que les clefs étrangères et les clefs principales soient "uniques" et non pas "primaires" peut-il avoir un impact sur les verrous...?

A tester...

**pachot** · 28/10/2009, 18h00

Enfin, le fait que les clefs étrangères et les clefs principales soient "uniques" et non pas "primaires" peut-il avoir un impact sur les verrous...?

En fait, je ne vois pas pourquoi ce serait différent

**Guigui2** · 28/10/2009, 18h17

Bonjour Pachot,

A propos des indexes Bitmap, tu écris : "
En transactionnel, c'est plutôt vrai. En terme de lock, un lock qui sera posé sur une entrée d'index va vérouiller plusieurs enregistrements."
Dois-je comprendre que le comportement indiqué : vérouillage de plusieurs enregistrements, est celui qui peut se produire avec des clefs bitmap et que c'est la raison pour laquelle on les évite en général ?

Par ailleurs concernant la différence de comportement entre clefs primaires et clefs uniques, il peut y en avoir une dans la mesure ou sauf erreur une clef primaire ne s'"update" pas alors qu'une clef unique (ex. un numéro de sécruité sociale) peut théoriquement s'"updater".

Cordialement

**Guigui2** · 28/10/2009, 18h30

Or donc, j'ai remarqué que quasiment tous mes locks étaient dûs à des "Row-X(SX)" qui bloquent des "Share".

Mais y'a un truc qui me titille...

Pachot dit :
"Je ne connais que 'LOCK TABLE table IN SHARE MODE;' et le pb des foreign key non indexes lors d'un delete du parent (mais le lock est relaché tout de suite depuis la 9i). Mais il y en a peut-être d'autres."

Alors, y'a des chances qu'il y en ait d'autres vu que nous des locks on en a tous les jours... et qu'on est en 11g... Donc encore un coup, si quelqu'un sait comment on peut provoquer un verrouillage en mode Share, autrement que :
- avec Create Index,
- qu'avec les clefs étrangères mal indexées
- avec 'LOCK TABLE table IN SHARE MODE;'

et bien je suis méga intéressé !

Merci à vous pour votre aide en tout cas.

**pachot** · 29/10/2009, 00h46

Dois-je comprendre que le comportement indiqué : vérouillage de plusieurs enregistrements, est celui qui peut se produire avec des clefs bitmap et que c'est la raison pour laquelle on les évite en général ?

C'est une raison, oui. L'autre raison, c'est que lorsqu'on update un enregistrement, c'est tout un paquet de bitmap qui est updaté dans l'index. beaucoup de undo/redo généré, et parfois ce paquet remonte dans les branches de l'index et l'index grossit (en hauteur).

Par ailleurs concernant la différence de comportement entre clefs primaires et clefs uniques, il peut y en avoir une dans la mesure ou sauf erreur une clef primaire ne s'"update" pas alors qu'une clef unique (ex. un numéro de sécruité sociale) peut théoriquement s'"updater".

Oui, mais ça ne vient pas du fait que tu déclare une contrainte primary key ou unique, ca vient plutôt de la nature des colonnes, et du fait qu'elles sont référencées par des foreign keys.

Si une table a une foreign key vers le numéro de SS, alors tu auras toujours le même problème qu'il soit déclaré en unique ou en primary key.

Alors, y'a des chances qu'il y en ait d'autres vu que nous des locks on en a tous les jours

Est-ce tu utilise dbms_lock ?

Il faudrait que tu sois prêt à lancer une requête sur v$lock et v$locked_objects dès que tu en vois. S'il y en a tous les jours, tu lse trouveras

**Guigui2** · 29/10/2009, 17h06

Bonjour à tous,

Je reviens vers vous car je ne m'en sorts toujours pas.

Nous avons mis en oeuvre la note 33453.1 de metalink pour vérifier que les clefs étrangères des tables étaient correctement indexées. Il semble que cela soit bien le cas. Le résultat de la requête qui vérifie cela est No line selected.

U:\>sqlplus xxxxxxxx/xxxxxxx@xxxxxxxxxxx
SQL*Plus: Release 10.1.0.4.2 - Production on Ven. Oct. 16 10:36:32 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.

ConnectÚ Ó :
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> -- check for Metalink Doc ID: 33453.1
SQL> select *
2 from user_constraints
3 where constraint_type = 'R'
4 and not exists ( select 1
5 from user_indexes
6 where index_name = user_constraints.r_constraint_name );

aucune ligne selectionnee
-------------------------------------------------------------

MAIS CETTE REQUETE CORRESPOND ELLE BIEN A CELLE QU'IL ME FAUT EFFECTUER POUR VERIFIER QUE TOUTES LES CLEFS ETRANGERES SONT BIEN INDEXEES ???

J'ai en outre essayé de comprendre une trace relevée sur notre serveur, mais ne suis pas très compétent en la matière.

J’ai par exemple regardé un blocage a priori sur TAB_FAC_ACMPT (voir copie écran jointe qui montre que SID 285 bloque SID 304 et aussi les modes de verrouillages).

Ce qui est curieux

c’est que le blocage semble se faire sur la table TAB_FAC_ACMPT alors que la requête bloquée appellerait en update la table TABDOS et que dans TABDOS il n’y a pas de clef étrangère provenant de TAB_FAC_ACMPT. TABDOS n’a donc a priori aucune raison de bloquer TAB_FAC_ACMPT.

A contrario il y a dans TAB_FAC_ACMPT des clefs étrangères dont une provenant de TABDOS.

Dans ces conditions, est-ce normal qu'un lock puisse se faire comme on le voit dans le fichier joint ?

1- Le bloquant effectuerai une manipulation sur TAB_FAC_ACMPT (il fait d’abord un INSERT sur TAB_FAC_ACMPT et ensuite un SELECT FOR UPDATE sur TAB_FAC_ACMPT) :

Voici l'insert :

INSERT INTO TAB_FAC_ACMPT (VLMNTACMPT, NUM_INT_FAC_ACMPT, NUM_FAC_ACMPT, NUMDOS, NBPART, VLUNIT, NBGRAT, CDTVAUN, VLTXTUN, VLFRAD, CDTVAAD, VLTXTAD, VLASSA, CDTVAAS, VLTXTAS, VLFACI, VLFRAN, CDTVAAN, VLTXTAN, VLFACD, TYPFAC, ETAFAC, DATFAC, TITRE, LI1DEST, LI2DEST, LI1ADR, LI2ADR, LI3ADR, LIBVIL, CODPOS, VLTAXE) VALUES (: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)
Bind#0 value=367.2
Bind#1 value=15974
Bind#2 value=1
Bind#3 value=67456
Bind#4 value=29
Bind#5 value=67.02
Bind#6 value=2
Bind#7 value="T0"
Bind#8 value=0
Bind#9 value=0
Bind#10 value="T0"
Bind#11 value=0
Bind#12 value=58.31
Bind#13 value="T0"
Bind#14 value=0
Bind#15 value=332.89
Bind#16 value=0
Bind#17 value="T0"
Bind#18 value=0
Bind#19 value=0
Bind#20 value="C"
Bind#21
Bind#22 value="10/12/2009 0:0:0"
Bind#23
Bind#24
Bind#25 value="NOM XXXXX"
Bind#26 value="AVENUE DES BIGOUDIS"
Bind#27 value="B.P. 32"
Bind#28
Bind#29 value="VILLE YYY"
Bind#30 value="11111"
Bind#31 value=0

Ensuite voici le SELECT FOR UPDATE que fait le bloquant sur TAB_FAC_ACMPT vraismbablement généré automatiquement par Forms 10g :
SELECT VLMNTACMPT, NUM_INT_FAC_ACMPT, NUM_FAC_ACMPT, NUMDOS, NBPART, VLUNIT, NBGRAT, CDTVAUN, VLTXTUN, VLFRAD, CDTVAAD, VLTXTAD, VLASSA, CDTVAAS, VLTXTAS, VLFACI, VLFRAN, CDTVAAN, VLTXTAN, VLFACD, TYPFAC, ETAFAC, DATFAC,TITRE, LI1DEST, LI2DEST, LI1ADR, LI2ADR, LI3ADR, LIBVIL, CODPOS, VLTAXE FROM TAB_FAC_ACMPT WHERE ROWID="AAAVViAAHAAAhf8AAG" FOR UPDATE OF VLMNTACMPT NOWAIT

2- Sauf erreur la requête bloquée serait la suivante :
UPDATE TABDOS SET CODDES=20, CODDOS=1, CODDOST=, CODDOSH=, LIBDOS=”Vacances”, COPETA=”BEL”, CODETA=186, COPANI=”BEL”, CODANI=544, APP_OFF=”N”, NUMAEX=11, CODETM=”P”, CODSEC=”SEC”, NUMDOS=69110, ETADOS=”OP”, NUVERD=1, NUMANN=9, CODUTI=”DOD”, FLIPAI=, DATANN=, DATOPT="10/12/2009 0:0:0", DATCON=, CODREF= WHERE ROWID=:"AAAVVsAAHAAAjsMAAL"

3- A noter que le bloquant et le bloqué ne semblent pas appeler les mêmes données.

4- Les tables TAB_FAC_ACMPT et TABDOS sont liées, parce qu’ il y a dans TAB_FAC_ACMPT une clef étrangère provenant de TABDOS qui est NUMDOS

5- Comme le montre la copie d’écran jointe, d’autres tables sont également bloquées TABDES, TABDOS, TABETA, TABETM, TABREG, TABUTI, TABVER et TABANI (mais mode requested = None et Mode Held = « Row-X (SX) ».
A noter que seul le blocage sur TAB_FAC_ACMPT a comme mode requested : « Share » et comme mode held = « None ». Toutes les autres tables ne sont pas directement appelées par l’UPDATE TABDOS mais sont liées à TABDOS qui en a les identifiants comme clefs étrangères (c est à dire que dans TABDOS il ya des clefs étrangères qui viennent de TABDES, TABDOS, TABETA, TABETM, TABREG, TABUTI, TABVER et TABANI).

6- Voici le DESC de 3 tables parmi celles concernées dans l’exemple

(TAB_FAC_ACMPT , TABDOS, TABDES)

SQL> desc TAB_FAC_ACMPT;
Nom NULL ? Type
---------------------------------------- -------- ------------------------
NUM_INT_FAC_ACMPT NOT NULL NUMBER(5) => ID
NUMDOS NOT NULL NUMBER(5) => FK de TABDOS
NUM_FAC_ACMPT NOT NULL NUMBER(2)
DATFAC NOT NULL DATE
TYPFAC NOT NULL VARCHAR2(1)
VLUNIT NUMBER(10,2)
NBPART NUMBER(3)
NBGRAT NUMBER(2)
VLFACI NOT NULL NUMBER(16,2)
VLFACD NOT NULL NUMBER(16,2)
VLFRAD NOT NULL NUMBER(5,2)
VLFRAN NOT NULL NUMBER(5,2)
ETAFAC CHAR(1)
VLASSA NUMBER(14,2)
NUMFAV NUMBER(5)
FLCMPT CHAR(1)
VLMNTACMPT NOT NULL NUMBER(16,2)
TITRE VARCHAR2(1)
LI1DEST VARCHAR2(35)
LI2DEST VARCHAR2(40)
LI1ADR VARCHAR2(35)
LI2ADR VARCHAR2(35)
LI3ADR VARCHAR2(35)
LIBVIL VARCHAR2(35)
CODPOS VARCHAR2(10)
CDTVAUN VARCHAR2(3)
VLTXTUN NUMBER(6,4)
CDTVAAD VARCHAR2(3)
VLTXTAD NUMBER(6,4)
CDTVAAN VARCHAR2(3)
VLTXTAN NUMBER(6,4)
CDTVAAS VARCHAR2(3)
VLTXTAS NUMBER(6,4)
VLTAXE NUMBER(16,2)

SQL> desc TABDOS ;
Nom NULL ? Type
----------------------------------------- -------- ----------------
NUMANN NOT NULL NUMBER(2)
NUMDOS NOT NULL NUMBER(5) => ID
CODSEC NOT NULL VARCHAR2(4)
LIBDOS NOT NULL VARCHAR2(60)
ETADOS NOT NULL VARCHAR2(2)
COPETA VARCHAR2(3)
CODETA NUMBER(5)
COPANI VARCHAR2(3)
CODANI NUMBER(5)
CODDES NOT NULL VARCHAR2(2)=>FK de TABDES
CODREF VARCHAR2(8)
NUVERD NUMBER(2)
FLIPAI VARCHAR2(1)
CODUTI VARCHAR2(3)
NUMAEX NOT NULL NUMBER(2)
CODETM CHAR(1)
DATCON DATE
DATOPT DATE
DATANN DATE
ETAFIN CHAR(1)
FLCMPT CHAR(1)
CODDOS VARCHAR2(3)
CODDOST VARCHAR2(1)
CODDOSH NUMBER(1)
APP_OFF VARCHAR2(1)

SQL> desc TABDES;
Nom NULL ? Type
----------------------------------------- ---------------------------
CODDES NOT NULL VARCHAR2(2) => ID
LIBDES NOT NULL VARCHAR2(40)
PRMARP NUMBER(4,2)

**Guigui2** · 29/10/2009, 17h16

Et merci pour l'aide !!!

locks sur 11g

Administration Oracle

Discussions similaires

Partager

Partager