EmergencyEMERGENCY? Get 24/7 Help Now!

Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS

 | July 1, 2012 |  Posted In: Insight for DBAs, MySQL


When executing an ALTER TABLE, InnoDB (and XtraDB) will create two InnoDB transactions:

  1. One transaction is created when the table being ALTERed is locked by the server.
    This will show up as something like “TABLE LOCK table schema.table_name trx id XXXX lock mode S” in SHOW ENGINE INNODB STATUS.
  2. Another is created when adding or dropping an index to perform operations on the InnoDB data dictionary.

A little known fact is that the InnoDB data dictionary is somewhat transactional (the big thing it’s missing is any form of MVCC. It’s not ACID). The largest part of ALTER TABLE not being completely crash safe in MySQL is the MySQL server manipulating FRM files.

The only MySQL storage engine I’m aware of having fully crash safe DDL is MySQL Cluster (NDB).

Proving that MEMORY is/isn’t crash safe around DDL is an exercise left to the reader 🙂

Thanks go to Alexey Kopytov for looking into the InnoDB code for this post.

Stewart Smith

Stewart Smith has a deep background in database internals including MySQL, MySQL Cluster, Drizzle, InnoDB and HailDB. he is also one of the founding core developers of the Drizzle database server. He served at Percona from 2011-2014. He is a former Percona employee.


Leave a Reply


Percona’s widely read Percona Database Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.