mysql拦截 mysql 阻塞

MYSQL 服务器逻辑架构图

连接/线程处理 == 》 （解析器 –> 查询缓存） ===》 优化器 ===》 存储引擎

服务器级别锁

MYSQL 使用的锁类型：
表锁（显式:LOCK TABLE，隐式）
全局锁（read_only=1,FLUSH TABLE WITH READ LOCK）
命名锁 ：服务器重命名或者删除一个表时创建。重命名可能会影响到触发器相关的表
字符锁

LOCK TABLES film READ;
LOCK TABLES film WRITE;

查看那些线程持有并阻塞查询的表锁。 SHOW PROCESSLIST;
state
LOCKED 表锁
WAITING for release of readlock 全局锁
Wating for table 命名锁
user Lock 用户锁 ：SELECT GET_LOCK(‘my lock’, 100);

存储引擎中的锁

InnoDB中的锁

• 查看InnoDb锁信息————-（5.1以下）SHOW INNODB STATUS; 更高 版本show engine innodb status\G;
• 查看INFROMATION_SCHEMA表中 INNODB_LOCKS表，5.5版本以上默认安装，5.1中可能会没有

INFROMATION_SCHEMA来查询mysql使用情况

• 使用information_schema 来查询哪条sql阻塞，谁在等待方法 转自

• SELECT 
 p2.HOSTBlockedhost, 
 p2.USERBlockedUser, 
 r.trx_id BlockedTrxId, 
 r.trx_mysql_thread_id BlockedThreadId, 
 TIMESTAMPDIFF( 
 SECOND, 
 r.trx_wait_started, 
 CURRENT_TIMESTAMP 
 ) WaitTime, 
 r.trx_query BlockedQuery, 
 l.lock_table BlockedTable, 
 m.lock_modeBlockedLockMode, 
 m.lock_typeBlockedLockType, 
 m.lock_indexBlockedLockIndex, 
 m.lock_spaceBlockedLockSpace, 
 m.lock_page BlockedLockPage, 
 m.lock_rec BlockedLockRec, 
 m.lock_data BlockedLockData, 
 p.HOSTblocking_host, 
 p.USERblocking_user, 
 b.trx_id BlockingTrxid, 
 b.trx_mysql_thread_id BlockingThreadId, 
 b.trx_query BlockingQuery, 
 l.lock_modeBlockingLockMode, 
 l.lock_typeBlockingLockType, 
 l.lock_indexBlockingLockIndex, 
 l.lock_spaceBlockingLockSpace, 
 l.lock_page BlockingLockPage, 
 l.lock_rec BlockingLockRec, 
 l.lock_data BlockingLockData, 
 IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx 
 FROM 
 information_schema.INNODB_LOCK_WAITS w 
 INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id 
 INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id 
 INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.lock_trx_id=b.trx_id 
 INNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id=w.requested_lock_idAND m.lock_trx_id=r.trx_id 
 INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id 
 INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id 
 ORDER BY 
 WaitTime DESC \G;

• 
*************************** 1. row *************************** 
 被阻塞方host: 10.192.203.9:54401 
 被阻塞方用户: root 
 被阻塞方事务id: 2844 
 被阻塞方线程号: 312 
 等待时间: 6 
 被阻塞的查询: update eip.t_offer_instanceset description='jiao' where id = 431 
 阻塞方锁住的表:eip.t_offer_instance 
 被阻塞方的锁模式: X 
 被阻塞方的锁类型(表锁还是行锁): RECORD 
 被阻塞方锁住的索引:PRIMARY 
 被阻塞方锁对象的space_id: 62 
 被阻塞方事务锁定页的数量: 7 
 被阻塞方事务锁定行的数量: 116 
 被阻塞方事务锁定记录的主键值: 431 
 阻塞方主机: 10.192.203.9:54392 
 阻塞方用户: root 
 阻塞方事务id: 2841 
 阻塞方线程号: 310 
 阻塞方查询: NULL 
 阻塞方的锁模式: X 
 阻塞方的锁类型(表锁还是行锁): RECORD 
 阻塞方锁住的索引:PRIMARY 
 阻塞方锁对象的space_id: 62 
 阻塞方事务锁定页的数量: 7 
 阻塞方事务锁定行的数量: 116 
 阻塞方事务锁定记录的主键值: 431 
 阻塞方事务空闲的时间: 19 秒

复制这个代码执行的时候报错：
[Err] 3167 - The ‘INFORMATION_SCHEMA.GLOBAL_STATUS’ feature is disabled; see the documentation for ‘show_compatibility_56’
5.7提供了show_compatibility_56参数，设置为ON可以兼容5.7之前的用法，否则就会报上面的错。

执行   set global show_compatibility_56=on; 
      show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| show_compatibility_56 | ON    |
+-----------------------+-------+

再次执行不会报错了~~ ( : 嘿嘿~

1分钟内产生binlog大小计算

select @a1:=VARIABLE_VALUE as a1 
 from information_schema.GLOBAL_STATUS 
 where VARIABLE_NAME=’innodb_os_log_written’ 
 union all 
 select sleep(60) 
 union all 
 select @a2:=VARIABLE_VALUE as a2 
 from information_schema.GLOBAL_STATUS 
 where VARIABLE_NAME=’innodb_os_log_written’;select round((@a2-@a1)/1024/1024/@@innodb_log_files_in_group) as MB;

查看某段时间以来未关闭事务：

select 
 trx_id, 
 trx_started, 
 trx_mysql_thread_id 
 from 
 information_schema.innodb_trx 
 where 
 trx_started < date_sub(now(), interval 1 minute) 
 and trx_operation_state is null 
 and trx_query is null;

查看事务等待状况：

select 
 r.trx_id waiting_trx_id, 
 r.trx_mysql_thread_id waiting_thread, 
 r.trx_query waiting_query, 
 b.trx_id blocking_trx_id, 
 b.trx_mysql_thread_id blocking_thread, 
 b.trx_query blocking_query 
 from 
 information_schema.innodb_lock_waits w 
 inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id 
 inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;

查看更具体的事务等待状况：

select 
 b.trx_state, 
 e.state, 
 e.time, 
 d.state as block_state, 
 d.time as block_time, 
 a.requesting_trx_id, 
 a.requested_lock_id, 
 b.trx_query, 
 b.trx_mysql_thread_id, 
 a.blocking_trx_id, 
 a.blocking_lock_id, 
 c.trx_query as block_trx_query, 
 c.trx_mysql_thread_id as block_trx_mysql_tread_id 
 from 
 information_schema.innodb_lock_waits a 
 left join information_schema.innodb_trx b on a.requesting_trx_id = b.trx_id 
 left join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_id 
 left join information_schema.processlist d on c.trx_mysql_thread_id = d.id 
 left join information_schema.processlist e on b.trx_mysql_thread_id = e.id 
 order by 
 a.requesting_trx_id;

查看未关闭的事务：

–mysql 5.6 
 select 
 a.trx_id, 
 a.trx_state, 
 a.trx_started, 
 a.trx_query, 
 b.id, 
 b.user, 
 b.db, 
 b.command, 
 b.time, 
 b.state, 
 b.info, 
 c.processlist_user, 
 c.processlist_host, 
 c.processlist_db, 
 d.sql_text 
 from 
 information_schema.innodb_trx a 
 left join information_schema.processlist b on a.trx_mysql_thread_id = b.id 
 and b.command = ‘sleep’ 
 left join performance_schema.threads c on b.id = c.processlist_id 
 left join performance_schema.events_statements_current d on d.thread_id = c.thread_id;–mysql 5.5(别人写的，我在5.7里面测了也可以用) 
 select 
 a.trx_id, 
 a.trx_state, 
 a.trx_started, 
 a.trx_query, 
 b.id, 
 b. user, 
 b. host, 
 b.db, 
 b.command, 
 b.time, 
 b.state, 
 b.info 
 from 
 information_schema.innodb_trx a 
 left join information_schema.processlist b on a.trx_mysql_thread_id = b.id 
 where 
 b.command = ‘sleep’;