锁等待监控

1看看有没有锁等待：show status like 'innodb_row_lock%';

db03 [oldguo]>show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1     | #当前锁等待
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 2     | #历史锁等待
+-------------------------------+-------+
5 rows in set (0.12 sec)

db03 [oldguo]>      

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）

trx_query:当前被阻塞的操作(一般是要丢给开发的)

 3查看锁源，谁锁的我：

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：锁源的线程号

4找到锁源的thread_id：

select * from performance_schema.threads where processlist_id=15;

5找到锁源的sql语句：

当前在执行的语句：

select * from performance_schema.'events_statements_current' where thread_id=41;

执行语句的历史：

select * from performance_schema.'events_statements_history' where thread_id=41;