# 常见问题 #### 查询与回放冲突取消查询 - 问题现象：当检测到查询与回放冲突时，回放线程将等待参数max_standby_streaming_delay设定的延迟时间，超时后向冲突线程发送取消指令，导致查询被终止并返回报错信息。 报错详情如下： ``` User was holding shared buffer pin for too long. 触发场景：备库在回放VACUUM相关类型日志时，若回放线程需获取的buffer因查询线程持有pin锁而等待，且此时存在活跃查询访问该数据页，则触发buffer pin冲突。系统将等待max_standby_streaming_delay参数指定时长后取消冲突查询（通常因冲突查询提前完成而极少触发）。 User was holding a relation lock for too long. 触发场景：DROP TABLE、TRUNCATE或多数ALTER TABLE等操作会获取与SELECT相冲突的锁，若备库在查询活跃的表上回放此类锁操作，将触发锁冲突，等待时长由max_standby_streaming_delay参数控制。 User was or might have been using tablespace that must be dropped. 触发场景：主库删除表空间时，备库的查询恰好在向该表空间写临时文件，则发生冲突。 User query might have needed to see row versions that must be removed. 触发场景：参数max_standby_streaming_delay决定查询与回放冲突的处理策略。备机暂停日志回放直至达到参数指定时长上限，若冲突查询仍持续运行，则终止查询并抛出错误。 User transaction caused buffer deadlock with recovery. 触发场景：回放线程与备机读线程形成死锁时，系统在出现冲突未达到max_standby_streaming_delay阈值前，每5秒执行一次死锁检测。 User was connected to a database that must be dropped. 触发场景：主库执行DROP DATABASE删除数据库时，若备库存在活动会话连接至该数据库，则产生冲突。 ``` - 原因分析：在查询与回放冲突场景下，系统优先保障回放功能正常执行，会取消查询。可能引发冲突的回放操作类型包括：VACUUM、执行8级锁的DDL/DCL语句、DROP DATABASE、DROP TABLESPACE。 ![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 极致RTO方案已针对VACUUM、DROP TABLE、TRUNCATE TABLE（仅TABLE）、DROP DATABASE解除冲突限制。 - 在极致RTO回放方案下，主机执行常用DDL时，备机回放对应DDL过程中可能出现的阻塞情况： 表1 | 主节点操作 || 验证结果（当前用户、表、库） | 操作语句 | 验证结果（非当前用户、表、库） | 操作语句 | |:---|---|:---|:---|:---|:---| | add/drop/spit/exchange partition | add partition | 不阻塞 | ALTER TABLE test_p1 ADD PARTITION p4 VALUES LESS THAN (50); | 不阻塞 | ALTER TABLE test_p2 ADD PARTITION p4 VALUES LESS THAN (50); | | add/drop/spit/exchange partition | drop partition | 阻塞 | ALTER TABLE test_p1 DROP PARTITION p3; | 不阻塞 | ALTER TABLE test_p2 DROP PARTITION p3; | | add/drop/spit/exchange partition | split partition | 阻塞 | ALTER TABLE test_p1 SPLIT PARTITION p4 INTO (PARTITION p4_1 VALUES LESS THAN (40), PARTITION p4_2 VALUES LESS THAN (50)); | 不阻塞 | ALTER TABLE test_p2 SPLIT PARTITION p4 INTO (PARTITION p4_1 VALUES LESS THAN (40), PARTITION p4_2 VALUES LESS THAN (50)); | | add/drop/spit/exchange partition | exchange partition | 阻塞 | CREATE TABLE test_ep1(col1 INT,col2 INT); | 不阻塞 | CREATE TABLE test_ep2(col1 INT,col2 INT); | | add/drop/spit/exchange partition | exchange partition | 阻塞 | INSERT INTO test_ep1 VALUES (GENERATE_SERIES(1,5), 1000); | 不阻塞 | INSERT INTO test_ep2 VALUES (GENERATE_SERIES(1,5), 1000); | | add/drop/spit/exchange partition | exchange partition | 阻塞 | ALTER TABLE test_p1 EXCHANGE PARTITION (p1) WITH TABLE test_ep1 VERBOSE; | 不阻塞 | ALTER TABLE test_p2 EXCHANGE PARTITION (p1) WITH TABLE test_ep2 VERBOSE; | | create/alter/drop role/user | creat role | 不阻塞 | CREATE ROLE tpcc1 WITH LOGIN PASSWORD '\*\*\*\*\*\*\*\*'; | 不阻塞 | CREATE ROLE tpcc2 WITH LOGIN PASSWORD '\*\*\*\*\*\*\*\*'; | | create/alter/drop role/user | alter role | 不阻塞 | ALTER ROLE tpcc1 PASSWORD '\*\*\*\*\*\*\*\*'; | 不阻塞 | ALTER ROLE tpcc2 PASSWORD '\*\*\*\*\*\*\*\*'; | | create/alter/drop role/user | drop role | 阻塞 | DROP ROLE tpcc1; | 不阻塞 | DROP ROLE tpcc2; | | create/alter/drop role/user | drop role | 阻塞 | CREATE ROLE admin WITH LOGIN PASSWORD '\*\*\*\*\*\*\*\*'; | 不阻塞 | DROP ROLE tpcc2; | | create/alter/drop role/user | drop role | 阻塞 | ALTER SCHEMA tpcc1 OWNER TO admin; | 不阻塞 | DROP ROLE tpcc2; | | create/alter/drop role/user | drop role | 阻塞 | ALTER TABLE t1 OWNER TO admin; | 不阻塞 | DROP ROLE tpcc2; | | create/alter/drop role/user | drop role | 阻塞 | REVOKE ALL PRIVILEGES ON SCHEMA public FROM tpcc1; | 不阻塞 | DROP ROLE tpcc2; | | create/alter/drop role/user | drop role | 阻塞 | DROP ROLE tpcc1; | 不阻塞 | DROP ROLE tpcc2; | | create/alter/drop role/user | creat user | 不阻塞 | create user tpcc identified by '\*\*\*\*\*\*\*\*'; | 不阻塞 | create user tpcc3 identified by '\*\*\*\*\*\*\*\*'; | | create/alter/drop role/user | alter user | 不阻塞 | ALTER USER tpcc IDENTIFIED BY "\*\*\*\*\*\*\*\*"; | 不阻塞 | ALTER USER tpcc3 IDENTIFIED BY "\*\*\*\*\*\*\*\*"; | | create/alter/drop role/user | drop user | 阻塞 | drop user tpcc CASCADE; | 不阻塞 | drop user tpcc3 CASCADE; | | create/alter/drop schema/tablespace/table/index | create schema | 不阻塞 | CREATE SCHEMA school; | 不阻塞 | CREATE SCHEMA school1; | | create/alter/drop schema/tablespace/table/index | alter schema | 不阻塞 | ALTER SCHEMA school RENAME TO middle_school; | 不阻塞 | ALTER SCHEMA school1 RENAME TO middle_school1; | | create/alter/drop schema/tablespace/table/index | drop schema | 阻塞 | DROP SCHEMA middle_school CASCADE; | 不阻塞 | DROP SCHEMA middle_school1; | | create/alter/drop schema/tablespace/table/index | create tablespace | 不阻塞 | create tablespace tablespace_school LOCATION '/home/perfadm'; | 不阻塞 | create tablespace tablespace_school3 LOCATION '/var/standard/var/lib'; | | create/alter/drop schema/tablespace/table/index | alter tablespace | 阻塞 | create tablespace tablespace_school2 LOCATION '/var/standard/var/lib'; | 不阻塞 | CREATE TABLE students3 ( id INT, name VARCHAR2(100), age INT) TABLESPACE tablespace_school3; | | create/alter/drop schema/tablespace/table/index | alter tablespace | 阻塞 | ALTER TABLE students SET TABLESPACE tablespace_school2; | 不阻塞 | create tablespace tablespace_school2 LOCATION '/var/standard/usr/local/core'; | | create/alter/drop schema/tablespace/table/index | alter tablespace | 阻塞 | - | 不阻塞 | ALTER TABLE students3 SET TABLESPACE tablespace_school2; | | create/alter/drop schema/tablespace/table/index | drop tablespace | 不阻塞 | DROP TABLESPACE tablespace_school2; | 阻塞 | DROP TABLESPACE tablespace_school2; | | create/alter/drop schema/tablespace/table/index | create table | 不阻塞 | create table t1 (id int); | 不阻塞 | create table t2 (id int); | | create/alter/drop schema/tablespace/table/index | alter table | 阻塞 | ALTER TABLE t1 ADD COLUMN age INT; | 不阻塞 | ALTER TABLE t2 ADD COLUMN age INT; | | create/alter/drop schema/tablespace/table/index | drop table | 阻塞 | drop table t1; | 不阻塞 | drop table t2; | | create/alter/drop schema/tablespace/table/index | create index | 不阻塞 | CREATE INDEX t1_index1 ON t1(id); | 不阻塞 | CREATE INDEX t2_index1 ON t2(id); | | create/alter/drop schema/tablespace/table/index | alter index | 阻塞 | ALTER INDEX t1_index1 SET (fillfactor = 80); | 不阻塞 | ALTER INDEX t2_index1 SET (fillfactor = 80); | | create/alter/drop schema/tablespace/table/index | drop index | 阻塞 | drop INDEX t1_index1; | 不阻塞 | drop INDEX t2_index1; | | create/alter/drop sequence | create sequence | 不阻塞 | CREATE SEQUENCE seq1 START 101 INCREMENT 10; | 不阻塞 | CREATE SEQUENCE seq3 START 101 INCREMENT 10; | | create/alter/drop sequence | alter sequence | 不阻塞 | ALTER SEQUENCE seq1 RESTART WITH 200; | 不阻塞 | ALTER SEQUENCE seq3 RESTART WITH 200; | | create/alter/drop sequence | alter sequence | 不阻塞 | ALTER SEQUENCE seq1 INCREMENT BY 2; | 不阻塞 | ALTER SEQUENCE seq3 INCREMENT BY 2; | | create/alter/drop sequence | drop sequence | 阻塞 | DROP SEQUENCE seq1; | 不阻塞 | DROP SEQUENCE seq3; | | grant/revoke/alter default privileges/reassign owned | grant | 不阻塞 | GRANT SELECT ON TABLE t1 TO tpcc; | 不阻塞 | GRANT SELECT ON TABLE t1 TO tpcc; | | grant/revoke/alter default privileges/reassign owned | grant | 不阻塞 | GRANT SELECT, INSERT ON t1 TO tpcc; | 不阻塞 | GRANT SELECT, INSERT ON t1 TO tpcc; | | grant/revoke/alter default privileges/reassign owned | revoke | 不阻塞 | REVOKE SELECT ON TABLE t1 FROM tpcc; | 不阻塞 | REVOKE SELECT ON TABLE t1 FROM tpcc; | | grant/revoke/alter default privileges/reassign owned | alter default privileges | 不阻塞 | ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tpcc; | 不阻塞 | ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tpcc; | | grant/revoke/alter default privileges/reassign owned | reassign owned | 阻塞 | CREATE ROLE admin WITH LOGIN PASSWORD '\*\*\*\*\*\*\*\*'; | 不阻塞 | CREATE ROLE admin WITH LOGIN PASSWORD '\*\*\*\*\*\*\*\*'; | | grant/revoke/alter default privileges/reassign owned | reassign owned | 阻塞 | REASSIGN OWNED BY tpcc TO admin; | 不阻塞 | REASSIGN OWNED BY tpcc TO admin; | | truncate partition | truncate partition | 阻塞 | ALTER TABLE test_p1 TRUNCATE PARTITION p1; | 不阻塞 | CREATE TABLE test_p2 (col1 INT, col2 INT) PARTITION BY RANGE (col1) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30) ); | | truncate partition | truncate partition | 阻塞 | ALTER TABLE test_p1 TRUNCATE PARTITION p1; | 不阻塞 | INSERT INTO test_p2 (col1, col2) VALUES (5, 10); | | truncate partition | truncate partition | 阻塞 | ALTER TABLE test_p1 TRUNCATE PARTITION p1; | 不阻塞 | INSERT INTO test_p2 (col1, col2) VALUES (12, 20); | | truncate partition | truncate partition | 阻塞 | ALTER TABLE test_p1 TRUNCATE PARTITION p1; | 不阻塞 | INSERT INTO test_p2 (col1, col2) VALUES (20, 30); | | truncate partition | truncate partition | 阻塞 | ALTER TABLE test_p1 TRUNCATE PARTITION p1; | 不阻塞 | ALTER TABLE test_p2 TRUNCATE PARTITION p1; | | reindex | reindex | 阻塞 | REINDEX TABLE t1; | 不阻塞 | REINDEX TABLE t2; | - 处理方法：需通过业务层面对受影响查询进行重试。 #### 备机读场景下8级锁冲突引发死锁问题 - 问题现象：当主机端短时间内生成多条8级锁日志时，备机读操作可能触发会话线程死锁并抛出错误码"GAUSS-23827: deadlock detected."。 - 问题原因：备机回放线程在连续处理8级锁日志时，先持有relation1的8级锁并尝试获取relation2的8级锁，而查询线程持有relation2的1级锁并试图获取relation1的1级锁，形成相互等待的锁循环。会产生8级锁的语句类型：ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL，或未声明其他锁模式的LOCK TABLE操作。 - 处理方法：建议业务侧实施重试应对该异常场景。 #### 备机索引扫描与日志回放锁冲突 - 问题现象：在串行回放与并行回放场景下，备机执行读业务时报错："Index scan conflicts with replay. Try again later. The original maxoff is , but now it is."。 - 问题原因：备机查询线程在执行索引扫描时，首先对索引页面加读锁，并在扫描过程中逐条检查元组的可见性。若该元组对应事务处于提交中（committing）状态，则需等待其提交后再进行判断。由于备机事务提交依赖日志回放线程推进，而回放线程在此期间会对索引页面执行修改，因此需要加锁。查询线程在等待过程中必须主动释放已获取的索引页锁，否则将形成查询线程等待回放线程提交事务、同时回放线程等待查询线程释放锁的死锁局面。 - 规避方法：通过业务端进行重试。 #### 主备元数据异步引发备机查询异常 - 问题现象：在串行回放与并行回放场景下，备机执行读业务时报错："relation \\"\\\" does not exist"、"column \\"\\\" does not exist"。 - 问题原因：DDL操作会修改元数据，导致主备元数据不一致。此时备机读操作将因元数据不一致触发上述错误或其他错误信息。此场景下需等待备机完成对应元数据日志回放（与主机元数据状态一致后），备机读业务方能正常执行。 - 规避方案：确保主备元数据同步完成后执行备机读操作。