Comment les Bases de Données Populaires Gèrent les Commandes DDL dans les Transactions

L’intégration des commandes DDL dans les transactions est l’une des fonctionnalités les plus puissantes offertes par les bases de données. Mais toutes les bases de données ne sont pas capables d’annuler les modifications DDL. Oracle, PostgreSQL, MySQL, MariaDB, DB2, MSSQL, Teradata, Greenplum, Netezza, Redshift, Aurora sont les bases de données relationnelles les plus populaires et cet article décrit leur capacité à supporter les commandes DDL dans les transactions.

Une transaction est une séquence de commandes incorporées en une seule unité logique. Ainsi, une transaction est exécutée comme une entité unique. Lorsqu’une exécution de transaction est interrompue, la transaction n’est pas exécutée du tout. Les transactions sont utilisées pour préserver l’intégrité de la base de données, l’exécution unique de toutes les commandes que la transaction comprend, ce qui signifie le respect des contraintes d’intégrité et l’impossibilité de laisser la base de données dans un état transitoire, non cohérent.

DDL (Data Definition Language) est une famille d’éléments du langage SQL utilisés pour définir la structure de la base de données (y compris les opérations sur les bases de données, les tables, les colonnes, les index, les vues, les procédures stockées, les contraintes).

Pour comprendre ce qu’est le DDL, il est important de savoir que les instructions DDL jouent un rôle crucial dans les systèmes de gestion de bases de données (SGBD) en permettant aux utilisateurs de définir et de modifier la structure de la base de données. La capacité à exécuter des DDL dans les systèmes de gestion de bases de données est essentielle pour maintenir l’intégrité et la cohérence des données stockées dans la base de données. Cependant, le comportement des instructions DDL au sein des transactions varie selon les différentes implémentations de SGBD. Comprendre comment les instructions DDL se comportent au sein des transactions dans votre SGBD spécifique est crucial pour garantir l’intégrité des données et pour mettre en place des mécanismes appropriés de gestion des erreurs.

Comprendre le DDL dans les Systèmes de Gestion de Bases de Données

Le Data Definition Language (DDL) constitue un composant critique des systèmes de gestion de bases de données, fournissant les commandes nécessaires pour définir et modifier la structure de la base de données. Au sein d’un SGBD, les commandes DDL créent le cadre qui contient les données et déterminent la manière dont elles sont organisées, consultées et maintenues.

Les commandes DDL les plus fondamentales dans les systèmes de gestion de bases de données incluent CREATE, ALTER, DROP, TRUNCATE, COMMENT et RENAME. La commande CREATE établit de nouveaux objets de base de données tels que des tables, des vues, des index ou des procédures stockées. ALTER modifie des objets existants en ajoutant des colonnes, en changeant les types de données ou en implémentant des contraintes. DROP supprime complètement les objets du schéma de la base de données.

TRUNCATE, bien que parfois classée comme DML, fonctionne comme une commande DDL dans la plupart des systèmes de gestion de bases de données, car elle réinitialise le stockage de la table et ne peut généralement pas être annulée. COMMENT ajoute des notes descriptives aux objets de la base de données, améliorant ainsi la documentation. RENAME modifie les identifiants des objets pour une meilleure clarté ou organisation.

Lors de l’exécution de commandes DDL dans les systèmes de gestion de bases de données, ces commandes acquièrent généralement des verrous de schéma qui empêchent les modifications concurrentes des mêmes objets. Ce comportement de verrouillage varie d’un système à l’autre — certains mettent en place des verrous de courte durée lors des modifications des métadonnées tandis que d’autres verrouillent les objets pendant toute la durée de l’opération. Comprendre ces comportements de verrouillage est essentiel lors de la planification des opérations de maintenance dans les environnements de production.

Au-delà de la simple modification de la structure, le DDL dans les systèmes de gestion de bases de données modernes gère également des fonctionnalités plus avancées, telles que le partitionnement (division des tables en segments plus petits et plus gérables), la mise en œuvre de politiques de sécurité via les commandes GRANT et REVOKE, et la définition de déclencheurs qui s’exécutent automatiquement en réponse à des événements spécifiques. Ces capacités font du DDL un outil exceptionnellement puissant pour les architectes et administrateurs de bases de données.

Notre suite de sécurité des bases de données DataSunrise est spécialement conçue pour protéger les bases de données relationnelles contre les accès non autorisés et les fuites de données. Pour accomplir cette tâche, un système puissant de politiques de sécurité (les règles) est mis en place afin de restreindre l’accès à la base de données et d’appliquer un masquage dynamique des données. Pour utiliser cette fonctionnalité, notre programme doit connaître l’état des métadonnées de la base de données à tout moment. Les métadonnées correspondent au schéma de la base de données et à l’ensemble des valeurs des variables système, qui influencent l’exécution des requêtes et l’interprétation des résultats. Ces métadonnées peuvent être obtenues en envoyant une série de requêtes au serveur de base de données.

DataSunrise prend également en charge le suivi des changements incrémentiels en enregistrant les résultats d’exécution des commandes DDL traitées par notre produit. Mais la partie véritablement intéressante commence lorsque vous devez gérer les changements de métadonnées au sein de la transaction. Certains SGBDR supportent la transactionalité des commandes DDL, c’est-à-dire que vous pouvez annuler ces commandes lors du rollback de la transaction. D’autres SGBDR ne supportent pas les DDL dans les transactions. Dans ces SGBDR, les commandes DDL terminent implicitement la transaction en cours ou entraînent une erreur du serveur SQL.

Cependant, les SGBDR les plus populaires se situent quelque part entre les deux : ils supportent la transactionalité des commandes DDL mais pas pour toutes les commandes. Habituellement, les commandes les plus globales, utilisées pour créer et supprimer des objets du système de fichiers dans le stockage de la base de données, ne peuvent pas bénéficier d’une procédure de rollback, ainsi la transactionalité est supportée pour des commandes mineures dont l’annulation n’entraîne pas de modifications de la structure du système de fichiers. Les transactions dans tous les SGBDR ne se ressemblent qu’en général. Mais en pratique, chaque base de données dispose d’une syntaxe unique pour les commandes de contrôle des transactions et chaque SGBDR possède ses propres mécanismes de contrôle des transactions. Nous appelons cela le modèle transactionnel d’un SGBDR donné.

Pour le traitement correct des modifications de métadonnées, notre programme doit être capable d’annuler les modifications DDL interrompues lors du rollback d’une transaction. Il s’agit d’une tâche algorithmique assez complexe : elle nécessite la prise en charge d’un delta de métadonnées (diff) qui correspond aux modifications DDL intervenues dans la transaction en cours de chaque connexion à la base de données. Un tel delta existe avant la clôture de la transaction et peut être annulé en une seule fois ou en plusieurs parties (pour les SGBDR qui supportent les transactions à plusieurs niveaux ou les points de sauvegarde).

Examinons maintenant les fonctionnalités que les bases de données populaires peuvent offrir dans le contexte du DDL transactionnel.

Oracle Database

Oracle ne supporte pas le DDL transactionnel : la transaction est considérée comme clôturée dès l’exécution d’une commande CREATE, DROP, RENAME ou ALTER. Si la transaction contient des commandes DML, Oracle valide la transaction dans son ensemble puis valide la commande DDL comme une transaction séparée.

PostgreSQL

PostgreSQL supporte le DDL transactionnel : toutes les commandes DDL sont supportées, à l’exception des opérations d’envergure destinées à la création et à la suppression d’objets tels que DATABASE, TABLESPACE, CLUSTER. PostgreSQL supporte les transactions à plusieurs niveaux via des points de sauvegarde. Contrairement au SQL standard, PostgreSQL autorise des points de sauvegarde homonymes. Cela signifie que les anciens points ne sont pas accessibles tant que des points plus récents existent.

En cas d’erreur au sein d’une transaction, PostgreSQL annule l’ensemble de la transaction mais exige qu’une commande soit émise pour terminer la transaction en cours (COMMIT, ROLLBACK, ABORT) dans tous les cas. PostgreSQL démarre une transaction implicite au début d’une multi-instruction et la transforme en transaction explicite lorsqu’une commande BEGIN apparaît dans la multi-instruction (la transaction est considérée comme démarrée dès le début de la multi-instruction).

MySQL

MySQL ne supporte pas le DDL transactionnel. Aucune transaction n’est disponible pour MyISAM. Pour InnoDB, les commandes DDL entraînent une validation implicite de la transaction en cours.

MariaDB

MariaDB hérite de son comportement en matière de DDL transactionnel de MySQL et ne le supporte pas.

DB2

DB2 supporte les transactions à plusieurs niveaux, à la fois au niveau des transactions imbriquées et au niveau des points de sauvegarde. Les points de sauvegarde sont fournis avec des espaces de noms indépendants à chaque niveau d’imbrication.

Microsoft SQL Server (MS SQL)

La prise en charge des transactions à plusieurs niveaux dans MS SQL se traduit par l’appui sur les points de sauvegarde. Dans SQL Server, les dites transactions imbriquées servent uniquement de compteur aux appels de BEGIN TRANSACTION. Pour valider la transaction, il est nécessaire d’appeler un certain nombre de COMMIT TRANSACTION, et la commande ROLLBACK annule l’intégralité de la transaction, quel que soit le niveau d’imbrication. Le premier BEGIN TRANSACTION est toujours considéré comme le début de la transaction et permet en même temps de travailler pleinement avec des transactions à plusieurs niveaux via des points de sauvegarde. Ceux-ci peuvent être utilisés à l’aide des commandes SAVE TRANSACTION et ROLLBACK TRANSACTION.

Teradata

Teradata ne supporte pas le DDL transactionnel. Le DDL peut être placé dans une transaction mais il ne peut y avoir plus d’une commande par transaction et seulement en tant que dernière commande dans la transaction. Le comportement de Teradata est similaire à celui d’Oracle à cet égard. Ainsi, un DDL peut être exécuté avec la transaction ou ne pas être exécuté du tout si la transaction est annulée. Cela signifie que les modifications DDL, qui pourraient exister pendant un certain temps avant d’être annulées, ne sont pas disponibles.

Greenplum

Le comportement de Greenplum est similaire à celui de PostgreSQL.

Netezza

Netezza est également issu de PostgreSQL, mais il y a longtemps, et présente quelques différences notables en matière de support des transactions. Netezza supporte le DDL dans les transactions, mais il ne fournit pas de support pour des transactions à plusieurs niveaux (points de sauvegarde) et il est impossible de démarrer une transaction en dehors du début d’une multi-instruction si celle-ci contient déjà des commandes DDL.

Amazon Redshift

Similaire à PostgreSQL.

Amazon Aurora

Similaire à MySQL.

Toutes les connaissances concernant les fonctionnalités citées sont implantées dans la suite de sécurité des bases de données DataSunrise. Nous savons toujours à quoi ressemble le schéma de votre base de données et nous pouvons en assurer une protection optimale.

DataSunrise prend en charge toutes les bases de données majeures et les entrepôts de données, y compris Oracle ou Exadata. Il supporte également IBM DB2, IBM Netezza, MySQL, MariaDB, Greenplum, Amazon Aurora, Amazon Redshift, Microsoft SQL Server, Azure SQL, Teradata et bien d’autres. Vous êtes invités à télécharger une version d’essai gratuite si vous souhaitez l’installer sur vos locaux. Dans le cas où vous seriez un utilisateur cloud et que vous exécutez votre base de données sur Amazon AWS ou sur Microsoft Azure, vous pouvez l’obtenir sur le AWS marketplace ou sur le Azure marketplace.