ORA-02049在查询数据时等待锁并最终超时

应用开发者专区
#1

SQL: SELECT COUNT() FROM (select c. from((select u.CLIENT_NAME, u.MOBILE_TEL, u.ID_NO, u.ID_KIND, u.CHANNEL_CODE, u.ACTIVITY_NO, u.ACTIVITY_NAME, u.MARKETING_TEAM, u.BRANCH_NO branch_name, u.branch_no, u.AUDIT_OPERATOR_NO, u.AUDIT_OPERATOR_NAME, u.audit_finish_datetime, u.REVIEW_OPERATOR_NO, u.REVIEW_OPERATOR_NAME, u.review_finish_datetime, u.double_operator_no, u.double_operator_name, u.double_finish_datetime, u.REQUEST_DATETIME, u.VIDEO_TYPE, u.mobile_location, u.open_channel, u.channel_name, u.app_id, (case when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘1’ then ‘待见证’ when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘2’ then ‘见证中’ when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘a’ then ‘见证挂起’ when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘3’ then ‘见证通过’ when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘4’ then ‘见证驳回’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘1’ then ‘待复核’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘2’ then ‘复核中’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘a’ then ‘复核挂起’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘3’ then ‘复核通过’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘4’ then ‘复核驳回’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘1’ then ‘待二次复核’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘2’ then ‘二次复核中’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘a’ then ‘二次复核挂起’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘3’ then ‘二次复核通过’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘4’ then ‘二次复核驳回’ END) task_type, U.busin_type ,b.serial_id,b.request_no,b.create_datetime,b.deal_datetime, (case when b.task_status in (‘1’,‘2’,‘a’) then ‘show’ when b.task_status in (‘3’,‘4’) then ‘noshow’ end ) show_button ,b.task_source ,b.task_status from BUSINFLOWTASK b left join USERQUERYEXTINFO u on b.REQUEST_NO = u.REQUEST_NO where 1=1 and b.task_status not in (‘3’,‘4’) and b.task_type IN ( ? , ? , ? ) and u.branch_no IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) and( (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) ) and (b.operator_no = ? or (b.operator_no = ’ ’ and b.push_flag=‘8’)) and (b.not_allow_auditor = ’ ’ or instr(b.not_allow_auditor,?) = 0) and b.task_status in (‘1’,‘2’,‘a’,‘3’,‘4’,‘b’) ) UNION ALL (select u.CLIENT_NAME, u.MOBILE_TEL, u.ID_NO, u.ID_KIND, u.CHANNEL_CODE, u.ACTIVITY_NO, u.ACTIVITY_NAME, u.MARKETING_TEAM, u.BRANCH_NO branch_name, u.branch_no, u.AUDIT_OPERATOR_NO, u.AUDIT_OPERATOR_NAME, u.audit_finish_datetime, u.REVIEW_OPERATOR_NO, u.REVIEW_OPERATOR_NAME, u.review_finish_datetime, u.double_operator_no, u.double_operator_name, u.double_finish_datetime, u.REQUEST_DATETIME, u.VIDEO_TYPE, u.mobile_location, u.open_channel, u.channel_name, u.app_id, (case when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘1’ then ‘待见证’ when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘2’ then ‘见证中’ when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘a’ then ‘见证挂起’ when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘3’ then ‘见证通过’ when b.TASK_TYPE = ‘audit’ and b.TASK_STATUS = ‘4’ then ‘见证驳回’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘1’ then ‘待复核’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘2’ then ‘复核中’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘a’ then ‘复核挂起’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘3’ then ‘复核通过’ when b.TASK_TYPE = ‘review’ and b.TASK_STATUS = ‘4’ then ‘复核驳回’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘1’ then ‘待二次复核’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘2’ then ‘二次复核中’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘a’ then ‘二次复核挂起’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘3’ then ‘二次复核通过’ when b.TASK_TYPE = ‘secondary_review’ and b.TASK_STATUS = ‘4’ then ‘二次复核驳回’ END) task_type, U.busin_type ,b.serial_id,b.request_no,b.create_datetime,b.deal_datetime, (case when b.task_status in (‘1’,‘2’,‘a’) then ‘show’ when b.task_status in (‘3’,‘4’) then ‘noshow’ end ) show_button ,b.task_source ,b.task_status from BUSINFLOWTASK b left join USERQUERYEXTINFO u on b.serial_id = u.REQUEST_NO where 1=1 and u.is_snapshot = ‘1’ and b.task_status in (‘3’,‘4’) and b.task_type IN ( ? , ? , ? ) and u.branch_no IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) and( (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) OR (b.task_type = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 1) AND b.task_status = REGEXP_SUBSTR(?, ‘[^-]+’, 1, 2)) ) and (b.operator_no = ? or (b.operator_no = ’ ’ and b.push_flag=‘8’)) and (b.not_allow_auditor = ’ ’ or instr(b.not_allow_auditor,?) = 0) and b.task_status in (‘1’,‘2’,‘a’,‘3’,‘4’,‘b’) )) c order by decode(c.task_status,‘a’,‘6’,‘2’,‘5’,‘1’,‘4’,‘1’) desc , c.create_datetime asc) TOTAL

Cause: java.sql.SQLTransientConnectionException: (conn=3508472) ORA-02049: timeout: distributed transaction waiting for lock

; (conn=3508472) ORA-02049: timeout: distributed transaction waiting for lock; nested exception is java.sql.SQLTransientConnectionException: (conn=3508472) ORA-02049: timeout: distributed transaction waiting for lock

#3

建议你通过以下方式寻求帮助：
1.如你所在的企业客户已签署OceanBase企业版销售合同，请你联系客户经理；
2.如你所在的企业客户尚未签署OceanBase企业版销售合同，你可通过OceanBase官网商务咨询页面留下你的联系方式，OceanBase企业版的业务顾问会在一个工作日内与你联系。
OceanBase官网商务咨询

#4

ORA-02000 ~ ORA-04999 -OceanBase 数据库-OceanBase文档中心-分布式数据库使用文档

ORA-02049：timeout: distributed transaction waiting for lock

  • OceanBase 错误码：6004
  • SQLSTATE：HY000
  • 错误原因：当前语句需要读取一行数据时，如果该行数据被另一个事务修改，并且这个事务正在提交，那么该事务需要等待另一个事务提交完成后决定是否读取该行数据，读语句等待时间超时则报超时错误。
  • 解决方式：使用 oceanbase.__all_virtual_trans_stat 虚拟表查询当前正在提交的事务，根据事务的 ID 在对应机器上查看日志，进一步分析事务卡在提交阶段未能完成的原因。
#5

检查 => 查询数据过程中业务是否有 大事务 或者 长事务？
海量数据查询、抽取，尽量避开事务高峰期。无论使用任何数据库，不建议再高峰期拖数据。

1 个赞