Transactional DDL

Post by Luke Bredeson

If you’ve written any reasonably complex MySQL script you may have been disappointed to find out that DDL statements in MySQL cause an implicit commit (looks like Oracle also suffers from this problem).  In practical terms, this means that if you executed the following:

begin;

-- A data table would be nice
create table data (id int);

-- Need more columns in my table, better get on that...
alter table data add column foo varchar(10);
alter table data add column bar varchar(10);
alter table data add column baz farchar(10);

commit;

…the result would be a data table with foo and bar columns, but no baz column (due to the “farchar” typo). Attempting a rollback will not help you, due to the implicit commit. If this were a part of a larger migration script of some kind, and you had expected to be able to rerun it cleanly after any potential failure due to having used transactional semantics (and you were of course using InnoDB rather than MyISAM), you would get duplicate table/column errors when you tried to rerun it, even after fixing your “farchar” typo.

It’s definitely unintuitive that certain database operations cause an implicit commit, but it turns out that PostgreSQL doesn’t have this problem.  In PostgreSQL, you can run the above successfully (the data table won’t exist after the error, as expected).  You can even safely involve multiple tables:

begin;

create table foo (id int);
create table bar (id int);
create table baz (id inty);

commit;

…and you’ll be left with no new tables at all.  You can manually rollback at will as well.  Pretty cool.

This entry was posted in Agile Processes and tagged , , . Bookmark the permalink.

You may also like:

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>