mysql 锁查看

前提：可以通过top命令查看cpu ：sys wait 高步高

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     | 历史一共产生锁的个数
+-------------------------------+-------+

2. 查找锁阻塞的语句

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select *  from innodb_trx where trx_state='lock wait';
Empty set (0.00 sec)

3. 查找锁源

select *  from  sys.innodb_lock_waits;

4.通过以上可以查找到连接层的id，再通过连接层的id找到锁源sql的id

select * from performance_schema.thread where processlist_id=xxx;

5.通过sql的id 找到sql

select * from performance_schema where event_statement_current where thread_id =xx;
select * from performance_schema where event_statement_history where thread_id =xx

6 死锁查看

show status like '%deadlock%';
可以打开死锁记录参数，记录到错误日志中