A propos des instructions DML et des transactions

Les instructions LMD (Langage de manipulation de données) ajoutent, modifient et suppriment des données de table de base de données. Une transaction est une séquence d'une ou de plusieurs instructions SQL qu'Oracle AI Database traite comme une unité : soit toutes les instructions sont exécutées, soit aucune.

A propos des instructions de contrôle de transaction

Une transaction est une séquence d'une ou de plusieurs instructions SQL qu'Oracle AI Database traite comme une unité : soit toutes les instructions sont exécutées, soit aucune. Les transactions permettent de modéliser les processus métier qui requièrent plusieurs opérations exécutées en tant qu'une seule et même unité.

Par exemple, quand un responsable quitte l'entreprise, une ligne doit être insérée dans la table JOB_HISTORY pour notifier le départ du responsable et, pour chaque employé subordonné au responsable, la valeur de MANAGER_ID doit être mise à jour dans la table EMPLOYEES. Pour modéliser ce processus dans une application, vous devez regrouper les instructions INSERT et UPDATE dans une même transaction.

Les instruction de contrôle de transaction de base sont les suivantes :

SAVEPOINT, qui marque un point de secours dans une transaction (un point à l'aide duquel vous pourrez ensuite effectuer une annulation). Les points de sauvegarde sont facultatifs et il peut y en avoir plusieurs dans une même transaction.
COMMIT, qui met fin à la transaction en cours, rend permanentes les modifications qu'elle a effectuées, efface ses points d'enregistrement et libère ses verrous.
ROLLBACK, qui annule la transaction en cours (annule) soit l'ensemble de la transaction en cours, soit uniquement les modifications apportées après le point de sauvegarde spécifié.

Dans l'environnement SQL*Plus, vous pouvez saisir une instruction de contrôle de transaction après l'invite SQL>.

Dans l'environnement SQL Developer, vous pouvez entrer une instruction de contrôle de transaction dans la feuille de calcul. De plus, SQL Developer possède des icônes Valider les changements et Annuler les changements, dont le rôle est décrit aux sections "Validation des transactions" et "Annuler des transactions".

Attention :

Si vous ne validez pas explicitement une transaction et qu'elle se termine de façon anormale, la base de données annule automatiquement la dernière transaction non validée.

Oracle recommande de mettre fin explicitement aux transactions dans les programmes d'application en les validant ou en les annulant.

Voir aussi :

Oracle AI Database Concepts pour plus d'informations sur la gestion des transactions
Référence du langage SQL d'Oracle AI Database pour plus d'informations sur les instructions de contrôle des transactions

Validation de transactions

La validation d'une transaction rend permanentes les modifications que celle-ci a apportées, efface ses points de sauvegarde et libère ses verrous.

Pour valider explicitement une transaction, utilisez l'instruction COMMIT ou, dans l'environnement SQL Developer, l'icône Valider les modifications.

Remarque : Oracle AI Database émet une instruction COMMIT implicite avant et après toute instruction LDD (langage de définition de données). Pour plus d'informations sur les instructions LDD, reportez-vous à la section "A propos des instructions DLD (Data Definition Language)".

Avant de procéder à la validation d'une transaction :

Vous pouvez visualiser vos modifications mais pas les autres utilisateurs de l'instance de base de données.
Vos modifications ne sont pas définitives : vous pouvez les annuler à l'aide d'une instruction ROLLBACK.

Après avoir effectué la validation d'une transaction :

Vos modifications sont visibles par d'autres utilisateurs et leurs instructions exécutées après la validation de votre transaction.
Vos modifications sont définitives : il est impossible de les annuler à l'aide d'une instruction ROLLBACK.

L'exemple 3-1 ajoute une ligne à la table REGIONS (une transaction très simple), vérifie le résultat, puis valide la transaction.

Exemple 3-1 Validation d'une transaction

Avant la transaction :

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Résultats :

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

4 rows selected.

Transaction (ajouter une ligne à une table) :

INSERT INTO regions (region_id, region_name) VALUES (5, 'Africa');

Résultats :

1 row created.

Vérification que la ligne a été ajoutée :

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Résultats :

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Validation de la transaction :

COMMIT;

Résultats :

Commit complete.

Voir aussi : Référence du langage SQL d'Oracle AI Database pour plus d'informations sur l'instruction COMMIT

Annulation des transactions

L'annulation d'une transaction annule les modifications lui ayant été apportées. Vous pouvez annuler la totalité de la transaction en cours ou l'annuler uniquement à un point de sauvegarde spécifié.

Pour annuler la transaction en cours uniquement à un point de sauvegarde spécifié, vous devez utiliser l'instruction ROLLBACK avec la clause TO SAVEPOINT.

Pour annuler la totalité de la transaction en cours, utilisez l'instruction ROLLBACK sans la clause TO SAVEPOINT ou (dans l'environnement SQL Developer) l'icône Annuler les modifications.

L'annulation de la totalité de la transaction en cours :

met fin à la transaction,
annule toutes les modifications apportées,
efface tous les points de sauvegarde,
libère tous les verrous de transaction.

L'annulation de la transaction en cours uniquement au point de sauvegarde spécifié :

ne met pas fin à la transaction,
annule uniquement les modifications effectuées après le point de sauvegarde spécifié,
efface uniquement les points de sauvegarde définis après le point de sauvegarde spécifié (à l'exclusion du point de sauvegarde spécifié lui-même),
libère tous les verrous de table et de ligne acquis après le point de sauvegarde spécifié.

Les autres transactions ayant demandé un accès aux lignes verrouillées après le point de sauvegarde spécifié doivent attendre que la transaction soit validée ou annulée. Les autres transactions n'ayant pas demandé un accès aux lignes peuvent en demander un et accéder immédiatement aux lignes.

Pour voir l'effet d'une annulation dans SQL Developer, vous devez cliquer sur l'icône Régénérer.

Suite à l'exemple 3-1, la table REGIONS possède une région appelée "Moyen-Orient et Afrique" et une région appelée "Afrique". L'exemple 3-2 corrige ce problème (une transaction très simple) et vérifie la modification, puis annule la transaction et vérifie l'annulation.

Exemple 3-2 Annulation de l'ensemble d'une transaction

Avant la transaction :

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Résultats :

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Transaction (modifier la table) :

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

Résultats :

1 row updated.

Vérification de la modification :

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Résultats :

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Annulation de la transaction :

ROLLBACK;

Résultats :

Rollback complete.

Vérification de l'annulation :

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Résultats :

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Voir aussi : Référence du langage SQL Oracle AI Database pour plus d'informations sur l'instruction ROLLBACK

Définition de points de sauvegarde dans des transactions

L'instruction SAVEPOINT marque un point de secours dans une transaction (le point à l'aide duquel vous pourrez ensuite procéder à l'annulation). Les points de sauvegarde sont facultatifs et il peut y en avoir plusieurs dans une même transaction. L'exemple 3-3 effectue une transaction qui comporte plusieurs instructions DML et plusieurs points de sauvegarde, puis annule la transaction jusqu'à un point d'enregistrement particulier, annulant uniquement les modifications réalisées après ce point d'enregistrement.

Exemple 3-3 Annulation d'une transaction jusqu'à un point de sauvegarde

Vérification de la table REGIONS avant la transaction :

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Résultats :

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Vérification des pays de la région 4 avant la transaction :

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Résultats :

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          4
Israel                                   IL          4
Kuwait                                   KW          4
Nigeria                                  NG          4
Zambia                                   ZM          4
Zimbabwe                                 ZW          4

6 rows selected.

Vérification des pays de la région 5 avant la transaction :

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Résultats :

no rows selected

Transaction comportant plusieurs points de sauvegarde :

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZM';
SAVEPOINT zambia;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'NG';
SAVEPOINT nigeria;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZW';
SAVEPOINT zimbabwe;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'EG';
SAVEPOINT egypt;

Vérification de la table REGIONS après la transaction :

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Résultats :

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Vérification des pays de la région 4 après la transaction :

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Résultats :

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Israel                                   IL          4
Kuwait                                   KW          4

2 rows selected.

Vérification des pays de la région 5 après la transaction :

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Résultats :

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          5
Nigeria                                  NG          5
Zambia                                   ZM          5
Zimbabwe                                 ZW          5

4 rows selected.

ROLLBACK TO SAVEPOINT nigeria;

Vérification de la table REGIONS après l'annulation :

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Résultats :

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Vérification des pays de la région 4 après l'annulation :

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Résultats :

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          4
Israel                                   IL          4
Kuwait                                   KW          4
Zimbabwe                                 ZW          4

4 rows selected.

Vérification des pays de la région 5 après l'annulation :

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Résultats :

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Nigeria                                  NG          5
Zambia                                   ZM          5

2 rows selected.

Voir aussi : Référence du langage SQL d'Oracle AI Database pour plus d'informations sur l'instruction SAVEPOINT