登录|注册|帮助中心|联系我们

导航
首页 综合百科 生活常识 数码科技 明星名人 传统文化 互联网 健康 影视 美食 教育 旅游 汽车 职场 时尚 运动 游戏 家电 地理 房产 金融 节日 服饰 乐器 歌曲 动物 植物
当前位置:首页 > 互联网

mysql脚本怎么写(数据库mysql基本语句)

发布时间:2022年12月31日责任编辑:曾小东标签:mysql数据
概述

很多时候我们在MySQL数据库中会经常出现事务之间阻塞的问题,也就是阻塞lock,oracle的话实际上之前提供的脚本已经可以很直观看出阻塞的问题,那么对于mysql数据库我们应如何快速查找定位问题根源?

之前分享了innotop工具和show engine innodb status都不能很好的解决我们的需求,所以今天主要基于几张事务表来写sql看能不能定位到。

?一、环境准备

数据库:mysql5.7.24 操作系统:centos7.3

1、数据准备

create database t DEFAULT CHARSET utf8 COLLATE utf8_general_ci;use t;create table test_blocking(id int primary key, name varchar(12));insert into test_blocking select 1, \\\'hwb\\\' from dual;insert into test_blocking select 2, \\\'hwb2\\\' from dual;insert into test_blocking select 3, \\\'hwb3\\\' from dual; ???

2、参数设置

为了实验效果,我们先将参数innodb_lock_wait_timeout设置为100,否则很快就会提示ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

show variables like \\\'innodb_lock_wait_timeout\\\';set global innodb_lock_wait_timeout=100 ; ????二、被阻塞环境模拟及定位

1、第一个会话

select connection_id() from dual;set session autocommit=0;select * from test_blocking where id=1 for update; ???

2、第二个会话

在第二个连接会话中执行更新脚本

select connection_id() from dual;update test_blocking set name=\\\'kk\\\' where id=1; ???

3、第三个会话–通过查询information_schema数据库下与事务相关的几个系统表

--查看哪个线程被哪个堵塞,waiting_thread_id代表等待线程,blocking_thread_id代表堵塞线程SELECtr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread_id,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread_id,b.trx_query blocking_query,now( ) - r.TRX_STARTED blocking_time 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_id; --查看源头sqlSELECta.sql_text,c.id,d.trx_started,b.processlist_user,b.processlist_host FROMPERFORMANCE_SCHEMA.events_statements_current aJOIN PERFORMANCE_SCHEMA.threads b ON a.thread_id = b.thread_idJOIN information_schema.PROCESSLIST c ON b.processlist_id = c.idJOIN information_schema.innodb_trx d ON c.id = d.trx_mysql_thread_id WHERec.id = 304192 ORDER BYd.trx_started;

如下截图所示,第一个SQL语句能够查到线程304193被线程304192阻塞了, 被阻塞的SQL语句为“update test_blocking set name=’kk’ where id=1;”, 能够查到被阻塞了多长时间,但是无法查到源头SQL语句。此时就需要第二个SQL语句登场,找到源头语句。

????

ps:附一段查看阻塞线程更多信息的sql

SELECtp2.HOST Blockedhost,p2.USER BlockedUser,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_mode BlockedLockMode,m.lock_type BlockedLockType,m.lock_index BlockedLockIndex,m.lock_space BlockedLockSpace,m.lock_page BlockedLockPage,m.lock_rec BlockedLockRec,m.lock_data BlockedLockData,p.HOST blocking_host,p.USER blocking_user,b.trx_id BlockingTrxid,b.trx_mysql_thread_id BlockingThreadId,b.trx_query BlockingQuery,l.lock_mode BlockingLockMode,l.lock_type BlockingLockType,l.lock_index BlockingLockIndex,l.lock_space BlockingLockSpace,l.lock_page BlockingLockPage,l.lock_rec BlockingLockRec,l.lock_data BlockingLockData,IF( p.COMMAND = \\\'Sleep\\\', CONCAt( p.TIME, \\\' seconds\\\' ), 0 ) idel_in_trx 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_idINNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id = w.requested_lock_id AND 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_id ORDER BYWaitTime DESC G; ????

这里不要太天真的认为第二个SQL语句能够获取所有场景下的阻塞源头SQL语句,实际业务场景,会话可能在执行一个存储过程或复杂的业务,有可能它执行完阻塞源头SQL后,继续在执行其它SQL语句,此时,你抓取的是这个连接会话最后执行的SQL语句。

其它知识推荐

溜溜百科知识网——分享日常生活学习工作各类知识。 垃圾信息处理邮箱 tousu589@163.com
icp备案号 闽ICP备14012035号-2 互联网安全管理备案 不良信息举报平台 Copyright 2023 www.6za.net All Rights Reserved