J’ai eu aujourd’hui une discussion plus qu’instructive avec mon « ami » de MySQL. Il faut dire que je fais maintenant le pitre avec MySQL aussi pendant que lui, apprend Oracle ! Enfin, si je peux générer un peu de revenu pour Oracle : j’utilise uniquement InnoDB…

Voilà comment ça commence : « Il ne comprend pas que les données validées par un commit sont accessibles des autres transactions en cours ; l’isolation des transactions n’est pas respecté avec Oracle [sic] ». Pour ceux qui me connaissent, je vous laisse imaginer comment ce genre de trucs me tue.

Par défaut, le niveau d’isolation d’Oracle est READ COMMITED qui signifie justement ce qui est validé par un commit est lu des autres sessions, même si elles ont une transaction en cours. REPEATABLE READ est le niveau d’isolation par défaut de MySQL et… Ça n’existe pas dans Oracle ! Avant que vous ne vous mettiez à rigoler, c’est parce que ce niveau autorise les lignes fantômes qu’Oracle ne le supporte pas. Tom Kyte l’explique 1000 fois que moi les différents niveaux d’isolation et ce n’est pas le sujet de ce post. Et pourtant…

Alors si REPEATABLE READ « sucks », pourquoi MySQL/InnoDB a-t-il ce niveau par défaut ? Parce que InnoDB ne laisse pas apparaître de lignes fantômes en READ COMMITED dans la majorité des cas ! merci à l’algorithme dit « Next-Key Locking » qui verrouille visiblement tous les plages de valeurs parcourues, y compris avant la première valeur et après la dernière valeur, sur les index (ou les tables s’il s’agit d’un table scan). J’avoue que la page qui décrit le fonctionnement me laisse perplexe… Ça ressemble à un niveau SERIALIZABLE (même si j’ai bien compris que le niveau SERIALIZABLE de MySQL est carrément inutilisable à plus d’un utilisateur concurrent) avec une faiblesse quand même.

Ne nous arrêtons pas là, tout ça donne plein d’exemples très intéressants ! Evidemment pour limiter ces effets, MySQL vient aussi par défaut avec autocommit=on ;).

Etape 1 : Créer une table et des index pour MySQL

Avant tout, créer un utilisateur pour faire des tests sous MySQL :

mysql -u root -p
mysql> create user scott identified by 'tiger';
mysql> create database users;
mysql> grant all on users.* to scott;
mysql> exit;

Ensuite, créer une table et quelques index :

mysql -u scott -p -D users

create table gark(a int, b int, c int)
engine innodb;
create unique index gark_a_uk on gark(a);
create index gark_b_idx on gark(b);
insert into gark(a,b,c) values (1,1,10);
insert into gark(a,b,c) values (2,2,9);
insert into gark(a,b,c) values (3,1,8);
insert into gark(a,b,c) values (4,2,7);
insert into gark(a,b,c) values (5,1,6);
insert into gark(a,b,c) values (6,2,5);
insert into gark(a,b,c) values (7,1,4);
insert into gark(a,b,c) values (8,2,3);
insert into gark(a,b,c) values (9,1,2);
insert into gark(a,b,c) values (10,2,1);
[Pas besoin de commit ;0 ; vous êtes en autocommit=on !]

Etape 2 : Créer les mêmes structures sous Oracle XE (gratuit et disponible pour Linux Ubuntu) :

Les structures sont semblables à celles créées dans MySQL :

sqlplus scott
create table gark(a number,b number,c number);
create unique index gark_a_uk on gark(a);
create index gark_b_idx on gark(b);
insert into gark(a,b,c) values (1,1,10);
insert into gark(a,b,c) values (2,2,9);
insert into gark(a,b,c) values (3,1,8);
insert into gark(a,b,c) values (4,2,7);
insert into gark(a,b,c) values (5,1,6);
insert into gark(a,b,c) values (6,2,5);
insert into gark(a,b,c) values (7,1,4);
insert into gark(a,b,c) values (8,2,3);
insert into gark(a,b,c) values (9,1,2);
insert into gark(a,b,c) values (10,2,1);
commit;

Etape 3 : Update de la 5eme valeur d’une colonne non indexée dans MySQL en REPEATABLE READ :

Ouvrez 2 sessions sur MySQL ; Les tests qui suivent ont été réalisés avec MySQL Beta 5.1.20 sur Linux, mais j’imagine que ça n’a pas beaucoup d’importance :

1ère session :

mysql -u scott -p -D users
set autocommit=off;
select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
select * from gark where c=5 for update;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    6 |    2 |    5 |
+------+------+------+

2ème session (pas la peine de se mettre en autocommit off) :

mysql -u scott -p -D users
select * from gark where c=3 for update;
[un peu de patience...]
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[Remarquez que les "waits" sur les verrous sont limités dans le temps par défaut...On comprend bien pourquoi]

Ça n’arrive pas si la colonne dans la clause where est indexée. Nous verrons plus tard que même dans le cas de colonnes indexées, ça a un impact. mais avant…

1ère session :

rollback;

Etape 4 : Le même test sous Oracle en niveau d’isolation SERIALIZABLE

1ère session :

sqlplus scott
set transaction isolation level serializable;
select * from gark where c=5 for update;
  A          B          C
---------- ---------- ----------
  6          2          5

2ème session :

sqlplus scott
set transaction isolation level serializable;
select * from gark where c=5 for update;
  A          B          C
---------- ---------- ----------
  8          2          3

Un commentaire particulier ?

1ère session :

rollback;

2ème session :

rollback;

Etape 5 : Update d’une ligne avec un index non-unique dans MySQL

Toujours en « repeatable read », nous allons maintenant utiliser une colonne indexée dans la clause where puisque semble-t-il il le faut :

1ère session :

mysql -u scott -p -D users
set autocommit=off;
select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
select * from gark where b=2 for update;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    2 |    9 |
|    4 |    2 |    7 |
|    6 |    2 |    5 |
|    8 |    2 |    3 |
|   10 |    2 |    1 |
+------+------+------+

2ème session (pas la peine de se mettre en autocommit off) :

mysql -u scott -p -D users
insert into gark values (11,2,0);
[un peu de patience...]
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Ici, la colonne dans la clause where est indexée. Imaginons que je veuille créer un « batch » qui pre
nne les valeurs dans une table et pour les traiter avant de les supprimer. Je suppose que la bonne façon, c’est de les sélectionner ensuite sur la clé primaire ou unique « for update »… Avant de continuer :

1ère session :

rollback;

Etape 6 : Même test avec Oracle XE en niveau d’isolation SERIALIZABLE

1ère session :

sqlplus scott
set transaction isolation level serializable;
select * from gark where b=2 for update;
       A          B          C
---------- ---------- ----------
       2          2          9
       4          2          7
       6          2          5
       8          2          3
      10          2          1

2ème session :

sqlplus scott
set transaction isolation level serializable;
insert into gark values (11,2,0);
commit;

1ère session :

select * from gark where b=2 for update;
       A          B          C
---------- ---------- ----------
       2          2          9
       4          2          7
       6          2          5
       8          2          3
      10          2          1

Toujours rien à ajouter ?

1ère session :

rollback;

2ème session :

rollback;

Etape 7 : Update d’un ensemble de lignes avec un index unique dans MySQL

Toujours en « repeatable read », nous allons maintenant utiliser une colonne avec un index unique puisqu’il semble que ce soit la solution :

1ère session :

mysql -u scott -p -D users
set autocommit=off;
delete from gark where a=5;
commit;
select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
select * from gark
where a<=8 for update; 
+------+------+------+ 
| a    | b    | c    |
+------+------+------+ 
|    1 |    1 |   10 |  
|    2 |    2 |    9 |  
|    3 |    1 |    8 |  
|    4 |    2 |    7 |  
|    6 |    2 |    5 |  
|    7 |    1 |    4 |  
|    8 |    2 |    3 |  
+------+------+------+ 

2ème session (pas la peine de se mettre en autocommit off) :

mysql -u scott -p -D users
insert into gark values (5,1,6);
[un peu de patience...]
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction

Ici, la colonne dans la clause where est indexée avec un index unique. Avant de continuer :

1ère session :

rollback;

Etape 8 : Même test avec Oracle XE en niveau d’isolation SERIALIZABLE

1ère session :

sqlplus scott
delete from gark where a=5;
commit;
set transaction isolation level serializable;
select * from gark
where a<=8 for update;
         A          B          C 
---------- ---------- ----------
         1          1         10 
         2          2          9 
         3          1          8 
         4          2          7 
         6          2          5 
         7          1          4 
         8          2          3 

2ème session :

sqlplus scott
set transaction isolation level serializable;
insert into gark values (5,1,6);
commit;

1ère session :

select * from gark
where a<=8 for update;
         A          B          C 
---------- ---------- ---------- 
         1          1         10 
         2          2          9 
         3          1          8 
         4          2          7 
         6          2          5 
         7          1          4 
         8          2          3

1ère session :

rollback;

2ème session :

rollback;

Etape 9 : En fait, Oracle ne fonctionne peut-être pas ?

L’objectif de MySQL a travers ce fonctionnement est d’empêcher les clés duplicate. Voyons comment Oracle se comporte si on essaye d’insérer 2 fois la même clé… Juste pour vérifier qu’Oracle fonctionne correctement et n’est pas juste trop optimiste

1ère session :

sqlplus scott
set transaction isolation level serializable;
insert into gark values (12,2,-2);

2ème session :

sqlplus scott
set transaction isolation level serializable;
insert into gark values (12,2,-2);
[Maintenant j'attends... et avec Oracle, ce risque de durer longtemps]

1ère possibilité : rollback de la 1ère session, 2ème session :

1 row created.

2ème possibilité : commit de la 1ère session, 2ème session :

insert into gark values (12,2,-2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.GARK_A_UK) violated

[Et pourtant... dans la même session...]
select * from gark where a=12;

no rows selected

Etape 10 : Jusque maintenant tout allait bien pour MySQL

Bon, tout ça c’est bien mais revenons au début de nos réflexions… REPEATABLE READ, « it sucks » avec un exemple très simple qui illustre ce qui peut vous arriver si vous utiliser ce niveau d’isolation :Session 2 (On crée une seconde table dans InnoDB avec un Index) :

set autocommit=on;
create table gark2(a int)
engine innodb;
create unique index
select * from gark2;

[on est d'accord, il n'y a rien dans la table]
Empty Set (0.00 sec)

Session 1 :

rollback;
set autocommit=off;
select a from gark where a=1 for update;
+------+
| a    |
+------+
|    1 |
+------+
[on est d'accord, la transaction a bien commencé]

Session 2 :

insert into gark2 values(1);
[Pas besoin de faire de commit puisqu'on est en autocommit]

Session 1 :

select a from gark2 where a=1 for update;
+------+
| a    |
+------+
|    1 |
+------+
[on est d'accord, cette valeur n'existait pas quand la transaction a commencé.
Autrement dit, vous pouvez voir les lignes insérées par d'autres transactions]

Bon, je résume… MySQL formatte les lignes beaucoup mieux qu’Oracle, on est d’accord ? Je vous laisse réfléchir au niveaux d’isolation des transactions dans Oracle et MySQL. Et si vous êtes un fanatique de MySQL, je vous confirme que je suis plutôt agréablement surpris et que dans ce genre de cas, DB2 est bien pire ! Bon je retourne vois ce qu’il a dans le ventre…

-Grégory

Au fait :

• Vous
  pouvez utiliser « SET TRANSACTION ISOLATION LEVEL SERIALIZABLE » dans le corps d’une procédure ou d’une fonction
• Oracle a un autre niveau d’isolation dit READ-ONLY et depuis 10g vous pouvez simplement utiliser une commande comme celle-ci (A condition d’avoir bien dimensionné vos UNDO – Plus à venir en 11g):

select * from my_table
 as of timestamp
    to_timespamp('22/07/2007 17:12:00',
                 'DD/MM/YYYY HH24:MI:SS');