Une transaction dans SQL Server est un groupe séquentiel d'instructions ou de requêtes pour effectuer une ou plusieurs tâches dans une base de données. Chaque transaction peut comporter des opérations de lecture, d'écriture, de mise à jour ou de suppression uniques ou une combinaison de toutes ces opérations. Chaque transaction doit se produire deux choses dans SQL Server :
- Soit toutes les modifications réussissent lorsque la transaction est validée.
- Ou bien, toutes les modifications sont annulées lorsque la transaction est annulée.
Une transaction ne peut réussir que lorsque toutes les opérations de l’ensemble sont terminées. Cela signifie que si un argument échoue, l'opération de transaction échouera. Chaque transaction commence par la première instruction SQL exécutable et se termine lorsqu'elle trouve une validation ou une annulation, explicitement ou implicitement. Il utilise le COMMETTRE ou RETOUR EN ARRIERE explicitement, ainsi qu'implicitement lorsqu'une instruction DDL est utilisée.
La représentation picturale ci-dessous explique le processus de transaction :
L'exemple suivant expliquera le concept de transaction :
Cet exemple utilisera le système de base de données bancaire pour expliquer le concept de transaction. Supposons qu'un client d'une banque souhaite retirer de l'argent de son compte en utilisant le mode ATM. L'ATM peut réaliser cette opération en trois étapes :
- Le premier pas est de vérifier la disponibilité du montant demandé sur le compte.
- Le deuxième étape déduit le montant du compte si le montant est disponible, puis met à jour le solde du compte.
- Le troisième étape est d'écrire l'opération de retrait d'argent dans le fichier journal. Cette étape indique que la transaction a réussi ou échoué. En cas de succès, écrivez la modification des données dans la base de données. Sinon, la transaction sera rétablie à son état précédent.
Le principe de base des transactions est que si l'une des instructions renvoie une erreur, l'ensemble des modifications est annulé pour garantir l'intégrité des données. Et si les transactions réussissent, toutes les modifications seront permanentes dans la base de données. Par conséquent, en cas de panne de courant ou d’autres problèmes lors du retrait d’argent à un guichet automatique, les transactions garantissent que notre solde reste cohérent. Une instruction de transaction effectue le mieux ces opérations car les quatre propriétés clés de la transaction rendent toutes les opérations plus précises et plus cohérentes. Les quatre propriétés de la transaction sont appelées ACID.
Propriétés des transactions
Les propriétés de transaction sont appelées propriétés ACID (Atomicité, Consistence, Isolation, Durabilité), qui sont décrites en détail ci-dessous :
chaîne java cmp
Atomicité : Cette propriété garantit que toutes les instructions ou opérations incluses dans la transaction doivent être exécutées avec succès. Sinon, la transaction entière sera abandonnée et toutes les opérations seront rétablies à leur état précédent en cas d'échec d'une opération.
Cohérence: Cette propriété garantit que la base de données change d'état uniquement lorsqu'une transaction est validée avec succès. Il est également responsable de la protection des données contre les plantages.
Isolement: Cette propriété garantit que toutes les transactions sont isolées des autres transactions, ce qui signifie que chaque opération de la transaction est effectuée indépendamment. Cela garantit également que les déclarations sont transparentes les unes par rapport aux autres.
Durabilité: Cette propriété garantit que le résultat des transactions validées persiste en permanence dans la base de données même en cas de panne ou d'échec du système.
Modes de transaction dans SQL Server
Il existe trois modes de transaction différents que SQL Server peut utiliser :
Mode de transaction à validation automatique : Il s'agit du mode de transaction par défaut de SQL Server. Il évaluera chaque instruction SQL en tant que transaction et les résultats seront validés ou annulés en conséquence. Ainsi, les instructions réussies sont immédiatement validées, tandis que les instructions ayant échoué sont immédiatement annulées.
Mode de transaction implicite. Ce mode permet à SQL Server de commencer la transaction implicite pour chaque instruction DML, mais il nécessite explicitement l'utilisation de commandes commit ou rollback à la fin des instructions.
Mode de transaction explicite : Ce mode est défini par l'utilisateur et permet d'identifier exactement le début et la fin d'une transaction. Il s'abandonnera automatiquement en cas d'erreur fatale.
Contrôle des transactions
Voici les commandes utilisées pour contrôler les transactions :
REMARQUE : Nous pouvons utiliser uniquement les instructions DML (INSERT, UPDATE et DELETE) pour les commandes Transaction Control Language. Nous ne pouvons pas les utiliser 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.
État de la transaction
Il indique comment se déroulent les transactions au cours de leur durée de vie. Il décrit l'état actuel de la transaction ainsi que la manière dont la transaction sera traitée à l'avenir. Ces états définissent les règles qui déterminent si une transaction est validée ou abandonnée.
Décrivons chaque état de transaction dans SQL Server :
unix créer un répertoire
État actif : La transaction est dans un état actif pendant que les instructions de la transaction sont en cours d'exécution. Cela change pour le « État partiellement engagé » si toutes les opérations de « lecture et écriture » sont terminées sans erreur. Si une instruction échoue, elle passe à « l’état d’échec ».
Partiellement engagé : Lorsque toutes les opérations de lecture et d'écriture sont terminées, la modification est apportée à la mémoire principale ou au tampon local. L'État irait à « État engagé » si les modifications sont rendues permanentes sur la base de données. Dans le cas contraire, on passe à « l’État défaillant ».
État défaillant: Une transaction passe à l'état d'échec lorsqu'une instruction de transaction échoue ou qu'une modification permanente de la base de données échoue.
État avorté : La transaction passe d'un 'État défaillant' à un 'état avorté' quand un quelconque type de panne survient. Les modifications sont supprimées ou annulées car ces modifications ne sont apportées qu'au tampon local ou à la mémoire principale dans les états précédents.
État engagé : Une transaction est terminée et passe dans cet état lorsque les modifications sont rendues permanentes dans la base de données et terminées dans le « état terminé ».
État terminé : S'il n'y a pas d'annulation et que la transaction est en cours « État engagé », le système est cohérent et prêt pour une nouvelle transaction tandis que l'ancienne est terminée.
Implémentation de transaction dans SQL Server
Prenons quelques exemples pour comprendre comment nous pouvons implémenter la transaction dans SQL Server. Nous utiliserons ici le 'Produit' tableau pour démontrer tous les états de transaction.
Les scripts SQL suivants créent la table Product dans la base de données sélectionnée :
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Ensuite, exécutez les scripts ci-dessous pour insérer des données dans cette table :
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Exécutez l'instruction SELECT pour vérifier les données :
Exemple de transaction COMMIT
C'est une bonne idée de diviser les instructions SQL utilisées dans la transaction en plusieurs parties logiques. Et puis, nous pouvons décider de valider ou de restaurer les données. Les étapes suivantes illustrent la création d'une transaction :
- Démarrez la transaction en utilisant le COMMENCER LA TRANSACTION commande.
- Écrivez les instructions SQL et divisez-les en fonction de nos besoins
- Utilisez le COMMETTRE déclaration pour terminer la transaction et enregistrer les modifications de manière permanente.
Vous trouverez ci-dessous les commandes qui expliquent les opérations COMMIT dans SQL Server :
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Si aucune erreur n'est trouvée, nous verrons le résultat suivant où chaque instruction SQL de transaction s'exécutera indépendamment :
Les instructions INSERT et UPDATE ne peuvent pas être annulées une fois la transaction validée. Lorsque nous vérifions la table après l'opération de validation, nous verrons les données suivantes :
Sree Ramanujan
Exemple de transaction ROLLBACK
Nous utiliserons la commande ROLLBACK pour annuler toutes les transactions qui n'ont pas encore été enregistrées dans la base de données et revenir au point où la transaction a commencé. L'exemple suivant explique l'opération ROLLBACK dans SQL Server :
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Une fois que nous exécutons la transaction ci-dessus, nous pouvons voir qu'elle sera exécutée avec succès. Cependant, cela n'affectera aucune modification dans la base de données car tant que nous n'exécutons pas l'instruction COMMIT ou ROLLBACK, les modifications ne peuvent pas devenir permanentes. Par conséquent, nous avons la possibilité d'utiliser la commande de transaction ROLLBACK pour annuler toutes les opérations de la base de données. Voici le relevé complet de la transaction :
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Utilisation de la variable @@Error Global dans les transactions :
Cette variable est utilisé pour vérifier s'il y a une erreur ou non. L'exemple ci-dessous explique son concept. Ici, nous allons d'abord démarrer la transaction à l'aide de la commande BEGIN, puis écrire deux instructions d'insertion. Ensuite, nous utiliserons la variable système globale @@ERREUR dans le Instruction SI pour vérifier une erreur. Si la valeur est supérieure à 0, cela signifie qu'il y a une erreur. Maintenant, la transaction est annulée ; sinon, la transaction est validée.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Lorsque la transaction ci-dessus sera exécutée, nous remarquerons qu'elle a été annulée. Cela est dû à notre tentative d'insérer une valeur en double dans la colonne Clé primaire.
Transaction de restauration automatique
La plupart des transactions contiennent plusieurs requêtes. Lors de l'exécution de la transaction, si l'une des instructions SQL produit une erreur, aucune modification n'a lieu dans la base de données et les instructions restantes ne sont pas exécutées. Ce concept est connu sous le nom de transaction de restauration automatique dans SQL Server. Utilisons un exemple simple pour démontrer ce processus.
pieds contre pied
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Cette transaction produit le résultat suivant :
Dans cette sortie, nous pouvons voir que l’instruction insert a été exécutée avec succès. Cependant, l'exécution de l'instruction de mise à jour a détecté une erreur due au problème de conversion du type de données. Dans ce cas, SQL Server n'autorise aucune modification dans la base de données, ce qui signifie que l'opération d'insertion n'ajoute aucune valeur et que l'instruction select n'est pas exécutée.
Point de sauvegarde dans les transactions
Le point de sauvegarde insère une marque spéciale dans une transaction qui nous permet d'annuler toutes les modifications exécutées après le point de sauvegarde. Il est également utilisé pour annuler une partie spécifique de la transaction plutôt que la totalité de la transaction. Nous pouvons le définir en utilisant le ENREGISTRER LA TRANSACTION nom_sp déclaration. L'exemple suivant explique l'utilisation du point de sauvegarde dans les transactions qui valident l'instruction insert et annulent l'instruction delete.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Voir le résultat ci-dessous où nous pouvons voir que l'ID de produit 116 est supprimé et 117 est inséré dans la première sortie. Cependant, dans la deuxième sortie, l'opération de suppression est annulée en raison du point de sauvegarde.
Comment libérer un point de sauvegarde dans une transaction ?
Libérer le point de sauvegarde est utilisé pour supprimer le point de sauvegarde nommé de la transaction en cours sans annuler les résultats des requêtes exécutées après le point de sauvegarde. MySQL dispose de cette commande, mais SQL Server ne fournit aucune commande pour libérer un point de sauvegarde. Au lieu de cela, ils sont automatiquement libérés à la fin d'une transaction de validation ou d'annulation, nous n'avons donc pas à nous soucier d'eux intermédiairement.
Transaction implicite dans SQL Server
Nous pouvons définir une transaction implicite en activant l'option IMPLICIT_TRANSACTIONS. L'exemple suivant expliquera facilement ce concept :
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Dans cette transaction, nous avons utilisé deux options @@OPTION et @@TRANCOUNT. @@OPTOPN fournit les informations concernant les options SET actuelles et @@TRANCOUNT fournit l'instruction BEGIN TRANSACTION dans la session en cours.
Maintenant, l'exécution de la transaction renverra le résultat ci-dessous :
Transaction explicite dans SQL Server
Une transaction explicite doit être définie via la commande BEGIN TRANSACTION car elle identifie le point de départ de la transaction explicite. Nous pouvons définir la transaction explicite dans SQL Server comme ci-dessous :
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
Dans la syntaxe, l'option trans_name indique un nom unique d'une transaction. Le @trans_name_var indique une variable définie par l'utilisateur qui stocke le nom de la transaction. Finalement, le MARQUE L'option nous permet de marquer une transaction spécifique dans le fichier journal.
La transaction explicite via la commande BEGIN TRANSACTION a acquis un verrou en fonction du niveau d'isolement des ressources liées à la transaction. Cela aide à réduire les problèmes de verrouillage. Voir l'exemple ci-dessous :
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Voici le résultat :
Transaction marquée dans SQL Server
La transaction marquée est utilisée pour ajouter une description à une transaction spécifique dans les fichiers journaux. Nous pouvons l'utiliser comme point de récupération à la place d'une date et d'une heure lors de la restauration d'une base de données à un état antérieur. Il faut savoir que la marque est ajoutée dans les fichiers journaux uniquement lorsque la transaction marquée modifie la base de données. Nous pouvons comprendre son concept à l’aide de l’exemple suivant.
disquette
Supposons que nous ayons modifié la base de données accidentellement et que nous ne connaissions pas le moment exact de la modification des données ; dans ce cas, la récupération des données peut prendre beaucoup de temps. Cependant, si nous utilisons les transactions marquées, cela peut être un outil utile pour déterminer le moment exact des modifications des données.
La syntaxe suivante illustre la transaction marquée dans SQL Server :
BEGIN TRANSACTION trans_name WITH MARK 'description';
Ici, nous devons définir le nom de la transaction puis ajouter l'option WITH MARK. Dans l'exemple ci-dessous, nous allons supprimer des enregistrements et ajouter la marque dans le fichier journal :
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
Le historique des marques de journalisation le tableau est inclus dans le base de données msdb et stocke des informations concernant chaque transaction marquée qui a été validée. Exécutez l'instruction ci-dessous pour obtenir les détails de la table logmarkhistory :
SELECT * FROM msdb.dbo.logmarkhistory
Transaction nommée dans SQL Server
Nous pouvons également fournir un nom pour notre transaction dans SQL Server. Il est toujours recommandé d'utiliser la transaction nommée lorsque vous travaillez avec plusieurs transactions dans une seule requête. L'exemple ci-dessous explique comment renommer une transaction :
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Voici le résultat :
Conclusion
Cet article donnera un aperçu complet de la transaction dans les instructions SQL Server. Les transactions sont utiles dans les systèmes de bases de données relationnelles car elles garantissent l'intégrité de la base de données.