Réservation de lignes

Réserver les lignes d’une table pour les manipuler plus tard

      La syntaxe pour réserver une ou plusieurs lignes est relativement simple quel que soit le SGBDR, en résumé il faut ajouter FOR UPDATE à la requête :

-- Oracle Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL> create table t1(c11 integer, c12 integer); Table created. SQL> create table t2(c21 integer, c22 integer); Table created. SQL> insert into t1 values(1,1); 1 row created. SQL> insert into t1 values(2,2); 1 row created. SQL> insert into t2 values(1, 1); 1 row created. SQL> insert into t2 values(2, 2); 1 row created. SQL> commit; Commit complete. -- session a SQL> select * from t1 where c11 = 1 for update; C11 C12 ---------- ---------- 1 1 -- session b SQL> update t1 set c12 = 2 where c11 = 1; ... EN ATTENTE ! -- session c SQL> update t1 set c11 = 3 where c11 = 2; 1 row updated. -- PostgreSQL select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.2.0-10) 6.2.0 20161027, 64-bit (1 ligne) create table t1(c11 integer, c12 integer); CREATE TABLE create table t2(c21 integer, c22 integer); CREATE TABLE insert into t1 values(1,1); INSERT 0 1 insert into t1 values(2,2); INSERT 0 1 insert into t2 values(1, 1); INSERT 0 1 insert into t2 values(2, 2); INSERT 0 1 -- session a start transaction; START TRANSACTION select * from t1 where c11 = 1 for update; c11 | c12 -----+----- 1 | 1 (1 ligne) -- session b start transaction; START TRANSACTION update t1 set c12 = 2 where c11 = 1; ... EN ATTENTE ! -- session c start transaction; START TRANSACTION update t1 set c11 = 3 where c11 = 2; UPDATE 1 -- annulation de tout le travail

OF, un supplément à FOR UPDATE qui peut être mal compris

      Avec Oracle comme avec PostgreSQL, il faut simplement indiquer l’ensemble des lignes à réserver avec une requête SELECT habituelle en ajoutant les mots clés FOR UPDATE. Il est possible de compléter la clause FOR UPDATE avec le mot clé OF afin de préciser ce qui doit être réservé. Comment cela fonctionne-t-il avec Oracle et PostgreSQL ?

-- Oracle Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production -- session a select * from t1 where c11 = 1 for update of t1; select * from t1 where c11 = 1 for update of t1 * ERROR at line 1: ORA-00904: "T1": invalid identifier SQL> select * from t1 where c11 = 1 for update of t1.c11; C11 C12 ---------- ---------- 1 1 -- session b SQL> update t1 set c12 = 2 where c11 = 1; ... EN ATTENTE ! -- session c SQL> update t1 set c11 = 3 where c11 = 2; 1 row updated. -- PostgreSQL select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.2.0-10) 6.2.0 20161027, 64-bit (1 ligne) -- session a start transaction; START TRANSACTION select * from t1 where c11 = 1 for update of t1.c11; ERREUR: FOR UPDATE doit indiquer les noms de relation non qualifiés LIGNE 1 : select * from t1 where c11 = 1 for update of t1.c11; rollback; ROLLBACK start transaction; START TRANSACTION select * from t1 where c11 = 1 for update of t1; c11 | c12 -----+----- 1 | 1 (1 ligne) -- session b start transaction; START TRANSACTION update t1 set c12 = 2 where c11 = 1; ... EN ATTENTE ! -- session c start transaction; START TRANSACTION update t1 set c11 = 3 where c11 = 2; UPDATE 1 -- annulation de tout le travail

Utilité du "OF"

      Avec Oracle la clause OF est utilisée avec un nom de COLONNE alors qu’avec PostgreSQL c’est avec un nom de TABLE. Mais cela ne veut pas dire que le verrouillage se fait au niveau COLONNE avec Oracle ou au niveau de toute la TABLE avec PostgreSQL. Ce sont bien des LIGNES qui sont réservées.
      Dans les exemples précédents nous avons obtenu exactement le même résultat avec ou sans la clause OF. Cette clause est-elle alors complètement inutile ? Non car la requête peut impliquer les lignes de plusieurs tables. Démonstration :

-- Oracle Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production -- session a SQL> select * from t1 join t2 on (t1.c11 = t2.c21) where t1.c11 = 1 for update of t1.c11; C11 C12 C21 C22 ---------- ---------- ---------- ---------- 1 1 1 1 -- session b SQL> update t1 set c12 = 2 where c11 = 1; ... EN ATTENTE ! -- session c SQL> update t2 set c22 = 2 where c21 = 1; 1 row updated. -- PostgreSQL select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.2.0-10) 6.2.0 20161027, 64-bit (1 ligne) -- session a start transaction; START TRANSACTION select * from t1 join t2 on (t1.c11 = t2.c21) where t1.c11 = 1 for update of t1; c11 | c12 | c21 | c22 -----+-----+-----+----- 1 | 1 | 1 | 1 (1 ligne) -- session b start transaction; START TRANSACTION update t1 set c12 = 2 where c11 = 1; ... EN ATTENTE ! -- session c start transaction; START TRANSACTION update t2 set c22 = 2 where c21 = 1; UPDATE 1

      Nous avons ici réalisé une jointure et, grâce à la précision apportée par OF, seules les lignes d’une des deux tables ont été réservées pour mise à jour.
      En conclusion, si vous voulez réserver les lignes d’une table ou de toute les tables ramenées par une requête, alors il est possible d’avoir une syntaxe commune entre Oracle et PostgreSQL. Cela doit représenter la majeure partie des cas. C’est uniquement pour des besoins plus avancés, si vous ne souhaitez pas réserver les lignes provenant de toutes les tables impliquées dans une requête, que les syntaxes sont différentes.

Mise à jour : 05/02/2017