SQL Server : Petit rappel sur les transactions

Dans une application, lorsque l’on veut s’assurer qu’un ensemble d’opérations va s’exécuter entièrement ou pas du tout, on peut utiliser des transactions SQL.

Sans rentrer dans les détails vous pouvez commencer par aller voir la classe TransactionScope du Framework .Net et au niveau de SQL Server les instructions de transactions.

Or parfois il est utile, voire indispensable, d’écrire des scripts SQL pour modifier des données en base, ne serait-ce que pour des opérations de type batch (supprimer des données de tests, corriger des données issues d’un bogue applicatif, etc. ) qui n’ont pas à être rejoués plus tard et qui ne nécessitent pas par conséquent un développement spécifique dans un back-office ou ailleurs.

Application à des scripts manuels

Lorsque l’on fait ce genre d’opération il est préférable (surtout lorsqu’il s’agit d’une base de production) de vérifier ce que l’on fait.

Pour cela, il est possible d’utiliser les transactions de cette manière :

BEGIN TRANSACTION 
 
-- Requête à tester

ROLLBACK
 
-- COMMIT

Les éléments se trouvant entre le BEGIN TRANSACTION et le ROLLBACK seront exécutés puis annulés. Il est ainsi possible de voir dans les messages réponse si le nombre de lignes affectés par notre requête nous semble cohérent ou si des messages d’erreurs apparaissent.

Pour avoir un meilleur aperçu, il est aussi possible de faire un SELECT avant le ROLLBACK :

BEGIN TRANSACTION 
 
-- Requête à tester
DELETE FROM [dbo].[Customer] WHERE [Customer_Email] <> '[email protected]'
 
-- Vérification de la requête
SELECT * FROM [dbo].[Customer] 
 
-- La requête n'était pas bonne => On annule tout 
--ROLLBACK 
 
-- Autrement => On valide les modifications 
--COMMIT

Si l’on est sûr de sa requête il suffit de d’exécuter le COMMIT, sinon il faut exécuter le ROLLBACK.

Automatisons le processus

Avec les exemples ci-dessus, on voit qu’une action manuelle reste nécessaire, mais parfois on écrit un script et on le passe à un DBA ou à un technicien système qui va ensuite jouer ce script. Il n’a pas forcément la connaissance fonctionnelle permettant de valider ou non le résultat de la requête (et en outre il a probablement autre chose à faire).

Dans ce cas, nous pouvons lui mâcher le travail en automatisant un peu plus le script.

Par exemple supposons que nous devons écrire un script permettant de supprimer un utilisateur test mais que nous souhaitons tout de même vérifier que nous n’en supprimons qu’un seul (ou aucun).

BEGIN TRANSACTION
DELETE FROM [dbo].[Customer] WHERE [Customer_Name] = 'Doe'
 -- Vérification de la requête
IF @@ROWCOUNT > 1
BEGIN
    ROLLBACK
    PRINT 'Suppression annulée, trop d''enregistrements'
END
ELSE
    COMMIT
    PRINT 'Suppression effectuée'

Si on veut vraiment attirer l’attention de la personne qui va jouer le script, on peut remplacer le premier PRINT par un RAISEERRORavec une sévérité au moins égale à 11 (mais pas trop non plus, inutile de crier au feu) pour que le moteur SQL utilise le style des erreurs.

BEGIN TRANSACTION
DELETE FROM [dbo].[Customer] WHERE [Customer_Name] = 'Doe'
-- Vérification de la requête
IF @@ROWCOUNT > 1
BEGIN
    ROLLBACK
    RAISERROR (N'Suppression annulée, trop d''enregistrements', 11, 1);
END
ELSE
    COMMIT
    PRINT 'Suppression effectuée'

Pour obtenir ceci :

En effet, il serait probablement contre-productif de vouloir traiter tous les cas d’erreurs possibles. Il est parfois aussi simple d’anticiper un échange avec la personne qui a un accès à la base de production et d’ajuster le script en fonction des éventuelles erreurs.

Conclusion

Voilà, ce n’est pas grand chose, mais avec un peu de chance, cet article vous évitera de faire des erreurs bêtes sur les données de production tout en sécurisant vos requêtes SQL.