MySQL学习笔记锁等待模拟
概念：读的一致性，防止幻读 Record Lock 行级锁 Next Lock 下级锁 GAP Lock间隙锁 X 排查锁 IX意向排查锁 S 共享锁，与读取相关 IS
锁等待演示
## tx1: db03 [oldboy]>select @@autocommit;保证自动提交关闭 +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ USE oldboy UPDATE t_100w SET k1='av' WHERE id=10; ## tx2: USE oldboy UPDATE t_100w SET k1='az' WHERE id=10;
监控锁状态
## 1. 看有没有锁等待 SHOW STATUS LIKE 'innodb_row_lock%'; ## 2. 查看哪个事务在等待(被阻塞了) USE information_schema SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT'; trx_id : 事务ID号 trx_state : 当前事务的状态 trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id ) trx_query : 当前被阻塞的操作(一般是要丢给开发的)
查看锁源,谁锁的我!
SELECT * FROM sys.innodb_lock_waits; ## ====>被锁的和锁定它的之间关系 locked_table : 哪张表出现的等待 waiting_trx_id: 等待的事务(与上个视图trx_id 对应) waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id) blocking_trx_id : 锁源的事务ID blocking_pid : 锁源的连接线程号（processlist_id）
找到锁源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=15; ====> 41
找到锁源的SQL语句
-- 当前在执行的语句 SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41; -- 执行语句的历史 SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41; 得出结果,丢给开发 表信息 被阻塞的 锁源SQL 练习: 一键获得以上信息,请写出具体的SQL语句
优化项目:锁的监控及处理
1. 背景
硬件环境: DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10 在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)
2. 项目的职责
1 通过top详细排查,发现mysqld进程占比达到了700-800% 2 其中有量的CPU是被用作的SYS和WAIT,us处于正常 3 怀疑是MySQL 锁 或者SQL语句出了问题 4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句 (1) pt-query-diagest 查看慢日志 (2) 锁等待有没有? db03 [(none)]>show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | +-------------------------------+-------+ 情况一: 有100多个current_waits,说明当前很多锁等待情况 情况二: 1000多个lock_waits,说明历史上发生过的锁等待很多 5 查看那个事务在等待(被阻塞了) 6 查看锁源事务信息(谁锁的我) 7 找到锁源的thread_id 8 找到锁源的SQL语句
3. 找到语句之后,和应用开发人员进行协商
(1)开发人员描述,此语句是事务挂起导致 我们提出建议是临时kill 会话,最终解决问题 (2) 开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待 临时解决方案,将阻塞事务的会话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;
死锁监控
show engine innodb status\G show variables like '%deadlock%'; vim /etc/my.cnf innodb_print_all_deadlocks = 1