Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS
+1 Vote Up -0 Vote Down

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

  • 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.
  • 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.

    Votes:

    You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

    Planet MySQL © 1995, 2013, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.