Mysql死锁、锁超时、慢sql总结

融极

已于 2022-07-19 15:47:05 修改

阅读量4k

点赞数

于 2022-07-19 15:19:07 首次发布

本文链接：https://blog.csdn.net/tianzhonghaoqing/article/details/125871814

版权

数据库专栏收录该内容

63 篇文章 2 订阅

订阅专栏

概述

造成锁的场景

执行DML操作没有commit，在执行删除操作就会锁表。
表索引设计不当，导致数据库出现死锁。
长事务，阻塞DML，继而阻塞所有同表的后续操作。

锁问题定位

如何查询死锁或锁等待信息

SELECT
	a.trx_state,
	a.trx_started,
	now() currentTime,
	b.state,
	b.time,
	b.state AS block_state,
	b.time AS block_time,
	a.trx_id,
	a.trx_requested_lock_id,
	a.trx_query AS block_trx_query,
	a.trx_mysql_thread_id AS block_trx_mysql_tread_id 
FROM
	information_schema.INNODB_TRX a
	LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id;
	
# 锁问题相关的表
SELECT * from information_schema.INNODB_TRX;
select * from information_schema.`PROCESSLIST`;

我们看事务表INNODB_TRX里面是否有正在锁定的事务线程，看看trx_mysql_thread_id是否在information_schema.PROCESSLIST里面的sleep线程中，如果有，那么就证明了这个休眠的线程事务一直没有commit（提交）或者roolback（回滚）而是卡住了。
临时解决方案可以先kill掉；
根本解决方案需要去看代码，产生的原因是什么，根本上解决死锁或锁超时的问题。

锁超时问题（Lock wait timeout exceeded）

Lock wait timeout exceeded（锁等待超时）表示后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间，就会引发这个异常。
事务必须执行完提交后，才会释放事务中获取的锁，如果事务没有执行完，其他事务想获取执行事务中的锁是获取不到的，如果长时间获取不到就会锁等待超时。
超时原因就是因为一个事务处理的时间过长、逻辑过多，比如调外部服务（超时），比如更新多表的操作，这样就会导致后面事务请求超时，报上述错误。

问题现象

日志中有lock wait timeout exceeded错误。
数据更新或新增后数据经常自动回滚；这是因为事务没有提交成功的原因。
表操作总报Lock wait timeout exceeded，并长时间无反应。

配置的超时时间

如果查看具体的值是多少，单位是秒。
show global variables like ‘wait_timeout’;
SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’;
innodb_lock_wait_timeout与lock_wait_timeout是不一样的。

Innodb_lock_wait_timeout：innodb的dml操作的行级锁的等待时间。
lock_wait_timeout：数据结构ddl操作的锁的等待时间。

解决方法

应急方法

show full processlist；kill 掉出现问题的进程。
多次执行show full processlist，发现有些进程一直在执行。
例如：mysql客户端执行：kill 259255;
在这里插入图片描述

根治方法

select * from information_schema.INNODB_TRX，查看是哪些事务占据了表资源。
说起来很简单找到它杀掉它就搞定了，但是实际上并没有想象的这么简单，当问题出现要分析问题的原因，通过原因定位业务代码可能某些地方实现的有问题，从而来避免今后遇到同样的问题。

查询锁等待信息

SELECT trx_id, trx_requested_lock_id, trx_mysql_thread_id, trx_query FROM innodb_trx WHERE trx_state = 'LOCK WAIT';

kill 掉锁等待的线程
trx_mysql_thread_id 即kill掉事务线程 ID。
从上述方法中得到了相关信息，我们可以得到发生锁等待的线程 ID，然后将其 KILL 掉。 KILL 掉发生锁等待的线程。

kill id;

死锁问题（Dead Lock）

两个事务互相等待对方释放相同资源的源，从而造成死循环，就会引发这个异常。
比如事务A和事务B，行锁r1,r2，当事务A开启后获取到r1，准备去拿r2；事务B开启后获取到r2，准备去拿r1，这个时候就好导致死锁。

慢sql查询

使用show processlist 命令，show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。
show processlist 命令主要有两个作用：

查看慢查询的sql是哪个
查看出现锁的sql是哪个
show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。所以使用下面的查询语句可以获得相同的结果：

select * from information_schema.processlist

id：就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。
user：就是指启动这个线程的用户。
HOST：记录了发送请求的客户端的 IP 和端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
DB：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command：是指此刻该线程正在执行的命令。
Time：指线程这个状态持续的时间，单位是秒。
State：线程的状态，和 Command 对应。
Info：一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

Command列主要值

Sleep：正在等待客户端向它发送执行语句（已经建立了connect，涉及到数据库连接池）。
Query：该线程正在执行一个语句。

state列主要值

Locked：被其他查询锁住了。（存在Locked就说明当前读写操作存在被阻塞的情况）

应用场景

找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀。time字段的单位是秒。

select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

select concat('kill ', id, ';') from information_schema.processlist where COMMAND = 'query' and Time > 60*5 order by Time desc;

Innodb_*表的解释

Mysql的InnoDB存储引擎是支持事务的，事务开启后没有主动Commit，导致该资源被长期占用，其他事务在抢占该资源时，因上一个事务的锁而导致抢占失败！因此出现Lock wait timeout exceeded。
下面是几张表是information_schema数据库中的innodb的事务和锁的信息表，理解这些就能很好的定位问题。

Innodb_trx表

trx_id：事务ID。
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事务开始时间。
trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started：事务开始等待的时间。
trx_weight：事务的权重。
trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务当前操作状态。
trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked：当前执行 SQL 的行锁数量。
trx_lock_structs：事务保留的锁数量。
trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified：事务更改的行数。
trx_concurrency_tickets：事务并发票数。
trx_isolation_level：当前事务的隔离级别。
trx_unique_checks：是否打开唯一性检查的标识。
trx_foreign_key_checks：是否打开外键检查的标识。
trx_last_foreign_key_error：最后一次的外键错误信息。
trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

其中比较关键的是trx_state、trx_query、trx_mysql_thread_id这三个字段，通过这3个字段，可以指定事务被那个线程执行，执行状态和正在执行的sql语句。

Innodb_locks表

该表在8.0之后的版本已经不存在，可以在5.0的版本上使用。

lock_id：锁 ID。
lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
lock_table：被锁定的或者包含锁定记录的表的名称。
lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

Innodb_lock_waits表

该表在8.0之后的版本已经不存在，可以在5.0的版本上使用。

requesting_trx_id：请求事务的 ID。
requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id：阻塞事务的 ID。
blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

锁相关配置查询

1、数据结构ddl操作的锁的等待时间。
show global variables like ‘wait_timeout’;
2、DML操作行级锁的等待时间。
SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’;
3、锁相关的配置
SHOW GLOBAL VARIABLES LIKE ‘%lock%’;
4、事务相关配置
SHOW GLOBAL VARIABLES LIKE ‘%tx%’;
5、查询数据库版本
select @@version;
6、查询事务隔离级别
select @@transaction_isolation;
7、查询事务是否自动提交
select @@autocommit;
8、查询表锁状态
select @@innodb_table_locks;

数据库配置修改

Innodb lock wait timeout值修改

参数修改的范围有Session和Global，并且支持动态修改，可以有两种方法修改：

方法1：
通过下面语句修改，单位是秒。

set innodb_lock_wait_timeout=100;
set global innodb_lock_wait_timeout=100;

注意global的修改对当前线程是不生效的，只有建立新的连接才生效。

方法2：
修改参数文件/etc/my.cnf innodb_lock_wait_timeout = 50
Innodb_lock_wait_timeout指的是事务等待获取资源的最长时间，超过这个时间还未分配到资源则会返回应用失败；当锁等待超过设置时间的时候，就会报如下的错误：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。
其参数的时间单位是秒，最小可设置1s（一般不会设置的这么小），最大可以是指1073741824秒，默认按照时这个值是50s（默认参数设置）。