innodb 锁问题监控处理

监控锁等待

show status like 'innodb_row_lock%';
--关注点：
Innodb_row_lock_current_waits 当前有多少锁等待
Innodb_row_lock_waits         系统启动以来一共发生过多少锁等待

查找被阻塞的事务

select * from information_schema.innodb_trx where trx_state='lock wait';

查看锁源

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        锁的类型（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    处理建议

根据锁源的pid，找到锁源SQL的线程id

select * from performance_schema.threads where processlist_id=1691;

根据锁源sql线程id，找到锁源的sql语句

select * from performance_schema.`events_statements_current` where thread_id=17176;

死锁的监控

show variables like '%deadlock%';
vi /etc/my.cnf
innodb_print_all_deadlocks = 1

主从优化开启从库多线程MTS

必须是5.7以上的版本
必须开启GTID模式
binlog必须时row模式
在my.cnf中加入：
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK    (默认时database库级别，改成时钟，事务级别)
slave-parallel-workers=8    (cpu核心数/2)
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON