3.5 应用层面优化 锁的监控及处理 概念： Recored Lock 行级锁 Next Lock 下键锁，比如你要修改大于3的值，大于3的值都会被锁定 GAP Lock 间隙锁，比如你要修改大于3的值，它就会锁定，3到5之间的值，没有也会被锁定。 t1---表 1 3 5 7 9 Next Lock GAP Lock 只会锁索引的列 IS----->意向共享锁，表级别，事务在请求S锁前，要先获得IS锁 IX----->异向排他锁，表级别。事务在请求X锁前，要先获得IX锁 功能，便于mysql 的锁管理器，去判断这个表上有没有相应的锁，如果有的话，就等会再来看。 S-----> 共享锁，加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁 在普通的select 语句末尾加lock in share mode，才会触发，比如：select * from city where xxx lock in shard mode; 不会去阻塞，别人还是可以继续查询。 X------>排他锁，加了X锁的记录，不允许其他事务再加S锁或者X锁 select * from city where xxx for update; 也会导致X锁 +++++++++++ IS S IX X 锁，都是Mysql自动维护的。监控锁问题的步骤： ## 1、监控有没有锁等待 *****
show status like 'innodb_row_lock%'; ***** ### 查看是否有锁等待
##2、查看哪个事务在等待（被阻塞了） select * from information_schema.innodb_trx where trx_state='lock wait';
select trx_id,trx_state,trx_mysql_thread_id,trx_query 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：当前被阻塞的操作（一般是要丢给开发的） ##3、查看锁源，谁锁的我！ ****** select * from sys.innodb_lock_waits; 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;
locked_table： 产生锁等待的表 locked_type： 锁的类型（record lock,gap locak,next lock） waiting_trx_id： 等待的事务ID（与上个视图trx_id对应） waiting_pid： 等待的线程ID（与上个视图trx_mysql_thread_id对应） waiting_query： 等待的事务语句 waiting_lock_mode：等待锁的类型（X、S） blocking_trx_id： 锁源的事务ID blocking_pid： 锁源的事务连接线程ID（应用发起SQL语句到达连接层，连接层会分配一个专门的连接线程，连接线程的作用是，接收语句和返回结果。） sql_kill_blocking_connection #处理建议 ## 4、根据锁源的pid，找到锁源SQL的线程ID（SQL层接收到连接层提交给过的SQL语句，也会分配一个专门的线程，用来处理SQL语句，这个线程ID就是 sql thread id） ***** 通过锁源的pid (blocking_pid)找到执行的SQL 的 thread_id select thread_id,name,processlist_id from performance_schema.threads where processlist_id=9;
##5、根据锁源SQL线程ID，找到锁源的SQL语句 select thread_id,event_name,sql_text from performance_schema.events_statements_current where thread_id=35;
++++++++++++++++ 优化项目：锁的监控及处理 (1)、背景： 硬件环境：Dell R720 E系列16核，48G 内存，SAS 900G * 6 ，RAID10 在例行巡检时，以现9----11点，时间段的CPU压力非常高（80----90%） (2)、项目的职责： 通过top详细排查，发现mysqld进程占比达到了700---800% 其中有大量的CPU是被用作SYS 和 WAIT，us 处于正常 怀疑是Mysql锁 或者 SQL语句出了问题 经过排查slowlog及锁等待情况，发现有大量锁等待及海量慢语句 pt-query-diagest 查看慢日志 锁等待有没有？ 情况一： 有100多个currnet_waits，说明有很多锁等待情况 情况二： 1000多个lock_waits，说明历史上发生过的锁等待很多 查看那个事务在等待（被阻塞了） 查看锁源事务信息（谁锁的我） 找到锁源的thread_id 找到锁源的SQL语句 (3)、找到语句之后，和应用开发人员进行协商 开发人员描述，此语句是事务挂起导致，我们提出建议是临时kill 会话，最终解决问题。 开发人员查看后，发现是业务逻辑问题导致的死锁，产生了大量锁等待，临时解决方案，将阻塞事务的会话kill掉，最终解决方案，修改代码中的业务逻辑 项目结果： 经过排查处理，锁等待的个数减少80%，解决了CPU持续峰值的问题。
死锁监控： 临时监控： show engine innodb status\G show variables like '%deadlock%'; 记录到日志： vim /etc/my.cnf innodb_print_all_deadlocks = 1 ##会输入到error_log里面 8、主从优化： ## 5.7 从库多线程MTS 基本要求： 5.7以上的版本 必须开启GTID binlog必须是row模式 ##gtid模式 gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON ##专门开启slave端的SQL线程的一个并发的 slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=8 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON ##开启relay_log的恢复功能 5.7： slave-parallerel-type=LOGICAL_CLOCK slave-parallel-workers=8 cpu 核心数作为标准，设置cpu核心数的50%