mysql怎么查死锁的表 mysql死锁查询

mysql 查看死锁

第一步,查出已锁的进程

创新互联主要从事成都网站设计、网站制作、网页设计、企业做网站、公司建网站等业务。立足成都服务鄂伦春,10年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18980820575

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

``

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

``

INNODB_TRX表主要是包含了正在InnoDB引擎中执行的所有事务的信息,包括waiting for a lock和running的事务

select * from information_schema.innodb_trx

``

第二步,kill进程

show engin innodb status; //最后一次死锁信息及sql

show open tables where in_use 0 //查看锁表

MySQL数据库中查询表是否被锁以及解锁

1.查看表被锁状态

2.查看造成死锁的sql语句

3.查询进程

4.解锁(删除进程)

5.查看正在锁的事物  (8.0以下版本)

6.查看等待锁的事物 (8.0以下版本)

查询mysql 哪些表正在被锁状态

1.查看表是否被锁:

(1)直接在mysql命令行执行:showengineinnodbstatus\G。

(2)查看造成死锁的sql语句,分析索引情况,然后优化sql。

(3)然后showprocesslist,查看造成死锁占用时间长的sql语句。

(4)showstatuslike‘%lock%。

2.查看表被锁状态和结束死锁步骤:

(1)查看表被锁状态:showOPENTABLESwhereIn_use0;这个语句记录当前锁表状态。

(2)查询进程:showprocesslist查询表被锁进程;查询到相应进程killid。

(3)分析锁表的SQL:分析相应SQL,给表加索引,常用字段加索引,表关联字段加索引。

(4)查看正在锁的事物:SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS。

(5)查看等待锁的事物:SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS。

扩展资料

MySQL锁定状态查看命令:

Checkingtable:正在检查数据表(这是自动的)。

Closingtables:正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

ConnectOut:复制从服务器正在连接主服务器。

Copyingtotmptableondisk:由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。

Creatingtmptable:正在创建临时表以存放部分查询结果。

deletingfrommaintable:服务器正在执行多表删除中的第一部分,刚删除第一个表。

deletingfromreferencetables:服务器正在执行多表删除中的第二部分,正在删除其他表的记录。

Flushingtables:正在执行FLUSHTABLES,等待其他线程关闭数据表。

Killed:发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。

Locked:被其他查询锁住了。

Sendingdata:正在处理SELECT查询的记录,同时正在把结果发送给客户端。

Sortingforgroup:正在为GROUPBY做排序。

Sortingfororder:正在为ORDERBY做排序。

Openingtables:这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTERTABLE或LOCKTABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。

Removingduplicates:正在执行一个SELECTDISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。

Reopentable:获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。

Repairbysorting:修复指令正在排序以创建索引。

Repairwithkeycache:修复指令正在利用索引缓存一个一个地创建新索引。它会比Repairbysorting慢些。

Searchingrowsforupdate:正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。

Sleeping:正在等待客户端发送新请求。

Systemlock:正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加--skip-external-locking参数来禁止外部系统锁。

Upgradinglock:INSERTDELAYED正在尝试取得一个锁表以插入新记录。

Updating:正在搜索匹配的记录,并且修改它们。

UserLock:正在等待GET_LOCK()。

Waitingfortables:该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。

waitingforhandlerinsert:INSERTDELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。

mysql 死锁排查

一、show ENGINE INNODB status

查看死锁位置,分析。

二、

首先解决死锁可以从死锁发生的条件入手,最容易解决的就是更改获取资源的顺序;

其次是避免长事务,让事务执行的时间尽可能少,让事务的覆盖范围尽可能小,长事务会导致并发度降低,且会有更多的SQL查 询延迟;

给整个方法加事务是否是必须的?可以不加事务的尽量不加。

怎么查看mysql表是否被锁定

当你开始执行一个 ALTER ,而你遇到了可怕的“元数据锁定等待”,我敢肯定你一定遇见过。我最近遇到了一个案例,其中被更改的表要执行一个很小范围的更新(100行)。ALTER 在负载测试期间一直等待了几个小时。在停止负载测试后,ALTER 按预期在不到一秒的时间内就完成了。那么这里发生了什么?

检查外键

每当有奇数次锁定时,我的第一直觉就是检查外键。当然这张表有一些外键引用了一个更繁忙的表。但是这种行为似乎仍然很奇怪。对表运行 ALTER 时,会针对子表请求一个 SHARED_UPGRADEABLE 元数据锁。还有针对父级的 SHARED_READ_ONLY 元数据锁。

我们来看看如何根据文档获取元数据锁定[1]:

如果给定锁定有多个服务器,则首先满足最高优先级锁定请求,并且与 max_write_lock_count系统变量有关。写锁定请求的优先级高于读取锁定请求。

[1]:

请务必注意锁定顺序是序列化的:语句逐个获取元数据锁,而不是同时获取,并在此过程中执行死锁检测。

通常在考虑队列时考虑先进先出。如果我发出以下三个语句(按此顺序),它们将按以下顺序完成:

1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent

但是当子 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` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`parent_id` int(11) DEFAULT NULL,

`val` varchar(10) 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

) ENGINE=InnoDB;

INSERT INTO `parent` VALUES (1, "one"), (2, "two"), (3, "three"), (4, "four");

Session 1:

start transaction;update parent set val = "four-new" where id = 4;

Session 2:

alter table child add index `idx_new` (val);

Session 3:

start transaction;update parent set val = "three-new" where id = 3;

此时,会话 1 具有打开的事务,并且处于休眠状态,并在父级上授予写入元数据锁定。 会话 2 具有在子级上授予的可升级(写入)锁定,并且正在等待父级的读取锁定。最后会话 3 具有针对父级的授权写入锁定:

mysql select * from performance_schema.metadata_locks;+-------------+-------------+-------------------+---------------+-------------+| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE         | LOCK_DURATION | LOCK_STATUS |+-------------+-------------+-------------------+---------------+-------------+| TABLE       | child       | SHARED_UPGRADABLE | TRANSACTION   | GRANTED     | - ALTER (S2)| TABLE       | parent      | SHARED_WRITE      | TRANSACTION   | GRANTED     | - UPDATE (S1)| TABLE       | parent      | SHARED_WRITE      | TRANSACTION   | GRANTED     | - UPDATE (S3)| TABLE       | parent      | SHARED_READ_ONLY  | STATEMENT     | PENDING     | - ALTER (S2)+-------------+-------------+-------------------+---------------+-------------+

请注意,具有挂起锁定状态的唯一会话是会话 2(ALTER)。会话 1 和会话 3 (分别在 ALTER 之前和之后发布)都被授予了写锁。排序失败的地方是在会话 1 上发生提交的时候。在考虑有序队列时,人们会期望会话 2 获得锁定,事情就会继续进行。但是,由于元数据锁定系统的优先级性质,会话 3 具有锁定,会话 2 仍然等待。

如果另一个写入会话进入并启动新事务并获取针对父表的写锁定,则即使会话 3 完成,ALTER 仍将被阻止。

只要我保持一个对父表打开元数据锁定的活动事务,子表上的 ALTER 将永远不会完成。更糟糕的是,由于子表上的写锁定成功(但是完整语句正在等待获取父读锁定),所以针对子表的所有传入读取请求都将被阻止!

另外,请考虑一下您通常如何对无法完成的语句进行故障排除。您查看已经打开较长时间的事务(在进程列表和 InnoDB 状态中)。但由于阻塞线程现在比 ALTER 线程更年轻,因此您将看到的最旧的事务/线程是 ALTER 。

这正是这种情况下发生的情况。在准备发布时,我们的客户端正在运行 ALTER 语句并结合负载测试(一种非常好的做法!)以确保顺利发布。问题是负载测试保持对父表打开一个活动的写事务。这并不是说它只是一直在写,而是有多个线程,一个总是活跃的。 这阻止了 ALTER 完成并阻止对相对静态的子表的随后的读请求。

幸运的是,这个问题有一个解决方案(除了从设计模式中驱逐外键)。变量 max_write_lock_count[2] 可用于允许在写入锁定之后在读取锁定之前授予读取锁定连续写锁。默认情况下,此变量设置为 18446744073709551615,如果你对该表发出 10,000 次写入/秒,那么你的读将被锁定 5800 万年……


分享标题:mysql怎么查死锁的表 mysql死锁查询
当前路径:http://myzitong.com/article/doceodd.html