ODBMS.org

Operational Database Management Systems

DDL+ACID – You‘ve heard of ‘transactional’, let’s talk ‘isolated’

by ·

SEP 19 2019 by Martin Kysel

In SQL you can perform a lot of DDL operations, such as creating or renaming tables, creating or removing columns, and more, and these DDL statements are most often used when creating or upgrading your application schema. However, there’s more to transactional DDL than just being able to do large schema updates atomically. Transactional DDL also provides protection against failures. That means that networks can fail, machines can fail, and still, NuoDB will guarantee that all schema modifications are either all applied or nothing is applied. Essentially this means that all operations in NuoDB aspire to be failure resistant. Learn how we’ve approached that when building our database. 

In this article, we’ll focus on only one of the transactional aspects of ACID, namely Isolation. I will explain how it might impact your application code and how to avoid some of the caveats.

WHAT DOES TRANSACTIONAL DDL MEAN?

WHAT IS DDL?

DDL stands for Data Definition Language and is a group of SQL statements that modifies the metadata of the database. CREATE TABLE T1(I INT); is a DDL statement and so is ALTER TABLE T1 ADD COLUMN J INT;.

WHAT IS ‘TRANSACTIONAL’?

We are accustomed to talking about transactions in the context of DML (Data Manipulation Language), but the same principles apply when we talk about DDL transactions. A transaction must be ACID – Atomic, Consistent, Isolated, and Durable. In this case, that means that a transaction executes several statements, some of which are DDL, while treating them as a single operation that can either be rolled back or committed. 

WHAT IS ‘ISOLATION’?

This article is focused on one aspect of transactionality: Isolation. A transaction is isolated from the rest of the system if its effects do not spill over to other transactions. You can pick various isolation levels that suit your needs the best. NuoDB supports Consistent Read and Read Committed. In this article, we’ll explore the various interactions of DML vs. DDL and isolation levels.

DOES DDL HAVE TO BE TRANSACTIONAL?

There is no fundamental reason why DDL statements shouldn’t be transactional, but historically, databases haven’t provided this functionality. Even today, only a handful of databases provide truly transactional DDL, and it has limitations in most cases.

It is hard to say why the vendors of the ‘90s decided to exclude DDL from the ACIDity guarantees. The ISO 9075 standard has never made any distinction between DML and DDL when it comes to transactions. 

The lack of transactionality means that many application developers are accustomed to performing application upgrades during a database maintenance window. These windows are no longer tolerated in today’s always-up world.

Transactional DDL implies that DDL needs to happen in Isolation from other transactions that happen concurrently. This means that the metadata of the modified table needs to be versioned. MVCC is a natural precursor for transactional DDL. In a database that implements snapshot isolation, readers do not block writers and writers do not block readers. Using MVCC semantics for DDL means that DDL can happen under the covers while readers proceed with using the old snapshot of the metadata. Writers, on the other hand, are not allowed to proceed while DDL is happening.

If maintenance windows are not viable and thus DDL has to coexist with DML, proper Isolation becomes key. Without it, DML can observe the database in a semi upgraded state that is unexpected and hence was never tested. This leads to unexpected/undesired behavior that can cause application outages and downtime.

One popular alternative in the databases that don’t support Transactional DDL is to commit every transaction before and after a DDL statement. This ensures that the DDL has the newest snapshot, and thanks to table locks, it removes the need for metadata versioning. It also shields the application developer from the convoluted case when DDL is executed in Consistent Read isolation level, which we will explore soon.

Autocommitting DDL is definitely viable and NuoDB offers a compatibility mode for those who are migrating from databases that use this. To enable auto-commit of DDL, execute the following statement:

set system property AUTOCOMMIT_DDL=true;

HOW TO MAKE DML ISOLATED FROM DDL CORRECTLY

In this section, I use the terms update DML (insert, update, delete, select for update) and writer interchangeably. They both refer to a subset of all DML. Read DML (select) is not impacted by concurrent DDL.

If write DML was allowed to proceed while a DDL statement was modifying a table, the database would have to give up Isolation guarantees.

We are assuming that a table T1 (create table T1(i int);) has been created prior to both conflicting transactions.

You might notice that we never specify what the isolation level of the first transaction is. Isolation levels only impact the transaction that is “second,” as they allow the application developer to violate serializability and act on the results of transactions that have started concurrently and have been committed in the past. NuoDB does not support any isolation level lower than Read Committed, so it is impossible to observe uncommitted state.

Let me explain using a concrete conflict between update DML insert into T1(i) values(5) and DDL alter table T1 add column j int not null. Thanks to table locks, these two operations can never happen concurrently and order is enforced. Additionally, NuoDB supports two Isolation levels: Read Committed and Consistent Read, which further complicates the interaction.

DDL IS FIRST; INSERT IS READ COMMITTED

If the insert is in Read Committed, it has to operate on a consistent snapshot of the database that is recalculated for every statement. This means that once the table lock is released by the DDL, the insert is allowed to reload the metadata and proceed. In this case, the snapshot of the insert will contain the new column and hence the insert will fail with Error 58000: illegal null in field J in table T1. This result might be surprising to application developers who did not anticipate a concurrent metadata modification.

DDL IS FIRST; INSERT IS CONSISTENT READ

If the insert is in Consistent Read, it has to operate on a consistent snapshot of the database. NuoDB includes the metadata at the start of the transaction in the snapshot. This means that reads always return the same columns, even if concurrent metadata modifications are happening. For writes, enforcing a consistent snapshot leads to potential Consistency violations. If the insert happens after the DDL, it might insert NULL into a column that has been defined as NOT NULL. Clearly, this is a violation of the table constraints, hence the write needs to be aborted with Error 58000: Table USER.T1 has been changed.

INSERT IS FIRST; DDL IS READ COMMITTED

After the insert completes, the table contains the value 5 in column i. There is no column J. If one attempted to add a column J, all rows would be implicitly expanded with the value of NULL. Since J is defined as NOT NULL in our scenario, the DDL will fail with Error 42000: field J doesn't allow NULL values, but a default value for the existing rows hasn't been specified. The application developer now has to rewrite the insert DML to include data for column J.

INSERT IS FIRST; DDL IS CONSISTENT READ

This scenario is probably the most confusing one. DDL, similarly to DML, contains a snapshot of the database. The snapshot contains all records that are visible at the start of the transaction. The newly inserted row is not visible to the consistent read DDL transaction because it happened after the transaction started. If DDL were allowed to operate on the row, it would be a violation of the Isolation level. On the other hand, if the DDL were allowed to proceed, it would have to ignore the row and not expand it with NULL. This would be a violation of the NOT NULL constraint and a violation of Consistency. To prevent this from happening, the DDL transaction needs to fail. This can be confusing if you have a workflow that looks like the following:

SQL> START TRANSACTION isolation level consistent READ;
SQL> SELECT * FROM T1;
SQL> ALTER TABLE T1 ADD COLUMN j INT NOT NULL;
Error 42000: FIELD J doesn't allow NULL values, but a default value for the existing rows hasn't been specified
SQL> SELECT * FROM T1;
SQL> ROLLBACK;

As you can see, the snapshot of the read DML does not contain the newly inserted record, yet the DDL is prevented. I believe that this is why some traditional databases decided to only allow transactional DDL in Read Committed transactions.

WHY IS TRANSACTIONAL DDL RELEVANT?

So far we have talked about transactional DDL from the point of view of the database vendor. But the use case is from the point of view of the database administrator. When we apply ACIDity to DDL transactions, we realize that Atomicity is extremely useful. The definition states that: 

“Each transaction is treated as a single “unit,” which either succeeds completely or fails completely.”

Transactional DDL gives database administrators the ability to perform multiple modifications (for example, several ALTER TABLE statements) in a single operation.

For developers, the strong Isolation guarantees of transactional DDL makes development of applications easier. An application can only observe the database in state A (before the upgrade) or in state B (after the upgrade) and will never see partial results. This reduces the required test matrix and increases confidence in the rolling upgrade procedure.

Stay tuned for future articles that explore the ability to roll back large schema modifications.

SUMMARY

Implementing transactional DDL in a database requires a non-trivial amount of work and not all database vendors have decided to put in the effort. NuoDB supports transactional DDL and we believe in it as the base of our always-on promise. We’ll explore the competitive landscape and provide an overview of transactional DDL support in 2019 in the next article.

Applications change on a regular basis given the ever-changing business requirements. When writing an always-on application, you should expect concurrent/online metadata modifications by different actors. After reading our examples in this article, we recommend looking at your application code and asking yourself the question: “What will this query do if the metadata is changed concurrently?” 

Want to try our NuoDB? Download our Community Edition today. 

ADDITIONAL READING

Sponsored by NuoDB

You may also like...