Transactions
publicité
FORMATION D’INGÉNIEURS EN INFORMATIQUE FIIFO IUT d’ORSAY et FACULTÉ des SCIENCES d’ORSAY UNIVERSITÉ PARIS SUD Spécialisation Bases de données G.Ausseresse TRANSACTIONS ET ACCÈS CONCURRENTS Page I. II. INTRODUCTION ....................................................................................................................... 2 GESTION DES TRANSACTIONS............................................................................................. 3 1. Définitions. ............................................................................................................................... 3 2. Contrôle des transactions. ........................................................................................................ 3 III. 1. 2. 3. 4. 5. GESTION DES ACCÈS CONCURRENTS ............................................................................ 5 Introduction .............................................................................................................................. 5 Intégrité des données ................................................................................................................ 5 Concurrence des données ......................................................................................................... 5 Consistance des données .......................................................................................................... 5 Consistance des données en lecture (read consistency) ........................................................... 5 IV. 1. 2. 3. 4. VERROUILLAGE DES DONNÉES....................................................................................... 7 Concepts relatifs au verrouillage.............................................................................................. 7 Verrouillage bloquant .............................................................................................................. 8 Modes d’activation de verrouillage.......................................................................................... 8 Différents types de verrouillage ............................................................................................. 10 V. RÉFÉRENCES........................................................................................................................... 14 FIIFO 582698532 édité le 18/04/2017 00:04:00 I. INTRODUCTION L’un des objectifs d’un SGBD est de mettre à la disposition d’un grand nombre d’utilisateurs un ensemble cohérent de données. En présence de ce grand nombre d’utilisateurs, une attention particulière doit être apportée pour garantir la cohérence des données lors de leur manipulation simultanée par différents utilisateurs. Cette cohérence est assurée à l’aide des concepts de transaction et d’accès concurrents. Ce cours est consacré à la présentation des concepts et mécanismes relatifs aux transactions et accès concurrents. Nous présentons d’abord les concepts et les techniques offerts par Oracle pour gérer les transactions :définition de début et de fin de transaction, validation et annulation d’une transaction, puis le découpage d’une transaction en sous-transactions. Nous présentons ensuite la gestion des accès concurrents dans Oracle. Après avoir donné les concepts de base relatifs aux accès concurrents, nous présentons en détail les différents modes et types de verrouillage de données. 582698532 Page 2 sur 14 II. GESTION DES TRANSACTIONS 1. Définitions. Une transaction est une unité logique de traitements, regroupant un ensemble d’opérations élémentaires (commandes SQL). Ces opérations doivent être exécutés entièrement, soit pas du tout, permettant ainsi à la base de données de passer d’un état cohérent à un nouvel état cohérent. Exemple : Soient les deux commandes suivantes : INSERT INTO ligne_com VALUES (100, 1, 1001, 10) ; et UPDATE article SET qtestock = qtestock – 10 WHERE idarticle = 1001 ; La première commande une nouvelle ligne de commande et la seconde met à jour l’état du stock pour l’article commandé. Pour que la base de données reste dans un état cohérent, ces deux commandes doivent être exécutées et validées. Si, pour une raison quelconque, la seconde commande n’a pas pu être traitée, le système doit annuler la première. Lorsque toutes les opérations constituant la transaction sont exécutées et deviennent effectives, nous disons que la transaction est validée. Les modifications apportées par les opérations élémentaires deviennent alors définitives. Dans le cas contraire où au moins une opération n’a pu être exécutée pour une raison quelconque (condition non vérifiée, accès impossible aux données, panne), nous disons que la transaction est annulée. Les modifications apportées par toutes les opérations élémentaires sont alors annulées et on revient à l’état qu’avait la base avant la transaction. Dans le but d’assurer une souplesse dans la gestion des transactions, il est possible de découper une longue transaction en sous-transactions à l’aide de repères permettant d’effectuer, en cas de besoin, des annulations partielles de la transaction. Cette notion de sous-transaction permet d’effectuer un découpage plus fin des unités de traitement. 2. Contrôle des transactions. Le contrôle des transactions consiste à définir le début et la fin d’une transaction (validation ou annulation) ainsi que son découpage éventuel en sous-transactions. 2.1. Début de transaction 582698532 Page 3 sur 14 Il n’existe pas de commande permettant de marquer explicitement le début d’une transaction. Ce début est implicitement défini par la première commande SQL exécutée ou par la fin d’une transaction précédente. Le début d’une application ou d’une session SQL constitue automatiquement le début d’une transaction. De même, la fin d’une transaction (par validation ou par annulation) marque le début d’une nouvelle transaction. 2.2. Fin de transaction La fin d’une transaction peut être définie explicitement ou implicitement. La fin explicite d’une transaction est réalisée à l’aide des deux commandes COMMIT ou ROLLBACK. La première valide las opérations élémentaires de la transaction et la seconde les annule. Les événements suivants constituent une fin implicite de transaction : Exécution d’une commande de définition de données (CREATE, ALTER, RENAME et DROP) : toutes les opérations exécutées depuis le début de la transaction sont validées. Fin normale d’un programme ou d’une session avec déconnexion d’Oracle : la transaction est validée. Fin anormale d’un programme ou d’une session (sortie sans déconnexion d’Oracle) : la transaction est annulée. 2.3. Découpage d’une transaction. Il est possible, dans Oracle, d’effectuer un découpage d’un transaction en insérant des points de repère (en anglais savepoints). A l’aide de ces points de repère, il est possible d’annuler un sousensemble d’opérations d’une transaction à partir d’un point de repère. La création d’un point de repère se fait à l’aide de la commande suivante : SAVEPOINT point_de_repère ; Pour annuler les opérations à partir d’un point de repère « point_repère », on utilise la commande ROLLBACK en précisant le point de repère. ROLLBACK TO [SAVEPOINT] point_repère ; 582698532 Page 4 sur 14 III. GESTION DES ACCÈS CONCURRENTS 1. Introduction L’une des fonctions du noyau d’un SGBD est d’assurer la gestion des accès concurrents dans les environnements multi-utilisateurs. Selon la norme ANSI/ISO du langage SQL, la gestion des accès concurrents consiste à assurer la sérialisation des transactions multiples accédant simultanément aux mêmes données. Cette sérialisation consiste à s’assurer que l’exécution simultanée d’un ensemble de transactions accédant aux mêmes données donne le même résultat que leur exécution en série. La gestion des accès concurrents est basé sur les concepts d’intégrité, de concurrence et de consistance de données. Elle utilise essentiellement la technique de verrouillage de données. Avant d’aborder les techniques utilisées dans Oracle pour assurer la gestion des accès concurrents, nous allons d’abord présenter les concepts d’intégrité, de concurrence et de consistance des données. 2. Intégrité des données L’intégrité des données est assurée en respectant les différentes contraintes d’intégrité définies lors de la création de la base de données. En particulier, cette intégrité doit être assurée suite à une mise à jour de mêmes données par différents utilisateurs. La base de données doit passer d’un état cohérent avant les mises à jour à un autre état cohérent après les mise à jour. 3. Concurrence des données La concurrence de données consiste à assurer une coordination des accès concurrents de plusieurs utilisateurs aux mêmes données. Plus le nombre d’utilisateurs pouvant accéder simultanément aux mêmes données est grand, plus le degré de concurrence devient important. 4. Consistance des données La consistance des données est assurée lorsque ces données restent inchangées jusqu’à la fin d’une opération de mise à jour ou de lecture, c’est à dire que, lorsqu’un utilisateur utilise ces données en lecture ou en mise à jour, elles doivent rester dans un état consistant (inchangé) jusqu’à la fin de l’opération même si d’autres transactions essaient de la modifier. 5. Consistance des données en lecture (read consistency) 582698532 Page 5 sur 14 Lors du traitement d’une commande de mise à jour, Oracle donne pendant tout le temps du traitement de cette commande la même image des données prise au début de son exécution. Les modifications effectuées par cette commande ne deviennent visibles qu’après sa validation. Exemple : Utilisateur 1 Temps Mise à jour table T t1 t2 Validation de la mise à jour de T Utilisateur 2 Lecture table T t3 t4 Lecture table T La lecture effectuée sur la table T à l’instant t2 par l’utilisateur 2 ne voit pas les modifications apportées par l’utilisateur 1. Par contre, la lecture effectuée à l’instant t4 voit les modifications apportées par l’utilisateur 1 car il y a eu validation à l’instant t3. La consistance des données en lecture est assurée automatiquement par Oracle au niveau des commandes. Il est également possible dans Oracle d’étendre la consistance des données au niveau des transactions. La consistance de données au niveau d’une transaction permet de rendre invisibles les modifications apportées aux données manipulées par la transaction tout au long de son exécution. Exemple : Transaction 1 Début transaction Mise à jour table T Validation Mise à jour table T Validation Temps t0 t1 t2 t3 t4 t5 t6 t7 Transaction 2 Début transaction Lecture table T Lecture table T Validation La mise à jour effectuée par la transaction T1 à l’instant t1 est prise en compte lors de la lecture de la table T par la transaction T2 à l’instant t4, alors que la seconde mise à jour effectuée à l’instant t3 n’est pas prise en compte lors de la seconde lecture effectuée par T2 à l’instant t6 bien qu’elle ait déjà été validée. Les deux lectures donneront donc le même résultat. En l’absence de la consistance de lecture au niveau de la transaction, la seconde mise à jour serait prise en compte lors de la seconde lecture. Nous verrons dans le paragraphe 4.3.2. comment on peut passer de la consistance de lecture du niveau commande (mode par défaut) à une consistance au niveau transaction. 582698532 Page 6 sur 14 IV. VERROUILLAGE DES DONNÉES 1. Concepts relatifs au verrouillage Pour que l’exécution simultanée de plusieurs transactions donne le même résultat qu’une exécution séquentielle, la solution utilisée consiste à verrouiller momentanément les données utilisées par une transaction jusqu’à la fin de la mise à jour. Les autres transactions demandant ces données sont mises en attente jusqu’à leur libération (déverrouillage). Cette technique de verrouillage permet d’éviter les interactions destructives des transactions, c’est-à-dire les opérations n’assurant pas l’intégrité des données. Le cas typique d’interaction destructive est la perte de mise à jour. La figure suivante donne un exemple de perte de mise à jour. Transaction T1 Transaction T2 Lecture D1 = 50 Lecture D1 = 50 Calcul D1 = D1 + 10 Calcul D1 = D1 + 20 Ecriture D1 = 60 Ecriture D1 = 70 Validation T1 Validation T2 L’absence de verrouillage de données a pour conséquence la perte de la mise à jour faite par la transaction T1. Au lieu que D1 ait comme résultat final la valeur 80 (50+10+20), elle n’a que 70, car la mise à jour effectuée par T1 n’a pas été prise en compte. Le verrouillage s’applique d’une façon générale à une Ressource. Une ressource peut correspondre aux objets crées par les utilisateurs tels que les tables (ou uniquement quelques lignes d’une table), ainsi qu’aux objets système tels que les éléments du dictionnaire ou des zones de données en mémoire centrale (SGA). Un paramètre important qui affecte les performances est le niveau de verrouillage des données, appelé aussi granule de verrouillage. Ce granule peut être logique (une table, une ligne) ou physique (segment, fichier ou page). La taille du granule influence le degré de concurrence. Un petit granule favorise la concurrence, mais il entraîne des contrôles plus importants. Généralement, les petits granules (ligne) sont mieux adaptés aux transactions courtes ne manipulant qu’un faible volume de données (quelques lignes), alors que des granules plus importants sont mieux adaptés aux transactions lourdes. Certains SGBD modifient d’une façon dynamique le niveau de verrouillage. On parle d’extension du niveau de verrouillage (Lock escalation). Ceci peut arriver, par exemple, lorsque plusieurs lignes d’une même table ont été verrouillées. Le SGBD peut alors verrouiller la totalité de la table. Le granule de verrouillage devient plus grand, mais le nombre de verrous est réduit. Cette technique d’extension du niveau de verrouillage pourrait être une cause des verrouillages bloquants. Oracle n’applique pas cette technique d’extension du niveau de verrouillage. 582698532 Page 7 sur 14 2. Verrouillage bloquant La technique de verrouillage peut entraîner des situations dans lesquelles deux ou plusieurs transactions se trouvent bloquées parce qu’elles utilisent les mêmes ressources. Chacune des transactions se trouve en attente de la libération de ressources utilisées par une autre transaction qui à son tour attend la libération d’autres données pour pouvoir libérer les ressources verrouillées. On parle alors de verrouillage bloquant (deadlock). Exemple : L’exécution simultanée des deux transactions suivantes entraîne un verrouillage bloquant. Transaction T1 Transaction T2 UPDATE article SET prixunit = prixunit * 1.05 WHERE idarticle = 1; UPDATE article SET prixunit = prixunit * 1.05 WHERE idarticle = 2; UPDATE article SET prixunit = prixunit * 1.05 WHERE idarticle = 2; UPDATE article SET prixunit = prixunit * 1.05 WHERE idarticle = 1; COMMIT ; COMMIT ; 3. Modes d’activation de verrouillage Le verrouillage de données peut être activé explicitement par l’utilisateur ou une application, ou implicitement suite à l’exécution d’une commande de définition ou de manipulation de données. 3.1. Verrouillage implicite. Le SGBD Oracle effectue automatiquement tous les verrouillages nécessaires pour le maintien de la cohérence des données. Ces verrouillages sont effectués sans aucune intervention de l’utilisateur. 3.2. Verrouillage explicite. Dans certains cas, l’utilisateur peut ressentir le besoin de contrôler lui-même les mécanismes de verrouillage des données. Ce contrôle peut se faire au niveau des transactions ou au niveau d’une instance. Au niveau de la transaction, le verrouillage explicite peut être activé dans les cas suivants : En utilisant la commande : LOCK TABLE nom_table IN mode_verrouillage MODE 582698532 Page 8 sur 14 Avec : nom_table mode_verrouillage nom de la table à verrouiller. mode selon lequel la table sera verrouillée. Il peut être : ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE et EXCLUSIVE. La description de chacune de ces modes est donnée dans les sections qui suivent. La table nom_table est ainsi verrouillée selon le mode mode_verrouillage. En utilisant la commande : SELECT liste_sélection FROM nom_table WHERE condition FOR UPDATE; Les lignes de la table nom_table vérifiant la condition ‘condition’ sont verrouilées. La consistance de lecture (read consistency) est appliquée par défaut à toutes les transactions. Elle a pour conséquence de rendre visible à une transaction T2 toute modification apportée aux données par une transaction T1, même si ces modifications interviennent pendant le déroulement de la transaction T2. dans certains cas, on ne souhaite pas voir les modifications qui ont lieu après le début de la transaction. Ce mode est appelé mode READ ONLY. Pour passer du mode par défaut au mode READ ONLY, on utilise la commande suivante au début d’une transaction : SET TRANSACTION READ ONLY; Ce mode reste valable jusqu’à la fin de la transaction. Le mode par défaut est ensuite rétabli. Le verrouillage implicite peut être désactivé au niveau global en démarrant une instance avec les deux paramètres d’initialisation suivants : SERIALIZABLE = TRUE ROW_LOCKING = INTENT L’attribution de la valeur TRUE au paramètre SERIALIZABLE entraîne un verrouillage systématique au niveau des tables. Il n’y a donc plus de verrouillage au niveau des lignes. Toute transaction se référant à une table entraîne son verrouillage et interdit donc à toute autre transaction d’utiliser toute ou une partie de cette table. Toutes ces transactions sont exécutées en série et non pas d’une façon concurrente, d’où la dégradation des performances. La valeur par défaut de ce paramètre est FALSE. L’attribution de la valeur INTENT au paramètre ROW_LOCKING interdit la mise à jour (insertion, suppression ou mise à jour) simultanée des lignes d’une table par plusieurs transactions. Deux ou plusieurs transactions peuvent simultanément lire (sélection) ou verrouiller (SELECT … FOR UPDATE) plusieurs lignes, mais les mises à jour ne peuvent être effectuées que lorsque la transaction précédente a libéré la table. 582698532 Page 9 sur 14 La valeur par défaut de ce paramètre est ALWAYS. Elle permet à différentes transactions d’effectuer des mises à jour simultanées sur des lignes différentes d’une même table. Remarque importante : La modification des valeurs par défaut de ces deux paramètres est fortement déconseillée, car elle entraîne une dégradation importante des performances. Les deux cas typiques où ces modifications s’avèrent nécessaires sont l’utilisation d’applications développées avec des versions d’Oracle inférieures à la version 7 ou le souci de compatibilité au mode ANSI/ISO. 4. Différents types de verrouillage La technique de verrouillage peut être appliquée à trois ressources : les données, le dictionnaire de données et la mémoire centrale. A chacune de ces ressources correspond un type de verrouillage. Nous avons donc trois type de verrouillage : Verrouillage des données. Verrouillage du dictionnaire. Verrouillage interne. Dans ce qui suit, nous allons présenter chacune de ces trois types de verrouillage. 4.1. Verrouillage des données. Le verrouillage des données, appelé aussi verrouillage du langage de manipulation de données, permet de maintenir la cohérence des données en cas d’accès concurrents. Oracle utilise deux niveaux de verrouillage des données :verrouillage des lignes (row locks) et verrouillage des tables. La combinaison de ces deux niveaux de verrouillage donc lieu aux cinq modes de verrouillage suivants : Mode lignes partagées (Row Share ou RS), Mode lignes exclusives (Row eXclusive ou RX), Mode table partagée (Share ou S), Mode partage exclusif de lignes (Share Row eXclusive ou SRX), Mode table exclusive (eXclusive ou X). Le verrouillage de données prend fin après la validation ou l’annulation d’une transaction. Lorsqu’une transaction est découpée en sous-transactions, les données verrouillées à partir d’un point de repère sont libérées dès qu’une annulation partielle de la transaction est effectuée pour ce point de repère. Dans ce qui suit, nous allons présenter chacun des modes de verrouillage. Verrouillage en mode lignes partagées (Row Share ou RS) : ce mode de verrouillage entraîne un verrouillage sélectif des lignes en vue de leur modification. Lorsqu’une table est verrouillée en mode lignes partagées, toutes les opérations et tous les autres modes de verrouillage sont autorisés, à l’exception du verrouillage exclusif de la table (X). 582698532 Page 10 sur 14 Ce mode peut être activé : Explicitement à l’aide de la commande : LOCK TABLE nom_table IN ROW SHARE MODE; Ou implicitement lors de la commande de sélection suivante : SELECT … FROM nom_table WHERE … FOR UPDATE ; Ce mode est le moins restrictif et permet donc un haut niveau d’accès concurrents. Verrouillage en mode lignes exclusives (Row eXclusive ou RX) : ce mode de verrouillage entraîne aussi un verrouillage sélectif des lignes d’une table lors de leur modification. Ces lignes ne peuvent pas être partagées par d’autres transactions. Lorsqu’une table est verrouillée en mode lignes exclusives, toutes les opérations et tous les autres modes de verrouillage sont autorisés, à l’exception des modes suivants : table partagée (S), lignes partagés (RS) et table exclusive (X). Ce mode peut être activé : Explicitement à l’aide de la commande : LOCK TABLE nom_table IN ROW EXCLUSIVE MODE; Ou implicitement lors de l’exécution de l’une des commandes suivantes : INSERT INTO nom_table …; UPDATE nom_table …; DELETE FROM nom_table …; Ce mode est plus restrictif que le mode RS. Verrouillage en mode table partagée (Share ou S) :ce mode permet de verrouiller entièrement une table tout en permettant aux autres transactions d’effectuer sur cette table des sélections, des verrouillages en mode lignes partagées (RS) ou en mode table partagée (S). Il est donc possible d’avoir plusieurs transactions qui effectuent ce mode de verrouillage simultanément sur la même table. Toutes les opérations modifiant le contenu de la table (insertion, suppression et mise à jour) ne peuvent pas être exécutées. Il n’est pas possible non plus de verrouiller cette table en mode partage exclusif de lignes (SRX), ni en mode table exclusive (X). Lorsque plusieurs transactions verrouillent simultanément une même table en mode table partagée (S), aucune de ces transactions ne peut effectuer une mise à jour sur cette table. Par contre, une transaction ayant verrouillé une table en mode partagée peut la mettre à jour (insertion, suppression ou mise à jour) si aucune autre transaction ne l’a verrouillé selon ce mode. Ce mode ne peut être activé qu’explicitement à l’aide de la commande : 582698532 Page 11 sur 14 LOCK TABLE nom_table IN SHARE ROW EXCLUSIVE MODE; Ce mode est plus restrictif que le mode table partagée. Verrouillage en mode table exclusive (eXclusive ou X) : ce mode de verrouillage permet à la transaction qui le demande d’effectuer une écriture exclusive sur la table. Les autres transactions ne peuvent effectuer que des opérations de sélection sur cette table. Une seule transaction peut effectuer ce mode de verrouillage sur une table. Toutes les opérations modifiant le contenu de la table (insertion, suppression ou mise à jour) ne peuvent pas être exécutées. Il n’est possible non plus d’effectuer aucun mode de verrouillage. Ce mode de verrouillage est le plus restrictif. Il ne peut être activé qu’explicitement à l’aide de la commande : LOCK TABLE nom_table IN EXCLUSIVE MODE; Compatibilité entre les modes de verrouillage: Le tableau suivant donne les compatibilités entre les différents modes de verrouillage : Mode de verrouillage RS : Lignes partagées RX : Lignes exclusives S : Table partagée SRX : Partage exclusif de lignes X : Table exclusive Commandes SQL correspondant Select … from … Lock table … in mode Insert into … Update … Delete from … Lock table … in mode Lock table … in Lock table … in exclusive mode for update row share Compatibilité (O/N) avec les autres modes de verrouillage RS RX S SRX X O O O O N O O N N N O O N N O N N N N N N N N N N row exclusive share mode share Lock table … in exclusive mode 4.2. Verrouillage du dictionnaire. Le verrouillage du dictionnaire des données, appelé aussi verrouillage du langage de définition de données, permet de protéger la définition des objets de la base (tables, vues, utilisateurs, procédures, etc…) lorsque ces objets sont manipulés par des commandes de définition de données dans une transaction. Par exemple, lors de la création d’une vue, Oracle verrouille la définition des tables référencées par cette vue, interdisant ainsi toute modification de structure ou suppression de ces tables. 582698532 Page 12 sur 14 Le verrouillage du dictionnaire de données est effectué automatiquement par les différentes commandes du langage de définition de données. Il n’existe donc pas de commandes explicites de verrouillage analogues à celles du verrouillage des données. Les objets du dictionnaire de données peuvent être verrouillés selon deux modes : Verrouillage exclusif : il s’agit de verrouiller certaines ressources en mode exclusif de façon à interdire leur utilisation par toute commande de définition ou de manipulation de données. C’est le cas de la plupart des commandes de définition de données. Par exemple, lors de la modification ou de la suppression de structure d’une table, un verrouillage exclusif est nécessaire afin d’interdire toute utilisation de cette table pendant cette opération. Verrouillage partagé :certaines commandes de définition de données ne nécessitent pas le verrouillage exclusif de la ressource correspondante. Dans ce cas, un verrouillage partagé est suffisant. C’est le cas, par exemple, lors de la création d’une vue. Un verrouillage partagé est suffisant sur les tables de base. Ce type de verrouillage est utilisé pour les commandes de création des tables, des vues, des procédures, des synonymes et des packages. Etant donné que chaque commande de définition de données met fin implicitement à une transaction, le(s) verrouillage(s) correspondant(s) au niveau du dictionnaire de données prennent fin juste après l’exécution de la commande. 4.3. Verrouillage interne Ce troisième type de verrouillage concerne la mémoire utilisée par Oracle pour l’exécution du noyau et des applications. Il est complètement transparent aux utilisateurs. Il existe un type particulier de verrous internes appelés loquets (Latches). Ce sont des mécanismes internes d’Oracle permettant la protection des structures partagées de la SGA telles que la liste des utilisateurs actuels de la base. Ce type de verrouillage est activé et désactivé par les différents processus internes d’Oracle (processus serveurs ou d’arrière-plan). 582698532 Page 13 sur 14 V. RÉFÉRENCES OUVRAGES PUBLIES SQL2, Application à ORACLE, ACCESS et RDB. P. Delmal De Boeck Université, 2000, 3ème édition. Oracle 7, A Abdellatif, M. Limame, A. Zeroual, Eyrolles, 1998, 7ème tirage. 582698532 Page 14 sur 14
