mysql lock处理_Mysql的metadata lock处理

--做了一个更改表结构加字段的操作，但是一直挂着不返回：

mysql> alter table tbl_test add remark varchar(8) after col_url;

--开启另一个窗口看进程

mysql> show processlist;

+------+--------+------------------------+-------------+---------+-------+---------------

| Id | User | Host | db | Command | Time | State Info

| 7113 | root | localhost | testdb| Sleep | 5512 | | NULL |

| 7134 | root | localhost | testdb| Sleep | 5513 | | NULL |

| 7141 | root | localhost | NULL | Sleep | 7942 | | NULL |

| 7142 | root | localhost | testdb| Sleep | 1755 | | NULL |

| 7153 | root | localhost | testdb| Sleep | 6950 | | NULL

| 7214 | root | localhost | testdb| Sleep | 4614 | | NULL |

| 7217 | root | localhost | testdb| Sleep | 4232 | | NULL |

| 7219 | root | localhost | testdb| Sleep | 4285 | | NULL |

| 7221 | root | localhost | testdb| Sleep | 4229 | | NULL |

| 7226 | root | localhost | testdb| Sleep | 878 | | NULL |

| 7254 | root | localhost | NULL | Sleep | 2137 | | NULL |

| 7256 | root | localhost | testdb| Sleep | 1511 | | NULL |

| 7269 | root | localhost | testdb| Query | 129 | Waiting for table metadata lock | alter table tbl_test add remark varchar(8) comment |

| 7271 | root | localhost | testdb| Query | 0 | init | show processlist |

| 7276 | root | localhost | NULL | Sleep | 1000 | | NULL

--有收获，显示是Waiting for table metadata lock，元数据上锁了，但是没有更详细的信息，只是提示表被锁了。既然提示锁，那我们再深入看一下是哪把锁锁住了。mysql的可以通过information_schema.locks、information_schema.innodb_trx和information_schema.innodb_lock_waits来查询。

mysql>SELECT trx_id，trx_state,trx_mysql_thread_id,trx_isolation_level,FROM information_schema.innodb_trx；

trx_id trx_state trx_mysql_thread_id trx_isolation_level

2080971 RUNNING 7134 REPEATABLE READ

--7134在processlist里面，目前只发现了这个事务，innodb_trx是innodb引擎包含了各种内部事务的系统表，目前在list里面是sleep状态的

--手工kill掉进程，生产上kill进程需要和研发的同学确认

mysql> kill 7134;

Query OK, 0 rows affected (0.00 sec)

--然后重新执行

mysql>alter table tbl_test add remark varchar(8) after col_url;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0