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, 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 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).
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 transaction. Some RDBMSs support DDL commands “transactionality”, i.e. you can rollback these commands when roll-backing 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 support of metadata delta (diff) which corresponds to DDL changes inside current transaction of each connection to the database. Such a delta exists before transaction is closed and could be rolled back as one piece or in parts (for those RDBMSs which support multi-level transactions or save points).
Not let’s take a look at the features the popular databases can offer in the context of transactional DDL.
Oracle does not support transactional DDL: the transaction is considered as closed when CREATE, DROP, RENAME or ALTER command is executed. If transaction contains DML commands, the Oracle commits the transaction as a whole and then commits the DDL command as a separate transaction.
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 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 does not support transactional DDL. No transactions available for MyISAM at all. For InnoDB, DDL commands cause implicit commit of the current transaction.
MariaDB inherits its behavior in terms of transactional DDL from MySQL and doesn’t support it.
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 ROLL BACK 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
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 roll back of the transaction. It means that DDL changes that can exist for a certain time and then be rolled back are not available.
Greenplum’s behavior is similar to PostgreSQL’s.
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 multistatement, if the multistatement already contains DDL commands.
Similar to PostgreSQL.
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.