低版本大查询死锁导致队列积压问题

问题现象

大查询背景

OceanBase 采用了一种基于查询时长的优先级机制，旨在确保短查询不会因大查询的复杂性而受到时效性的影响，以提升用户的查询体验。该机制的原理是，当一个线程执行的 SQL 查询超过一定的时间阈值，该查询就被视为大查询，而该线程就被标记为大查询线程。相反，执行时间低于阈值的查询被视为短查询，对应的线程被标记为短查询线程。在系统资源有限的情况下，大查询线程和短查询线程同时争抢 CPU，为了提升短查询的响应效率，让短查询不会被大查询阻塞，系统会限制大查询线程的 CPU 使用，优先给短查询线程提供 CPU 资源。大查询线程会被挂起，为其它的短查询线程让出 CPU 资源，然后需要依靠其它短查询线程执行一段时间后将大查询线程唤醒，从而恢复大查询线程的执行。通过这种机制，可以保证短查询的高时效性，同时不影响大查询的最终完成，从而提高整体的用户体验和系统的响应性能。

活跃线程背景

OceanBase 数据库根据租户的 cpu_count * cpu_quota_concurrency 来计算活跃线程数，这些线程在租户创建的时候就会被创建好，会严格保证活跃线程不超过 cpu_count * cpu_quota_concurrency 。例如，线程 A 是个活跃线程，它因为执行大查询被挂起，这时活跃线程就少了一个。作为补充，这个租户会额外再创建一个活跃线程。因此，在大查询线程被挂起后就不是活跃线程了，需要依赖其他线程将其唤醒。如果一直没有可用的其他活跃线程，例如所有其他活跃线程都被阻塞住，会导致大查询线程无法被唤醒。

本质原因

一个或几个工作线程持有锁，然后被判定为大查询线程挂起，而其它工作线程在执行过程中都卡在大查询线程持有的锁上无法有进展，因此就没有其他线程能够去唤醒大查询线程，最终导致了死锁。

关键诊断信息

触发条件

位置缓存（location cache）模块为了避免会有大量的并发查询 location cache ，通过信号量限制了最大的并发数（通过配置项 location_cache_cpu_quota 配置，默认 20）。当全部并发信号量都被大查询线程持有，且大查询线程被挂起，而此时其他的线程也在尝试查询 location cache 就会被阻塞在获取该信号量上卡住，这样就没有线程能够去唤醒大查询线程，导致了死锁。

事前巡检

无

事后诊断

1. 搜索日志里 dump tenant info 信息。如果存在有队列积压，且查看物理 CPU 使用率很低，即可怀疑是死锁。根据 lq waiting workers 字段可查看挂起的大查询线程个数。

2. obstack 抓堆栈，查看堆栈信息是否有如下类似的堆栈。

   大查询线程被挂起后堆栈会卡在 ObThWorker::wait_runnable ，等待被唤醒，堆栈上层有 ObPartitionLocationCache::renew_location 函数说明该大查询线程持有了 location cache 的信号量没有释放。

   -- 大查询线程堆栈
   #0    0x00007f65dbde2de2 in pthread_cond_timedwait@@GLIBC_2.3.2 from /usr/lib64/libpthread-2.17.so
   #1    0x0000000007adc64f in oceanbase::common::ObThreadCond::wait_us(unsigned long) at ./build_sanity/deps/oblib/src/lib/./deps/oblib/src/lib/lock/ob_thread_cond.cpp:120
   #2    0x00000000154470cd in oceanbase::omt::ObThWorker::wait_runnable() at ./build_sanity/src/observer/./deps/oblib/src/lib/ob_lib_config.h:63
   #3    0x0000000007b37bad in oceanbase::omt::ObThWorker::check_wait() from /data/1/liangchen.ylc/32x.liangchen.ylc/z2/obs0/bin/observer
   #4    0x0000000015449383 in oceanbase::omt::ObThWorker::check_status() at ./build_sanity/src/observer/./src/observer/omt/ob_th_worker.cpp:531
   #5    0x000000001efb4cdf in oceanbase::lib::Worker::sched_run(long) at ./build_sanity/deps/oblib/src/lib/./deps/oblib/src/lib/worker.cpp:67
   #6    0x000000002014a469 in oceanbase::obrpc::ObRpcProxy::send_request(oceanbase::rpc::frame::ObReqTransport::Request<oceanbase::obrpc::ObRpcPacket> const&, oceanbase::rpc::frame::ObReqTransport::Result<oceanbase::obrpc::ObRpcPacket>&) const at ./build_sanity/deps/oblib/src/rpc/./deps/oblib/src/rpc/obrpc/ob_rpc_proxy.cpp:324
   #7    0x0000000007fb379b in int oceanbase::obrpc::ObRpcProxy::rpc_call<oceanbase::common::ObPartitionKey, oceanbase::obrpc::ObMemberListAndLeaderArg>(oceanbase::obrpc::ObRpcPacketCode, oceanbase::common::ObPartitionKey const&, oceanbase::obrpc::ObMemberListAndLeaderArg&, oceanbase::obrpc::Handle*, oceanbase::obrpc::ObRpcOpts const&) at ./build_sanity/src/share/./deps/oblib/src/rpc/obrpc/ob_rpc_proxy.ipp:331
   #8    0x0000000007e0890a in oceanbase::obrpc::ObSrvRpcProxy::get_member_list_and_leader(oceanbase::common::ObPartitionKey const&, oceanbase::obrpc::ObMemberListAndLeaderArg&, oceanbase::obrpc::ObRpcOpts const&) at ./build_sanity/src/share/./src/share/ob_srv_rpc_proxy.h:163
   #9    0x000000000c375481 in oceanbase::share::ObLocationFetcher::renew_location_with_rpc_v2(oceanbase::common::hash::ObHashMap<oceanbase::share::ObReplicaRenewKey, oceanbase::obrpc::ObMemberListAndLeaderArg const*, oceanbase::common::hash::LatchReadWriteDefendMode, oceanbase::common::hash::hash_func<oceanbase::share::ObReplicaRenewKey>, oceanbase::common::hash::equal_to<oceanbase::share::ObReplicaRenewKey>, oceanbase::common::hash::SimpleAllocer<oceanbase::common::hash::ObHashTableNode<oceanbase::common::hash::HashMapPair<oceanbase::share::ObReplicaRenewKey, oceanbase::obrpc::ObMemberListAndLeaderArg const*> >, 77, oceanbase::common::hash::SpinMutexDefendMode, oceanbase::common::hash::DefaultSimpleAllocerAllocator>, oceanbase::common::hash::NormalPointer, oceanbase::common::ObMalloc, 1l>*, oceanbase::share::ObPartitionLocation const&, oceanbase::share::ObPartitionLocation&, bool&) from /data/1/liangchen.ylc/32x.liangchen.ylc/z2/obs0/bin/observer
   #10   0x000000000c39b2a1 in oceanbase::share::ObPartitionLocationCache::renew_location(unsigned long, long, long, oceanbase::share::ObPartitionLocation&, long, bool, bool, bool) at ./build_sanity/src/share/./src/share/partition_table/ob_partition_location_cache.cpp:3494
   #11   0x000000000c39c92a in oceanbase::share::ObPartitionLocationCache::get(unsigned long, long, oceanbase::share::ObPartitionLocation&, long, bool&, bool) at ./build_sanity/src/share/./src/share/partition_table/ob_partition_location_cache.cpp:2567
   #12   0x000000001bcc8f44 in oceanbase::sql::ObSqlPartitionLocationCache::get(unsigned long, long, oceanbase::share::ObPartitionLocation&, long, bool&, bool) at ./build_sanity/src/sql/./src/sql/ob_sql_partition_location_cache.cpp:89
   #13   0x000000001bcca7cf in oceanbase::sql::ObSqlPartitionLocationCache::get(oceanbase::common::ObPartitionKey const&, oceanbase::share::ObPartitionLocation&, long, bool&) at ./build_sanity/src/sql/./src/sql/ob_sql_partition_location_cache.cpp:114
   #14   0x0000000016d10c3b in oceanbase::sql::ObTaskExecutorCtxUtil::refresh_location_cache(oceanbase::sql::ObTaskExecutorCtx&, bool) at ./build_sanity/src/sql/./src/sql/executor/ob_task_executor_ctx.cpp:838
   #15   0x0000000014bf560c in oceanbase::observer::ObQueryRetryCtrl::test_and_save_retry_state(oceanbase::observer::ObGlobalContext const&, oceanbase::sql::ObSqlCtx const&, oceanbase::sql::ObResultSet&, int, int&, bool) at ./build_sanity/src/observer/./src/observer/mysql/ob_query_retry_ctrl.cpp:330
   #16   0x00000000076ca71a in oceanbase::observer::ObSyncPlanDriver::response_result(oceanbase::observer::ObMySQLResultSet&) at ./build_sanity/src/observer/./src/observer/mysql/ob_sync_plan_driver.cpp:80
   #17   0x0000000014c83583 in oceanbase::observer::ObMPQuery::process_with_tmp_context(oceanbase::sql::ObSQLSessionInfo&, bool, bool, bool&, bool&) at ./build_sanity/src/observer/./src/observer/mysql/obmp_query.cpp:1268
   #18   0x00000000076bd400 in oceanbase::observer::ObMPQuery::process_single_stmt(oceanbase::sql::ObMultiStmtItem const&, oceanbase::sql::ObSQLSessionInfo&, bool, bool, bool&, bool&) at ./build_sanity/src/observer/./src/observer/mysql/obmp_query.cpp:518
   #19   0x00000000076b6588 in oceanbase::observer::ObMPQuery::process() at ./build_sanity/src/observer/./src/observer/mysql/obmp_query.cpp:313
   #20   0x00000000076b2711 in oceanbase::rpc::frame::ObSqlProcessor::run() at ./build_sanity/deps/oblib/src/rpc/./deps/oblib/src/rpc/frame/ob_sql_processor.cpp:50
   #21   0x000000001544d419 in oceanbase::omt::ObWorkerProcessor::process_one(oceanbase::rpc::ObRequest&) at ./build_sanity/src/observer/./src/observer/omt/ob_worker_processor.cpp:76
   #22   0x00000000076b06dc in oceanbase::omt::ObWorkerProcessor::process(oceanbase::rpc::ObRequest&) at ./build_sanity/src/observer/./src/observer/omt/ob_worker_processor.cpp:147
   #23   0x000000001544810c in oceanbase::omt::ObThWorker::process_request(oceanbase::rpc::ObRequest&) at ./build_sanity/src/observer/./src/observer/omt/ob_th_worker.cpp:280
   #24   0x00000000076a8985 in oceanbase::omt::ObThWorker::worker(long&, long&, int&) at ./build_sanity/src/observer/./src/observer/omt/ob_th_worker.cpp:443
   #25   0x0000000015449019 in non-virtual thunk to oceanbase::omt::ObThWorker::run(long) from /data/1/liangchen.ylc/32x.liangchen.ylc/z2/obs0/bin/observer
   #26   0x000000001e1c1002 in std::_Function_handler<void (), oceanbase::lib::UserThread::start()::$_15::operator()(oblib::Thread*) const::{lambda()#1}>::_M_invoke(std::_Any_data const&) at ./build_sanity/deps/oblib/src/lib/./deps/oblib/src/lib/coro/co_user_thread.cpp:234
   #27   0x000000001e1c0c04 in eeentry(boost::context::detail::transfer_t) at ./build_sanity/deps/oblib/src/lib/./deps/oblib/src/lib/coro/co_user_thread.cpp:44
   

   其他线程也在执行 ObPartitionLocationCache::renew_location ，但始终无法获取信号量卡在 ObPartitionLocationCache::LocationSem::acquire 处。

   -- 其他线程堆栈
   #0    0x00007f83dfc46d62 in pthread_cond_timedwait@@GLIBC_2.3.2 from /usr/lib64/libpthread-2.17.so
   #1    0x0000562e537297c6 in oceanbase::common::ObThreadCond::wait_us(unsigned long) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #2    0x0000562e55a1eb3b in oceanbase::share::ObPartitionLocationCache::LocationSem::acquire(long) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #3    0x0000562e55a279d8 in oceanbase::share::ObPartitionLocationCache::renew_location(unsigned long, long, long, oceanbase::share::ObPartitionLocation&, long, bool, bool, bool) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #4    0x0000562e55a28b2b in oceanbase::share::ObPartitionLocationCache::get(unsigned long, long, oceanbase::share::ObPartitionLocation&, long, bool&, bool) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #5    0x0000562e5cd6c38c in oceanbase::sql::ObSqlPartitionLocationCache::get(unsigned long, long, oceanbase::share::ObPartitionLocation&, long, bool&, bool) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #6    0x0000562e5aa0827d in oceanbase::sql::ObOptimizerPartitionLocationCache::get(unsigned long, long, oceanbase::share::ObPartitionLocation&, long, bool&, bool) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #7    0x0000562e5cbdda3e in oceanbase::sql::ObTableLocation::set_partition_locations(oceanbase::sql::ObExecContext&, oceanbase::share::ObIPartitionLocationCache&, unsigned long, oceanbase::common::ObIArray<long> const&, oceanbase::common::ObIArray<oceanbase::sql::ObPhyPartitionLocationInfo>&, bool) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #8    0x0000562e5cbdd485 in oceanbase::sql::ObTableLocation::calculate_partition_location_infos(oceanbase::sql::ObExecContext&, oceanbase::common::ObPartMgr*, oceanbase::common::Ob2DArray<oceanbase::common::ObObjParam, 2096128, oceanbase::common::ObWrapperAllocator, false, oceanbase::common::ObSEArray<oceanbase::common::ObObjParam*, 1l, oceanbase::common::ObWrapperAllocator, false> > const&, oceanbase::share::ObIPartitionLocationCache&, oceanbase::common::ObIArray<oceanbase::sql::ObPhyPartitionLocationInfo>&, oceanbase::common::ObDataTypeCastParams const&, bool) const from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #9    0x0000562e5cc24f56 in oceanbase::sql::ObTablePartitionInfo::calculate_phy_table_location_info(oceanbase::sql::ObExecContext&, oceanbase::common::ObPartMgr*, oceanbase::common::Ob2DArray<oceanbase::common::ObObjParam, 2096128, oceanbase::common::ObWrapperAllocator, false, oceanbase::common::ObSEArray<oceanbase::common::ObObjParam*, 1l, oceanbase::common::ObWrapperAllocator, false> > const&, oceanbase::share::ObIPartitionLocationCache&, oceanbase::common::ObDataTypeCastParams const&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #10   0x0000562e5c90dde5 in oceanbase::sql::ObJoinOrder::compute_table_location(unsigned long, unsigned long, bool, oceanbase::sql::ObTablePartitionInfo&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #11   0x0000562e5c94968c in oceanbase::sql::ObJoinOrder::compute_table_meta_info(unsigned long, unsigned long) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #12   0x0000562e5c948994 in oceanbase::sql::ObJoinOrder::generate_access_paths(oceanbase::sql::ObJoinOrder::PathHelper&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #13   0x0000562e5c947d77 in oceanbase::sql::ObJoinOrder::generate_normal_access_path() from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #14   0x0000562e5c945e59 in oceanbase::sql::ObJoinOrder::generate_base_paths() from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #15   0x0000562e5a8566f9 in oceanbase::sql::ObLogPlan::generate_join_orders() from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #16   0x0000562e5a8bd5bd in oceanbase::sql::ObLogPlan::generate_plan_tree() from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #17   0x0000562e5cb978a9 in oceanbase::sql::ObSelectLogPlan::generate_plan_for_plain_select() from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #18   0x0000562e5cb971e6 in oceanbase::sql::ObSelectLogPlan::generate_raw_plan() from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #19   0x0000562e5cb9c81d in oceanbase::sql::ObSelectLogPlan::generate_plan() from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #20   0x0000562e5a9fd8b2 in oceanbase::sql::ObOptimizer::optimize(oceanbase::sql::ObDMLStmt&, oceanbase::sql::ObLogPlan*&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #21   0x0000562e5cd48299 in oceanbase::sql::ObSql::optimize_stmt(oceanbase::sql::ObOptimizer&, oceanbase::sql::ObSQLSessionInfo const&, oceanbase::sql::ObDMLStmt&, oceanbase::sql::ObLogPlan*&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #22   0x0000562e536e31be in oceanbase::sql::ObSql::generate_physical_plan(ParseResult&, oceanbase::sql::ObPlanCacheCtx*, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObResultSet&, bool, bool) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #23   0x0000562e535dfa8f in oceanbase::sql::ObSql::handle_physical_plan(oceanbase::common::ObString const&, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObResultSet&, oceanbase::sql::ObPlanCacheCtx&, int, bool) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #24   0x0000562e5cd42537 in oceanbase::sql::ObSql::handle_remote_query(oceanbase::sql::ObRemoteSqlInfo const&, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObExecContext&, oceanbase::sql::ObCacheObjGuard&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #25   0x0000562e537bbe44 in oceanbase::sql::ObRemoteBaseExecuteP<oceanbase::obrpc::ObExecutorRpcProxy::ObRpc<(oceanbase::obrpc::ObRpcPacketCode)1308, void> >::execute_with_sql(oceanbase::sql::ObRemoteTask&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #26   0x0000562e5d039645 in oceanbase::sql::ObRpcRemoteSyncExecuteP::process() from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #28   0x0000562e5a17aab1 in oceanbase::omt::ObWorkerProcessor::process_one(oceanbase::rpc::ObRequest&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #29   0x0000562e53540da0 in oceanbase::omt::ObWorkerProcessor::process(oceanbase::rpc::ObRequest&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #30   0x0000562e5a178351 in oceanbase::omt::ObThWorker::process_request(oceanbase::rpc::ObRequest&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #31   0x0000562e5353c8f6 in oceanbase::omt::ObThWorker::worker(long&, long&, int&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #32   0x0000562e5a1788c7 in non-virtual thunk to oceanbase::omt::ObThWorker::run(long) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #33   0x0000562e5decc6e3 in std::_Function_handler<void (), oceanbase::lib::UserThread::start()::$_14::operator()(oblib::Thread*) const::{lambda()#1}>::_M_invoke(std::_Any_data const&) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #34   0x0000562e5decc53f in eeentry(boost::context::detail::transfer_t) from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   #35   0x0000562e5e1a50cf in make_fcontext from /home/hudson/obdocker/obdocker.z1.obs0/bin/observer
   

问题原因

为了避免在异常情况下（比如切主）会有大量的并发查询 location cache， location cache 通过信号量限制了最大的并发数（通过配置项 location_cache_cpu_quota 配置，默认 20）。一部分线程执行 renew_localtion 等待获取信号量，但所有持有信号量的线程都被判定为大查询线程而被挂起了，其他的线程因为无法获取该信号量，阻塞了其他 SQL 刷新 location ，也无法去唤醒大查询线程，最终导致租户队列积压，从而影响租户登录。

问题的风险及影响

租户队列积压，登录 hung 住。

影响租户

影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。

影响的版本

OceanBase 数据库企业版 V2.2.77 GA（oceanbase-2.2.77-20210508211731）及之后版本、V3.1.2 GA（oceanbase-3.1.2-20210618150922）及之后版本、V3.2.3 GA（oceanbase-3.2.3.0-20220418212020）及之后版本、V3.2.4 GA（oceanbase-3.2.4.0-100000072022102819）及之后版本。

解决方法及规避方法

• 解决方法：

  升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V3.2.4 BP3（oceanbase-3.2.4.3-103000032023041816）。

规避方式

1. 可以通过设置 large_query_threshold 避免出现大查询请求来规避此问题。

   只有执行时间超过 large_query_threshold 的请求才会被判定为大查询请求，因此将 large_query_threshold 设置为一个较大的值可以避免出现大查询请求，如果客户对大查询功能的依赖度比较低，可以设置成一个相当大的值。设置成一个较大的值后，系统会将运行时间更久的查询判定为大查询。

   例如修改 large_query_threshold 为 10 天，那么只有执行时间超过 10 天的请求才会被判定为大查询请求：

   obclient> ALTER SYSTEM SET large_query_threshold= '10d';
   

   具体有关 large_query_threshold 的详细信息参见：large_query_threshold

2. 对于死锁，可以通过 kill session 的方式临时解死锁。