飘扬的红领巾
MySQL在并发场景下的问题及解决思路
2018-01-15 08:29 by 飘扬的红领巾, 4974 阅读, 13 评论, 收藏, 编辑目录
1、背景
2、表锁导致的慢查询的问题
3、线上修改表结构有哪些风险?
4、一个死锁问题的分析
5、锁等待问题的分析
6、小结
1、背景
对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标,既要满足大量并发访问的需求又必须保证在此条件下数据的安全,为了满足这一目标大多数数据库通过锁和事务机制来实现,MySQL数据库也不例外。尽管如此我们仍然会在业务开发过程中遇到各种各样的疑难问题,本文将以案例的方式演示常见的并发问题并分析解决思路。
2、表锁导致的慢查询的问题
首先我们看一个简单案例,根据ID查询一条用户信息:
mysql> select * from user where id=6;
这个表的记录总数为3条,但却执行了13秒。
出现这种问题我们首先想到的是看看当前MySQL进程状态:
从进程上可以看出select语句是在等待一个表锁,那么这个表锁又是什么查询产生的呢?这个结果中并没有显示直接的关联关系,但我们可以推测多半是那条update语句产生的(因为进程中没有其他可疑的SQL),为了印证我们的猜测,先检查一下user表结构:
果然user表使用了MyISAM存储引擎,MyISAM在执行操作前会产生表锁,操作完成再自动解锁。如果操作是写操作,则表锁类型为写锁,如果操作是读操作则表锁类型为读锁。正如和你理解的一样写锁将阻塞其他操作(包括读和写),这使得所有操作变为串行;而读锁情况下读-读操作可以并行,但读-写操作仍然是串行。以下示例演示了显式指定了表锁(读锁),读-读并行,读-写串行的情况。
显式开启/关闭表锁,使用lock table user read/write; unlock tables;
session1:
session2:
可以看到会话1启用表锁(读锁)执行读操作,这时会话2可以并行执行读操作,但写操作被阻塞。接着看:
session1:
session2:
当session1执行解锁后,seesion2则立刻开始执行写操作,即读-写串行。
总结:
到此我们把问题的原因基本分析清楚,总结一下——MyISAM存储引擎执行操作时会产生表锁,将影响其他用户对该表的操作,如果表锁是写锁,则会导致其他用户操作串行,如果是读锁则其他用户的读操作可以并行。所以有时我们遇到某个简单的查询花了很长时间,看看是不是这种情况。
解决办法:
1)、尽量不用MyISAM存储引擎,在MySQL8.0版本中已经去掉了所有的MyISAM存储引擎的表,推荐使用InnoDB存储引擎。
2)、如果一定要用MyISAM存储引擎,减少写操作的时间;
3、线上修改表结构有哪些风险?
如果有一天业务系统需要增大一个字段长度,能否在线上直接修改呢?在回答这个问题前,我们先来看一个案例:
以上语句尝试修改user表的name字段长度,语句被阻塞。按照惯例,我们检查一下当前进程:
从进程可以看出alter语句在等待一个元数据锁,而这个元数据锁很可能是上面这条select语句引起的,事实正是如此。在执行DML(select、update、delete、insert)操作时,会对表增加一个元数据锁,这个元数据锁是为了保证在查询期间表结构不会被修改,因此上面的alter语句会被阻塞。那么如果执行顺序相反,先执行alter语句,再执行DML语句呢?DML语句会被阻塞吗?例如我正在线上环境修改表结构,线上的DML语句会被阻塞吗?答案是:不确定。
在MySQL5.6开始提供了online ddl功能,允许一些DDL语句和DML语句并发,在当前5.7版本对online ddl又有了增强,这使得大部分DDL操作可以在线进行。详见:dev.mysql.com/doc/refman/…
所以对于特定场景执行DDL过程中,DML是否会被阻塞需要视场景而定。
总结:通过这个例子我们对元数据锁和online ddl有了一个基本的认识,如果我们在业务开发过程中有在线修改表结构的需求,可以参考以下方案:
1、尽量在业务量小的时间段进行;
2、查看官方文档,确认要做的表修改可以和DML并发,不会阻塞线上业务;
3、推荐使用percona公司的pt-online-schema-change工具,该工具被官方的online ddl更为强大,它的基本原理是:通过insert… select…语句进行一次全量拷贝,通过触发器记录表结构变更过程中产生的增量,从而达到表结构变更的目的。
例如要对A表进行变更,主要步骤为:
创建目的表结构的空表,A_new;
在A表上创建触发器,包括增、删、改触发器;
通过insert…select…limit N 语句分片拷贝数据到目的表
Copy完成后,将A_new表rename到A表。
4、一个死锁问题的分析
在线上环境下死锁的问题偶有发生,死锁是因为两个或多个事务相互等待对方释放锁,导致事务永远无法终止的情况。为了分析问题,我们下面将模拟一个简单死锁的情况,然后从中总结出一些分析思路。
演示环境:MySQL5.7.20 事务隔离级别:RR
表user:
CREATE TABLE `user` ( |
---|
下面演示事务1、事务2工作的情况:
事务1 | 事务2 | 事务监控 | |
---|---|---|---|
T1 |
begin; Query OK, 0 rows affected (0.00 sec) |
begin; Query OK, 0 rows affected (0.00 sec) |
|
T2 |
select * from user where id=3 for update; +----+------+------+ |
select * from user where id=4 for update; +----+------+------+ |
select * from information_schema.INNODB_TRX; 通过查询元数据库innodb事务表,监控到当前运行事务数为2,即事务1、事务2。 |
T3 |
update user set name='haha' where id=4; 因为id=4的记录已被事务2加上行锁,该语句将阻塞 |
监控到当前运行事务数为2。 | |
T4 | 阻塞状态 |
update user set name='hehe' where id=3; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction id=3的记录已被事务1加上行锁,而本事务持有id=4的记录行锁,此时InnoDB存储引擎检查出死锁,本事务被回滚。 |
事务2被回滚,事务1仍在运行中,监控当前运行事务数为1。 |
T5 |
Query OK, 1 row affected (20.91 sec)Rows matched: 1 Changed: 1 Warnings: 0 由于事务2被回滚,原来阻塞的update语句被继续执行。 |
监控当前运行事务数为1。 | |
T6 |
commit; Query OK, 0 rows affected (0.00 sec) |
事务1已提交、事务2已回滚,监控当前运行事务数为0。 |
这是一个简单的死锁场景,事务1、事务2彼此等待对方释放锁,InnoDB存储引擎检测到死锁发生,让事务2回滚,这使得事务1不再等待事务B的锁,从而能够继续执行。那么InnoDB存储引擎是如何检测到死锁的呢?为了弄明白这个问题,我们先检查此时InnoDB的状态:
show engine innodb status\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-01-14 12:17:13 0x70000f1cc000
*** (1) TRANSACTION:
TRANSACTION 5120, ACTIVE 17 sec starting index read
mysql tables in use 1, locked
1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 10, OS thread handle 123145556967424, query id 2764 localhost root updating
update user set name='haha' where id=4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5120 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004;
asc ;;
1: len 6; hex 0000000013fa; asc ;;
2: len 7; hex 520000060129a6; asc R ) ;;
3: len 4; hex 68616861; asc haha;;
4: len 4; hex 80000015; asc ;;
*** (2) TRANSACTION:
TRANSACTION 5121, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root
updating
update user set name='hehe' where id=3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL
RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 0000000013fa; asc ;;
2: len 7; hex 520000060129a6; asc R ) ;;
3: len 4; hex 68616861; asc haha;;
4: len 4; hex 80000015; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact
format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 0000000013fe; asc ;;
2: len 7; hex 5500000156012f; asc U V /;;
3: len 4; hex 68656865; asc hehe;;
4: len 4; hex 80000014; asc ;;
*** WE ROLL BACK TRANSACTION (2)
InnoDB状态有很多指标,这里我们截取死锁相关的信息,可以看出InnoDB可以输出最近出现的死锁信息,其实很多死锁监控工具也是基于此功能开发的。
在死锁信息中,显示了两个事务等待锁的相关信息(蓝色代表事务1、绿色代表事务2),重点关注:WAITING FOR THIS LOCK TO BE GRANTED和HOLDS THE LOCK(S)。
WAITING FOR THIS LOCK TO BE GRANTED表示当前事务正在等待的锁信息,从输出结果看出事务1正在等待heap no为5的行锁,事务2正在等待 heap no为7的行锁;
HOLDS THE LOCK(S):表示当前事务持有的锁信息,从输出结果看出事务2持有heap no为5行锁。
从输出结果看出,最后InnoDB回滚了事务2。
那么InnoDB是如何检查出死锁的呢?
我们想到最简单方法是假如一个事务正在等待一个锁,如果等待时间超过了设定的阈值,那么该事务操作失败,这就避免了多个事务彼此长等待的情况。参数innodb_lock_wait_timeout正是用来设置这个锁等待时间的。
如果按照这个方法,解决死锁是需要时间的(即等待超过innodb_lock_wait_timeout设定的阈值),这种方法稍显被动而且影响系统性能,InnoDB存储引擎提供一个更好的算法来解决死锁问题,wait-for graph算法。简单的说,当出现多个事务开始彼此等待时,启用wait-for graph算法,该算法判定为死锁后立即回滚其中一个事务,死锁被解除。该方法的好处是:检查更为主动,等待时间短。
下面是wait-for graph算法的基本原理:
为了便于理解,我们把死锁看做4辆车彼此阻塞的场景:
4辆车看做4个事务,彼此等待对方的锁,造成死锁。wait-for graph算法原理是把事务作为节点,事务之间的锁等待关系,用有向边表示,例如事务A等待事务B的锁,就从节点A画一条有向边到节点B,这样如果A、B、C、D构成的有向图,形成了环,则判断为死锁。这就是wait-for graph算法的基本原理。
总结:
1、如果我们业务开发中出现死锁如何检查出?刚才已经介绍了通过监控InnoDB状态可以得出,你可以做一个小工具把死锁的记录收集起来,便于事后查看。
2、如果出现死锁,业务系统应该如何应对?从上文我们可以看到当InnoDB检查出死锁后,对客户端报出一个Deadlock found when trying to get lock; try restarting transaction信息,并且回滚该事务,应用端需要针对该信息,做事务重启的工作,并保存现场日志事后做进一步分析,避免下次死锁的产生。
5、锁等待问题的分析
在业务开发中死锁的出现概率较小,但锁等待出现的概率较大,锁等待是因为一个事务长时间占用锁资源,而其他事务一直等待前个事务释放锁。
事务1 | 事务2 | 事务监控 | |
---|---|---|---|
T1 |
begin; Query OK, 0 rows affected (0.00 sec) |
begin; Query OK, 0 rows affected (0.00 sec) |
|
T2 |
select * from user where id=3 for update; +----+------+------+ |
其他查询操作 |
select * from information_schema.INNODB_TRX; 通过查询元数据库innodb事务表,监控到当前运行事务数为2,即事务1、事务2。 |
T3 | 其他查询操作 |
update user set name='hehe' where id=3; 因为id=3的记录被事务1加上行锁,所以该语句将阻塞(即锁等待) |
监控到当前运行事务数为2。 |
T4 | 其他查询操作 |
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 锁等待时间超过阈值,操作失败。注意:此时事务2并没有回滚。 |
监控到当前运行事务数为2。 |
T5 | commit; | 事务1已提交,事务2未提交,监控到当前运行事务数为1。 |
从上述可知事务1长时间持有id=3的行锁,事务2产生锁等待,等待时间超过innodb_lock_wait_timeout后操作中断,但事务并没有回滚。如果我们业务开发中遇到锁等待,不仅会影响性能,还会给你的业务流程提出挑战,因为你的业务端需要对锁等待的情况做适应的逻辑处理,是重试操作还是回滚事务。
在MySQL元数据表中有对事务、锁等待的信息进行收集,例如information_schema数据库下的INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS,你可以通过这些表观察你的业务系统锁等待的情况。你也可以用一下语句方便的查询事务和锁等待的关联关系:
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query wating_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; |
---|
结果:
waiting_trx_id: 5132
waiting_thread: 11
wating_query: update user set name='hehe' where id=3
blocking_trx_id: 5133
blocking_thread: 10
blocking_query: NULL
总结:
1、请对你的业务系统做锁等待的监控,这有助于你了解当前数据库锁情况,以及为你优化业务程序提供帮助;
2、业务系统中应该对锁等待超时的情况做合适的逻辑判断。
6、小结
本文通过几个简单的示例介绍了我们常用的几种MySQL并发问题,并尝试得出针对这些问题我们排查的思路。文中涉及事务、表锁、元数据锁、行锁,但引起并发问题的远远不止这些,例如还有事务隔离级别、GAP锁等。真实的并发问题可能多而复杂,但排查思路和方法却是可以复用,在本文中我们使用了show processlist;show engine innodb status;以及查询元数据表的方法来排查发现问题,如果问题涉及到了复制,还需要借助master/slave监控来协助。
参考资料:
姜承尧《InnoDB存储引擎》
李宏哲 杨挺 《MySQL排查指南》
何登成 hedengcheng.com
========================我是广告=======================
美团点评在成都、北京、上海招聘初中高级java后台、前端工程师,月薪20-50k,需要内推的可以发邮件给我:360841519@qq.com
本文基于署名 2.5 中国大陆许可协议发布,欢迎转载,演绎或用于商业目的,但是必须保留本文的署名李平(包含链接),具体操作方式可参考此处。如您有任何疑问或者授权方面的协商,请 给我留言。 |
-
#1楼 神牛步行3 2018-01-15 09:18
好文 支持(2)反对(0) http://pic.cnblogs.com/face/348819/20130218153953.png -
#2楼 FaceSun 2018-01-15 13:49
还可以 支持(0)反对(0) -
#3楼 多米诺 2018-01-15 19:38
很有用,谢谢 支持(0)反对(0) http://pic.cnblogs.com/face/u17196.jpg?id=29012616 -
#4楼 CowryLee 2018-01-16 10:47
为什么还有人在生产用myisam表 支持(0)反对(0) -
#5楼 涵s 2018-01-16 11:13
赞 支持(0)反对(0) http://pic.cnblogs.com/face/859549/20170630173301.png -
#6楼 牛腩 2018-01-16 14:07
支持支持 支持(0)反对(0) http://pic.cnblogs.com/face/u41249.jpg -
#7楼 alin_qu 2018-01-16 16:47
InnoDB,如果更新的字段不走索引,好像也需要锁表的呢 支持(0)反对(0) -
#8楼[楼主] 飘扬的红领巾 2018-01-16 16:51
@ alin_qu
是的 支持(0)反对(0) http://pic.cnblogs.com/face/352511/20150610133629.png -
#9楼 大石头 2018-01-17 09:28
通过sql来优化是一个永无止境的手段!
楼主能否分享一些MySql调优方案和测试性能指标?
比如默认安装MySql例子表每秒可执行w1次插入操作,或者执行r1次查询。经过xxx优化后,得到w2次插入或者r2次查询。这样子比较直观。 支持(0)反对(0) http://pic.cnblogs.com/face/u19592.jpg?id=28155008 -
#10楼 Daniel Cai 2018-01-17 11:28
@ CowryLee
在生产上用myisam很正常啊,别人查多写少用这种没问题 支持(0)反对(0) http://pic.cnblogs.com/face/45820/20161103205209.png -
#11楼 晓晨Master 2018-01-17 14:57
好文 支持(0)反对(0) http://pic.cnblogs.com/face/668104/20180314222145.png -
#12楼 ~雨落忧伤~ 2018-01-17 16:24
看不懂 支持(0)反对(0) http://pic.cnblogs.com/face/856389/20171120165836.png -
#13楼38927512018/1/18 14:54:59 owen zeng 2018-01-18 14:54
非常不错学习了,,, 支持(0)反对(0) http://pic.cnblogs.com/face/603809/20180102114138.png
【活动】2050 大会 - 博客园程序员团聚 (5.25 杭州·云栖小镇)
【推荐】0元免费体验华为云服务
【活动】腾讯云云服务器新购特惠,5折上云
最新IT新闻:
· 魅族Android Go手机已通过FCC认证 售价在600元以下
· 一个打十个,苏宁物流AGV机器人解析
· 解读:小米究竟该值多少钱?
· 新Surface Pro LTE正式上市,还有Surface Pro新配置型号
· F8大会第二天:VR助力下 社交和设计有了新的可能性
» 更多新闻... 最新知识库文章:
· 如何成为优秀的程序员?
· 菜鸟工程师的超神之路 -- 从校园到职场
· 如何识别人的技术能力和水平?
· 写给自学者的入门指南
· 和程序员谈恋爱
» 更多知识库文章...
About
|
李平,目前在一家O2O互联网公司从事设计、开发工作。业余时间喜欢跑步、看书、游戏。 喜欢简单而高效的工作环境,熟悉JavaEE、SOA、数据库架构、优化、系统运维,有大型门户网站,金融系统建设经验。RHCE、MySQL OCP。MyCAT开源项目成员。 |
园龄:6年5个月
荣誉:推荐博客
粉丝:846
关注: 0 +加关注
最新评论
- Re:电商系统中的商品模型的分析与设计
(上条是昨天就想发,结果新注册用户当天没发发评论)睡了一觉,起来思考下。其实应该规格表也和分类绑定,绑定规则和属性一样。不然的话,我输入iphone那么规格里面如何出现套餐一、套餐二,还有行货及港版的差别呢?毕竟如果选个衣服出这些很奇怪的吧。 -- function0917 - Re:电商系统中的商品模型的分析与设计
#分类表和属性表绑定的疑问,了解一下# --------------------------------------------- 商品分类表是无限极分类的,而商品属性表应该是平的。以鞋子为例,分类表中有鞋子(cid=1)-》高跟鞋(cid=1001),属性表中有鞋子的属性尺码、颜色、价格(这个点多亏博主提醒,不然真注意不到),那么问题来了。我的商品属性表中的分类id(字段为cid)该是鞋子还是高跟鞋的cid呢。 --------------------------------------------- 书读百遍其义自见,打字过程中忽然想到解决办法——“就高”,顾名思义就是如果鞋子和高跟鞋都可以有颜色、尺码等属性,那么就绑定更高一级的cid。这样以后高跟鞋可以有的属性=自己特殊属性(一般高+比较高+恨天低+我实在想不出更多高更鞋的了。。)+所有父分类的属性(不管多少父级,都拿过来。) -------------------------------------------- 这个问题博主怎么看? -- function0917 - Re:深入理解JVM(七)——性能监控工具
mark -- 小鱼儿2017 - Re:深入理解JVM(一)——基本原理
您好,可以转载您的这篇文章吗?会注明原文作者 ,原文链接。谢谢~ -- 工程师-搁浅 - Re:深入理解JVM(四)——垃圾回收算法
可以问一哈 可达对象 和不可达对象指的是什么吗? -- Mainthing
随笔档案
- 2018年1月(2)
- 2017年10月(1)
- 2017年9月(4)
- 2017年8月(7)
- 2015年6月(1)
- 2015年1月(2)
- 2014年10月(2)
- 2014年9月(2)
- 2014年5月(1)
- 2014年3月(2)
- 2014年1月(1)
- 2013年9月(1)
- 2013年8月(2)
- 2013年5月(1)
- 2013年4月(1)
- 2013年3月(1)
- 2012年12月(1)
- 2012年11月(1)
- 2012年9月(1)
- 2012年6月(2)
- 2012年5月(4)
- 2012年3月(1)
日历
|
||||||
日 | 一 | 二 | 三 | 四 | 五 | 六 |
---|---|---|---|---|---|---|
29 | 30 | 1 | 2 | 3 | 4 | 5 |
6 | 7 | 8 | 9 | 10 | 11 | 12 |
13 | 14 | 15 | 16 | 17 | 18 | 19 |
20 | 21 | 22 | 23 | 24 | 25 | 26 |
27 | 28 | 29 | 30 | 31 | 1 | 2 |
3 | 4 | 5 | 6 | 7 | 8 | 9 |
我的标签
- Maven(3)
- Jenkins(2)
- Nexus(2)
- Sonar(2)
- Svn(2)
- Tomcat(2)
- 并发(1)
- 并发 乐观锁 悲观锁(1)
- 大型网站(1)
- 代码质量 Checkstyle PMD JDepend Eclemma Metric(1)
- 更多
随笔分类
- Apache Mina(1)
- Eclipse(1)
- Hibernate(2)
- Java(19)
- JVM(8)
- MongoDB(2)
- MySQL(4)
- RCP/SWT/Jface(1)
- SOA(1)
- Spring(3)
- 持续集成(4)
- 大型网站(3)
- 多线程(1)
- 开源项目(2)
- 敏捷(1)
- 其他(7)
- 设计模式(1)
- 数据结构/算法(1)
- 系统架构(3)
- 支付(1)
- 重构(1)
推荐排行榜
- 1. 大型网站系统架构的演化(211)
- 2. 大型网站的灵魂——性能(63)
- 3. 电商系统中的商品模型的分析与设计—续(51)
- 4. 电商系统中的商品模型的分析与设计(47)
- 5. 做了两款数据库监控工具,打算在近期开源(39)
阅读排行榜
- 1. 大型网站系统架构的演化(48975)
- 2. 电商系统中的商品模型的分析与设计(15338)
- 3. 大型网站的灵魂——性能(15165)
- 4. 使用Maven+Nexus+Jenkins+Svn+Tomcat+Sonar搭建持续集成环境(一)(14527)
- 5. 深入理解JVM(一)——基本原理(14068)
Copyright ©2018 飘扬的红领巾