记一次mysql中set autocommit=0造成死锁的情况 以及和start transaction 区别和应用

背景：在业务逻辑中，涉及批量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还是不要轻易使用