背景:在业务逻辑中,涉及批量update,如果是mysql默认设置下autocommit=1,即多条update语句会一条一条的执行,而不会提交后一起commit,这时候我就想着用set autocommit=0 + update语句 + commit; 事实证明这样确实运行速度快了好几倍,但是也为后面多线程下出现死锁埋下伏笔
一、报错及解决
在单元测试没问题后,我用多线程执行了这个处理流程,但是发现偶尔会出现锁错误,经过一番折腾,发现是用这个set autocommit=0导致的,大致原因就是 在mybatis连接数据库中,本次连接的一条语句设置set autocommit=0,那么下面所有语句都会在commit后才会更新到数据库,这就导致了有的语句读和写出现了混乱,算是幻读吧,如下批量跟新:
<update id="batchUpdate" parameterType="java.util.Map" >
set autocommit=0;
<foreach collection="dataMap" item="value" index="key" open="" close="" separator=";">
update ${region}
<set >
xscj = #{value.xscj,jdbcType=VARCHAR},
zqlx = #{value.zqlx,jdbcType=VARCHAR},
parent_title = #{value.parent_title,jdbcType=VARCHAR},
title = #{value.title,jdbcType=VARCHAR},
lawid = #{value.lawid,jdbcType=CLOB},
lawidstatus = #{value.lawidstatus,jdbcType=VARCHAR},
</set>
where id = #{value.id,jdbcType=INTEGER}
</foreach>
;commit;
</update>
报错:
Cause: com.mysql.cj.jdbc.exceptions.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.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy56.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57)
at com.sun.proxy.$Proxy57.batchUpdate(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
Cause: java.sql.SQLException: Table definition has changed, please retry transaction
; uncategorized SQLException; SQL state [HY000]; error code [1412]; Table definition has changed, please retry transaction; nested exception is java.sql.SQLException: Table definition has changed, please retry transaction
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy56.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57)
at com.sun.proxy.$Proxy57.getDataList(Unknown Source)
at sun.reflect.GeneratedMethodAccessor50.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy58.getDataList(Unknown Source)
at com.bdyh.service.AddMissingCont.getDataMap(AddMissingCont.java:32)
at com.bdyh.service.HandlerSsyjFlow.hanlFlow(HandlerSsyjFlow.java:24)
at com.bdyh.service.HandlerSsyjFlow$$FastClassBySpringCGLIB$$394cc380.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
后来改成start transaction后问题解决
<update id="batchUpdate" parameterType="java.util.Map" >
start transaction;
<foreach collection="dataMap" item="value" index="key" open="" close="" separator=";">
update ${region}
<set >
xscj = #{value.xscj,jdbcType=VARCHAR},
zqlx = #{value.zqlx,jdbcType=VARCHAR},
parent_title = #{value.parent_title,jdbcType=VARCHAR},
title = #{value.title,jdbcType=VARCHAR},
lawid = #{value.lawid,jdbcType=CLOB},
lawidstatus = #{value.lawidstatus,jdbcType=VARCHAR},
</set>
where id = #{value.id,jdbcType=INTEGER}
</foreach>
;commit;
</update>
二、mysql中set autocommit=0与start transaction区别
1、不管autocommit 是1还是0
START TRANSACTION 后,只有当commit数据才会生效,ROLLBACK后就会回滚。
2、当autocommit 为 0 时
不管有没有START TRANSACTION。
只有当commit数据才会生效,ROLLBACK后就会回滚。
3、如果autocommit 为1 ,并且没有START TRANSACTION 。
调用ROLLBACK是没有用的。即便设置了SAVEPOINT。
三、总结
set autocommit=0与start transaction虽然在批量提交时候都可显著提高速度,但是还是不能乱用,正常来说,start transaction就够用了,set autocommit=0还是不要轻易使用