Sharding-JDBC foreach批量更新事務異常

異常問題

最近線上環境有一個服務頻繁出現更新異常，提示不能提交JDBC事務，但是事務並沒有回滾。

org.springframework.transaction.TransactionSystemException：Could not commit JDBC transaction; nested exception is java.sql.SQLException

完整報錯資訊：

com.XXX.XXXX.XX.XXXXXX.facade.RuleUpdateFacadeService.updatePromotionRule throw Exception! globalTicket= org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:332)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:533)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.XXX.XXXX.XX.XXXXXX.strategy.RuleUpdateStrategy4Redemption$$EnhancerBySpringCGLIB$$acd37971.update(<generated>)
at com.XXX.XXXX.XX.XXXXXX.facade.RuleUpdateFacadeService.updatePromotionRule(RuleUpdateFacadeService.java:71)
at com.XXX.XXXX.XX.XXXXXX.facade.RuleUpdateFacadeService$$FastClassBySpringCGLIB$$1e0d036d.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
at com.XXX.XXXX.XX.troop.exception.aop.ValidFacadeAspect.aroundAdvice(ValidFacadeAspect.java:77)
at sun.reflect.GeneratedMethodAccessor133.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.XXX.XXXX.XX.XXXXXX.facade.RuleUpdateFacadeService$$EnhancerBySpringCGLIB$$e135e9f3.updatePromotionRule(<generated>)
at com.alibaba.dubbo.common.bytecode.Wrapper0.invokeMethod(Wrapper0.java)
at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:45)
at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:71)
at com.alibaba.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:48)
at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:52)
at com.XXX.XXXX.XX.troop.common.log.filters.AttachValidationFilter.invoke(AttachValidationFilter.java:34)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.XXX.cat.dubbo.support.CatTransactionFilter.invoke(CatTransactionFilter.java:140)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:61)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.XXX.dubbo.monitor.support.XXXMonitorFilter.invoke(XXXMonitorFilter.java:82)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:41)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.XXX.dubbo.trace.TraceContextFilter.invoke(TraceContextFilter.java:45)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.XXX.cat.dubbo.support.CatRemoteCallFilter.invoke(CatRemoteCallFilter.java:20)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:72)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:131)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:98)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:96)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:168)
at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:50)
at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:79)
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: java.sql.SQLException
at io.shardingsphere.core.jdbc.adapter.WrapperAdapter.throwSQLExceptionIfNecessary(WrapperAdapter.java:82)
at io.shardingsphere.core.jdbc.adapter.AbstractConnectionAdapter.commit(AbstractConnectionAdapter.java:101)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:329)

上述報錯由 sharding-jdbc 丟擲，sharding-jdbc commit 異常，但是 mysql 的事務已經提交成功，檢視完整的變更記錄，發現線上更新操作存在大量這種場景，而新增場景沒有發現一次該異常。

排除步驟

1. 將線上存在異常的資料匯入至dev開發環境，模擬實際的操作流程，沒有發現問題，可正常執行並正確響應。

2. 在更新事務方法中手動新增異常，再次執行更新操作可正常回滾事務。

3. 在CAT上檢視異常發生時間點，然後到 Kibana 上檢視 k8slog ，發現存在 TransactionSystemException 異常時，k8s上都存在 jdbc 的 CommunicationsException。懷疑是不是資料來源配置有問題，專案中使用 Druid 作為資料庫連線池。

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 7,501,223 milliseconds ago. The last packet sent successfully to the server was 7,501,224 milliseconds ago. is longer than the server configured value of ‘wait_timeout’. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property ‘autoReconnect=true’ to avoid this problem.

4. 檢查資料庫連線池配置，沒有發現異常，和其它服務配置相同，具體檢查每個配置的細緻說明，也沒發現存在問題。

jdbc.share.dataSourceClassName = com.alibaba.druid.pool.DruidDataSource
jdbc.share.driverClassName = com.mysql.jdbc.Driver
# 配置初始化大小、最小、最大
jdbc.share.initialSize = 5
jdbc.share.minIdle = 5
jdbc.share.maxActive = 50
# 配置獲取連線等待超時的時間
jdbc.share.maxWait = 60000
# 配置間隔多久才進行一次檢測，檢測需要關閉的空閒連線，單位是毫秒
jdbc.share.timeBetweenEvictionRunsMillis = 60000
# 配置一個連線在池中最小生存的時間，單位是毫秒
jdbc.share.minEvictableIdleTimeMillis = 3600000
jdbc.share.validationQuery = SELECT 'x'
# 關閉abanded連線時輸出錯誤日誌
jdbc.share.logAbandoned = true
# 超過時間限制是否回收連線
jdbc.share.removeAbandoned = true
# 超時時間 單位為秒 180秒=3分鐘
jdbc.share.removeAbandonedTimeout = 180
jdbc.share.testWhileIdle = true
jdbc.share.testOnBorrow = false
jdbc.share.testOnReturn = false
# 開啟PSCache，並且指定每個連線上PSCache的大小，mysql設定為false
jdbc.share.poolPreparedStatements = false
jdbc.share.maxPoolPreparedStatementPerConnectionSize = 20

5. 偶然瞭解到 sharding-jdbc 在3.0版本之前不支援 batch insert，查閱了 sharding-jdbc release 中的新功能記錄(https://github.com/apache/shardingsphere/releases?after=4.0.0)，在3.0.0.M1版本中支援了批量新增功能(ISSUE #290 Support batch INSERT)，支援類似的批量插入sql語句。

INSERT INTO t_table (xx, xx) VALUES
(xx,xx),
(xx,xx),
(xx,xx);

6. 因此，懷疑是不是 sharding-jdbc 不支援 batch update 方式，搜尋sharding-jdbc issues 列表，搜尋到一個類似的issues(https://github.com/apache/shardingsphere/issues/6665)，如果是利用 mybatis foreach 批量更新，類似下列語句更新：

<update id="batchUpdateList" parameterType="java.util.List">
    <foreach collection="list" item="item" separator=";">
        update table
        set name = #{item.name},
        where id = #{item.id}
    </foreach>
</update>

最終生成實際的 sql 語句，sharding-jdbc 明確不支援並且也沒有支援打算，因為批量更新的資料有可能會路由至多個分庫節點，會導致分散式事務問題。

Actual SQL: ds_1 ::: update stock_change_detail_023
           set 
           update_time = now()
           where
           id = ?
           and tenant_id = ?
           and warehouse_no = ?
         ;
           update stock_change_detail
           set 
           update_time = now()
           where 
           id = ?
           and tenant_id = ?
           and warehouse_no = ? ::: [[[1, jd, 26, 2, jd, 26]]]

而專案中也存在一個通過 foreach 批量更新的方法。

7. 因為該操作是B端後臺操作，效能要求不高，將批量更新語句修改成迴圈單條執行。

參考資料

• Sharding-JDBC version 3.0.0.M1 releases features：support batch INSERT (https://github.com/apache/shardingsphere/releases?after=4.0.0)
• Sharding-JDBC version 3.0.0.M1 support batch INSERT issues(https://github.com/apache/shardingsphere/issues/290)
• Sharding jdbc do not support batch update(https://github.com/apache/shardingsphere/issues/6665)