T-SQL - Transactions

UNE transactionest 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 la table. Il est important de contrôler les 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 terminées avec succès; sinon, la transaction est abandonnée au point d'échec et 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 lors d'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 dans des groupes de transactions dans lesquels ROLLBACK.

• SET TRANSACTION - Place un nom sur une transaction.

Les commandes de contrôle transactionnel ne sont utilisées qu'avec les commandes DML 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.

Afin d'utiliser les commandes de contrôle transactionnel dans MS SQL Server, nous devons commencer la transaction par «begin tran» ou commencer la commande de transaction, sinon ces commandes ne fonctionneront pas.

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. Cette commande enregistre toutes les transactions dans la base de données depuis la dernière commande COMMIT ou ROLLBACK.

Syntaxe

Voici la syntaxe de la commande COMMIT.

COMMIT;

Exemple

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

L'exemple de commande suivant supprimera les enregistrements de la table ayant age = 25, puis COMMIT les modifications dans la base de données.

Begin Tran 
DELETE FROM CUSTOMERS 
   WHERE AGE = 25 
COMMIT

En conséquence, deux lignes de la table seraient supprimées et l'instruction SELECT produira la sortie suivante.

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

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.

Syntaxe

Voici la syntaxe de la commande ROLLBACK.

ROLLBACK

Exemple

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

L'exemple de commande suivant supprimera les enregistrements de la table ayant age = 25, puis ROLLBACK les modifications dans la base de données.

Begin Tran 
DELETE FROM CUSTOMERS 
   WHERE AGE = 25; 
ROLLBACK

Par conséquent, l'opération de suppression n'aura aucun impact sur la table et l'instruction SELECT produira 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

Commande SAVEPOINT

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

Syntaxe

Voici la syntaxe de la commande SAVEPOINT.

SAVE TRANSACTION SAVEPOINT_NAME

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

Voici la syntaxe pour revenir à un SAVEPOINT.

ROLLBACK TO SAVEPOINT_NAME

Dans l'exemple suivant, nous supprimerons trois enregistrements différents de la table CUSTOMERS. Nous devrons 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.

Exemple

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 la série d'opérations -

Begin Tran 
SAVE Transaction SP1 
Savepoint created. 
DELETE FROM CUSTOMERS WHERE ID = 1  
1 row deleted. 
SAVE Transaction SP2 
Savepoint created. 
DELETE FROM CUSTOMERS WHERE ID = 2 
1 row deleted.
SAVE Transaction SP3 
Savepoint created. 
DELETE FROM CUSTOMERS WHERE ID = 3 
1 row deleted.

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

ROLLBACK Transaction SP2 
Rollback complete.

Notez que seule la première suppression a eu lieu depuis que nous sommes revenus au SP2.

SELECT * FROM CUSTOMERS

6 lignes sélectionnées.

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

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.

Syntaxe

Voici la syntaxe de SET TRANSACTION.

SET TRANSACTION ISOLATION LEVEL <Isolationlevel_name>