锁的监控及处理

锁等待模拟

  • Record Lock : 记录锁
  • Next Lock : 下键锁
  • GAP Lock : 间隙锁

MySQL InnoDB一共有四种锁:共享锁(读锁,S锁),排他锁(写锁,X锁),意向共享锁(IS锁)和意向排他锁(IX锁).其中共享锁与排他锁属于行级锁,另外两个意向锁属于表级锁.

  • 共享锁(读锁,S锁):若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放S锁
  • 排他锁(写锁,X锁):若事务T对数据对象A加上X锁,则只允许T读取和修改A,其他事务不能再对A加作何类型的锁,直到T释放A上的X锁
  • 意向共享锁(IS锁):事务T在对表中数据对象加S锁前,首先需要对该表加IS(或更强的IX)锁
  • 意向排他锁(IX锁):事务T在对表中的数据对象加X锁前,首先需要对该表加IX锁

比如SELECT ... FROM T1 LOCK IN SHARE MODE语句,首先会对表T1加IS锁,成功加上IS锁后才会对数据加S锁,同样SELECT ... FROM T1 FOR UPDATE语句,首先会对表T1加IX锁,成功加上IX锁后才会对数据加X锁

MySQL InnoDB 锁兼容阵列

  X IX S IS
X ✗ ✗ ✗ ✗
IX ✗ ✓ ✗ ✓
S ✗ ✗ ✓ ✓
IS ✗ ✓ ✓ ✓

左侧窗口开启事务,然后执行update语句,右侧窗口开启事务也执行update语句,然后右侧窗口一定时间后因为左侧的锁而报错

锁等待.jpg

监控锁等待

16956686-9e1bcd370862c1fa.png

可以使用使用show status like 'innodb_row_lock%';查询相关锁的信息,关注以下参数

  • Innodb_row_lock_current_waits : 当前有多少锁等待
  • Innodb_row_lock_waits : 一共发生过多少锁等待
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1     |
| Innodb_row_lock_time          | 51012 |
| Innodb_row_lock_time_avg      | 25506 |
| Innodb_row_lock_time_max      | 51012 |
| Innodb_row_lock_waits         | 2     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

查看等待的事务

使用命令查看等待的事务

  • trx_id : 事务ID号
  • trx_state : 当前事务的状态
  • trx_mysql_thread_id : 连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id)
  • trx_query : 当前被阻塞的操作(一般是要丢给开发的)
SELECT trx_id,trx_state,trx_mysql_thread_id,trx_query FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';

QQ截图20200428104957.jpg

查看锁源,谁锁的我

查看被锁的和锁定它的之间关系

  • locked_table : 产生锁等待的表
  • locked_type : 锁的类型(recordlock,gaplock,nextlock)
  • waiting_trx_id : 等待的事务的ID
  • waiting_pid : 等待事务的连接线程ID
  • waiting_query : 等待事务语句
  • waiting_lock_mode : 等待锁的类型(X,S)
  • blocking_trx_id : 锁源的事务ID
  • blocking_pid : 锁源的事务连接线程ID
  • sql_kill_blocking_connection : 处理建议
SELECT locked_table,locked_type,waiting_trx_id,waiting_pid,waiting_query,waiting_lock_mode,blocking_trx_id,blocking_pid,sql_kill_blocking_connection FROM sys.innodb_lock_waits;

QQ截图20200428110926.jpg

根据上一步的blocking_pid,查找锁源SQL的线程ID

SELECT THREAD_ID,NAME,PROCESSLIST_ID FROM performance_schema.threads WHERE processlist_id=2;
  • THREAD_ID是真正执行sql的线程ID

QQ截图20200428112040.jpg

找到锁源的SQL语句

SELECT THREAD_ID,EVENT_NAME,SQL_TEXT FROM performance_schema.`events_statements_current` WHERE thread_id=27;
  • 将表信息和锁源SQL交给开发处理

QQ截图20200428112704.jpg

优化项目:锁的监控及处理

在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)

  • 硬件环境: DELL R720,E系列16核,48G MEM,SAS900G6,RAID10

排查过程

  • 通过top详细排查,发现mysqld进程占比达到了700-800%
  • 其中有量的CPU是被用作的SYS和WAIT,us处于正常
  • 怀疑是MySQL锁或者SQL语句出了问题
  • 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句
  • pt-query-diagest 查看慢日志

排查结果

  • 有100多个current_waits,说明当前很多锁等待情况
  • 1000多个lock_waits,说明历史上发生过的锁等待很多

查看锁源情况

  • 查看那个事务在等待(被阻塞了)
  • 查看锁源事务信息(谁锁的我)
  • 找到锁源的thread_id
  • 找到锁源的SQL语句,并向应用开发人员进行协商

协商解决过程

  • 向开发人员描述,此语句是事务挂起导致,我们提出建议是临时kill会话,最终解决问题
  • 开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待.临时解决方案,将阻塞事务的会话kill掉.最终解决方案,修改代码中的业务逻辑

最终结果

  • 经过排查处理,锁等待的个数减少80%.解决了CPU持续峰值的问题

锁监控涉及到的命令

show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;

死锁监控

死锁.jpg

#通过命令查看死锁情况(只会记录最后一次死锁)

show variables like '%deadlock%';

配置文件设置记录死锁

#查看死锁相关参数
show variables like '%deadlock%';

#开启错误日志中记录死锁
vim /etc/my.cnf 

innodb_print_all_deadlocks = 1