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);