MySQL Online DDL (the difference from Oracle)

As for MySQL, it is known that when there are uncommitted activity transactions on a business table, you go to the online DDL, which is quite dangerous and will be stuck directly, and show Processlist shows that the DDL has encountered an MDL lock wait, that is, "waiting for Table metadata Lock ", at this point if you go to drink coffee ... The cup has occurred because the business table even select will be blocked.

MySQL says in 5.6 official documents that it can support most of the online DDL, including common add fields, add indexes, change fields, and so on. Note, however, that the so-called support for online DDL refers to the case of a dead table (there are no uncommitted transactions on the DDL table), that is, when DDL is executed against the dead table in mysql5.6, the mysql5.5 "Copy to TMP table" does not appear, just a hint " Altering table ", the DML on this table will not be blocked like mysql5.5 during DDL execution.

But the busy business table of the production system is definitely a live table (there are uncommitted transactions on the table all the time), so the online DDL on it is another topic, which is not explained in detail here.

But one thing, MySQL does not do well with Oracle, MySQL some dangerous SQL, in the execution of the time, will be directly stuck, no hint, very unfriendly, as follows:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/7E/C1/wKioL1cHlMOSNkXYAAAX6INuuk0612.png "title=" Session2.png "alt=" Wkiol1chlmosnkxyaaax6inuuk0612.png "/>

Oracle, when executing DDL on a table with an active transaction, Oracle reports

"Ora-00054:resourcebusy and acquire with NOWAIT specified", this kind of hint is very friendly.

This article is from "Memory Fragment" blog, declined reprint!

MySQL Online DDL (the difference from Oracle)