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é à prendre en charge les commandes DDL dans les transactions.

Une transaction est une séquence de commandes intégrée dans une seule unité logique. Ainsi, une transaction est exécutée comme une seule entité. 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 incluses dans la transaction, ce qui signifie la sauvegarde des contraintes d’intégrité et l’impossibilité de laisser la base de données dans un état transitoire et 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é d’exécuter des instructions 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 dans les transactions varie selon les différentes implémentations de SGBD. Comprendre comment les instructions DDL se comportent dans les transactions dans votre SGBD spécifique est crucial pour garantir l’intégrité des données et mettre en œuvre des mécanismes de gestion des erreurs appropriés.

Notre suite de sécurité de base 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 puissant système de politiques de sécurité (les règles) est mis en œuvre pour la restriction d’accès à la base de données et le masquage dynamique des données. Pour exploiter cette fonctionnalité, notre programme doit connaître l’état du schéma de la base de données à tout moment. Les méta-données sont constituées des ensembles de valeurs du schéma de la base de données et des variables du système, qui affectent l’exécution des requêtes et l’interprétation des résultats. Les méta-données peuvent être obtenues en envoyant une série de requêtes au serveur de la base de données.

DataSunrise prend également en charge le suivi des changements incrémentiels et enregistre les résultats d’exécution des commandes DDL traitées par notre produit. Mais la partie vraiment intéressante commence lorsque vous devez prendre en charge les modifications de méta-données à l’intérieur de la transaction. Certains SGBDR prennent en charge la transactionnalité des commandes DDL, c’est-à-dire que vous pouvez annuler ces commandes lors de l’annulation de la transaction. D’autres SGBDR ne prennent pas en charge les DDL dans les transactions. Les commandes DDL dans ces SGBDR mettent fin à la transaction en cours de manière implicite ou provoquent une erreur de serveur SQL.

Mais les SGBDR les plus populaires se situent quelque part au milieu : ils prennent en charge la transactionnalité des commandes DDL mais pas pour toutes les commandes. Habituellement, les commandes les plus globales utilisées pour créer et supprimer des objets de système de fichiers dans le stockage de la base de données ne peuvent pas prendre en charge la procédure de retour arrière, ainsi la transactionnalité est prise en charge pour des commandes mineures dont le retour en arrière ne provoque pas de modifications de la structure du système de fichiers. Les transactions dans tous les SGBDR ne se ressemblent que dans les grandes lignes. Mais en pratique, chaque base de données comporte une syntaxe unique pour les commandes de contrôle de transaction et chaque SGBDR dispose de ses propres mécanismes de contrôle de transaction uniques. Nous appelons cela un modèle de transaction d’un certain SGBDR.

Pour le traitement correct des modifications de méta-données, notre programme doit être capable d’annuler les modifications DDL abandonnées lors de l’annulation de la transaction. C’est une tâche algorithmique assez complexe : elle nécessite la prise en charge de la différence de méta-données (delta) qui correspond aux modifications DDL à l’intérieur de la transaction en cours de chaque connexion à la base de données. Un tel delta existe avant la fermeture de la transaction et peut être annulé en une seule pièce ou en parties (pour les SGBDR qui prennent en charge les transactions multi-niveaux ou les points de sauvegarde).

Maintenant, examinons les fonctionnalités offertes par les bases de données populaires dans le contexte des DDL transactionnels.

Base de Données Oracle

Oracle ne prend pas en charge les DDL transactionnels : la transaction est considérée comme fermée lorsque la commande CREATE, DROP, RENAME ou ALTER est exécutée. Si la transaction contient des commandes DML, Oracle valide la transaction dans son ensemble puis valide la commande DDL comme une transaction distincte.

PostgreSQL

PostgreSQL prend en charge les DDL transactionnels : toutes les commandes DDL sauf les opérations de grande envergure visant la création et la suppression de tels objets que DATABASE, TABLESPACE, CLUSTER. PostgreSQL prend en charge les transactions multi-niveaux au niveau des points de sauvegarde. Contrairement au SQL standard, PostgreSQL prend en charge les points de sauvegarde homonymes. Cela signifie que les anciens points ne sont pas disponibles tant que les nouveaux existent.

Si une erreur se produit à l’intérieur d’une transaction, PostgreSQL annule toute la transaction mais exige une commande pour compléter la transaction en cours (COMMIT, ROLLBACK, ABORT) dans tous les cas. PostgreSQL commence une transaction implicite au début de la multi-instruction et transforme la transaction en une explicite lorsqu’il y a une commande BEGIN à l’intérieur de la multi-instruction (la transaction est considérée comme commencée avec le début de la multi-instruction).

MySQL

MySQL ne prend pas en charge les DDL transactionnels. Aucune transaction n’est disponible pour MyISAM. Pour InnoDB, les commandes DDL provoquent une validation implicite de la transaction en cours.

MariaDB

MariaDB hérite de son comportement en termes de DDL transactionnel de MySQL et ne le prend pas en charge.

DB2

DB2 prend en charge les transactions multi-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)

Le support des transactions multi-niveaux dans MS SQL s’exprime par le support des points de sauvegarde. Dans SQL Server, les transactions dites imbriquées ne servent que de compteur d’appels BEGIN TRANSACTION. Pour confirmer la transaction, il est nécessaire d’appeler un certain nombre de COMMIT TRANSACTION, et la commande ROLLBACK annule toute 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 de travailler pleinement avec des transactions multi-niveaux via des points de sauvegarde en même temps. Les points de sauvegarde peuvent être utilisés via les commandes SAVE TRANSACTION et ROLLBACK TRANSACTION.

Teradata

Teradata ne prend pas en charge les DDL transactionnels. Les DDL peuvent être placés à l’intérieur d’une transaction, mais pas plus d’une commande par transaction et seulement en tant que dernière commande de la transaction. Le comportement de Teradata est similaire à celui d’Oracle à cet égard. Ainsi, une commande DDL peut être exécutée avec l’exécution de la transaction ou pas exécutée du tout avec l’annulation de la transaction. Cela signifie que les modifications DDL qui peuvent exister pendant un certain temps puis être annulées ne sont pas disponibles.

Greenplum

Le comportement de Greenplum est similaire à celui de PostgreSQL.

Netezza

Netezza est également originaire de PostgreSQL mais il y a longtemps. Et présente quelques différences sérieuses en ce qui concerne la prise en charge des transactions. Netezza prend également en charge les DDL dans les transactions, mais elle ne prend pas en charge les transactions multi-niveaux (points de sauvegarde) et il est impossible de commencer une transaction non pas au début d’une multi-instruction, si la multi-instruction contient déjà des commandes DDL.

Amazon Redshift

Similaire à PostgreSQL.

Amazon Aurora

Similaire à MySQL.

Toutes les connaissances sur les fonctionnalités mentionnées sont mises en œuvre dans la suite de sécurité de base de données DataSunrise. Nous savons toujours à quoi ressemble votre schéma de base de données et pouvons fournir sa protection parfaite.

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