文档章节

MySQL并发引起的死锁案例分析

KingSirLee
 KingSirLee
发布于 2016/05/25 10:22
字数 875
阅读 198
收藏 10

在做项目的过程中,由于写SQL太过随意,一不小心就抛了一个死锁异常,如下:

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction  
        at sun.reflect.GeneratedConstructorAccessor247.newInstance(Unknown Source)  
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)  
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)  
        at com.mysql.jdbc.Util.getInstance(Util.java:381)  
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045)  
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)  
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)  
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)  
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)  
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)  
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)  
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)  
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)  
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)  
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)  

表结构如下:

CREATE TABLE `user_item` (  
  `id` BIGINT(20) NOT NULL,  
  `user_id` BIGINT(20) NOT NULL,  
  `item_id` BIGINT(20) NOT NULL,  
  `status` TINYINT(4) NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `idx_1` (`user_id`,`item_id`,`status`)  
) ENGINE=INNODB DEFAULT CHARSET=utf-8 

SQL语句如下:

update user_item set status=1 where user_id=? and item_id=?  

原因分析 MySQL的事务支持与存储引擎有关,MyISAM不支持事务,INNODB支持事务,更新时采用的是行级锁。这里采用的是INNODB做存储引擎,意味着会将update语句做为一个事务来处理。前面提到行级锁必须建立在索引的基础,这条更新语句用到了索引idx_1,所以这里肯定会加上行级锁。 行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。 这个update语句会执行以下步骤: 1、由于用到了非主键索引,首先需要获取idx_1上的行级锁 2、紧接着根据主键进行更新,所以需要获取主键上的行级锁; 3、更新完毕后,提交,并释放所有锁。 如果在步骤1和2之间突然插入一条语句:update user_item .....where id=? and user_id=?,这条语句会先锁住主键索引,然后锁住idx_1。 蛋疼的情况出现了,一条语句获取了idx_1上的锁,等待主键索引上的锁;另一条语句获取了主键上的锁,等待idx_1上的锁,这样就出现了死锁。

解决方案 1、先获取需要更新的记录的主键

select id from user_item where user_id=? and item_id=?  

2、逐条更新

for (Long id : idList) {  
    userItemDAO.updateStatus(id, userId, 1);  
}  
update user_item set status=? where id=? and user_id=? 

这样貌似解决了,都是对单条进行操作,都是先获取主键上的锁,再获取idx_1上的锁。 不过这个解决方案与先前的更新语句不一样,先前的更新语句对所有记录的更新在一个事务中,采用循环更新后并不在同一个事务中,所以在for循环外面还得开一个事务。

Exception e = (Exception)getDbfeelTransactionTemplate().execute(new TransactionCallback() {  
   public Object doInTransaction(TransactionStatus status) {  
      try {  
            for(Long id:idList) {  
        <span style="white-space:pre">  </span>userItemDAO.updateStatus(id,userId,1)  
        }  
            return null;  
      }catch(DAOException e) {  
         status.setRollbackOnly();  
         return e;  
      }  
      catch (Exception e) {  
         status.setRollbackOnly();  
         return e;  
      }  
   }  
});  

**小结:**在采用INNODB的MySQL中,更新操作默认会加行级锁,行级锁是基于索引的,在分析死锁之前需要查询一下mysql的执行计划,看看是否用到了索引,用到了哪个索引,对于没有用索引的操作会采用表级锁。如果操作用到了主键索引会先在主键索引上加锁,然后在其他索引上加锁,否则加锁顺序相反。在并发度高的应用中,批量更新一定要带上记录的主键,优先获取主键上的锁,这样可以减少死锁的发生。

© 著作权归作者所有

共有 人打赏支持
上一篇: base64转图片 java
下一篇: 随手记150806
KingSirLee
粉丝 3
博文 26
码字总数 7500
作品 0
杭州
程序员
私信 提问
Mysql并发时经典常见的死锁原因及解决方法

Mysql并发时经典常见的死锁原因及解决方法 1. mysql都有什么锁 MySQL有三种锁的级别:页级、表级、行级。 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最...

rshare
2017/12/06
0
0
MySQL并发时经典常见的死锁原因及解决方法

MySQL并发时经典常见的死锁原因及解决方法 1. mysql都有什么锁 MySQL有三种锁的级别:页级、表级、行级。 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最...

推荐码发放
2018/08/20
0
0
Mysql并发时经典常见的死锁原因及解决方法

mysql都有什么锁 MySQL有三种锁的级别:页级、表级、行级。 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。 行级锁:开销大,加锁慢;会出现死锁;锁...

满风
2017/10/19
0
0
mysql的InnoDB行锁的一点反思

刚参加工作时,遇到的mysql死锁的问题。具体情况如下: 网页有个统计访问次数功能,然后这个次数num是int类型,网页每次被访问一次就增1,然后运营在跑批量任务的时候,造成数据量这个字段死...

狐狈筱筱生
2018/06/21
0
0
MySQL 在并发场景下的问题及解决思路

原文出处:李平 1、背景 对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标,既要满足大量并发访问的需求又必须保证在此条件下数据的安...

李平
2018/05/11
0
0

没有更多内容

加载失败,请刷新页面

加载更多

redis&memcached&SQL&NoSQL

Why 一个应用肯定有两部分逻辑:数据逻辑和业务逻辑。 数据逻辑包括各种I/O,和内部的数据的处理,也就是在内部如何组织数据结构,变量也属于这一部分。 当一个应用变成一个进程,它负责数据逻...

backbye
今天
2
0
什么时候使用字节流、什么时候使用字符流,二者的区别

在程序中所有的数据都是以流的方式进行传输或保存的,程序需要数据的时候要使用输入流读取数据,而当程序需要将一些数据保存起来的时候,就要使用输出流完成。 InputStream 和OutputStream,...

watermelon11
今天
6
0
Alpakka Kafka,反应式Kafka客户端

Alpakka Kafka 是一个要用于 Java 和 Scala 语言的开源的流感知和反应式集成数据线项目。它建立在 Akka Stream之上,提供了 DSL 来支持反应式和流式编程,内置回压功能。Akka Streams 是 Re...

羊八井
今天
5
0
PHP7源码编译安装详解

$ yum groupinstall "development tools"$ yum install -y gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype \freetype-devel libpng libpng-devel libxml2 ......

问题终结者
今天
5
0
Django学习笔记-从创建虚拟环境到启用服务

1 前置条件 windows系统下,目前已经完成anaconda3或miniconda3的安装,这也意味着已经完成了python3的安装。 2 创建虚拟环境 1、通过Anaconda Prompt,使用conda命令创建虚拟环境(base) e...

davidwbnu
昨天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部