Verrou autobloquant sur un DELETE

Bonjour,

J'ai une requête de purge de table qui se présente sous la forme suivante :

Code:

---

1 2 3 4

delete from T1 where T1.COL1 in (select COL1 from T2 where T2.JOUR >= '01/01/2008') or T1.COL2 in (select COL2 from T3 where T3.JOUR >= '01/01/2008')

---

Et quand je la lance j'ai systématiquement un verrou de type 'TM' et un de type 'TX' sur la table T1, ce qui fait qu'elle se bloque toute seule ! Je n'arrive pas à comprendre comment c'est possible...
Quelqu'un a une idée ?

P.S. : c'est une base ... 8.1.7.4

Merci.

faudrait faire 2 DELETE peut-être. T'as des indexes sur col1 et col2 ?

Citation:

---

A row lock, also called a TX lock, is a lock on a single row of table. A transaction
acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or
SELECT ... FOR UPDATE statement. The row lock exists until the transaction
commits or rolls back.

---

Citation:

---

A table lock, also called a TM lock, is acquired by a transaction when a table is
modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE
clause, or LOCK TABLE statement. DML operations require table locks to reserve DML
access to the table on behalf of a transaction and to prevent DDL operations that
would conflict with the transaction.

---

Jusqu'au la c'est normal.
Que est-ce que vous comprenez par "qu'elle se bloque toute seule " ? Qu'il y a une deadlock ou que le delete n'abouti pas ?

Elle se bloque toute seule : j'entends par là qu'avant de lancer la requête je n'ai pas de verrou. Et dès qu'elle est lancée elle est systématiquement bloquée par ces verrous, même après suppression/recréation des tables.. Comme il n'y a aucune autre session en cours sur ce schéma j'imagine que ce verrou est produit par la requête elle-même. Ce qui semble assez curieux.

J'avais regardé les définitions de ces verrous mais je n'ai rien vu qui puisse le justifier.

Mais je viens de voir un indice intéressant : il y a un index sur T1.COL2, mais pas sur T1.COL1. Et en décomposant en 2, la requête suivante reste toujours bloquée

Code:

---

1 2	DELETE FROM T1 WHERE T1.COL1 IN (SELECT COL1 FROM T2 WHERE T2.JOUR >= '01/01/2008')

---

alors que si on prend T3 à la place de T2 elle ne bloque plus.
La table T1 a environ 600.000 lignes et la T2 950.000 et ça fait 2 full scan.

Je vais essayer de mettre un index sur T1.COL2 pour voir.

Session 10

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 Connected as mni   SQL> SQL> select * 2 from v$lock 3 where sid = 25 4 /   ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------   SQL>

---

Session 25

Code:

---

1 2 3

delete from employees /

---

Session 10

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> SQL> select * 2 from v$lock 3 where sid = 25 4 /   ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------   SQL> SQL> select * 2 from v$lock 3 where sid = 25 4 /   ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- 32EDC154 32EDC16C 25 TM 27124 0 3 0 9 0 32EFBD44 32EFBE60 25 TX 458767 1951 6 0 9 0   SQL>

---

Comme vous pouvez les constater il y a toujours 2 verrous qui sont posés!
Il me semble que en fait vous voulez dire que votre delete dure trop longtemps et que vous avez l'impresion qu'il est bloqué.

OK, cela veut donc dire que 2 verrous sont alors tout simplement les siens.
Cela donnait effectivement l'impression qu'elle n'avançait pas, au bout de 5 ou 6 heures (avec des boucles sur ROWNUM < 20000 alternés avec des commit).

En tous cas en mettant l'index cela marche en quelques secondes avec 2 delete séparés, mais pas en laissant le OR entre les 2...

Merci.

Citation:

---

Envoyé par mnitu

Comme vous pouvez les constater il y a toujours 2 verrous qui sont posés!
.

---

Oui on ne voit que 2 verrous dans V$LOCK mais cela ne signifie pas qu'il s'agit des seuls verrous pris par Oracle dans ce cas: le verrou de niveau ligne n'apparaît jamais dans V$LOCK car Oracle écrit ce verrou dans le bloc de la table qui contient la ligne.