使用mysql事务不同场景导致的死锁问题以及解决方法

MySQL各存储引擎使用了三种级别的锁定机制:table-level(表级锁定),row-level(行级锁定)和page-level(页级锁定)此处只介绍使用InnoDB存储引擎行过程中经常常遇到的问题以及解决方法。

1.变更字段有异常事务未提交导致锁表

使用mysql最常见的场景莫过于对表新增或修改字段,新增字段过程中如果没有提前判断表的运行状态,直接执行新增或修改字段的操作很可能导致锁表导致较严重的后果。

解决方法:

先查看下在跑的事务

select * from information_schema.INNODB_TRX order by trx_started asc limit 10\G

如果同一个事务长时间运行1分钟甚至1小时,根据trx_mysql_thread_id查询是不是处于sleep 状态,如果是sleep基本可以确认是未提交的事务

select * from information_schema.processlist where id=371061658\G

确认事务如果属于异常,则可将事务kill掉

kill 371061658;

变更过程中最好新开窗口实时查询是否有异常sleep中的异常事务

select * from information_schema.processlist where command <>'Sleep' limit 10;

结论:对于线上使用MYSQL的重要服务最好将DB通过旁路的方式将表中的异常事务监控起来,自动kill掉sleep中耗时较长的异常事务,以防止因变更DB事务未提交导致锁表异常。

2.执行事务中SQL语句on duplicate使用不当致死锁

使用MYSQL的抢购活动中为防止并发抢购而update 带条件自增导致死锁(这里只说使用MYSQL特定场景可能遇到的问题,至于使用MYSQL性能问题可以另外讨论)

小量的抢购活动使用MYSQL防止优惠被刷我们常用下面SQL,通过DB对同一条记录的串行更新来防止并发请求

update tableA set num=num+1 where ... and num<100;

使用上述方式如果没有记录存在的时候我们还需要新增记录,我们可能会基于功能逻辑来用更简单的INSERT ON DUPLICATE KEY语句来执行SQL,但此时会造成更大的问题, 如果有两个事务并发的执行同样的语句,那么就会产生death lock(参考官方文档https://bugs.mysql.com/bug.php?id=58637

insert...on duplicate key update;

3.使用MYSQL事务异常分支未回滚事务导致行死锁(异常现象多为:同一接口某个或某些用户请求不可用)

mysql InnoDB存储引擎为我们使用事务带来了巨大便利,但是事务异常处理不当出现异常分支未捕获并回滚事务可能会导致死锁,建议使用事务时小心处理,对各个可能的异常分支都要对事务进行回滚。

结论:使用mysql过程中可能遇到各种死锁的坑,这里只简单列举了常用的几点,后续继续补充。

参考资料

https://bugs.mysql.com/bug.php?id=52020

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

小文章

1 篇文章1 人订阅

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏BigNerdCoding

SQLite 并发的四种处理方式

SQLite 是一款轻型的嵌入式数据库它占用资源非常的低,处理速度快,高效而且可靠。在嵌入式设备中,可能只需要几百 K 的内存就够了。因此在移动设备爆发时,它依...

1K7
来自专栏L宝宝聊IT

MySQL架构组成、逻辑模块组成

Mysql逻辑结构可以看成是二层架构,第一层通常叫做SQL Layer,在mysql数据库系统处理底层数据之前的所有工作都在这一层完成的,包括权...

913
来自专栏架构师之路

InnoDB并发如此高,原因竟然在这?

《InnoDB行锁,如何锁住一条不存在的记录?》埋了一个坑,没想到评论反响剧烈,大家都希望深挖下去。原计划写写InnoDB的锁结束这个case,既然呼声这么高,...

1503
来自专栏cs

知识点找回2.0

Servlet(server Applet),全称Java Servlet, 是用java编写的服务器端程序。而这些Servlet都要实现Servlet的这个借...

841
来自专栏云计算

如何在Debian 7上安装MySQL

MySQL是一种流行的数据库管理系统,用于Web和服务器应用程序。本指南将介绍如何在Debian 7(Wheezy)的Linode上安装,配置和管理MySQL。

1183
来自专栏吴柯的运维笔记

部署Cacti监控平台实现网络监控

Cacti是一套基于PHP,MySQL,SNMP及RRDTool开发的网络流量监测图形分析工具。 Cacti是通过 snmpget来获取数据,使用 RRDtoo...

4568
来自专栏架构师之路

InnoDB,能将数据存储在DVD里?

2. alter table可以不拷贝表了,且不阻塞写操作,online DDL,酷炫吧。

1061
来自专栏杨建荣的学习笔记

巧用flashback database实现灵活的数据切换(r5笔记第9天)

今天是DTCC第二天了,抽空去听了下,因为手头有一些活,听到一半只能赶回公司继续工作。 客户今天有一个需求,因为开发现在在生产环境中遇到了一些困难,需要在测试生...

3475
来自专栏沈唁志

WordPress添加评论回复邮件提醒通知功能

1792
来自专栏重庆的技术分享区

如何在Debian 8上安装MySQL

MySQL是一种流行的数据库管理系统,用于Web和服务器应用程序。本指南将介绍如何在运行Debian 8(Jessie)的Linode上安装,配置和管理MySQ...

7962

扫码关注云+社区

领取腾讯云代金券