select for update 语句在AT模式下不支持tidb数据库

问题描述

AT模式：

分布式事务 下其中一个子事务有select for update 相关语句，在tidb中会报错：

Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 9 near "SAVEPOINT 10b1b272_ef80_464b_9017_9b75828a5b30"

The error may exist in com/service/base/wms/inventory/domain/WmsInventoryBusinessMapper.java (best guess)

The error may involve com.service.base.wms.inventory.domain.WmsInventoryBusinessMapper.selectByExample-Inline

The error occurred while setting parameters

SQL: SELECT version,id,house_id,house_code,house_name,house_type,inventory_id,owner_id,owner_code,owner_name,goods_id,goods_code,goods_name,shy_num,shy_spec_code,shy_spec_name,shy_spec_key,shy_spec_ratio,shy_unit_type,small_spec_code,small_spec_name,small_spec_key,small_spec_ratio,small_unit_type,shy_small_num,allocated_small_num,create_time,update_time FROM wms_inventory_business WHERE ( ( inventory_id in ( ? , ? ) ) ) FOR UPDATE

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 9 near "SAVEPOINT 10b1b272_ef80_464b_9017_9b75828a5b30"

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 9 near "SAVEPOINT 10b1b272_ef80_464b_9017_9b75828a5b30" , dubbo version: 2.7.8, current host: 173.16.200.97 org.springframework.jdbc.BadSqlGrammarException:

Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 9 near "SAVEPOINT 10b1b272_ef80_464b_9017_9b75828a5b30"

The error may exist in com/service/base/wms/inventory/domain/WmsInventoryBusinessMapper.java (best guess)

The error may involve com.service.base.wms.inventory.domain.WmsInventoryBusinessMapper.selectByExample-Inline

The error occurred while setting parameters

SQL: SELECT version,id,house_id,house_code,house_name,house_type,inventory_id,owner_id,owner_code,owner_name,goods_id,goods_code,goods_name,shy_num,shy_spec_code,shy_spec_name,shy_spec_key,shy_spec_ratio,shy_unit_type,small_spec_code,small_spec_name,small_spec_key,small_spec_ratio,small_unit_type,shy_small_num,allocated_small_num,create_time,update_time FROM wms_inventory_business WHERE ( ( inventory_id in ( ? , ? ) ) ) FOR UPDATE

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 9 near "SAVEPOINT 10b1b272_ef80_464b_9017_9b75828a5b30"

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 9 near "SAVEPOINT 10b1b272_ef80_464b_9017_9b75828a5b30" at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) 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.$Proxy199.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) at com.sun.proxy.$Proxy221.selectByExample(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.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88) at com.service.wms.common.biz.aspect.SqlAccessorAspect.around(SqlAccessorAspect.java:78) at sun.reflect.GeneratedMethodAccessor441.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.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy222.selectByExample(Unknown Source) at com.service.base.wms.inventory.service.impl.inventory.BusinessInventoryServiceImpl.innerQueryByInventoryIds(BusinessInventoryServiceImpl.java:268) at com.service.base.wms.inventory.service.impl.inventory.BusinessInventoryServiceImpl.queryBusinessMap(BusinessInventoryServiceImpl.java:272) at com.service.base.wms.inventory.service.impl.inventory.BusinessInventoryServiceImpl.release(BusinessInventoryServiceImpl.java:343) at com.service.base.wms.inventory.service.impl.inventory.BusinessInventoryServiceImpl$$FastClassBySpringCGLIB$$9ded9cc6.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) at org.springframework.retry.interceptor.RetryOperationsInterceptor$1.doWithRetry(RetryOperationsInterceptor.java:91) at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:287) at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:164) at org.springframework.retry.interceptor.RetryOperationsInterceptor.invoke(RetryOperationsInterceptor.java:118) at org.springframework.retry.annotation.AnnotationAwareRetryOperationsInterceptor.invoke(AnnotationAwareRetryOperationsInterceptor.java:153) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) at com.service.base.wms.inventory.service.impl.inventory.BusinessInventoryServiceImpl$$EnhancerBySpringCGLIB$$9ba64199.release() at com.service.base.wms.inventory.dubbo.RpcInventoryWmsServiceImpl.releaseAllocateBusinessByInventory(RpcInventoryWmsServiceImpl.java:98) at com.service.base.wms.inventory.dubbo.RpcInventoryWmsServiceImpl$$FastClassBySpringCGLIB$$3f461e59.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:685) at com.service.base.wms.inventory.dubbo.RpcInventoryWmsServiceImpl$$EnhancerBySpringCGLIB$$9e49591b.releaseAllocateBusinessByInventory() at org.apache.dubbo.common.bytecode.Wrapper5.invokeMethod(Wrapper5.java) at org.apache.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:47) at org.apache.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:84) at org.apache.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:56) at org.apache.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:56) at org.apache.dubbo.validation.filter.ValidationFilter.invoke(ValidationFilter.java:101) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at com.alibaba.dubbo.rpc.Invoker$CompatibleInvoker.invoke(Invoker.java:55) at io.seata.integration.dubbo.alibaba.AlibabaDubboTransactionPropagationFilter.invoke(AlibabaDubboTransactionPropagationFilter.java:45) at com.alibaba.dubbo.rpc.Filter.invoke(Filter.java:29) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at io.seata.integration.dubbo.ApacheDubboTransactionPropagationFilter.invoke(ApacheDubboTransactionPropagationFilter.java:69) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:47) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at com.alibaba.csp.sentinel.adapter.dubbo.SentinelDubboProviderFilter.invoke(SentinelDubboProviderFilter.java:80) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:46) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:89) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:129) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:152) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:41) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:83) at org.apache.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:145) at org.apache.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:100) at org.apache.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:175) at org.apache.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:51) at org.apache.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:57) 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)

其他信息

TIDB 5.3.0 版本目前不支持savepoint 机制，但seata 的 SelectForUpdateExecutor 处理 select for update 语句时使用了savepoint。

环境信息

JDK version : JDK1.8 Seata version: 1.4.2 OS : Linux Others: TIDB 5.3.0

原提问者GitHub用户panhc1997