如何在6张表增量加载期间阻止读查询访问以避免数据不一致？

方法1：显式添加表级排他锁 #

在ETL事务启动时，先对所有6张目标表添加排他表锁，确保任何读请求都会被阻塞，直到整个ETL事务完成并释放锁。不同数据库的语法略有差异：

MySQL/MariaDB #

START TRANSACTION;
-- 对6张表加写锁，阻塞所有读操作
LOCK TABLES table1 WRITE, table2 WRITE, table3 WRITE, table4 WRITE, table5 WRITE, table6 WRITE;

-- 依次执行每张表的增量加载逻辑
-- 加载table1...
-- 加载table2...
-- ...加载table6...

COMMIT;
UNLOCK TABLES;

注意：LOCK TABLES会释放当前会话之前持有的所有表锁，且锁持有期间，当前会话仅能访问被锁定的表，需确保ETL逻辑仅操作这6张表。

SQL Server #

BEGIN TRANSACTION;
-- 对每张表添加排他表锁，锁会保持到事务结束
SELECT 1 FROM table1 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0;
SELECT 1 FROM table2 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0;
SELECT 1 FROM table3 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0;
SELECT 1 FROM table4 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0;
SELECT 1 FROM table5 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0;
SELECT 1 FROM table6 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0;

-- 执行每张表的增量加载
-- 加载table1...
-- 加载table2...
-- ...加载table6...

COMMIT TRANSACTION;

TABLOCKX是排他表锁，HOLDLOCK确保锁在事务周期内持续生效，WHERE 1=0仅用于获取锁，不会返回数据。

PostgreSQL #

BEGIN TRANSACTION;
-- 对每张表加最高级别排他锁，阻塞所有读/写操作
LOCK TABLE table1, table2, table3, table4, table5, table6 IN ACCESS EXCLUSIVE MODE;

-- 执行增量加载逻辑
-- 加载table1...
-- 加载table2...
-- ...加载table6...

COMMIT TRANSACTION;

方法2：影子表切换（适用于允许短暂不可用窗口的场景） #

如果业务无法接受6分钟的全程阻塞，可采用影子表方案，仅在切换瞬间短暂锁定表：

1. 创建与目标表结构一致的临时影子表（如table1_staging）。
2. 在影子表上完成所有增量加载，期间原表可正常被读取。
3. 所有影子表加载完成后，通过原子重命名操作切换原表与影子表，确保数据一致性。

以MySQL为例：

-- 先完成所有影子表的增量加载
INSERT INTO table1_staging SELECT ...;
INSERT INTO table2_staging SELECT ...;
-- ...加载完6张影子表

-- 原子切换（瞬间完成，阻塞时间极短）
RENAME TABLE table1 TO table1_old, table1_staging TO table1;
RENAME TABLE table2 TO table2_old, table2_staging TO table2;
-- ...切换所有6张表

-- 可选：清理旧表
DROP TABLE table1_old, table2_old, ...;

关键注意事项 #

• 阻塞时长评估：方法1会导致约6分钟的完全阻塞，若业务对可用性要求高，优先选择方法3。
• 锁异常处理：添加锁后需确保事务能正常提交，可设置事务超时时间，避免因异常导致锁长期占用。
• 性能影响：表级锁会影响整张表的所有操作，需确认ETL执行期间无其他必要的写操作。

内容的提问来源于stack exchange，提问作者variable