Un UPDATE pas si bête…

Il y a des questions qu’on ne se pose jamais lorsqu’on utilise Oracle! Celle-ci en fait partie… « Comment influencer l’ordre dans lequel un update fait ses mises à jour? ». D’où vient cette idée bizarre me direz-vous?

Soit une table T1 avec une colonne COL1 qui a une contrainte d’unicité.

create table t1(col1 number,
constraint t1_col1_uk unique(col1));

begin
for i in 1..10 loop
insert into t1 values (i);
end loop;
commit;
end;
/

Imaginez que vous vouliez changer la valeur de 1 en 2, celle de 2 en 3, celle de 3 en 4, etc. Il suffit d’exécuter:

update t1 set col1=col1+1;

commit;

Bon ça marche! fin de l’histoire? Pas tout à fait: si vous capturer le SQL depuis Logminer et le rejouez, ça ne marche pas

alter system archive log current;

col name format a100 new_value archivelog
set lines 100

select name
from v$archived_log
where sequence#=
(select max(sequence#)
from v$archived_log
where thread#=(select thread#
from v$thread)
);

NAME
-------------------------------
/archivelog/1_221_681179242.dbf

begin
dbms_logmnr.add_logfile(
logfilename => '&&archivelog',
options => dbms_logmnr.new);
end;
/

exec dbms_logmnr.start_logmnr( options => -
dbms_logmnr.dict_from_online_catalog);

select sql_redo
from v$logmnr_contents
where seg_owner=user
and table_name='T1'
and operation='UPDATE';

SQL_REDO
----------------------------------------------------------------------
update "SYS"."T1" set "COL1" = '2' where "COL1" = '1' and ROWID [...];
update "SYS"."T1" set "COL1" = '3' where "COL1" = '2' and ROWID [...];
update "SYS"."T1" set "COL1" = '4' where "COL1" = '3' and ROWID [...];
update "SYS"."T1" set "COL1" = '5' where "COL1" = '4' and ROWID [...];
update "SYS"."T1" set "COL1" = '6' where "COL1" = '5' and ROWID [...];
update "SYS"."T1" set "COL1" = '7' where "COL1" = '6' and ROWID [...];
update "SYS"."T1" set "COL1" = '8' where "COL1" = '7' and ROWID [...];
update "SYS"."T1" set "COL1" = '9' where "COL1" = '8' and ROWID [...];
update "SYS"."T1" set "COL1" = '10' where "COL1" = '9' and ROWID [...];
update "SYS"."T1" set "COL1" = '11' where "COL1" = '10' and ROWID [...];

Si vous annulez votre update et vous rejouez les ordres capturés par logminer, vous obtenez le résultat ci-dessous:

update t1 set col1=col1-1;
commit;

update "SYS"."T1" set "COL1" = '2'
where "COL1" = '1'
and ROWID = 'AAATDpAABAAAVxpAAA';

update "SYS"."T1" [...]
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.T1_COL1_UK) violated

Une erreur… Plutôt intéressant quand on sait que Streams utilise LogMiner pour la capture et qu’il retombe parfaitement sur ses pieds dans ce cas particulier, sans utiliser de contrainte DEFFERABLE.

Comment les autres s’en sortent? Intéressante question à laquelle je ne repondrai pas… Enfin, j’ai testé avec MySQL et pour vous en sortir, il faut ajouter une clause order by à votre update:

mysql>  select * from demo;
+----+
| id |
+----+
| 0 |
| 1 |
| 2 |
+----+
3 rows in set (0.00 sec)

mysql> update demo set id=id+1;
ERROR 1062 (23000): Duplicate entry '1' for key 1

mysql> update demo set id=id+1 order by id desc;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Reste qu’Oracle fait encore mieux, à l’image de la commande ci-dessous qui remplace en une seule passe 1 en 2 et 2 en 1:

update t1
set col1=decode(col1,1,2,1)
where col1 in (1,2);

6 réflexions sur “Un UPDATE pas si bête…”

  1. Avec Streams, les contraintes sont vérifiées; Et pour cause, il peut s’agir d’une replication bi-directionnelle et c’est important de détecter les conflits.

  2. C’est un peu plus qu’un « defer » d’une contrainte (qui n’est deja pas deferrable).

    En effet, dans l’update ci-dessus: 1->2 et 2->1; Imaginons que Streams transforme 1 en 2 avec un 1er update, le 2eme update (avec where id=2), devrait toucher 2 lignes pour lesquelles id=2 desormais.

  3. En fait, peut-être que tout simplement les contraintes ne sont pas vérifiées car c’est inutile puisque déjà fait sur la base lorsque la transaction est validée.
    C’est ce que dit la doc. Oracle pour une logical standby:

    Constraints — Check constraints are evaluated on the primary database and do not need to be re-evaluated on the logical standby database

  4. D’après la page 263 de Expert Oracle (Tom Kyte), les contraintes ne sont vérifiées qu’à la fin de l’exécution de l’instruction SQL. Ceci explique pourquoi l’instruction SQL ensembliste initiale ne déclenche pas l’erreur ORA-00001. Je suppose que Streams doit savoir que les instructions unitaires viennent d’une seule instruction ensembliste et qu’il doit donc différer la vérification de la contrainte.

  5. Bonjour,
    La question serai plutôt : « Comment fait Streams pour retomber sur ses pieds ? » 😉

    Cordialement,

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *