当你开始执行一个 ALTER ,而你遇到了可怕的"元数据锁定等待",我敢肯定你一定遇见过.我最近遇到了一个案例,其中被更改的表要执行一个很小范围的更新（100行）.ALTER 在负载测试期间一直等待了几个小时.在停止负载测试后,ALTER 按预期在不到一秒的时间内就完成了.那么这里发生了什么?
检查外键
每当有奇数次锁定时,我的第一直觉就是检查外键.当然这张表有一些外键引用了一个更繁忙的表.但是这种行为似乎仍然很奇怪.对表运行 ALTER 时,会针对子表请求一个 SHARED_UPGRADEABLE 元数据锁.还有针对父级的 SHARED_READ_ONLY 元数据锁.
我们来看看如何根据文档获取元数据锁定[1]：
如果给定锁定有多个服务器,则首先满足最高优先级锁定请求,并且与 max_write_lock_count系统变量有关.写锁定请求的优先级高于读取锁定请求.
[1]：
请务必注意锁定顺序是序列化的：语句逐个获取元数据锁,而不是同时获取,并在此过程中执行死锁检测.
通常在考虑队列时考虑先进先出.如果我发出以下三个语句（按此顺序）,它们将按以下顺序完成：
但是当子 ALTER 语句请求对父进行读取锁定时,尽管排序,但两个插入将在 ALTER 之前完成.以下是可以演示此示例的示例场景：
数据初始化：
CREATE TABLE +parent+ (
+id+ int(11) NOT NULL AUTO_INCREMENT,
+val+ varchar(10) DEFAULT NULL,
PRIMARY KEY (+id+)
) ENGINE=InnoDB;
CREATE TABLE +child+ (
+parent_id+ int(11) DEFAULT NULL,
PRIMARY KEY (+id+),
KEY +idx_parent+ (+parent_id+),
CONSTRAINT +fk_parent+ FOREIGN KEY (+parent_id+) REFERENCES +parent+ (+id+) ON DELETE CASCADE ON UPDATE NO ACTION
Session 1:
alter table child add index +idx_new+ (val);
只要我保持一个对父表打开元数据锁定的活动事务,子表上的 ALTER 将永远不会完成.更糟糕的是,由于子表上的写锁定成功（但是完整语句正在等待获取父读锁定）,所以针对子表的所有传入读取请求都将被阻止！
另外,请考虑一下您通常如何对无法完成的语句进行故障排除.您查看已经打开较长时间的事务（在进程列表和 InnoDB 状态中）.但由于阻塞线程现在比 ALTER 线程更年轻,所以呢您将看到的最旧的事务/线程是 ALTER .
第一步,查看行锁使用情况,命令：
show statue like 'innodb_row_lock%';
如下图所示：
第二步,创建数据库表monitor_amount,如下图所示：
第三步,查看innodb的状态,命令：
show innodb status \G;
第四步,向数据库表monitor_amount插入四条记录,如下图所示：
第五步,再次查看innodb状态,如下图所示：
第六步,可以利用删除表命令来停止查看,如下图所示：
①查看表被锁状态
一：检查是否锁表, 查询进程并杀死进程
①.) 查询是否锁表
show open tables where in_use 0;
show processlist;
二：查看在锁事务,杀死事务对应的线程ID
①.) 查看正在锁的事务
select * from information_schema.INNODB_LOCKS;
kill 线程ID
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
其它：
①.) 查看服务器状态
show status like '%lock%';
show variables like '%timeout%';
可直接在mysql命令行执行：show engine innodb status\G;
查看造成死锁的sql语句,分析索引情况,然后优化sql然后show processlist;
show status like '%lock%'
show OPEN TABLES where In_use 0; 这个语句记录当前锁表状态
另外可以打开慢查询日志,linux下打开需在my.cnf的[mysqld]里面加上以下内容：
slow_query_log=TRUE(有些mysql版本是ON)
slow_query_log_file=/usr/local/mysql/slow_query_log.txt
扩展资料：
MySQL锁定状态查看命令
Checking table：正在检查数据表（这是自动的）.?
Closing tables：正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表.这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中.
Connect Out：复制从服务器正在连接主服务器.?
Copying to tmp table on disk：由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存.
Creating tmp table：正在创建临时表以存放部分查询结果.
deleting from main table：服务器正在执行多表删除中的第一部分,刚删除第一个表.
以上就是土嘎嘎小编为大家整理的mysql怎么知道被锁相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！