多线程并发修改表，获取锁等待超时

作者: zianL | 来源:发表于2020-05-17 11:00 被阅读0次

MySQL InnoDB 锁等待和锁等待超时的处理
基于Redis的实现分布式锁(本文提供两种方式)
java多线程之二——Synchronized
锁对象改变引发的线程问题
并发实战（1）- 模拟等待超时模式的连接池
5. Java中的锁
mysql事务没有提交导致锁等待Lock wait timeo
InnoDB行锁等待
java中的自旋锁和互斥锁
Mysql锁有哪些，如何理解 --- 2021-09-14


  @Test
    public void deadlock () throws Exception{

        OfferReceiveRequestDto dto = new OfferReceiveRequestDto();
        dto.setActivityId(527);
        dto.setOfferId("fa389470-5e33-48ce-ba05-7c78e62270db");
        dto.setGroupId("ad8ff217-4cb4-4279-bc40-6f19477d7b76");
        dto.setOfferType(OfferTypeEnum.point);
        int ou = 200000;

        for (int j = 0; j<ou;j++) {
            executor1.execute(new Runnable() {
                @Override
                public void run() {
                    ArrayList<OfferReceiveRequestDto.CustomNum> customNums = new ArrayList<>();
                    for (int i = 0; i < 5; i++) {
                        OfferReceiveRequestDto offerReceiveRequestDto = new OfferReceiveRequestDto();
                        OfferReceiveRequestDto.CustomNum customNum = offerReceiveRequestDto.new CustomNum();
                        customNum.setCardNum(i + "");
                        customNum.setIdNum(i + "");
                        customNums.add(customNum);
                    }
                    dto.setCustoms(customNums);
                    offerReceiveService.offerReceive(dto);
                }
            });
        }

        for (int j = 0; j<ou;j++) {
            executor2.execute(new Runnable() {
                @Override
                public void run() {
                    for (int j = 0; j < 2; j++) {
                        List<String> ids = customPointService.getUnaccountedIds();
                    }
                }
            });
        }
        for (int j = 0; j<ou;j++) {
            executor3.execute(new Runnable() {
                @Override
                public void run() {
                    for (int j = 0; j < 2; j++) {
                        //获取未入账集合
                        List<String> ids = customPointService.getUnaccountedIds();
                        ids.forEach(id -> {
                            TCustomPoint customPoint = customPointService.selectByKey(id);
                            offerReceiveService.updReceive(527, customPoint.getIdNum(), customPoint.getCreatedTime());
                        });
                    }
                }
            });
        }

        Thread.sleep(1000000L);
    }

    
    Exception in thread "pool-1-thread-191" org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.gzcb.creditcard.coupon.mapper.TOfferReceiveMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO t_offer_receive  ( serial_no,activity_id,group_id,id_num,offer_count,offer_receive_count,push_state,create_time ) VALUES( ?,?,?,?,?,?,?,? )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:266)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy135.insert(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:57)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    at com.sun.proxy.$Proxy174.insert(Unknown Source)
    at com.gzcb.creditcard.coupon.service.impl.OfferReceiveServiceImpl.inReceive(OfferReceiveServiceImpl.java:160)
    at com.gzcb.creditcard.coupon.service.impl.OfferReceiveServiceImpl.customReceiveOffer(OfferReceiveServiceImpl.java:138)
    at com.gzcb.creditcard.coupon.service.impl.OfferReceiveServiceImpl.lambda$offerReceive$0(OfferReceiveServiceImpl.java:86)
    at java.util.ArrayList.forEach(ArrayList.java:1249)
    at com.gzcb.creditcard.coupon.service.impl.OfferReceiveServiceImpl.offerReceive(OfferReceiveServiceImpl.java:76)
    at com.gzcb.creditcard.coupon.service.impl.OfferReceiveServiceImpl$$FastClassBySpringCGLIB$$728c7b8b.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at com.gzcb.creditcard.coupon.service.impl.OfferReceiveServiceImpl$$EnhancerBySpringCGLIB$$e3a5b5cd.offerReceive(<generated>)
    at com.gzcb.creditcard.coupon.IOfferReceiveServiceTest$1.run(IOfferReceiveServiceTest.java:94)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at sun.reflect.GeneratedConstructorAccessor124.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051)
    at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1080)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
    at sun.reflect.GeneratedMethodAccessor162.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    at com.sun.proxy.$Proxy213.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at sun.reflect.GeneratedMethodAccessor200.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    at com.sun.proxy.$Proxy210.update(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
    at sun.reflect.GeneratedMethodAccessor217.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 23 more
Exception in thread "pool-1-thread-202" org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting

原因：多线程插入、查询、修改
在下列修改sql 中where条件没有用到索引就会扫全表，当进行批量修改时就容易导致获取锁等待超时，死锁等情况发生；

 <update id="updateReceiveCount">
    update
      t_offer_receive
    set
      offer_receive_count = offer_receive_count + #{num}
    where
      activity_id = #{activityId}
      and create_time = #{createTime}
      and id_num = #{idNum}
  </update>

解决方案建立联合索引对activity_id,id_numm,create_time 字段

ALTER table t_offer_receive add index agd(activity_id,id_num,create_time);

建立唯一索引更好
ALTER table t_offer_receive add UNIQUE index agd(activity_id,id_num,create_time);

再执行多线程代码，未发现死锁，以及等待锁超时等情况，业务处理时间也快了很多

MySQL InnoDB 锁等待和锁等待超时的处理
1. Innodb 引擎表行锁等待和等待超时发生的场景当一个MySQL 连接会话等待另外一个会话持有的互斥行锁时...
基于Redis的实现分布式锁(本文提供两种方式)
关于分布式锁我们学习过并发编程中的锁机制：synchronized和Lock。在单进程的系统中，当多线程同时修改...
java多线程之二——Synchronized
在java多线程并发编程中,Synchronized一直占有很重要的角色。Synchronized通过获取锁来实现...
锁对象改变引发的线程问题
Java多线程锁对象的改变用lock获取锁对象,当lock被修改以后,会产生是一把新的锁,另一个线程获取锁对象时...
并发实战（1）- 模拟等待超时模式的连接池
@[TOC](并发实战（1）- 模拟等待超时模式的连接池) 前言我们来进行并发的实战，用等待超时模式来实现连接池...
5. Java中的锁
Lock接口需要显式的获取和释放锁，支持非阻塞的获取锁，支持中断的获取锁，支持超时获取锁； Synchronze...
mysql事务没有提交导致锁等待Lock wait timeo
今儿遇到一个锁等待超时的问题解决方案首先我们查看被锁表的进程找进程号: trx_mysql_thread_i...
InnoDB行锁等待
关键词：InnoDB、行锁、行锁等待、行锁等待超时问题：数据库错误什么是锁等待（Lock Wait）呢？当事...
java中的自旋锁和互斥锁
多线程中，对共享资源进行访问，为了防止并发引起的相关问题，通常都是引入锁的机制来处理并发问题。获取到资源的线程A...
Mysql锁有哪些，如何理解 --- 2021-09-14
按锁的粒度分：行锁，锁某行数据，锁粒度最小，并发度高表锁，锁整张表，锁粒度最大，并发低间隙锁，锁的是一个区间...

多线程并发修改表，获取锁等待超时

相关文章

MySQL InnoDB 锁等待和锁等待超时的处理

基于Redis的实现分布式锁(本文提供两种方式)

java多线程之二——Synchronized

锁对象改变引发的线程问题

并发实战（1）- 模拟等待超时模式的连接池

5. Java中的锁

mysql事务没有提交导致锁等待Lock wait timeo

InnoDB行锁等待

java中的自旋锁和互斥锁

Mysql锁有哪些，如何理解 --- 2021-09-14

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读