OceanBase低版本SDK驱动包的坑

柒个小矮鱼

已于 2024-07-24 15:07:29 修改

阅读量479

点赞数 25

文章标签： oceanbase java

于 2024-07-24 15:01:34 首次发布

本文链接：https://blog.csdn.net/avatar6780/article/details/140633597

版权

最近有个需求，需要把一张亿级表数据中某些字段更新到另一张目标表中，由于该表的数据是在Oracle数据库同步过来，我们的数据库使用的OceanBase，表名、字段名、数据结构都与OceanBase目标表不匹配，故不能用数据库的同步工具进行同步，只能通过代码进行同步。于是就采用分治的思想写了个数据同步的任务：从最早的时间开始，每个线程取不通时间段内的数据，筛选出来后更新到目标表中。本地测试几十万条数据一两分钟内跑完，数据也没问题，提测测试完成后上线。结果上线没跑多久，就没有再执行下去了，本地和测试环境都没测出问题，思来想去完全想不出为什么，于是就到线上找到有问题的容器打印了线程堆栈日志，发现迁移的线程wating住了，还是在获取一个锁的情况：

   java.lang.Thread.State: WAITING (parking)
        at sun.misc.Unsafe.park(Native Method)
        - parking to wait for  <0x00000005fa6788d0> (a java.util.concurrent.locks.ReentrantLock$NonfairSync)
        at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.parkAndCheckInterrupt(AbstractQueuedSynchronizer.java:836)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireQueued(AbstractQueuedSynchronizer.java:870)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquire(AbstractQueuedSynchronizer.java:1199)
        at java.util.concurrent.locks.ReentrantLock$NonfairSync.lock(ReentrantLock.java:209)
        at java.util.concurrent.locks.ReentrantLock.lock(ReentrantLock.java:285)
        at com.oceanbase.jdbc.JDBC4PreparedStatement.executeInternal(JDBC4PreparedStatement.java:208)
        at com.oceanbase.jdbc.JDBC4PreparedStatement.execute(JDBC4PreparedStatement.java:158)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:56)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)

为什么线程会一直持有着锁不放？于是就根据0x00000005fa6788d0查询持有锁的那个线程，好家伙整个堆栈日志里都没搜到。看OceanBase的SDK源码逻辑是等待其他线程执行完sql释放锁后，后续的线程才能拿到锁，这应该是对每个数据库连接进行的并发控制。

按照这逻辑，如果持有锁的线程没有释放锁的话，它应该还正在执行中没有销毁才对，如果已经执行完成，它应该已经释放锁才对，然而这两种可能在堆栈日志中都找不到结果，用搜索引擎、GPT也没问出个所以然来，查看数据库监控也没看到有相应的慢sql，最后只能让运维重启这个有问题的实例，就这样不了了之了。

然而根据墨菲定律：任何可能出错的事情最终都会出错。果不其然，没过多久又出现了这问题，还是老样子，打印线程堆栈日志，找问题，没结果。断断续续经过几轮实例重启后，终于出现了一个显而易见的堆栈信息，典型的死锁场景：

Found one Java-level deadlock:
=============================
"fillUserPassIdExecutor-15":
  waiting for ownable synchronizer 0x00000005c8954f58, (a java.util.concurrent.locks.ReentrantLock$NonfairSync),
  which is held by "fillUserPassId-SOUTH"
"fillUserPassId-SOUTH":
  waiting for ownable synchronizer 0x00000005c3a7ec80, (a java.util.concurrent.locks.ReentrantLock$NonfairSync),
  which is held by "fillUserPassIdExecutor-15"

Java stack information for the threads listed above:
===================================================
"fillUserPassIdExecutor-15":
        at sun.misc.Unsafe.park(Native Method)
        - parking to wait for  <0x00000005c8954f58> (a java.util.concurrent.locks.ReentrantLock$NonfairSync)
        at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.parkAndCheckInterrupt(AbstractQueuedSynchronizer.java:836)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireQueued(AbstractQueuedSynchronizer.java:870)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquire(AbstractQueuedSynchronizer.java:1199)
        at java.util.concurrent.locks.ReentrantLock$NonfairSync.lock(ReentrantLock.java:209)
        at java.util.concurrent.locks.ReentrantLock.lock(ReentrantLock.java:285)
        at com.oceanbase.jdbc.JDBC4PreparedStatement.executeInternal(JDBC4PreparedStatement.java:208)
        at com.oceanbase.jdbc.JDBC4PreparedStatement.execute(JDBC4PreparedStatement.java:158)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
        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:64)
        at com.sun.proxy.$Proxy236.query(Unknown Source)
        at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
        at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
        at com.sun.proxy.$Proxy235.query(Unknown Source)
        at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
        at com.sun.proxy.$Proxy235.query(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
        at sun.reflect.GeneratedMethodAccessor150.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:427)
        at com.sun.proxy.$Proxy154.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:122)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:86)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
        at com.sun.proxy.$Proxy155.selectPage(Unknown Source)
        at com.zyhl.yun.user.job.repository.impl.BmsUserAccountRepositoryImpl.queryByUpdateTime(BmsUserAccountRepositoryImpl.java:55)
        at com.zyhl.yun.user.job.repository.impl.BmsUserAccountRepositoryImpl$$FastClassBySpringCGLIB$$84c51a88.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
        at com.zyhl.yun.user.job.repository.impl.BmsUserAccountRepositoryImpl$$EnhancerBySpringCGLIB$$c3a8a57b.queryByUpdateTime(<generated>)
        at com.zyhl.yun.user.job.application.task.FillUserPassIdTask.lambda$processFillPassId$5(FillUserPassIdTask.java:112)
        at com.zyhl.yun.user.job.application.task.FillUserPassIdTask$$Lambda$1446/631621528.get(Unknown Source)
        at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590)
        at com.zyhl.yun.user.job.config.MDCTaskDecorator.lambda$decorate$0(MDCTaskDecorator.java:18)
        at com.zyhl.yun.user.job.config.MDCTaskDecorator$$Lambda$1445/93257419.run(Unknown Source)
        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)
"fillUserPassId-SOUTH":
        at sun.misc.Unsafe.park(Native Method)
        - parking to wait for  <0x00000005c3a7ec80> (a java.util.concurrent.locks.ReentrantLock$NonfairSync)
        at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.parkAndCheckInterrupt(AbstractQueuedSynchronizer.java:836)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireQueued(AbstractQueuedSynchronizer.java:870)
        at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquire(AbstractQueuedSynchronizer.java:1199)
        at java.util.concurrent.locks.ReentrantLock$NonfairSync.lock(ReentrantLock.java:209)
        at java.util.concurrent.locks.ReentrantLock.lock(ReentrantLock.java:285)
        at com.oceanbase.jdbc.JDBC4PreparedStatement.executeInternal(JDBC4PreparedStatement.java:208)
        at com.oceanbase.jdbc.JDBC4PreparedStatement.execute(JDBC4PreparedStatement.java:158)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
        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:64)
        at com.sun.proxy.$Proxy236.query(Unknown Source)
        at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
        at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
        at com.sun.proxy.$Proxy235.query(Unknown Source)
        at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
        at com.sun.proxy.$Proxy235.query(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
        at sun.reflect.GeneratedMethodAccessor150.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:427)
        at com.sun.proxy.$Proxy154.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:122)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:86)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
        at com.sun.proxy.$Proxy155.selectPage(Unknown Source)
        at com.zyhl.yun.user.job.repository.impl.BmsUserAccountRepositoryImpl.queryByUpdateTime(BmsUserAccountRepositoryImpl.java:55)
        at com.zyhl.yun.user.job.repository.impl.BmsUserAccountRepositoryImpl$$FastClassBySpringCGLIB$$84c51a88.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
        at com.zyhl.yun.user.job.repository.impl.BmsUserAccountRepositoryImpl$$EnhancerBySpringCGLIB$$c3a8a57b.queryByUpdateTime(<generated>)
        at com.zyhl.yun.user.job.application.task.FillUserPassIdTask.lambda$processFillPassId$5(FillUserPassIdTask.java:112)
        at com.zyhl.yun.user.job.application.task.FillUserPassIdTask$$Lambda$1446/631621528.get(Unknown Source)
        at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590)
        at com.zyhl.yun.user.job.config.MDCTaskDecorator.lambda$decorate$0(MDCTaskDecorator.java:18)
        at com.zyhl.yun.user.job.config.MDCTaskDecorator$$Lambda$1445/93257419.run(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$CallerRunsPolicy.rejectedExecution(ThreadPoolExecutor.java:2038)
        at java.util.concurrent.ThreadPoolExecutor.reject(ThreadPoolExecutor.java:830)
        at java.util.concurrent.ThreadPoolExecutor.execute(ThreadPoolExecutor.java:1379)
        at org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor$1.execute(ThreadPoolTaskExecutor.java:268)
        at org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor.execute(ThreadPoolTaskExecutor.java:360)
        at java.util.concurrent.CompletableFuture.asyncSupplyStage(CompletableFuture.java:1604)
        at java.util.concurrent.CompletableFuture.supplyAsync(CompletableFuture.java:1830)
        at com.zyhl.yun.user.job.application.task.FillUserPassIdTask.processFillPassId(FillUserPassIdTask.java:109)
        at com.zyhl.yun.user.job.application.task.FillUserPassIdTask.lambda$fillUserPassId$0(FillUserPassIdTask.java:57)
        at com.zyhl.yun.user.job.application.task.FillUserPassIdTask$$Lambda$1406/1917453.run(Unknown Source)
        at java.lang.Thread.run(Thread.java:748)

Found 1 deadlock.

于是反馈给领导、客户那边的技术负责人、反馈给数据库厂商，怀疑可能是他们提供的数据库SDK驱动包有问题，结果都说是不是写的代码或者sql有问题导致的死锁或者慢查询。WTF？大厂的代码就一定没问题吗？开源项目都还有那么多人提PR。

于是下班回家后把之前看的那本《java并发编程实战》死锁那一个篇章回顾了一遍，里面有一段内容是这样说的：多个线程试图同时使用同一个JDBC连接，这并不是程序设计的初衷——开发人员惊讶地发现同一个Connection被两个线程并发使用。在JDBC规范中没有要求Connection必须是线程安全的，以及Connection通常被封闭在单个线程中使用。这个厂商试图提供一个线程安全的JDBC驱动，因此在驱动程序代码内部对多个JDBC对象施加了同步机制。然而，生产商却没有考虑锁的顺序，因而驱动程序很容易发生死锁，而正是由于这个存在死锁风险的驱动程序与错误共享Connection的应用程序发生了交互，才使得这个问题暴露出来。因为单个错误不会产生死锁，只有两个错误同时发生时才会产生，即使它们分别进行了大量测试。

看过之后感同身受，我也觉得大概率就是数据库厂商提供的数据库驱动有问题，于是就上OceanBase社区提问，果然还是社区大佬多，直接回复我说建议升级到最新的SDK即可。链接奉上：https://ask.oceanbase.com/t/topic/35608493/10

最后把社区的这个链接发到群里，他们也同意了这么操作，升级了SDK后，几天时间就把十几亿的数据同步到了目标表，也没有再发生线程死锁的问题。前前后后折腾了几回，终于得到了解决，功夫不负有心人啊！

柒个小矮鱼

关注

25
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
OceanBase低版本SDK驱动包的坑

按照这逻辑，如果持有锁的线程没有释放锁的话，它应该还正在执行中没有销毁才对，如果已经执行完成，它应该已经释放锁才对，然而这两种可能在堆栈日志中都找不到结果，用搜索引擎、GPT也没问出个所以然来，查看数据库监控也没看到有相应的慢sql，最后只能让运维重启这个有问题的实例，就这样不了了之了。果不其然，没过多久又出现了这问题，还是老样子，打印线程堆栈日志，找问题，没结果。最后把社区的这个链接发到群里，他们也同意了这么操作，升级了SDK后，几天时间就把十几亿的数据同步到了目标表，也没有再发生线程死锁的问题。
复制链接

扫一扫