SQL - Transactions

Une transaction est une unité de travail effectuée sur une base de données. Les transactions sont des unités ou des séquences de travail accomplies dans un ordre logique, que ce soit de manière manuelle par un utilisateur ou automatiquement par une sorte de programme de base de données.

Une transaction est la propagation d'une ou plusieurs modifications dans la base de données. Par exemple, si vous créez un enregistrement, mettez à jour un enregistrement ou supprimez un enregistrement de la table, vous effectuez une transaction sur cette table. Il est important de contrôler ces transactions pour garantir l'intégrité des données et gérer les erreurs de base de données.

En pratique, vous regrouperez de nombreuses requêtes SQL dans un groupe et vous les exécuterez toutes ensemble dans le cadre d'une transaction.

Propriétés des transactions

Les transactions ont les quatre propriétés standard suivantes, généralement désignées par l'acronyme ACID.

• Atomicity- s'assure que toutes les opérations au sein de l'unité de travail sont menées à bien. Sinon, la transaction est abandonnée au point d'échec et toutes les opérations précédentes sont restaurées à leur état antérieur.

• Consistency - garantit que la base de données change correctement d'état après une transaction validée avec succès.

• Isolation - permet aux transactions de fonctionner indépendamment et de manière transparente les unes des autres.

• Durability - garantit que le résultat ou l'effet d'une transaction validée persiste en cas de défaillance du système.

Contrôle des transactions

Les commandes suivantes sont utilisées pour contrôler les transactions.

• COMMIT - pour enregistrer les modifications.

• ROLLBACK - pour annuler les modifications.

• SAVEPOINT - crée des points au sein des groupes de transactions dans lesquels ROLLBACK.

• SET TRANSACTION - Place un nom sur une transaction.

Commandes de contrôle transactionnel

Les commandes de contrôle transactionnel ne sont utilisées qu'avec le DML Commandstel que - INSERT, UPDATE et DELETE uniquement. Ils ne peuvent pas être utilisés lors de la création ou de la suppression de tables car ces opérations sont automatiquement validées dans la base de données.

La commande COMMIT

La commande COMMIT est la commande transactionnelle utilisée pour enregistrer les modifications invoquées par une transaction dans la base de données.

La commande COMMIT est la commande transactionnelle utilisée pour enregistrer les modifications invoquées par une transaction dans la base de données. La commande COMMIT enregistre toutes les transactions dans la base de données depuis la dernière commande COMMIT ou ROLLBACK.

La syntaxe de la commande COMMIT est la suivante.

COMMIT;

Example

Considérez la table CUSTOMERS ayant les enregistrements suivants -

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Voici un exemple qui supprimerait ces enregistrements de la table qui ont age = 25, puis COMMIT les modifications dans la base de données.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> COMMIT;

Ainsi, deux lignes de la table seraient supprimées et l'instruction SELECT produirait le résultat suivant.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

La commande ROLLBACK

La commande ROLLBACK est la commande transactionnelle utilisée pour annuler des transactions qui n'ont pas déjà été enregistrées dans la base de données. Cette commande ne peut être utilisée que pour annuler des transactions depuis la dernière commande COMMIT ou ROLLBACK a été émise.

La syntaxe d'une commande ROLLBACK est la suivante -

ROLLBACK;

Example

Considérez la table CUSTOMERS ayant les enregistrements suivants -

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Voici un exemple, qui supprimerait ces enregistrements de la table qui ont l'âge = 25, puis ROLLBACK les modifications dans la base de données.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> ROLLBACK;

Ainsi, l'opération de suppression n'aurait pas d'impact sur la table et l'instruction SELECT produirait le résultat suivant.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

La commande SAVEPOINT

Un SAVEPOINT est un point dans une transaction où vous pouvez restaurer la transaction à un certain point sans annuler la transaction entière.

La syntaxe d'une commande SAVEPOINT est la suivante.

SAVEPOINT SAVEPOINT_NAME;

Cette commande sert uniquement à la création d'un SAVEPOINT parmi toutes les instructions transactionnelles. La commande ROLLBACK est utilisée pour annuler un groupe de transactions.

La syntaxe pour revenir à un SAVEPOINT est la suivante.

ROLLBACK TO SAVEPOINT_NAME;

Voici un exemple où vous prévoyez de supprimer les trois enregistrements différents de la table CUSTOMERS. Vous souhaitez créer un SAVEPOINT avant chaque suppression, afin de pouvoir ROLLBACK vers n'importe quel SAVEPOINT à tout moment pour ramener les données appropriées à leur état d'origine.

Example

Considérez la table CUSTOMERS ayant les enregistrements suivants.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Le bloc de code suivant contient la série d'opérations.

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

Maintenant que les trois suppressions ont eu lieu, supposons que vous avez changé d'avis et décidé de ROLLBACK vers le SAVEPOINT que vous avez identifié comme SP2. Étant donné que SP2 a été créé après la première suppression, les deux dernières suppressions sont annulées -

SQL> ROLLBACK TO SP2;
Rollback complete.

Notez que seule la première suppression a eu lieu depuis que vous êtes revenu au SP2.

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

La commande RELEASE SAVEPOINT

La commande RELEASE SAVEPOINT est utilisée pour supprimer un SAVEPOINT que vous avez créé.

La syntaxe d'une commande RELEASE SAVEPOINT est la suivante.

RELEASE SAVEPOINT SAVEPOINT_NAME;

Une fois qu'un SAVEPOINT a été libéré, vous ne pouvez plus utiliser la commande ROLLBACK pour annuler les transactions effectuées depuis le dernier SAVEPOINT.

La commande SET TRANSACTION

La commande SET TRANSACTION peut être utilisée pour lancer une transaction de base de données. Cette commande permet de spécifier les caractéristiques de la transaction qui suit. Par exemple, vous pouvez spécifier une transaction en lecture seule ou en lecture-écriture.

La syntaxe d'une commande SET TRANSACTION est la suivante.

SET TRANSACTION [ READ WRITE | READ ONLY ];