Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

SGBD – SQL Gestion des transactions

Système de gestion de base de données



Introduction:

  • SQL comporte un moteur transactionnel, une transaction correspondant à la modification d’une donnée.
  • On parle, pour évoquer un système transactionnel, de système OLTP (On Line Transaction Processing).
  • Les transactions peuvent être implicites ou déclarées explicitement afin de recouvrir un ensemble de modifications solidaires.
  • La définition explicite de transactions permet de s’assurer de la complétude d’une demande de modification pouvant comporter plusieurs opérations de modification sur plusieurs lignes de plusieurs tables.
  • Il est ainsi possible de garantir une bonne cohérence à la base de données.
  • Pour expliciter ce propos, prenons l’exemple d’une transaction bancaire :  Elle est toujours composée du débit d’un compte et du crédit d’un autre compte.
  • Il convient donc de s’assurer que les deux opérations aient bien été effectuées.
  • Si l’une des deux opérations n’aboutit pas, nous aurons alors un système « bancal ».
  • üLes données d’une transaction sont validées dans leur ensemble par l’application d’un ordre COMMIT, ou invalidées (elles reprennent alors leur état initial) par l’ordre ROLLBACK.
  • Une transaction est une suite d’opérations effectuées comme une seule unité logique de travail.
  • Une unité logique de travail doit posséder quatre propriétés appelées propriétés ACID (Atomicité, Cohérence, Isolation et Durabilité), pour être considérée  comme une transaction.

Atomicité:

  • Une transaction doit être une unité de travail indivisible ; soit toutes les modifications de données sont effectuées, soit aucune ne l’est.

Cohérence:

  • Lorsqu’elle est terminée, une transaction doit laisser les données dans un état cohérent.
  • Dans une base de données relationnelle, toutes les règles doivent être appliquées aux modifications apportées par la transaction, afin de conserver l’intégrité de toutes les données.

Isolation:

  • Les modifications effectuées par des transactions concurrentes doivent être isolées transaction par transaction.
  • Une transaction accède aux données soit dans l’état où elles étaient avant d’être modifiées par une transaction concurrente, soit telles qu’elles se présentent après exécution de cette dernière, mais jamais dans un état intermédiaire.
  • Cette propriété est nommée mise en série, car elle permet de recharger les données de départ et de répéter une suite de transactions dont le résultat sur les données sera identique à celui des transactions d’origine.

Durabilité:

  • Lorsqu’une transaction est terminée, ses effets sur le système sont permanents.
  • Les modifications sont conservées même en cas de défaillance du système.

Spécification et maintien de la cohérence des transactions:

Les programmeurs SQL doivent concevoir des transactions dont les points de début et de fin permettent de maintenir la cohérence logique des données. La séquence de modifications des données qu’ils définissent doivent laisser les données dans un état cohérent par rapport aux règles d’entreprise définies par leur société. Ces instructions de modification des données doivent par conséquent être contenues dans une seule transaction pour que Microsoft® SQL Server™ puisse assurer l’intégrité physique de la transaction.

Mécanismes système mis en œuvre:

Un système de bases de données d’entreprise comme SQL Server se doit de fournir des mécanismes permettant de garantir l’intégrité physique de chaque transaction. SQL Server dispose des mécanismes suivants : Verrouillage,  Consignation, Assurer la cohérence.

Verrouillage:

Des fonctionnalités de verrouillage permettant d’assurer l’isolation des transactions.

Consignation:

Des fonctionnalités de consignation assurant la durabilité des transactions. En cas de panne matérielle du serveur ou de défaillance du système d’exploitation ou de SQL Server, les journaux de transaction permettent à SQL Server, lorsqu’il redémarre, d’annuler automatiquement toutes les transactions incomplètes au moment de la panne du système.

Assurer la cohérence:

Des fonctionnalités de gestion des transactions qui assurent l’atomicité et la cohérence des transactions. Lorsqu’une transaction a débuté, elle doit se dérouler correctement jusqu’à la fin, sans quoi SQL Server annule toutes les modifications effectuées sur les données depuis le début de celle-ci.

Eléments de programmation: Transact-SQL

Les commentaires:

Règles de dénomination des objets:

Tous les noms d’objets (table, colonne, variable, etc.) doivent respecter les règles suivantes :

  • Le nombre de caractères maximum est de 128 et les caractères qui composent le nom seront de préférence non accentués.
  • Il sera aussi préférable d’éviter les caractères spéciaux qui peuvent par ailleurs avoir une utilisation réservée par le langage.
  • Les noms doivent commencer par une lettre et ne doivent pas comporter d’espace. Si le nom ne respecte pas ces 2 dernières règles, vous devrez alors le délimiter par des crochets [].
  • Les noms doivent commencer par une lettre et ne doivent pas comporter d’espace. Si le nom ne respecte pas ces 2 dernières règles, vous devrez alors le délimiter par des crochets [].
  • Il n’est pas nécessaire de respecter la casse des caractères mais par convention seuls les mots clefs du langage seront en majuscule.

Types numériques:

  • INT entier (et ses dérivés SMALLINT, TINYINT, BIGINT).
  • DECIMAL(11,2) montant à 11 chiffres (décimaux) dont 2 après la virgule.
  • Il convient de définir la précision (nombre maximal de chiffres) et l’échelle (nombre de chiffres à droite de la virgule). La taille occupée en mémoire dépendra de l’échelle.
  • REAL réel flottant codé sur 4 octets  et FLOAT (double précision) sur 8 octets.

Types caractères:

  • CHAR(50) : chaîne de caractères de longueur fixe quelle que soit la valeur .
  • VARCHAR(25) : chaîne de caractères de longueur variable ne pouvant contenir plus de 25 caractères.
  • NCHAR et NVARCHAR sont de même nature que les précédents mais la définition du caractère est en Unicode et stockée sur 2 octets (Ce type est particulièrement intéressant si vous devez stocker des valeurs de données exprimées en plusieurs langues, comme c’est souvent le cas dans les applications multilingues.).
  • TEXT ou NTEXT : Permet de stocker une chaîne au format ASCII ou Unicode.
  • IMAGE : Permet de stocker un flux d’octets.

Types divers:

  • MONEY : décimal avec symbole monétaire. Il est préférable de ne pas avoir recours à ce type et stocker la valeur de la devise dans une colonne spécifique.
  • DATETIME, SMALLDATETIME : date et heure. DATETIME s’emploie pour les données comprises entre le 1er janvier 1753 et le 31 décembre 9999 (chaque valeur est stockée dans 8 octets).
  • SMALLDATETIME , s’applique aux dates comprises entre le 1er janvier 1900 et le 6 juin 2079 (chaque valeur est stockée dans 4 octets). Des fonctions spécifiques permettent de manipuler ces types.
  • BIT : Booléen.
  • TIMESTAMP : Ce type de données présente des nombres binaires automatiquement générés, et dont l’unicité est garantie dans une base de données (TIMESTAMP  est généralement utilisé en tant que mécanisme d’affectation d’un numéro de version aux lignes des tables.).
  • Il est possible de dériver des types SQL Server afin de créer ses propres types, désignés sous le vocable de Types de données utilisateur.
  • Cette approche permet de rendre plus explicite les définitions des données et de leur associer des règles qui devront être vérifiées systématiquement lors d’une demande de stockage d’une valeur dans la colonne.
  • Pour créer un type dérivé, il faut recourir à l’exécution de la procédure stockée sp_addtype.

Déclaration et Affectation d’une variable:

  • Une variable se déclare à l’aide du mot clé DECLARE.
  • Par convention, le nom de la variable locale à la procédure ou au script est préfixé par un @.
  • Il existe aussi des variables dites globales qui représentent des valeurs du système. Elles sont alors préfixées de @@.
  • L’opérateur SET permet d’affecter une valeur à une variable.
  • On peut aussi affecter une valeur à une variable par le biais de l’exécution d’une requête de sélection. Il convient de s’assurer que la requête ne renvoie alors qu’une seule ligne afin d’obtenir une valeur scalaire.
  • L’opérateur PRINT permet d’imprimer le contenu d’une variable. A n’utiliser que lors des phases de tests…

Les structures conditionnelles et itératives:

Délimitation des blocs d’instructions:

Un bloc d’instructions est délimité par les instructions BEGIN et END. Cette structure est utilisée par toutes les autres structures, conditionnelles, itératives, transactionnelles, …

  • Vous retrouvez dans le langage Transact-SQL les structures les plus usuelles d’un langage de programmation qui vous permettront de programmer l’exécution conditionnelle d’instructions ou la répétition de l’exécution de blocs d’instructions.

Expression de blocs conditionnels:

IF expression conditionnelle

  Instruction ou Bloc d’instructions

ELSE  facultatif

  Instruction ou Bloc d’instructions

Il est possible d’avoir recours à la structure CASE qui permet l’évaluation successive de différentes conditions au sein d’un même groupe. ü

  • La fonction CASE peut être mise en œuvre de deux manières : ü La fonction CASE détermine le résultat en comparant une expression à un jeu d’expressions simples ;
  • La fonction CASE détermine le résultat en évaluant un jeu d’expressions booléennes.

A noter:

Lorsque vous recourez à cette structure pour réaliser des mises à jour conditionnelles, il faut prendre garde au fait que la colonne devant être modifiée vaudra Null si vous ne lui affectez aucune valeur et ne conservera donc pas sa valeur initiale.

D’où la programmation de l’instruction sur ELSE qui sera exécutée dans le cas où aucune condition n’a été vérifiée dans l’expression des différents cas.

Expression de blocs itératifs conditionnels:

Il n’existe qu’une seule structure qui permette de spécifier des boucles d’instructions, la structure WHILE.

WHILE expression de la condition

Instruction ou bloc d’instructions

Syntaxe relative aux transactions

  • BEGIN TRAN ou BEGIN TRANSACTION marque le point de référence du début de la transaction.
  • La transaction peut éventuellement être nommée.
  • Les instructions émises dans le cadre la transaction sont verrouillées par le système et l’accès aux données sous jacentes restreint pour les autres connexions.
  • Les transactions peuvent être imbriquées.
  • BEGIN TRAN ou BEGIN TRANSACTION marque le point de référence du début de la transaction.
  • Les données sont déverrouillées lors de l’exécution : § d’un ordre de validation (application des modifications) COMMIT § d’un ordre d’invalidation (retour version précédente des données) ROLL BACK ou relatif à une erreur système. §
  • Les transactions peuvent être nommées et être ainsi référencées plus facilement. Toutefois dans le cadre de transactions imbriquées, seule la transaction la plus extérieure peut être nommée.
  • L’utilisation de la clause WITH MARK [‘description’] lors de la déclaration d’une transaction indique qu’elle est marquée dans le journal des transactions.
  • Si WITH MARK est utilisé, un nom de transaction doit être spécifié.
  • WITH MARK permet de restaurer un journal de transactions par rapport à une marque nommée.

L’exemple suivant illustre l’intérêt et les conditions de mise ne place d’une transaction.  Il s’agit ici de l’insertion de lignes de commandes dans la table [Order details] du comptoir anglais.

La clé primaire de la table [Order Details] est constituée de deux colonnes [OrderID] et [ProductID].

Deux lignes d’une même commande ne peuvent donc pas porter sur le même article.

Exemple sans transaction:

Exemple avec transaction explicite :

Dans cet exemple, la différence réside dans le fait qu’aucune ligne ne sera insérée en cas de problèmes. La cohérence des informations reste ainsi assurée.

Gestion des exceptions:

  • Avec SQL Server les choses ont évolué et il est possible de délester son code d’accès aux données de la prise de charge des instructions de gestion d’erreurs.
  • SQL Server peut prendre en compte, tout comme un langage de programmation évolué ; la gestion des erreurs au sein d’un bloc Try-Catch
  • L’instruction TRY … CATCH permettra donc d’intercepter les exceptions critiques comme les violations de contrainte d’intégrité par exemple.

Syntaxe:

Gestion des verrouillages

Principe du verrouillage transactionnel:

  • Le système recourt à la mise en place de verrous pour réduire les préjudices susceptibles d’affecter les ressources en cours de modification.
  • Ainsi, lorsqu’un utilisateur effectue des transactions sur une ressource, SQL Server n’autorise pas d’autres utilisateurs à effectuer sur cette ressource des opérations qui nuiraient aux dépendances de l’utilisateur détenteur du verrou.
  • Les verrous sont gérés de manière interne par le logiciel système et sont placés et libérés en fonction des actions entreprises par l’utilisateur.
  • Il s’agit là d’un système complexe que nous n’aborderons pas en détail mais dont il faut comprendre les enjeux et les conséquences éventuelles sur le niveau de performance de votre système.

Les verrous peuvent être appliqués à différents niveaux de la base de données depuis la ligne (niveau le plus fin) jusqu’à la table voire la base de données dans son ensemble (par exemple lors d’une opération de restauration, un verrou exclusif est mis en place sur la base de données).

La transaction 1 a posé un verrou sur le fournisseur et demande un verrou sur le client sur laquelle la transaction 2 est déjà détentrice d’un verrou et qui demande à son tour un verrou sur le fournisseur. Il s’agit là d’une situation où les deux demandes se bloquent : on parle d’étreinte fatale

Le système peut mettre fin à cette situation de blocage en annulant la requête du thread victime du verrou (déterminée par des règles complexes visant à tuer le « plus faible »…): on parle alors de DEADLOCK.

Verrous et niveaux d’isolation des transactions:

  • Il existe plusieurs modes de verrouillage : partagé, mise à jour, exclusif, intent et schéma.
  • Les verrous sont maintenus le temps nécessaire pour protéger la ressource au niveau demandé mais la durée des verrous partagés utilisés pour protéger les lectures dépend des niveaux d’isolement de la transaction.
  • Les verrous exclusifs utilisés pour protéger les mises à jour sont maintenus jusqu’à la fin de la transaction.

Exemple:

Que faut-il faire pour débloquer la situation et permettre à la requête d’extraction de s’exécuter ?

Mais c’est bien sûr terminer la transaction 1 par un COMMIT !


Comments are closed, but trackbacks and pingbacks are open.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More