Come i Database Popolari Gestiscono i Comandi DDL nelle Transazioni

L’integrazione dei comandi DDL nelle transazioni è una delle funzionalità più potenti fornite dai database. Ma non tutti i database sono in grado di rollbackare le modifiche DDL. Oracle, PostgreSQL, MySQL, MariaDB, DB2, MSSQL, Teradata, Greenplum, Netezza, Redshift, Aurora sono i database relazionali più popolari e questo articolo descrive la loro capacità di supportare comandi DDL nelle transazioni.

Una transazione è una sequenza di comandi incorporati in una singola unità logica. Quindi, una transazione viene eseguita come un’entità unica. Quando l’esecuzione di una transazione viene interrotta, la transazione non viene eseguita affatto. Le transazioni vengono utilizzate per mantenere l’integrità del database, l’esecuzione singola di tutti i comandi inclusi nella transazione significa salvaguardare i vincoli di integrità e l’impossibilità di lasciare il database in uno stato transitorio e non coerente.

DDL (Data Definition Language) è una famiglia di elementi del linguaggio SQL utilizzati per definire la struttura del database (inclusi operazioni con database, tabelle, colonne, indici, viste, procedure memorizzate, vincoli).

Per capire cos’è il DDL, è importante sapere che le istruzioni DDL giocano un ruolo cruciale nei sistemi di gestione dei database (DBMS) permettendo agli utenti di definire e modificare la struttura del database. La capacità di eseguire DDL nei sistemi di gestione dei database è essenziale per mantenere l’integrità e la coerenza dei dati memorizzati nel database. Tuttavia, il comportamento delle istruzioni DDL all’interno delle transazioni varia tra le diverse implementazioni DMBS. Capire come le istruzioni DDL si comportano all’interno delle transazioni nel suo specifico DBMS è cruciale per garantire l’integrità dei dati e implementare meccanismi di gestione degli errori appropriati.

La nostra DataSunrise Database Security Suite è appositamente progettata per la protezione dei database relazionali contro accessi non autorizzati e perdite di dati. Per svolgere questo compito, viene implementato un potente sistema di politiche di sicurezza (le regole) per la restrizione dell’accesso al database e il mascheramento dinamico dei dati. Per utilizzare questa funzionalità, il nostro programma deve conoscere lo stato dei metadata del database in ogni momento. I metadata sono insiemi di schema del database e valori delle variabili di sistema, che influenzano l’esecuzione delle query e l’interpretazione dei risultati. I metadata possono essere ottenuti inviando una serie di query al server di database.

DataSunrise supporta anche il tracciamento delle modifiche incrementali, registra i risultati di esecuzione dei comandi DDL elaborati dal nostro prodotto. Ma la parte veramente interessante inizia quando è necessario supportare le modifiche dei metadata all’interno della transazione. Alcuni RDBMS supportano la transactionalità dei comandi DDL, ossia è possibile rollbackare questi comandi quando si effettua il rollback della transazione. Altri RDBMS non supportano i DDL nelle transazioni. I comandi DDL in tali RDBMS terminano implicitamente la transazione corrente o causano un errore del server SQL.

Ma i RDBMS più popolari sono in una posizione intermedia: supportano la transactionalità dei comandi DDL ma non per tutti i comandi. Di solito, i comandi più generali usati per creare e eliminare oggetti del file system nello storage del database, non possono supportare la procedura di rollback, quindi la transactionalità è supportata per comandi minori il cui rollback non causa cambiamenti nella struttura del file system. Le transazioni in tutti i RDBMS sono simili solo in generale. Ma in pratica, ciascun database dispone di una sintassi unica per i comandi di controllo delle transazioni e ciascun RDBMS ha i propri meccanismi unici di controllo delle transazioni. Chiamiamo questo un modello di transazione di un determinato RDBMS.

Per la corretta elaborazione delle modifiche ai metadata, il nostro programma deve essere in grado di rollbackare le modifiche DDL abortite al rollback della transazione. È un compito algoritmico piuttosto complesso: richiede il supporto del delta dei metadata (diff) che corrisponde alle modifiche DDL all’interno della transazione corrente di ciascuna connessione al database. Tale delta esiste prima che la transazione venga chiusa e può essere rollbackato come un’unica parte o in parte (per quei RDBMS che supportano transazioni multilivello o punti di salvataggio).

Ora diamo un’occhiata alle caratteristiche che i database popolari possono offrire nel contesto del DDL transazionale.

Oracle Database

Oracle non supporta il DDL transazionale: la transazione è considerata chiusa quando viene eseguito un comando CREATE, DROP, RENAME o ALTER. Se la transazione contiene comandi DML, Oracle effettua il commit della transazione nel suo complesso e poi effettua il commit del comando DDL come transazione separata.

PostgreSQL

PostgreSQL supporta il DDL transazionale: tutti i comandi DDL eccetto le operazioni di alto livello mirate alla creazione e rimozione di oggetti come DATABASE, TABLESPACE, CLUSTER. PostgreSQL supporta transazioni multilivello a livello di punti di salvataggio. A differenza dello standard SQL, PostgreSQL supporta punti di salvataggio omonimi. Ciò significa che i punti più vecchi non sono disponibili finché esistono punti più nuovi.

Se si verifica un errore all’interno di una transazione, PostgreSQL rollbacka l’intera transazione ma richiede un comando per completare l’attuale transazione (COMMIT, ROLLBACK, ABORT) in ogni caso. PostgreSQL avvia una transazione implicita all’inizio di una dichiarazione multipla e trasforma la transazione in esplicita quando c’è un comando BEGIN all’interno di una dichiarazione multipla (la transazione è considerata iniziata con l’inizio della dichiarazione multipla).

MySQL

MySQL non supporta il DDL transazionale. Nessuna transazione disponibile per MyISAM. Per InnoDB, i comandi DDL causano un commit implicito della transazione corrente.

MariaDB

MariaDB eredita il suo comportamento in termini di DDL transazionale da MySQL e non lo supporta.

DB2

DB2 supporta transazioni multilivello sia a livello di transazioni nidificate che a livello di punti di salvataggio. I punti di salvataggio sono forniti con namespace indipendenti su ciascun livello di nidificazione.

Microsoft SQL Server (MS SQL)

Il supporto delle transazioni multilivello in MS SQL si esprime nel supporto dei punti di salvataggio. In SQL Server, le cosiddette transazioni nidificate servono solo come contatore delle chiamate BEGIN TRANSACTION. Per confermare la transazione è necessario chiamare un certo numero di COMMIT TRANSACTION, e il comando ROLLBACK rollbacka l’intera transazione indipendentemente dal livello di nidificazione. La prima BEGIN TRANSACTION è sempre considerata come l’inizio della transazione e permette il lavoro a tutto tondo con transazioni multilivello tramite punti di salvataggio nel contempo. I punti di salvataggio possono essere utilizzati tramite i comandi SAVE TRANSACTION e ROLLBACK TRANSACTION.

Teradata

Teradata non supporta il DDL transazionale. Il DDL può essere inserito in una transazione ma non più di un comando per transazione e solo come comando finale della transazione. Il comportamento di Teradata è simile a quello di Oracle a questo riguardo. Quindi un DDL può essere eseguito con l’esecuzione della transazione o non essere eseguito affatto con il rollback della transazione. Ciò significa che le modifiche DDL che possono esistere per un certo tempo e poi essere rollbackate non sono disponibili.

Greenplum

Il comportamento di Greenplum è simile a quello di PostgreSQL.

Netezza

Netezza ha origine anch’essa da PostgreSQL ma da molto tempo. E presenta alcune differenze sostanziali riguardo al supporto delle transazioni. Netezza supporta DDL nelle transazioni, ma non fornisce supporto per transazioni multilivello (punti di salvataggio) ed è impossibile iniziare una transazione non dall’inizio di una dichiarazione multipla, se la dichiarazione multipla contiene già comandi DDL.

Amazon Redshift

Simile a PostgreSQL.

Amazon Aurora

Simile a MySQL.

Tutte le conoscenze sulle caratteristiche menzionate sono implementate in DataSunrise Database Security Suite. Siamo sempre a conoscenza di quale sia lo schema del database e possiamo fornire la sua protezione perfetta.

DataSunrise supporta tutti i principali database e data warehouse tra cui Oracle o Exadata. Supporta anche IBM DB2, IBM Netezza, MySQL, MariaDB, Greenplum, Amazon Aurora, Amazon Redshift, Microsoft SQL Server, Azure SQL, Teradata e altri. È benvenuto a scaricare una prova gratuita se desidera installare nel proprio ambiente. Nel caso Lei sia un utente Cloud e faccia girare il suo database su Amazon AWS o Microsoft Azure, può ottenerlo dal mercato AWS o dal mercato Azure.