Procédure détection Deadlock / insertion impossible

Version imprimable

Bonjour,
J'ai créé une procédure qui permet d'enregistrer des informations concernant les interbloquages dans une base de données.
Je créé tout d'abord une table pour stocker mes informations:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
create table report_deadlock(
lock_date VARCHAR2(30),
wa_waiting_session NUMBER,
wa_username VARCHAR2(30),
wa_lock_type VARCHAR2(26),
wa_sql_id VARCHAR2(13),
wa_sql_text VARCHAR2(1000),
ho_holding_session NUMBER,
ho_username VARCHAR2(30),
ho_lock_type VARCHAR2(26),
ho_sql_id VARCHAR2(13),
ho_sql_text VARCHAR2(1000));

Ensuite je crée une procédure autonome, cette procédure doit enregistrer les informations de l'interbloquage dans une table:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE OR REPLACE PROCEDURE insert_report_deadlock IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO report_deadlock(lock_date, wa_waiting_session, wa_username, wa_lock_type, wa_sql_id, wa_sql_text, ho_holding_session, ho_username, ho_lock_type, ho_sql_id, ho_sql_text) 
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text
from dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea
where wa_dba_waiters.waiting_session=wa_v$session.sid
and wa_v$session.sql_address=wa_v$sqlarea.address
and ho_dba_waiters.holding_session=ho_v$session.sid
and ho_v$session.sql_address=ho_v$sqlarea.address;
 
COMMIT;
END insert_report_deadlock;

Puis je crée un trigger

Code:

1
2
3
4
5
6
7
8
9
 
CREATE OR REPLACE TRIGGER report_deadlock_trigger
AFTER SERVERERROR
ON DATABASE
BEGIN
IF (IS_SERVERERROR (60)) THEN
sys.insert_report_deadlock;
END IF;
END report_deadlock_trigger;

J'ai vérifié en provoquant un interbloquage et en exécutant mon "INSERT" qui est dans la procédure, les valeurs sont bien insérées dans la tables:

Code:

1
2
3
4
5
6
7
8
 
INSERT INTO report_deadlock(lock_date, wa_waiting_session, wa_username, wa_lock_type, wa_sql_id, wa_sql_text, ho_holding_session, ho_username, ho_lock_type, ho_sql_id, ho_sql_text) 
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text
from dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea
where wa_dba_waiters.waiting_session=wa_v$session.sid
and wa_v$session.sql_address=wa_v$sqlarea.address
and ho_dba_waiters.holding_session=ho_v$session.sid
and ho_v$session.sql_address=ho_v$sqlarea.address;

J'ai vérifier que le trigger se déclenche bien lorsque je provoque un interbloquage en modifiant le corps de ma procédure par:

Code:

1
2
 
insert into test(col1) values ('bibaboomm');

Je retrouve bien ma valeur insérée. Pourquoi es ce que je n'ai rien dans ma table "report_deadlock" lorsque je laisse le trigger se déclencher pour logger les informations sur les interbloquages????

02/12/2009, 15h11
mnitu
Je peur que l'information n'est plus disponible!
Ajoutez
Code:

1 2 dbms_output.put_line('Lignes inserées = '||To_Char(SQL%ROWCOUNT));
dans ta procédure après l'insert et avant le commit.

Ben malheureusement je pense que si.

Je provoque un interbloquage.
Tout de suite je lance ma requête

Code:

1
2
3
4
5
6
7
 
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text
from dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea
where wa_dba_waiters.waiting_session=wa_v$session.sid
and wa_v$session.sql_address=wa_v$sqlarea.address
and ho_dba_waiters.holding_session=ho_v$session.sid
and ho_v$session.sql_address=ho_v$sqlarea.address;

Elle me retourne les bonnes valeurs.
J'ai exécuté cette requête 15 minutes après elle me retourne toujours les bons résultats.
Je me suis dis peut être qu'il faut un peu de temps (quelques secondes pour que l'infos soit disponible j'ai donc rajouté un "dbms.sleep(60)" meme problème rien n'ai enregistré dans ma table.
Si pendant ces 15 minutes je lance à la main l'insertion:

Code:

1
2
3
4
5
6
7
8
 
INSERT INTO report_deadlock(lock_date, wa_waiting_session, wa_username, wa_lock_type, wa_sql_id, wa_sql_text, ho_holding_session, ho_username, ho_lock_type, ho_sql_id, ho_sql_text) 
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text
from dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea
where wa_dba_waiters.waiting_session=wa_v$session.sid
and wa_v$session.sql_address=wa_v$sqlarea.address
and ho_dba_waiters.holding_session=ho_v$session.sid
and ho_v$session.sql_address=ho_v$sqlarea.address;

Ma ligne est bien insérée, je ne comprends pas!!!

02/12/2009, 15h41
ricoflashfire
En faite si je provoque le deadlock et que j'exécute
Code:

1 2 3 4 begin sys.insert_report_deadlock; end;
ca marche, alors que si je laisse le trigger se déclencher ca ne marche pas.
Mais je sais que le trigger et bon car si je change le corps de la procédure ca marche.

Bien bizzar ....

Sur une base Oracle 10.2.0.4.0 ou Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production, ton select:

Code:

1
2
3
4
5
6
7
 
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text
FROM dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea
WHERE wa_dba_waiters.waiting_session=wa_v$session.sid
AND wa_v$session.sql_address=wa_v$sqlarea.address
AND ho_dba_waiters.holding_session=ho_v$session.sid
AND ho_v$session.sql_address=ho_v$sqlarea.address

ne me ramène rien. Si je le bidouille j'ai l'enregistrement.
Mais essayez d'ajouter la ligne dans la procédure comme j'ai vous le dit. Quelle est le résultat ?

Si je lance apres avoir provoqué un deadlock:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE OR REPLACE PROCEDURE insert_report_deadlock IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO report_deadlock(lock_date, wa_waiting_session, wa_username, wa_lock_type, wa_sql_id, wa_sql_text, ho_holding_session, ho_username, ho_lock_type, ho_sql_id, ho_sql_text) 
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text
from dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea
where wa_dba_waiters.waiting_session=wa_v$session.sid
and wa_v$session.sql_address=wa_v$sqlarea.address
and ho_dba_waiters.holding_session=ho_v$session.sid
and ho_v$session.sql_address=ho_v$sqlarea.address;
dbms_output.put_line('Lignes inserées = '||To_Char(SQL%ROWCOUNT));
COMMIT;
END insert_report_deadlock;
/
set serveroutput on
begin
sys.insert_report_deadlock;
end;

Ca me retourne "Lignes inser¿es = 1";
La procédure fonctionne, le problème c'est que si elle est déclenchée par le trigger, elle ne fonctionne pas.
Il y a quelque chose qui bloque ma procédure dans le trigger mais je ne vois pas quoi. Si je remplace cette procédure par une banale ca fonctionne donc ca viens bien du faite que j'utilise cette procédure dans ce trigger.

Je ne pense pas qu'il y a "quelque chose" qui bloque ta procédure quand tu l'appel depuis ton trigger. Je pense que le select en question ne ramène pas les données pour une cause à déterminer ; à propos tu n’as pas indiqué ta version d’Oracle.
Essaye de remplacer l’insert dans ta procédure avec une boucle sur le select et d’afficher les données avec dbms_output, quelque chose de type
Code:

1 2 3 4 5 6 7 8 9 10 11 12 For crs in (SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text FROM dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea WHERE wa_dba_waiters.waiting_session=wa_v$session.sid AND wa_v$session.sql_address=wa_v$sqlarea.address AND ho_dba_waiters.holding_session=ho_v$session.sid AND ho_v$session.sql_address=ho_v$sqlarea.address; ) Loop dbms_output.put_line(crs.waiting_session); ... End Loop;

02/12/2009, 18h08
ricoflashfire
Ok mais dans ce cas comment es possible que lorsque j'exécute explicitement la procédure ca marche:
Code:

1 2 3 4 begin sys.insert_report_deadlock; end;
02/12/2009, 20h08
mnitu

Citation:

Envoyé par ricoflashfire

Ok mais dans ce cas comment es possible que lorsque j'exécute explicitement la procédure ca marche:
...

Beh, pour l'instant ça ne marche que pour vous. Je vous ai demandé déjà 2 fois quelle est votre version de base des données.
Mais pour répondre à votre question savez-vous que vous interrogez en fait des tables virtuelles et non pas de vrai tables (Overview of the Dynamic Performance Views)?
03/12/2009, 09h11
ricoflashfire

La version est 10.2.4.
C'est normal que ca ne retourne rien, tant qu'il n'y pas de deadlock sur la bdd les vues dynamiques ne retournent rien. Dès qu'un blocage survient elles retournent des valeurs. C'est pour ca que je précise à chaque fois que je fais le test en provoquant un deadlock. Les vues sur lesquelles je travaillent permettent de retourner des informations au DBA sur ces deadlocks.

Citation:

Envoyé par ricoflashfire

La version est 10.2.4.
C'est normal que ca ne retourne rien, tant qu'il n'y pas de deadlock sur la bdd les vues dynamiques ne retournent rien.

C'était la premise il me semble
Session 1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as mni
 
SQL> update emp
  2     set sal = sal * 1.1 
  3   where empno = 7369
  4  /
 
1 row updated
 
SQL> 
SQL> update emp
  2     set sal = sal * 1.1
  3   where empno = 7900
  4  /

Session 2

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
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as mni
 
SQL> update emp
  2     set sal = sal * 1.2
  3   where empno = 7900
  4  /
 
1 row updated
 
SQL> 
SQL> update emp
  2     set sal = sal * 1.1
  3   where empno = 7369
  4  /
 
 
update emp
   set sal = sal * 1.1
 where empno = 7369
 
ORA-00060: détection d'interblocage pendant l'attente d'une ressource
 
SQL>

Votre requête

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as mni
 
SQL> 
SQL> SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text
  2    FROM dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea
  3   WHERE wa_dba_waiters.waiting_session=wa_v$session.sid
  4     AND wa_v$session.sql_address=wa_v$sqlarea.address
  5     AND ho_dba_waiters.holding_session=ho_v$session.sid
  6     AND ho_v$session.sql_address=ho_v$sqlarea.address;
 
TO_CHAR(SYSDATE,'DD-MM-YYYYHH2 WAITING_SESSION USERNAME                       LOCK_TYPE                  SQL_ID        SQL_TEXT                                                                         HOLDING_SESSION USERNAME                       LOCK_TYPE                  SQL_ID        SQL_TEXT
------------------------------ --------------- ------------------------------ -------------------------- ------------- -------------------------------------------------------------------------------- --------------- ------------------------------ -------------------------- ------------- --------------------------------------------------------------------------------
 
SQL> r
 
TO_CHAR(SYSDATE,'DD-MM-YYYYHH2 WAITING_SESSION USERNAME                       LOCK_TYPE                  SQL_ID        SQL_TEXT                                                                         HOLDING_SESSION USERNAME                       LOCK_TYPE                  SQL_ID        SQL_TEXT
------------------------------ --------------- ------------------------------ -------------------------- ------------- -------------------------------------------------------------------------------- --------------- ------------------------------ -------------------------- ------------- --------------------------------------------------------------------------------
 
SQL>

Votre requête modifiée

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as mni
 
SQL> 
SQL> SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'), wa_dba_waiters.WAITING_SESSION, wa_v$session.USERNAME, wa_dba_waiters.LOCK_TYPE, wa_v$sqlarea.SQL_ID, wa_v$sqlarea.sql_text, ho_dba_waiters.HOLDING_SESSION--, ho_v$session.USERNAME, ho_dba_waiters.LOCK_TYPE, ho_v$sqlarea.SQL_ID, ho_v$sqlarea.sql_text
  2    FROM dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session--, v$sqlarea ho_v$sqlarea
  3   WHERE wa_dba_waiters.waiting_session=wa_v$session.sid
  4     AND ho_dba_waiters.holding_session=ho_v$session.sid
  5     AND wa_v$session.sql_address=wa_v$sqlarea.address
  6  --   AND ho_v$session.sql_address=ho_v$sqlarea.address;
 
TO_CHAR(SYSDATE,'DD-MM-YYYYHH2 WAITING_SESSION USERNAME                       LOCK_TYPE                  SQL_ID        SQL_TEXT                                                                         HOLDING_SESSION
------------------------------ --------------- ------------------------------ -------------------------- ------------- -------------------------------------------------------------------------------- ---------------
03-12-2009 10:17:40                         25 MNI                            Transaction                2a1c6ktkdt8rq  update emp    set sal = sal * 1.1  where empno = 7900                                        38
 
SQL>

Procédure détection de deadlock => même problème si je l'utilise avec un trigger.

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
 
CREATE OR REPLACE PROCEDURE analyse_deadlock IS
CURSOR deadlock_cursor IS
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') lock_date, wa_dba_waiters.WAITING_SESSION wa_waiting_session, wa_v$session.USERNAME wa_username, wa_dba_waiters.LOCK_TYPE wa_lock_type, wa_v$sqlarea.SQL_ID wa_sql_id, wa_v$sqlarea.sql_text wa_sql_text, ho_dba_waiters.HOLDING_SESSION ho_holding_session, ho_v$session.USERNAME ho_username, ho_dba_waiters.LOCK_TYPE ho_lock_type, ho_v$sqlarea.SQL_ID ho_sql_id, ho_v$sqlarea.sql_text ho_sql_text
from dba_waiters wa_dba_waiters, v$session wa_v$session, v$sqlarea wa_v$sqlarea, dba_waiters ho_dba_waiters, v$session ho_v$session, v$sqlarea ho_v$sqlarea
where wa_dba_waiters.waiting_session=wa_v$session.sid
and wa_v$session.sql_address=wa_v$sqlarea.address
and ho_dba_waiters.holding_session=ho_v$session.sid
and ho_v$session.sql_address=ho_v$sqlarea.address;
BEGIN
FOR deadlock_record IN deadlock_cursor LOOP 
DBMS_OUTPUT.put('Analyse deadlock :');
DBMS_OUTPUT.put_line(deadlock_record.lock_date);
DBMS_OUTPUT.put_line('-------------------------------Session en bloquee-----------------------------');
DBMS_OUTPUT.put_line('SID :' || deadlock_record.wa_waiting_session);
DBMS_OUTPUT.put_line('Nom de l''utilisateur :' || deadlock_record.wa_username);
DBMS_OUTPUT.put_line('Type de lock :' || deadlock_record.wa_lock_type);
DBMS_OUTPUT.put_line('Sql ID de la requete :' || deadlock_record.wa_sql_id);
DBMS_OUTPUT.put_line('Requete :' || deadlock_record.wa_sql_text);
DBMS_OUTPUT.put_line('--------------------------------Session en bloquante--------------------------');
DBMS_OUTPUT.put_line('SID :' || deadlock_record.ho_holding_session);
DBMS_OUTPUT.put_line('Nom de l''utilisateur :' || deadlock_record.ho_username);
DBMS_OUTPUT.put_line('Type de lock :' || deadlock_record.ho_lock_type);
DBMS_OUTPUT.put_line('Sql ID de la requete :' || deadlock_record.ho_sql_id);
DBMS_OUTPUT.put_line('Requete :' || deadlock_record.ho_sql_text);
END LOOP;
END analyse_deadlock;