Shulou(Shulou.com)06/01 Report--

The varchar field modified by MySQL5.7 online is not in the lock table. The test process is as follows:

Click (here) to collapse or open

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.18-log |

+-+

Click (here) to collapse or open

Mysql > select count (*) from T_USER_INFO

+-+

| | count (*) |

+-+

| | 7147528 |

+-+ Open two command line windows:

Window one:

Click (here) to collapse or open

Mysql > alter table T_USER_INFO modify column USER_NAME varchar (100); window 2:

Click (here) to collapse or open

Mysql > delete from T_USER_INFO where user_name='rz19930119'; executes the first window command first, then the second window command, the second delete statement finishes quickly, and the first modification statement executes for 6 minutes.

Summary: for versions above MySQL5.7, online DDL will not lock the table.

Principle:

In versions above MySQL5.7, the DML operation log is written to a cache while the create or delete operation is performed. Wait for the modification to be completed and then return to the original table to maintain the consistency of the data. This cache size is controlled by innodb_online_alter_log_max_size, and the default is 128MB. If users change tables frequently and the online DML business is under great pressure, the innodb_online_alter_log_max_size space cannot store logs and an error will be thrown. In this case, you can increase innodb_online_alter_log_max_size to get more log cache space to solve the problem.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.