监控锁状态
1.查看有没有锁等待
mysql> 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 | 一共发生过多少锁等待
+-------------------------------+-------+
5 rows in set (0.00 sec)
2.查看那个事务在等待（被阻塞了）
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）
3查看锁源，谁锁的
select * from sys.innodb_lock_waits 被锁的和锁定它的之间关系
locaked_table:那张表出现的等待
locaked_type: 锁的类型（recordlock,gaplock,nextlock）
waiting_trx_id :等待的事务（与上个视图trx_id对应）
waiting_pid: 等待的线程号（与上个视图trx_mysql——thread_id）
waiting_lock_mode,等待锁的类型（x，s）
waiting_query:事务语句
blocking_trx_id: 锁源的事务ID
blocking_pid ：锁源的线程号
sql_kill_blocking_connection 处理建议
4.根据锁源的pid，找到锁源SQL的线程ID
select * from performance_schema.threads where processlist_id=1691;
5.根据锁源SQL线程ID，找到锁源的SQL语句
select * from performance_schema.`events_statements_current` where thread_id=1716;
死锁监控
show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
