Les transactions sous Sql Server 2012 – Partie 1

Au travers de cette série d’articles consacrés aux transactions sous SQL Server 2012, Nous verrons :

• Ce qu’est une transaction SQL => ACID,
• les transactions : concepts avancés,
• Les locks sous SQL Server 2012,
• Les niveaux d’isolation des transactions (Isolation Levels),
• Les transactions dans notre code C#.

La transaction sous SQL Server : ACID

Sous SQL Server 2012, la transaction se définie comme étant une “unité de travail” comportant une séquence d’opérations sur une Base de données. Elle nous permet de manipuler avec souplesse nos données : on peut ainsi effectuer un ensemble de modifications (ajout séquenciel-en masse/suppression/mise à jour/etc.) sur nos données suivant un ensemble de règles métiers souhaitées, le tout dans un “contexte unique et isolé“.

Un exemple pour extrapoler : lors d’opérations sensibles telles qu’un virement bancaire, la transaction SQL pourra s’assurer :

• Que le compte destinataire ne soit crédité qu’une fois le compte débiteur débité,
• de l’annulation de l’opération (le fameux ROLLBACK) en cas de non exécution correcte de nos instructions,
• D’empêcher d’autres virements d’interférer avec celui en court,
• Qu’en cas de crache système, transaction bancaire reprenne au moment de l’interruption tout en conservant nos données dans leur état intègre.

Bien entendu, les opérations bancaires requièrent des mécanisme plus complexes, mais cet exemple démontre la philosophie de la transaction sous SQL Server.

Il est donc important de comprendre son mécanisme afin d’en tirer profit dans nos applications.

La transaction sous SQL Server est ACID. Eh oui, on n’est plus sur du “basique” là, ça ne rigole plus :).

Cet acronyme résume très bien les caractéristiques principales de la transaction :

Atomic : indivisible : l’ensemble des modifications sur une transaction est unitaire : c’est tout ou rien : pour une succession d’instructions de DELETE, UPDATE et d’INSERT exécutées dans le contexte d’un transaction par exemple, celle-ci appliquera toutes les instructions (s’il n’y a aucun erreur sur chacune des instructions) ou annulera la totalité des instructions (si un problème intervient en cours d’exécution).

Consistant : cohérente. La transaction s’assure de garder la base cohérente :  à la fin de son exécution, la donnée traitée respectera la règle business implémentée et sera dans un état cohérent relativement à cette règle.

Isolated : verrouillé. Les modifications d’une donnée dans le contexte d’une transaction sont généralement isolées par rapport à d’autres transactions sur cette même donnée. Cette notion est paramétrable via les NIVEAUX D’ISOLATION (que nous verrons plus tard).

Durable : persistante. Son état est conservé même en cas de crache système. Elle peut ainsi être restaurée.

Utilisation basique :

la transaction SQL s’effectue via les commandes SQL suivantes :

-- Début de notre transaction
BEGIN TRANSACTION

-- nos instructions Sql
-- Par exemple une requête UPDATE et/ou DELETE

-- validation de nos instructions
COMMIT TRANSACTION

-- annulation de nos instructions
ROLLBACK TRANSACTION

-- on utilise soit un commit soit un rollback en fin de transaction

Vous trouverez usuellement la syntaxe équivalente suivante : BEGIN/COMMIT/ROLLBACK TRAN en lieu et place de BEGIN/COMMIT/ROLLBACK TRANSACTION.

Mécanisme interne aux transactions :

Tant qu’on ne fait pas de COMMIT/ROLLBACK, toutes les instructions internes à la transaction sont stockées en mémoire (SQL server trace celles-ci dans son fichier journal .ldf).

Une fois toutes les instructions de la transaction terminées, et qu’aucun COMMIT/ROLLBACK n’a été effectué => il n’ y a toujours pas d’écriture des données dans la base à proprement parler (dans le fichier .mdf).

A fréquence régulière, SQL Server via un algorithme interne vérifie le journal de transaction de façon répétitive à des moments opportuns (en arrière plan), depuis le dernier point de lecture (son dernier CheckPoint) => à ce moment précis :

• S’il constate la présence de “BEGIN TRANSACTION” non commité, il va actionner le COMMIT et donc l’écriture sur le disque (dans le .mdf).
• Si, par contre, il constate qu’il n’y a pas de COMMIT en mémoire (pas d’instructions COMMIT tracées dans le fichier .ldf),  il supprimera la transaction correspondante en mémoire.

Ce qui correspond en effet au respect de sa caractéristique Atomic.

C’est grâce à ce mécanisme que SQL Server, après un crache système, sait reprendre et terminer (ou non) des transactions.

Conclusion

Dans cette première partie consacrée aux transactions sous SQL Server, On a posé les bases sur la notion de transaction notamment avec la définition ACID qui résume bien ses caractéristiques majeures et nous laisse présager une souplesse évidente en terme de traitement de données. Ce que nous aurons l’occasion de constater dans le prochain billet que je posterai d’ici peu.