MySQL DDL ignores autocommit transactionality

[max-hoffman]

CREATE and ALTER statements ignore autocommit transactions in MySQL. They do not commit the working set, but they are immediately available to other sessions

• CREATE table is immediately visible to all sessions, including those with set autocommit=0
• ALTER table, CREATE_VIEW, PROCEDURE, TRIGGER are also immediately visible
• CREATE/ALTER do not commit in progress updates, but do block ROLLBACKs from undoing the current working set. Subsequent working set changes within the transaction can still be ROLLBACKed.
• CREATE DATABASE seems to be session specific, not visible to other sessions immediately.

For example:

/* client O */ set @@autocommit = 0;
/* client X */ set @@autocommit = 0;
/* client O */ create table wh1 (x int primary key);
/* client X */ insert into wh1 values (0); -- succeeds
/* client O */ select * from wh1; -- empty
/* client X */ alter table wh1 add index (x); -- also visible to client O
/* client X */ insert into wh1 values (1);
/* client X */ rollback;
/* client X */ select * from wh1; -- (0) row still in the working set

[max-hoffman]

Because only DDL operations and not data bleed through, it is maybe possible to merge empty tables into the working roots for active sessions on the same branch. We would have to think through the failure cases for multiple threads trying to DDL the same table name concurrently.

This impacts TPC-C setup, but is maybe a test setup problem more than a practical problem for most customers.