MySQL5.5 primary key lock reading

Background of MySQL5.5 primary key lock reading problems some people have discussed that when MySQL 5.5 adds a primary key to a large table, the read will be locked. It is suspected that it is related to fast index creation. Here is a brief description. To illustrate the cause of this problem, you can make a comparison experiment. 1) when creating a primary key for an InnoDB table, the read data on the table will be locked. 2) but when the same table is executed to delete the primary key, read operations on the table will not be locked-This indicates it has nothing to do with whether or not fast index creation, because the two operations are similar at the data level. In fact, data must be copied when you create or delete a primary key. 3) during the creation of the primary key, the show create table ---- run on the table will be locked. The phenomenon 1 and 3 can be guessed, which is actually related to meta data lock. About meta data lock (MDL) MySQL 5.5 introduces MDL. When you need to access or modify the table structure, You need to lock (read or write) meta data ). For example, when a thread needs to modify any part of the table structure, access to the table structure needs to be blocked. Of course, access to data rows needs to be blocked. When a table is added with a primary key, the general process is as follows: 1) MDL and write lock; 2) data operations are the most time-consuming part. copy data, therefore, the process is a) create A temporary table A, Table A is defined as the modified table structure B) read data from the original table and insert data into Table a c) delete the original table, rename table a to the original table name. 3) the MDL release write lock is protected by an X lock in the most time-consuming part. Therefore, show create table or select data will be blocked during this period. This explains 1) 3) above ). Delete primary key flow 1) MDL read lock 2) operation data, most time-consuming part a) create A temporary table A, Table a is defined as the modified table structure B) read data from the original table insert Table A 3) MDL upgrade the write lock to read lock c) delete the original table and rename table a to the original table name 4) MDL releases the write lock, which is the most time-consuming part of the data operation. It is added with the MDL read lock, which does not affect the access to the table structure or data of the original table (of course, it cannot be updated ). The last update to the write Lock operation is to rename the table, and the blocking time is very short. Conclusion 1) Obviously, the second process is more reasonable. 2) this can be considered as a possible improvement point for MySQL and has been improved under 5.6. 3) this issue has nothing to do with whether to execute DDL in copy data or inplace mode. In fact, because of InnoDB's clustered index structure, adding or deleting primary keys must all copy data.