How Popular Databases Deal with DDL Commands in Transactions

Integration of DDL commands into transactions is one of the most powerful features databases provide. But not all the databases are able to rollback DDL changes. Oracle, PostgreSQL, MySQL, MariaDB, DB2, MSSQL, Teradata, Greenplum, Netezza, Redshift, Aurora are the most popular relational databases and this article describes their capability to support DDL commands in transactions.

Transaction is a sequence of commands incorporated into one logical unit. Thus, a transaction is executed as one entity. When an execution of transaction is interrupted, the transaction is not executed at all. Transactions are used to preserve database integrity, singlet execution of all commands transaction includes, means saving integrity constraints and the impossibility to leave the database in a transitional, non-consistent state.

DDL (Data Definition Language) is a family of SQL language elements used to define database structure (including operations with databases, tables, columns, indexes, views, stored procedures, constraints).

To understand what is DDL, it’s important to know that DDL statements play a crucial role in database management systems (DBMS) by allowing users to define and modify the structure of the database. The ability to execute DDL in database management systems is essential for maintaining the integrity and consistency of the data stored within the database. However, the behavior of DDL statements within transactions varies among different DBMS implementations. Understanding how DDL statements behave within transactions in your specific DBMS is crucial for ensuring data integrity and implementing appropriate error handling mechanisms.

Our DataSunrise Database Security Suite is purpose-built for protection of relational databases against unauthorized access and data leaks. To perform this task, a powerful system of security policies (the rules) is implemented for database access restriction and dynamic data masking. To employ this functionality, our program should know database metadata state at every moment. Metadata is database schema and system variables’ values sets, which affect query execution and interpreting results. Metadata could be got by sending a series of queries to the database server.

DataSunrise also supports tracking of incremental changes, logs execution results of DDL commands processed by our product. But the really interesting part begins when you need to support metadata changes inside the transaction. Some RDBMSs support DDL commands transactionality, i.e. you can rollback these commands when rollbacking the transaction. Other RDBMSs don’t support DDLs in transactions. DDL commands in such RDBMSs end current transaction implicitly or cause SQL server error.

But the most popular RDBMSs are somewhere in the middle: they do support DDL commands transactionality but not for all commands. Usually, the most global commands used to create and delete file system objects in the database storage, cannot support rollback procedure, thus transactionality is supported for minor commands rollback of which does not cause file system structure changes. Transactions in all RDBMSs are similar only in general. But in practice, each database features unique syntax of transaction control commands and each RDBMS has its own unique transaction control mechanisms. We call this a transaction model of a certain RDBMS.

For correct processing of metadata changes, our program should be able to roll back DDL changes aborted on transaction rollback. It is a quite complex algorithmic task: it requires the support of metadata delta (diff) which corresponds to DDL changes inside current transaction of each connection to the database. Such a delta exists before the transaction is closed and could be rolled back as one piece or in parts (for those RDBMSs which support multi-level transactions or savepoints).

Not let’s take a look at the features the popular databases can offer in the context of transactional DDL.

Oracle Database

Oracle does not support transactional DDL: the transaction is considered as closed when CREATE, DROP, RENAME or ALTER command is executed. If the transaction contains DML commands, the Oracle commits the transaction as a whole and then commits the DDL command as a separate transaction.

PostgreSQL

PostgreSQL supports transactional DDL: all DDL commands except high-caliber operations aimed at creation and deletion of such objects as DATABASE, TABLESPACE, CLUSTER. PostgreSQL supports multi-level transactions on save points level. Unlike standard SQL, PostgreSQL supports homonymous save points. It means that the older points are not available for as long as newer ones exist.

If an error occurs inside a transaction, PostgreSQL rolls back the whole transaction but demands a command to complete the current transaction (COMMIT, ROLLBACK, ABORT) in any case. PostgreSQL starts an implicit transaction in the beginning of multistatement and transforms the transaction into explicit one when there is BEGIN command inside a multistatement (the transaction is considered as started with the beginning of multistatement).

MySQL

MySQL does not support transactional DDL. No transactions available for MyISAM at all. For InnoDB, DDL commands cause implicit commit of the current transaction.

MariaDB

MariaDB inherits its behavior in terms of transactional DDL from MySQL and doesn’t support it.

DB2

DB2 supports multi-level transactions both on nested transaction level and on save points level. Save points are provided with independent namespaces on each nesting level.

Microsoft SQL Server (MS SQL)

The support of multi-level transactions in MS SQL is expressed in the support of save points. In SQL Server, so-called nested transactions serves only as a counter of BEGIN TRANSACTION calls. To confirm transaction it is necessary to call a certain number of COMMIT TRANSACTION, and ROLLBACK command rolls back the whole transaction regardless of nesting level. The first BEGIN TRANSACTION is always regarded as the beginning of the transaction and it enables the full-fledged work with multi-level transactions through save points at the same time. Save points could be used through SAVE TRANSACTION and ROLLBACK TRANSACTION commands.

Teradata

Teradata does not support transactional DDL. DDL could be placed inside a transaction but not more than one command for a transaction and only as the last command in the transaction. Teradata’s behavior similar to Oracle’s in this regard. Thus a DDL could be executed with the execution of transaction or not executed at all with the rollback of the transaction. It means that DDL changes that can exist for a certain time and then be rolled back are not available.

Greenplum

Greenplum’s behavior is similar to PostgreSQL’s.

Netezza

Netezza also originated from PostgreSQL but a long time ago. And features some serious difference in regard to support of transactions. Netezza supports DDL in transactions as well, but it does not provide multi-level transactions support (save points) and it is impossible to begin a transaction not from the beginning of a multistatement, if the multistatement already contains DDL commands.

Amazon Redshift

Similar to PostgreSQL.

Amazon Aurora

Similar to MySQL.

All knowledge about the mentioned features is implemented in DataSunrise Database Security Suite. We always know what your database schema looks like and can provide its perfect protection.

DataSunrise supports all major databases and data warehouses including Oracle or Exadata. It also supports IBM DB2, IBM Netezza, MySQL, MariaDB, Greenplum, Amazon Aurora, Amazon Redshift, Microsoft SQL Server, Azure SQL, Teradata and more. You are welcome to download a free trial if would like to install on your premises. In case you are a cloud user and run your database on Amazon AWS or Microsoft Azure you can get it from AWS market place or Azure market place.