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


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.


Share this post

Comments (4)

  • Mikael Ronstrom Reply

    Actually pretty sure also the partitioning storage engine is crash-safe on DDLs as well since it uses a DDL log to either roll back or roll forward changes after a crash in the middle of a DDL.

    July 2, 2012 at 2:53 am
  • Stewart Smith Reply

    That was the other one I was thinking of that could be crash safe… it’s probably more likely to be than any other, but I’m not sure exactly how this interacts with some of the FRM writing code… there could still be a window or two.

    July 3, 2012 at 7:49 pm
  • Mikael Ronstrom Reply

    There are no windows with regard to FRM writing code, this is covered by the log. So no windows should exist there.

    July 4, 2012 at 1:05 am
  • Stewart Smith Reply

    Awesome. We should now chuckle at InnoDB having crash safe DDL only if it’s a partitioned table 🙂

    July 4, 2012 at 5:35 pm

Leave a Reply