Deadlock sur autonomous transaction [Résolu]

[Pozzo]

Bonjour,

Il y a un soucis de deadlock sur mon appli.
Je connais l'ordre SQL qui est bloqué et son emplacement dans le package PLSQPL concerné grâce au fichier trace généré par Oracle.
Reste à déterminer l'emplacement de l'ordre SQL qui a bloqué la ligne.

Dans le cas de mon autonomous transaction le scénario est le suivant :
La transaction mère supprime une ligne.
Elle appelle une procédure autonome qui supprime la même ligne.
La fille attend que la mère libère la ligne avec un commit.
Elle ne rend donc pas la main à la mère qui est bloquée à son tour.
=> Deadlock

Pour éviter cela nous utilisons une règle qui dit qu'une table est géré que par transaction autonome ou sans aucune transaction autonome. (tout ou rien).
Pas dur.

Sauf que là je pense que cette règle a été transgressée.

Ma question est :
"Est ce que le contenu du fichier trace (15250 lignes) est sensé me donner l'emplacement (PlSql + Sql) du verrouillage de la ligne ?"

J'ai fureté un peu, lu le doc 62365.1 mais l'interprétation du fichier trace me semble peu documentée.

Une idée ?
Pozzo

Je suis donc à la recherche de l'odre delete

[pachot]

Bonjour,

SQL_TRACE (event 10046) ne donnera pas directement qui a posé un lock, mais peut permettre de le deviner en regardant les requêtes qui peuvent avoir touché la ligne en question.
Les locks peuvent être tracés par l'event 10704.
Si c'est un verrou TM (niveau table) on le voit. Si c'est un verrou TX (niveau ligne) alors les verrou TM correspondant (mode 2 ou 3) donnera peut être un indication.

Mais probablement pas besoin d'aller jusque là. C'est au design qu'il y a un problème. Pour quelle raison cette autonomous transaction ? Les cas qui justifient son utilisation sont très rares.

Cordialement,
Franck.

[Pozzo]

Merci Pacho,

Je vais regarder dans la direction du 10704.

Plutôt que le 10046 je pense aussi faire un trigger en utilisant "who called me" ça sera moins vaste à dépouiller.

Pour répondre à ta question oui c'est un problème de design. Plus exactement un problème ne non respect des règles imposées par le design.

Sur l'appli 3 tables ne doivent pas dépendre de la session principale. Ce sont des tables de log :
Un traitement peut effectuer un rollback ou un commit mais la log est toutjours "commitée" car quoi qu'il se passe on doit pouvoir le savoir.
J'utilise donc une transaction autonome.
Il se trouve qu'une action dans la session mère manipule certainement les mêmes données d'où le deadlock et ma recherche.

En tout cas merci.

En revanche j'ai cherché à droite et à gauche mis à part Larry Ellison et l'ainé de ses neveux (le rouquin sympa mais un peu énervant) personne ne semble savoir comment analyser complètement la log générée par un deadlock.

A+
Pozzo

[pachot]

Envoyé par Pozzo

Un traitement peut effectuer un rollback ou un commit mais la log est toutjours "commitée" car quoi qu'il se passe on doit pouvoir le savoir.
J'utilise donc une transaction autonome.
Il se trouve qu'une action dans la session mère manipule certainement les mêmes données d'où le deadlock et ma recherche.

Effectivement c'est le cas d'utilisation des autonomous transaction... sauf que la transaction principale ne devrait pas y toucher.

En revanche j'ai cherché à droite et à gauche mis à part Larry Ellison et l'ainé de ses neveux (le rouquin sympa mais un peu énervant) personne ne semble savoir comment analyser complètement la log générée par un deadlock.

Tu peux poster la trace du deadlock. si c'est un verrou TX, on devrait pouvoir y retrouver les rowid sans l'aide de Larry

[Pozzo]

Oui la transaction principale est bien fautive.

Retrouver le rowid ne m'avancera pas à grand chose et il est dedans.
C'est le programme (ou la requête à partir de laquelle je tenterai de trouver le programme) que je cherche.

Je poste le rapport.

Merci

[pachot]

Envoyé par Pozzo

Retrouver le rowid ne m'avancera pas à grand chose et il est dedans.

Envoyé par Pozzo

C'est le programme (ou la requête à partir de laquelle je tenterai de trouver le programme) que je cherche.

Une idée: avant de lançer l'appli, tu vérouille la table pour bloquer là où un verrou est posé. Et lorsque c'est en attente, alors tu peux investiguer dans v$session.

Sinon, en examinant, le tkprof tu verras toutes les requêtes. Peut-être que tu pourras identifier lesquelles sont susceptibles de vérouiller la table en question (pas toujours évident lorsque c'est vérouillé par de l'intégrité référentielle).

[Pozzo]

Merci Patcho,

Cela me semble astucieux.
Pour le moment je continue sur mon fichier trace (que j'ai attaché à ce message).
Voici le genre de ligne qui s'y trouve.

Ce que fait la session bloquée est clairement identifié en tête de fichier :

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
 
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00030003-0000718e        28     926     X             28     926           X
session 926: DID 0001-001C-00000011     session 926: DID 0001-001C-00000011
 
Rows waited ON:
Session 926: obj - rowid = 000224C8 - AAAiTIAAUAAAd7SAAD
  (dictionary objn - 140488, file - 20, block - 122578, slot - 3)
 
Information ON the OTHER waiting sessions:
End of information ON OTHER waiting sessions.
 
Current SQL statement FOR this session:
DELETE MES_IDENTIFIANTS WHERE UN_IDENTIFIANT = :B1 
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c0000000f3bca800       375  package body MON_SCHEMA.PKG_IDENTIFIANTS
c0000000fe160c40       387  package body MON_SCHEMA.PKG_TRAITEMENT
c0000000fe160c40       908  package body MON_SCHEMA.PKG_TRAITEMENT
c0000000fe160c40      1055  package body MON_SCHEMA.PKG_TRAITEMENT
c0000000f05496e8         1  anonymous block

Jusque là c'est clair et limpide.
La ligne 375 du package PKG_IDENTIFIANTS émet un ordre delete et se trouve bloquée.

Je pense (j'espère) que le reste du fichier est consacré à "l'autre" session.
C'est plutôt moins lisible. En recherchant le mot clé DELETE je tombe sur des blocs de cette forme :

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
 
   SO: c0000000f4c73fa0, type: 53, owner: c0000000fd181248, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: LOCK=c0000000f4c73fa0 handle=c0000000f3b427d8 mode=N
      call pin=0000000000000000 session pin=0000000000000000 hpc=0000 hlc=0000
      htl=c0000000f4c74020[c0000000e9b09838,c0000000e9b737a0] htb=c0000000e9b09838 ssga=c0000000e9b090f0
      user=c0000000fd181248 session=c0000000fd181248 count=1 flags=[0000] savepoint=0x4e9c1865
      LIBRARY OBJECT HANDLE: handle=c0000000f3b427d8 mtx=c0000000f3b42908(1) cdp=1
      name=DELETE MES_IDENTIFIANTS WHERE UN_IDENTIFIANT = :B1 
      hash=cf1522b3c4164b57e20a8d16ec896e63 timestamp=10-14-2011 18:53:01
      namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
      kkkk-dddd-llll=0000-0001-0001 LOCK=N pin=0 latch#=5 hpc=fffc hlc=fffc
      lwt=c0000000f3b42880[c0000000f3b42880,c0000000f3b42880] ltm=c0000000f3b42890[c0000000f3b42890,c0000000f3b42890]
      pwt=c0000000f3b42848[c0000000f3b42848,c0000000f3b42848] ptm=c0000000f3b42858[c0000000f3b42858,c0000000f3b42858]
      ref=c0000000f3b428b0[c0000000f3b428b0,c0000000f3b428b0] lnd=c0000000f3b428c8[c0000000f3b37e78,c0000000f3b43ac8]
        LIBRARY OBJECT: object=c0000000ee950e10
        type=CRSR flags=EXS[0001] pflags=[0000] STATUS=VALD LOAD=0
        CHILDREN: size=16
        child#    table reference   handle
        ------ -------- --------- --------
             0 c0000000ee9508d8 c0000000ee950548 c0000000fe11a220
             1 c0000000ee9508d8 c0000000ee9507e0 c0000000f04aaad0
        DATA BLOCKS:
        DATA#     heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 c0000000fe1d02e8 c0000000ee950f28 I/P/A/-/-    0 NONE   00

Reste à tenter de déterminer l'objet.
Je suppose que c'est l'objet (LIBRARY OBJECT: object=)c0000000ee950e10.
Si c'est de l'hexa ça fait de l'objectid = 4031425232
Franchement pour un objectid je trouve ça un peu gros.

Comprends pas.

Pozzo

[pachot]

Envoyé par Pozzo

Je pense (j'espère) que le reste du fichier est consacré à "l'autre" session.

Malheureusement, non. L'autre partie du fichier donne plin d'informations, mais rien d'intéressant dans ce cas.
C'est un verrou sour une ligne (TX mode X) et la seule information qui est enregistrée est: la ligne XXX est vérouillée par la transaction YYY. Rien d'autre. Le SQL qui a vérouillé l'a peut être fait longtemps avant, et la transaction a peut-être fermé et ouvert plein de curseurs...

[Pozzo]

Merci Pachot de ce tout ce temps passé.

Je suis assez déçu par la trace.
Dans mon cas cela aurait été pratique car l'incident est en prod chez un client. C'est facile de demander un fichier mais c'est compliqué de demander des manips ou d'installer un programme modifier juste pour diagnostique.

C'est pourtant ce que j'ai involontairement fait. En tentant un contournement dans mon programme c'est l'autre "session" qui s'est retrouvée bloquée.
J'ai donc eu toutes les infos sur la ligne qui bloquait.

Comme supposé c'était un delete sur la table en direct sans utiliser la routine autonome.

Le problème est donc réglé.

Merci encore pour ton aide.
Je clos le sujet.
Pozzo