--- title: 锁冲突问题排查-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于锁冲突问题排查相关的常见问题和使用技巧，帮助您快速解决锁冲突问题排查的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 锁冲突问题排查 更新时间：2026-06-10 09:51 适用版本： V3.2.x、V3.1.x、V2.2.x、V2.1.x、V1.4.x 内容类型：Troubleshoot 在 OceanBase 数据库的使用场景中，行锁冲突是经常遇到的问题之一。通常存在两种冲突场景：读写冲突和写写冲突。 这类问题通常因为业务逻辑对相同数据对象的访问产生了时空交集导致。若出现事务长期抢锁失败的场景时，需要相关人员进行关注。 ## 背景信息 - 锁冲突超时时间计算规则为：由于 MIN(lock_wait_timeout, ob_query_timeout) 中 lock_wait_timeout 应该是一个 mock 的 mysql 兼容变量，没有实际作用。正常的锁冲突超时计算规则应该是 MIN(ob_trx_lock_timeout, ob_trx_timeout,ob_query_timeout)，不过通常情况下 ob_trx_timeout 都会大于 ob_query_timeout，所以一般只比较 MIN(ob_trx_lock_timeout, ob_query_timeout) 即可。 - lock_wait_timeout：在 OceanBase 中这应该对应 ob_trx_lock_timeout 参数，且默认值为 -1，即锁冲突无单独的超时时间，需要等到语句或事务超时。 - ob_query_timeout：语句超时时间，variables 配置项，默认 10s。 - 悬挂事务：指进入 `commit` 阶段但是长时间未完成的事务或完成 xa prepare 但长期未进行 xa commit 或 rollback 的 xa 事务。 - 长事务：指长期没有 `commit` 的事务。 ## 排查手段 ### 虚拟表查询 为了排查锁冲突问题，OceanBase 数据库提供了多个排查工具，具体请参见 [行锁问题排查工具使用介绍](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001012955)。 我们可以通过执行的 SQL 查询 `__all_virtual_sql_audit` 获取 session 和事务信息。以便进行匹配。 ```shell obclient> select * from __all_virtual_sql_audit where query_sql like '%***%'; obclient> desc oceanbase.__all_virtual_sql_audit +---------------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------------------+---------------------+------+-----+---------+-------+ | svr_ip | varchar(46) | NO | PRI | NULL | | | svr_port | bigint(20) | NO | PRI | NULL | | | tenant_id | bigint(20) | NO | PRI | NULL | | | request_id | bigint(20) | NO | PRI | NULL | | | trace_id | varchar(128) | NO | | NULL | | | client_ip | varchar(46) | NO | | NULL | | | client_port | bigint(20) | NO | | NULL | | | tenant_name | varchar(64) | NO | | NULL | | | effective_tenant_id | bigint(20) | NO | | NULL | | | user_id | bigint(20) | NO | | NULL | | | user_name | varchar(64) | NO | | NULL | | | db_id | bigint(20) unsigned | NO | | NULL | | | db_name | varchar(128) | NO | | NULL | | | sql_id | varchar(32) | NO | | NULL | | | query_sql | longtext | NO | | NULL | | | plan_id | bigint(20) | NO | | NULL | | | affected_rows | bigint(20) | NO | | NULL | | | return_rows | bigint(20) | NO | | NULL | | | partition_cnt | bigint(20) | NO | | NULL | | | ret_code | bigint(20) | NO | | NULL | | | qc_id | bigint(20) unsigned | NO | | NULL | | | dfo_id | bigint(20) | NO | | NULL | | | sqc_id | bigint(20) | NO | | NULL | | | worker_id | bigint(20) | NO | | NULL | | | event | varchar(64) | NO | | NULL | | | p1text | varchar(64) | NO | | NULL | | | p1 | bigint(20) unsigned | NO | | NULL | | | p2text | varchar(64) | NO | | NULL | | | p2 | bigint(20) unsigned | NO | | NULL | | | p3text | varchar(64) | NO | | NULL | | | p3 | bigint(20) unsigned | NO | | NULL | | | level | bigint(20) | NO | | NULL | | | wait_class_id | bigint(20) | NO | | NULL | | | wait_class# | bigint(20) | NO | | NULL | | | wait_class | varchar(64) | NO | | NULL | | | state | varchar(19) | NO | | NULL | | | wait_time_micro | bigint(20) | NO | | NULL | | | total_wait_time_micro | bigint(20) | NO | | NULL | | | total_waits | bigint(20) | NO | | NULL | | | rpc_count | bigint(20) | NO | | NULL | | | plan_type | bigint(20) | NO | | NULL | | | is_inner_sql | tinyint(4) | NO | | NULL | | | is_executor_rpc | tinyint(4) | NO | | NULL | | | is_hit_plan | tinyint(4) | NO | | NULL | | | request_time | bigint(20) | NO | | NULL | | | elapsed_time | bigint(20) | NO | | NULL | | | net_time | bigint(20) | NO | | NULL | | | net_wait_time | bigint(20) | NO | | NULL | | | queue_time | bigint(20) | NO | | NULL | | | decode_time | bigint(20) | NO | | NULL | | | get_plan_time | bigint(20) | NO | | NULL | | | execute_time | bigint(20) | NO | | NULL | | | application_wait_time | bigint(20) unsigned | NO | | NULL | | | concurrency_wait_time | bigint(20) unsigned | NO | | NULL | | | user_io_wait_time | bigint(20) unsigned | NO | | NULL | | | schedule_time | bigint(20) unsigned | NO | | NULL | | | row_cache_hit | bigint(20) | NO | | NULL | | | bloom_filter_cache_hit | bigint(20) | NO | | NULL | | | block_cache_hit | bigint(20) | NO | | NULL | | | disk_reads | bigint(20) | NO | | NULL | | | execution_id | bigint(20) | NO | | NULL | | | session_id | bigint(20) unsigned | NO | | NULL | | | retry_cnt | bigint(20) | NO | | NULL | | | table_scan | tinyint(4) | NO | | NULL | | | consistency_level | bigint(20) | NO | | NULL | | | memstore_read_row_count | bigint(20) | NO | | NULL | | | ssstore_read_row_count | bigint(20) | NO | | NULL | | | data_block_read_cnt | bigint(20) | NO | | NULL | | | data_block_cache_hit | bigint(20) | NO | | NULL | | | index_block_read_cnt | bigint(20) | NO | | NULL | | | index_block_cache_hit | bigint(20) | NO | | NULL | | | blockscan_block_cnt | bigint(20) | NO | | NULL | | | blockscan_row_cnt | bigint(20) | NO | | NULL | | | pushdown_storage_filter_row_cnt | bigint(20) | NO | | NULL | | | request_memory_used | bigint(20) | NO | | NULL | | | expected_worker_count | bigint(20) | NO | | NULL | | | used_worker_count | bigint(20) | NO | | NULL | | | sched_info | varchar(16384) | YES | | NULL | | | fuse_row_cache_hit | bigint(20) | NO | | NULL | | | user_client_ip | varchar(46) | NO | | NULL | | | ps_client_stmt_id | bigint(20) | NO | | NULL | | | ps_inner_stmt_id | bigint(20) | NO | | NULL | | | transaction_id | bigint(20) | NO | | NULL | | | snapshot_version | bigint(20) unsigned | NO | | NULL | | | snapshot_source | varchar(16) | NO | | NULL | | | request_type | bigint(20) | NO | | NULL | | | is_batched_multi_stmt | tinyint(4) | NO | | NULL | | | ob_trace_info | varchar(4096) | NO | | NULL | | | plan_hash | bigint(20) unsigned | NO | | NULL | | | user_group | bigint(20) | YES | | NULL | | | lock_for_read_time | bigint(20) | NO | | NULL | | | params_value | longtext | NO | | NULL | | | rule_name | varchar(256) | NO | | NULL | | | proxy_session_id | bigint(20) unsigned | NO | | NULL | | | tx_internal_route_flag | bigint(20) unsigned | NO | | NULL | | | partition_hit | tinyint(4) | NO | | NULL | | | tx_internal_route_version | bigint(20) unsigned | NO | | NULL | | +---------------------------------+---------------------+------+-----+---------+-------+ ``` ### 搜索日志 当无法通过虚拟表获取相应信息时，可以通过 observer.log 进行获取。 #### 遇到 -6004 报错可以执行如下命令 ```shell grep "lock_for_read fail" * ``` 展示的日志如下： ```shell observer.log:[2020-06-19 19:36:00.681313] WARN [STORAGE.TRANS] lock_for_read (ob_mvcc_row.cpp:1120) [39644][3862][Y72F564587653-0005A86771DBF02A] [lt=22] [dc=0] lock_for_read fail((ret=-6004, ctx=alloc_type=0 ctx_descriptor=7941866 ... pkey={tid:1102810162659511, partition_id:0, part_cnt:0} trans_id={hash:10466628054523668402, inc:4098058, addr:"xxx.xxx.xxx.xxx:xxx", t:1592566558782644} data_relocated=0 relocate_cnt=0 truncate_cnt=0 trans_mem_total_size=0 callback_alloc_count=0 callback_free_count=0 callback_mem_used=0 checksum_log_id=0, *key=table_id=1102810162659511 rowkey_object=[{"BIGINT":1003},{"BIGINT":1102810162709409},{"BIGINT":519}, {"VARCHAR":"100.88.118.83", collation:"utf8mb4_general_ci"},{"BIGINT":29429}] , conflict_id=3155625, conflict_ctx="alloc_type=0 ctx_descriptor=3155625 trans_start_time=1592541786500368 min_table_version=640 max_table_version=640 ... stmt_timeout=1897924 abs_lock_wait_timeout=1592541788399623 row_purge_version=0 lock_wait_start_ts=0 trx_lock_timeout=-1 end_code=0 is_readonly=false ref=0 pkey={tid:1102810162659511, partition_id:0, part_cnt:0} trans_id={hash:13185011132747685805, inc:1187948, addr:"xxx.xxx.xxx.xxx:xxx", t:1592541786499600} data_relocated=0 ...) ``` - 其中 `conflict_ctx` 展示的是持有行锁，一直提交不成功的事务上下文信息。 - `rowkey_object` 展示的是冲突的行。 #### 遇到 -6003 或 -6005 报错可以执行如下命令 ```shell grep "lock_for_write conflict" * ``` 展示的日志如下： ```shell observer.log.20200619112908:[2020-06-19 11:28:39.827864] WARN [STORAGE.TRANS] on_wlock_retry (ob_memtable_context.cpp:397) [38133][2238][Y72F564587653-0005A8676CC84C97] [lt=12] [dc=0] lock_for_write conflict((*this=alloc_type=0 ctx_descriptor=87954 ... pkey={tid:1102810162659511, partition_id:0, part_cnt:0} trans_id={hash:17908784496721112571, inc:20376, addr:"xxx.xxx.xxx.xxx:xxx", t:1592537319825033} data_relocated=0 relocate_cnt=0 truncate_cnt=0 trans_mem_total_size=504 callback_alloc_count=6 callback_free_count=0 callback_mem_used=1583 checksum_log_id=0, key=table_id=1102810162659511 ... rowkey_object=[{"BIGINT":1003},{"BIGINT":1102810162709405},{"BIGINT":433}, {"VARCHAR":"xxx.xxx.xxx.xxx", collation:"utf8mb4_general_ci"},{"BIGINT":29427}] , ... conflict_ctx="alloc_type=0 ctx_descriptor=87953 pkey={tid:1102810162659511, partition_id:0, part_cnt:0} trans_id={hash:11004138217283817019, inc:17824, addr:"xxx.xxx.xxx.xxx:xxx", t:1592537319809789} ``` - `conflict_ctx` 展示的是持有行锁的事务。 ## 处理方式 找到持锁事务后，我们可以根据起状态进行相应的应急处理，具体请参考： [悬挂事务&长事务应急文档]() [[XA] 悬挂事务分支应急手册]() [OceanBase 数据库 V4.2 版本，关于锁冲突问题的排查手册](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000664387?back=kb) 上一篇 [租户分区数上限计算方式](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000990095) 下一篇 [行锁问题排查介绍](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001012955) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线