mysql事务执行中阻塞_Mysql 查询阻塞和事物情况

最新推荐文章于 2024-06-08 16:00:53 发布

嵇海龙

最新推荐文章于 2024-06-08 16:00:53 发布

阅读量431

点赞数

文章标签： mysql事务执行中阻塞

本文链接：https://blog.csdn.net/weixin_30298279/article/details/113257288

版权

本文详细介绍了MySQL服务器的逻辑架构和锁机制，包括表锁、全局锁、命名锁、用户锁以及存储引擎中的InnoDB锁。重点讨论了如何查看和解决事务执行中出现的阻塞问题，如通过`SHOW PROCESSLIST`和`INNODB_LOCKS`等信息来识别和分析阻塞情况。同时提供了查询SQL阻塞和等待的示例，帮助用户理解和排查事务阻塞问题。

摘要由CSDN通过智能技术生成

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阻塞，谁在等待方法转自 http://blog.csdn.net/yabingshi_tech/article/details/46562985

SELECTp2.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_trxFROMinformation_schema.INNODB_LOCK_WAITS wINNER JOIN information_schema.INNODB_TRX b ON b.trx_id =w.blocking_trx_idINNER JOIN information_schema.INNODB_TRX r ON r.trx_id =w.requesting_trx_idINNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.lock_trx_id=b.trx_idINNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id=w.requested_lock_idAND m.lock_trx_id=r.trx_idINNER JOIN information_schema. PROCESSLIST p ON p.ID =b.trx_mysql_thread_idINNER JOIN information_schema. PROCESSLIST p2 ON p2.ID =r.trx_mysql_thread_idORDER BYWaitTimeDESC\G;--------------------------------------------------------------------------------------------

SELECTp2.`HOST` 被阻塞方host,

p2.`USER` 被阻塞方用户,

r.trx_id 被阻塞方事务id,

r.trx_mysql_thread_id 被阻塞方线程号,

TIMESTAMPDIFF(

SECOND,

r.trx_wait_started,CURRENT_TIMESTAMP) 等待时间,

r.trx_query 被阻塞的查询,

l.lock_table 阻塞方锁住的表,

m.`lock_mode` 被阻塞方的锁模式,

m.`lock_type` "被阻塞方的锁类型(表锁还是行锁)",

m.`lock_index` 被阻塞方锁住的索引,

m.`lock_space` 被阻塞方锁对象的space_id,

m.lock_page 被阻塞方事务锁定页的数量,

m.lock_rec 被阻塞方事务锁定行的数量,

m.lock_data 被阻塞方事务锁定记录的主键值,

p.`HOST` 阻塞方主机,

p.`USER` 阻塞方用户,

b.trx_id 阻塞方事务id,

b.trx_mysql_thread_id 阻塞方线程号,

b.trx_query 阻塞方查询,

l.`lock_mode` 阻塞方的锁模式,

l.`lock_type` "阻塞方的锁类型(表锁还是行锁)",

l.`lock_index` 阻塞方锁住的索引,

l.`lock_space` 阻塞方锁对象的space_id,

l.lock_page 阻塞方事务锁定页的数量,

l.lock_rec 阻塞方事务锁定行的数量,

l.lock_data 阻塞方事务锁定记录的主键值,IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,'秒'), 0) 阻塞方事务空闲的时间FROMinformation_schema.INNODB_LOCK_WAITS wINNER JOIN information_schema.INNODB_TRX b ON b.trx_id =w.blocking_trx_idINNER JOIN information_schema.INNODB_TRX r ON r.trx_id =w.requesting_trx_idINNER 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_id` AND m.`lock_trx_id`=r.`trx_id`INNER JOIN information_schema. PROCESSLIST p ON p.ID =b.trx_mysql_thread_idINNER JOIN information_schema. PROCESSLIST p2 ON p2.ID =r.trx_mysql_thread_idORDER BY等待时间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 variableslike '%show_compatibility_56%';+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| show_compatibility_56 | ON |

+-----------------------+-------+

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

1分钟内产生binlog大小计算

select @a1:=VARIABLE_VALUE asa1frominformation_schema.GLOBAL_STATUSwhere VARIABLE_NAME=’innodb_os_log_written’union all

select sleep(60)union all

select @a2:=VARIABLE_VALUE asa2frominformation_schema.GLOBAL_STATUSwhere VARIABLE_NAME=’innodb_os_log_written’;select round((@a2-@a1)/1024/1024/@@innodb_log_files_in_group) as MB;

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

selecttrx_id,

trx_started,

trx_mysql_thread_idfrominformation_schema.innodb_trxwheretrx_started< date_sub(now(), interval 1minute)and trx_operation_state is null

and trx_query is null;

查看事务等待状况：

selectr.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_queryfrominformation_schema.innodb_lock_waits winner join information_schema.innodb_trx b on b.trx_id =w.blocking_trx_idinner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;

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

selectb.trx_state,

e.state,

e.time,

d.stateasblock_state,

d.timeasblock_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_queryasblock_trx_query,

c.trx_mysql_thread_idasblock_trx_mysql_tread_idfrominformation_schema.innodb_lock_waits aleft join information_schema.innodb_trx b on a.requesting_trx_id =b.trx_idleft join information_schema.innodb_trx c on a.blocking_trx_id =c.trx_idleft join information_schema.processlist d on c.trx_mysql_thread_id =d.idleft join information_schema.processlist e on b.trx_mysql_thread_id =e.idorder bya.requesting_trx_id;

查看未关闭的事务：

–mysql 5.6

selecta.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_textfrominformation_schema.innodb_trx aleft join information_schema.processlist b on a.trx_mysql_thread_id =b.idand b.command =‘sleep’left join performance_schema.threads c on b.id =c.processlist_idleft join performance_schema.events_statements_current d on d.thread_id = c.thread_id;

–mysql 5.5(别人写的，我在5.7里面测了也可以用)