Come i Database più Diffusi Gestiscono i Comandi DDL nelle Transazioni

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

Una transazione è una sequenza di comandi incorporati in un’unica unità logica. Così, la transazione viene eseguita come un’entità unica. Quando l’esecuzione di una transazione viene interrotta, la transazione non viene eseguita affatto. Le transazioni sono utilizzate per preservare l’integrità del database; l’esecuzione singola di tutti i comandi inclusi nella transazione garantisce il rispetto dei vincoli di integrità e l’impossibilità di lasciare il database in uno stato transitorio, non coerente.

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

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

Comprendere il DDL nei Sistemi di Gestione dei Database

Il Data Definition Language (DDL) rappresenta un componente critico dei sistemi di gestione dei database, fornendo i comandi necessari per definire e modificare la struttura del database. All’interno di un DBMS, i comandi DDL creano il quadro che contiene i dati e ne determina l’organizzazione, l’accesso e la manutenzione.

I comandi DDL più fondamentali nei sistemi di gestione dei database includono CREATE, ALTER, DROP, TRUNCATE, COMMENT e RENAME. Il comando CREATE crea nuovi oggetti del database come tabelle, viste, indici o procedure memorizzate. ALTER modifica gli oggetti esistenti aggiungendo colonne, cambiando i tipi di dati o implementando vincoli. DROP rimuove completamente gli oggetti dallo schema del database.

TRUNCATE, sebbene talvolta classificato come DML, funziona come DDL nella maggior parte dei sistemi di gestione dei database poiché azzera la memorizzazione della tabella e tipicamente non può essere annullato. COMMENT aggiunge note descrittive agli oggetti del database, migliorando la documentazione. RENAME modifica gli identificativi degli oggetti per una migliore chiarezza o organizzazione.

Quando si eseguono istruzioni DDL nei sistemi di gestione dei database, questi comandi acquisiscono tipicamente dei lock sullo schema che impediscono modifiche concorrenti degli stessi oggetti. Questo comportamento di lock varia tra i sistemi: alcuni implementano lock brevi durante le modifiche dei metadati, mentre altri bloccano gli oggetti per tutta la durata dell’operazione. Comprendere questi comportamenti di lock è essenziale quando si pianificano operazioni di manutenzione in ambienti di produzione.

Oltre alla modifica della struttura di base, il DDL nei moderni sistemi di gestione dei database gestisce anche funzionalità più avanzate come il partizionamento (dividere le tabelle in segmenti più piccoli e gestibili), l’implementazione di politiche di sicurezza tramite i comandi GRANT e REVOKE, e la definizione di trigger che si eseguono automaticamente in risposta a eventi specifici. Queste capacità rendono il DDL uno strumento eccezionalmente potente per architetti e amministratori di database.

La nostra DataSunrise Database Security Suite è progettata per proteggere i 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 degli accessi al database e il data masking dinamico. Per utilizzare questa funzionalità, il nostro programma deve conoscere lo stato dei metadati del database in ogni momento. I metadati rappresentano lo schema del database e l’insieme dei valori delle variabili di sistema, che influenzano l’esecuzione delle query e l’interpretazione dei risultati. I metadati possono essere ottenuti inviando una serie di query al server di database.

DataSunrise supporta anche il tracciamento delle modifiche incrementali, registrando i risultati di esecuzione dei comandi DDL elaborati dal nostro prodotto. Ma la parte veramente interessante inizia quando è necessario gestire le modifiche dei metadati all’interno della transazione. Alcuni RDBMS supportano la transazionalità dei comandi DDL, ovvero è possibile annullare questi comandi durante il rollback della transazione. Altri RDBMS non supportano i DDL nelle transazioni. In tali RDBMS, i comandi DDL terminano implicitamente la transazione corrente o provocano un errore del server SQL.

Ma i RDBMS più diffusi si collocano a metà strada: supportano la transazionalità dei comandi DDL, ma non per tutti i comandi. Di solito, i comandi più globali usati per creare ed eliminare oggetti del file system all’interno dell’archiviazione del database non possono supportare la procedura di rollback, pertanto la transazionalità è supportata per i comandi minori, il cui rollback non comporta modifiche alla struttura del file system. Le transazioni in tutti i RDBMS sono simili solo in linea generale, ma in pratica ogni database presenta una sintassi unica per i comandi di controllo delle transazioni e ogni RDBMS ha i propri meccanismi unici di controllo delle transazioni. A questo definiamo il modello di transazione di un determinato RDBMS.

Per il corretto trattamento delle modifiche dei metadati, il nostro programma deve essere in grado di annullare le modifiche DDL interrotte da un rollback della transazione. Si tratta di un compito algoritmico piuttosto complesso: richiede il supporto di un delta dei metadati (diff) che corrisponda alle modifiche DDL effettuate all’interno della transazione corrente di ogni connessione al database. Tale delta esiste prima che la transazione venga chiusa e può essere annullato in un unico blocco o in parti (per quei RDBMS che supportano transazioni a più livelli o savepoints).

Ora diamo un’occhiata alle funzionalità che i database più diffusi possono offrire nel contesto delle DDL transazionali.

Oracle Database

Oracle non supporta il DDL transazionale: la transazione viene considerata chiusa al momento dell’esecuzione del comando CREATE, DROP, RENAME o ALTER. Se la transazione contiene comandi DML, Oracle conferma la transazione nel suo insieme e successivamente conferma il comando DDL come una transazione separata.

PostgreSQL

PostgreSQL supporta il DDL transazionale: tutti i comandi DDL ad eccezione delle operazioni di alto livello volte alla creazione e cancellazione di oggetti come DATABASE, TABLESPACE, CLUSTER. PostgreSQL supporta transazioni multi-livello a livello di savepoint. A differenza dello standard SQL, PostgreSQL supporta savepoint omonimi, il che significa che i savepoint più vecchi non sono disponibili finché esistono quelli più recenti.

Se si verifica un errore all’interno di una transazione, PostgreSQL annulla l’intera transazione, ma richiede comunque un comando per completare la transazione corrente (COMMIT, ROLLBACK, ABORT). PostgreSQL avvia una transazione implicita all’inizio di una multistruttura e la trasforma in una transazione esplicita quando viene emesso il comando BEGIN all’interno della multistruttura (la transazione viene considerata avviata con l’inizio della multistruttura).

MySQL

MySQL non supporta il DDL transazionale. Non sono disponibili transazioni per MyISAM. Per InnoDB, i comandi DDL provocano il 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 multi-livello sia a livello di transazioni annidate sia a livello di savepoint. I savepoint sono forniti con namespace indipendenti ad ogni livello di annidamento.

Microsoft SQL Server (MS SQL)

Il supporto delle transazioni multi-livello in MS SQL si esprime tramite il supporto dei savepoint. In SQL Server, le cosiddette transazioni annidate servono solo come un contatore delle chiamate a BEGIN TRANSACTION. Per confermare una transazione è necessario eseguire un certo numero di COMMIT TRANSACTION, mentre il comando ROLLBACK annulla l’intera transazione indipendentemente dal livello di annidamento. Il primo BEGIN TRANSACTION è sempre considerato l’inizio della transazione e consente al contempo il pieno utilizzo delle transazioni multi-livello tramite i savepoint. I savepoint possono essere utilizzati tramite i comandi SAVE TRANSACTION e ROLLBACK TRANSACTION.

Teradata

Teradata non supporta il DDL transazionale. Il DDL può essere inserito all’interno di una transazione, ma non più di un comando per transazione e solo come ultimo comando della stessa. Il comportamento di Teradata in questo ambito è simile a quello di Oracle. Quindi, un DDL può essere eseguito con l’esecuzione della transazione oppure non eseguito affatto in caso di rollback della transazione. Ciò significa che le modifiche DDL, che potrebbero esistere per un certo periodo di tempo e poi essere annullate, non sono disponibili.

Greenplum

Il comportamento di Greenplum è simile a quello di PostgreSQL.

Netezza

Netezza ha anch’esso origine da PostgreSQL, ma risale a molto tempo fa, e presenta alcune differenze significative per quanto riguarda il supporto delle transazioni. Netezza supporta il DDL nelle transazioni, ma non fornisce il supporto per transazioni multi-livello (savepoint) ed è impossibile iniziare una transazione da un punto che non sia l’inizio di una multistruttura, se la multistruttura contiene già comandi DDL.

Amazon Redshift

Simile a PostgreSQL.

Amazon Aurora

Simile a MySQL.

Tutte le conoscenze relative alle funzionalità menzionate sono implementate nella DataSunrise Database Security Suite. Sappiamo sempre come appare lo schema del tuo database e possiamo garantirne una protezione perfetta.

DataSunrise supporta tutti i principali database e data warehouse, inclusi Oracle o Exadata. Supporta inoltre IBM DB2, IBM Netezza, MySQL, MariaDB, Greenplum, Amazon Aurora, Amazon Redshift, Microsoft SQL Server, Azure SQL, Teradata e altri. Sei invitato a scaricare una versione di prova gratuita se desideri installarla presso la tua sede. Nel caso in cui tu sia un utente cloud e gestisca il tuo database su Amazon AWS o Microsoft Azure puoi ottenerla dal market place AWS o dal market place Azure.