Redshift中添加LOCK仍遇Serializable隔离违例问题求助

你遇到的问题核心是锁的时机不对，导致无法阻止事务执行过程中的并发干扰。

问题根源 #

在Serializable隔离级别下，事务会对读取的数据加范围锁来保证最高级别的一致性，但你的现有代码是先读取表数据、执行计算，之后才加表锁——这意味着在加锁前，其他事务已经有可能修改了incremental_table的数据，当你的事务后续执行更新时，就会触发隔离违反错误。

修正方案 #

1. 提前加表锁（最直接的调整）

必须把lock incremental_table;放到事务的最开头，在任何读取、更新操作之前执行，确保整个事务周期内表被独占锁定：

begin
 lock incremental_table;
 -- get the date from incremental_table
 -- do delta updates
 -- update incremental_table;
end 

2. 改用行级锁优化性能（推荐）

如果表数据量较大，整表锁会严重影响并发性能，可以针对需要更新的行精准加锁，避免锁定无关数据：

begin
 -- 读取时直接锁定目标行，替代整表锁
 SELECT [需要的字段] FROM incremental_table WHERE [过滤条件] FOR UPDATE;
 -- do delta updates
 -- update incremental_table SET [更新字段] WHERE [过滤条件];
end 

3. 评估隔离级别必要性

如果业务场景不需要最高级别的Serializable隔离（比如允许幻读），可以将事务隔离级别降低到REPEATABLE READ，能大幅减少这类锁冲突的概率，同时满足多数业务的一致性要求。

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