Analyse DEADLOCK oracle

**petitfrere** · 06/11/2009, 11h19

Quelqu'un peut m'aider a comprendre ce deadlock SVP

je galere

MERCI !

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
user session for deadlock lock 0x2b80d7a10
  pid=75 serial=15431 audsid=426035813 user: 0/SYS
  O/S info: user: afnicreg, term: , ospid: 16588, machine: gibil.nic.fr
            program: perl@gibil.nic.fr (TNS V1-V3)
  application name: zonecheck, hash value=1103514497
  Current SQL Statement:
  UPDATE SIE_CTXT_MODULES SET ETAT = 'M', RAISON = :B1 WHERE ROWID = :B2 
user session for deadlock lock 0x2b80d7770
  pid=111 serial=52846 audsid=422023299 user: 0/SYS
  O/S info: user: afnicreg, term: , ospid: 32678, machine: gibil.nic.fr
            program: perl@gibil.nic.fr (TNS V1-V3)
  client info: cozone $Revision: 1.36 $
  application name: cozone, hash value=408725250
  action name: Finished, hash value=922424518
  Current SQL Statement:
  UPDATE SIE_CTXT_MODULES SET ETAT = 'M', RAISON = :B1 WHERE ROWID = :B2 
user session for deadlock lock 0x2b80d7cb0
  pid=111 serial=52846 audsid=422023299 user: 0/SYS
  O/S info: user: afnicreg, term: , ospid: 32678, machine: gibil.nic.fr
            program: perl@gibil.nic.fr (TNS V1-V3)
  client info: cozone $Revision: 1.36 $
  application name: cozone, hash value=408725250
  action name: Finished, hash value=922424518
  Current SQL Statement:
  UPDATE SIE_CTXT_MODULES SET ETAT = 'M', RAISON = :B1 WHERE ROWID = :B2 
user session for deadlock lock 0x2b80d7b60
  pid=75 serial=15431 audsid=426035813 user: 0/SYS
  O/S info: user: afnicreg, term: , ospid: 16588, machine: gibil.nic.fr
            program: perl@gibil.nic.fr (TNS V1-V3)
  application name: zonecheck, hash value=1103514497
  Current SQL Statement:
  UPDATE SIE_CTXT_MODULES SET ETAT = 'M', RAISON = :B1 WHERE ROWID = :B2 
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x140007][0xd4c30],[TX]
----------resource 0x0x299b339a8----------------------
resname       : [0x140007][0xd4c30],[TX]
Local node    : 1
dir_node      : 1
master_node   : 1
hv idx        : 11
hv last r.inc : 2
current inc   : 4
hv status     : 0
hv master     : 1
open options  : dd 
grant_bits    : KJUSERNL KJUSEREX 
grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX
count         : 1         0         0         0         0         1
val_state     : KJUSERVS_NOVALUE
valblk        : 0x00000000000000000000000000000000 .
access_node   : 1
vbreq_state   : 0
state         : x0
resp          : 0x299b339a8
On Scan_q?    : N
Total accesses: 570
Imm.  accesses: 491
Granted_locks : 1 
Cvting_locks  : 1 
value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0x2b80d7770 gl KJUSEREX rp 0x299b339a8 [0x140007][0xd4c30],[TX]
  master 1 gl owner 0x2bb3e3030 possible pid 5694 xid 6F000-0002-0001D6FE bast 0 rseq 47 mseq 0 history 0x14951495
  open opt KJUSERDEADLOCK  
CONVERT_Q: 
lp 0x2b80d7a10 gl KJUSERNL rl KJUSEREX rp 0x299b339a8 [0x140007][0xd4c30],[TX]
  master 1 gl owner 0x2bb50b1a0 possible pid 6587 xid 4B000-0002-0002C97D bast 0 rseq 47 mseq 0 history 0x9549549a
  convert opt KJUSERGETVALUE  
----------enqueue 0x0x2b80d7770------------------------
lock version     : 5248957
Owner node       : 1
grant_level      : KJUSEREX
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x299b339a8
procp            : 0x2ba459e70
pid              : 6587
proc version     : 99183
oprocp           : (nil)
opid             : 0
group lock owner : 0x2bb3e3030
possible pid     : 5694
xid              : 6F000-0002-0001D6FE
dd_time          : 0.0 secs
dd_count         : 0
timeout          : 0.0 secs
On_timer_q?      : N
On_dd_q?         : N
lock_state       : GRANTED
Open Options     : KJUSERDEADLOCK 
Convert options  : KJUSERNOQUEUE 
History          : 0x14951495
Msg_Seq          : 0x0
res_seq          : 47
valblk           : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
  possible owner[111.5694]
Submitting asynchronized dump request [28]
----------enqueue 0x0x2b80d7a10------------------------
lock version     : 4236393
Owner node       : 1
grant_level      : KJUSERNL
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x299b339a8
procp            : 0x2ba459e70
pid              : 6587
proc version     : 99183
oprocp           : (nil)
opid             : 0
group lock owner : 0x2bb50b1a0
possible pid     : 6587
xid              : 4B000-0002-0002C97D
dd_time          : 80.0 secs
dd_count         : 1
timeout          : 0.0 secs
On_timer_q?      : N
On_dd_q?         : Y
lock_state       : OPENING CONVERTING 
Open Options     : KJUSERDEADLOCK 
Convert options  : KJUSERGETVALUE 
History          : 0x9549549a
Msg_Seq          : 0x0
res_seq          : 47
valblk           : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
  possible owner[75.6587]
Submitting asynchronized dump request [28]
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0xb0025][0xd4184],[TX]
----------resource 0x0x22833a368----------------------
resname       : [0xb0025][0xd4184],[TX]
Local node    : 1
dir_node      : 1
master_node   : 1
hv idx        : 125
hv last r.inc : 2
current inc   : 4
hv status     : 0
hv master     : 1
open options  : dd 
grant_bits    : KJUSERNL KJUSEREX 
grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX
count         : 1         0         0         0         0         1
val_state     : KJUSERVS_NOVALUE
valblk        : 0x00000000000000000000000000000000 .
access_node   : 1
vbreq_state   : 0
state         : x0
resp          : 0x22833a368
On Scan_q?    : N
Total accesses: 935
Imm.  accesses: 781
Granted_locks : 1 
Cvting_locks  : 1 
value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0x2b80d7b60 gl KJUSEREX rp 0x22833a368 [0xb0025][0xd4184],[TX]
  master 1 gl owner 0x2bb50b1a0 possible pid 6587 xid 4B000-0002-0002C97D bast 0 rseq 80 mseq 0 history 0x49a51495
  open opt KJUSERDEADLOCK  
CONVERT_Q: 
lp 0x2b80d7cb0 gl KJUSERNL rl KJUSEREX rp 0x22833a368 [0xb0025][0xd4184],[TX]
  master 1 gl owner 0x2bb3e3030 possible pid 5694 xid 6F000-0002-0001D6FE bast 0 rseq 80 mseq 0 history 0x5495149a
  convert opt KJUSERGETVALUE  
----------enqueue 0x0x2b80d7b60------------------------
lock version     : 4209563
Owner node       : 1
grant_level      : KJUSEREX
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x22833a368
procp            : 0x2ba3eb370
pid              : 5694
proc version     : 123385
oprocp           : (nil)
opid             : 0
group lock owner : 0x2bb50b1a0
possible pid     : 6587
xid              : 4B000-0002-0002C97D
dd_time          : 0.0 secs
dd_count         : 0
timeout          : 0.0 secs
On_timer_q?      : N
On_dd_q?         : N
lock_state       : GRANTED
Open Options     : KJUSERDEADLOCK 
Convert options  : KJUSERNOQUEUE 
History          : 0x49a51495
Msg_Seq          : 0x0
res_seq          : 80
valblk           : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
  possible owner[75.6587]
Submitting asynchronized dump request [28]
----------enqueue 0x0x2b80d7cb0------------------------
lock version     : 3494339
Owner node       : 1
grant_level      : KJUSERNL
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x22833a368
procp            : 0x2ba3eb370
pid              : 5694
proc version     : 123385
oprocp           : (nil)
opid             : 0
group lock owner : 0x2bb3e3030
possible pid     : 5694
xid              : 6F000-0002-0001D6FE
dd_time          : -1.0 secs
dd_count         : 0
timeout          : 0.0 secs
On_timer_q?      : N
On_dd_q?         : Y
lock_state       : OPENING CONVERTING 
Open Options     : KJUSERDEADLOCK 
Convert options  : KJUSERGETVALUE 
History          : 0x5495149a
Msg_Seq          : 0x0
res_seq          : 80
valblk           : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
  possible owner[111.5694]
Submitting asynchronized dump request [28]
Global blockers dump end:------------------

**Pomalaix** · 06/11/2009, 12h13

Bonjour

Peut-être que ces liens vous éclaireront :
http://forums.oracle.com/forums/thre...hreadID=668157
http://asktom.oracle.com/pls/asktom/...:1528515465282

La difficulté n'est pas tant de savoir quelles étaient les lignes de données en interblocage, mais
- d'identifier dans l'application à quel endroit le code est mal conçu (permettant à la session S1 de verrouiller X puis Y, pendant que S2 verrouille Y puis X).
- d'identifier l'éventuelle anomalie transactionnelle qui est résultée de l'annulation de l'instruction (et non de la transaction complète) effectuée automatiquement par Oracle

**petitfrere** · 06/11/2009, 16h32

si je pouvais l'avoir en live... c'est beaucoup plus simple,

j'ai trouvé une solution pour avoir tous ces deadlock en live.... ou presque

Le fonctionnement est le suivant: lorsque survient le message Oracle ORA-
00060, le trigger de base de données est activé et les informations importantes du
Deadlock sont insérées dans deux tables.

Voici les tables requises, le trigger de base de données et la procédure PL/SQL installés
sous User Sys:

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
 
Table tvd_lock_info_1
CREATE TABLE tvd_lock_info_1 (
     lock_date                  DATE
   , os_locker                  VARCHAR2(30)
   , locker_schema              VARCHAR2(30)
   , locker_pid                 VARCHAR2(9)
   , os_waiter                  VARCHAR2(30)
   , waiter_schema              VARCHAR2(30)
   , waiter_pid                 VARCHAR2(9)
   , sql_text_waiter            VARCHAR2(200)
);
Table tvd_lock_info_2
  CREATE TABLE tvd_lock_info_2 (
     lock_date                  DATE
   , wait                       VARCHAR2(3)
   , os_user                    VARCHAR2(30)
   , process                    VARCHAR2(9)
   , locker                     VARCHAR2(30)
   , object_owner               VARCHAR2(30)
   , object_name                VARCHAR2(30)
   , program                    varchar2(50)
)
TABLESPACE users
/
Procédure tvd_prc_lock_info
CREATE OR REPLACE PROCEDURE tvd_prc_lock_info
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
     INSERT INTO tvd_lock_info_1
       ( lock_date, os_locker, locker_schema, locker_pid,
         os_waiter, waiter_schema, waiter_pid, sql_text_waiter )
       SELECT      /* first the table-level locks (TM) and mixed TM/TX TX/TM */
               SYSDATE
            , 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
          OBJ$               O
        , USER$              U
        , V$LOCK             L_WAITER
        , V$LOCK             L_LOCKER
        , V$SESSION          S_WAITER
        , V$SESSION          S_LOCKER
    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 /* now the (usual) row-locks TX */
          SYSDATE
        , 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;
INSERT INTO tvd_lock_info_2
  ( lock_date, wait, os_user, process, locker,
    object_owner, object_name, program )
  SELECT SYSDATE
        , 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
        , USER$      U
        , OBJ$       O
            , V$SESSION S
         WHERE   U.USER# = O.OWNER#
           AND   S.SID = L.SID
           AND   L.ID1 = O.OBJ#
           AND   L.TYPE = 'TM'
           AND   U.NAME != 'SYS'
      UNION
        SELECT SYSDATE
             , 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';
    COMMIT;
END;
/
show errors
Trigger log_deadlock_errors
CREATE OR REPLACE TRIGGER log_deadlock_errors
  AFTER SERVERERROR
  ON DATABASE
BEGIN
  IF (IS_SERVERERROR (60)) THEN
    sys.tvd_prc_lock_info;
  END IF;
END log_deadlock_errors;
/
show errors

Analyse DEADLOCK oracle

Administration Oracle

Vue hybride

Discussions similaires

Partager

Partager