Redshift归档脚本报Serializable isolation violation错误如何解决

Redshift默认启用可序列化隔离级别来保障事务的强一致性，当两个并发事务以相反顺序访问同一组表资源、形成锁等待循环时，就会抛出该异常。你当前场景下，归档作业和午夜同时运行的常规批量加载作业都在操作abc.abc_data_365和abc.data_arch表，互相抢占资源触发了冲突。

• 1. 调整事务隔离级别（最快生效） #

  在归档脚本最开头添加语句SET TRANSACTION ISOLATION LEVEL READ COMMITTED;，读提交隔离级别下不会触发可序列化冲突，归档类离线作业使用该级别不会影响业务数据正确性。

• 2. 拆分大事务，缩小锁持有窗口 #

  你当前脚本把INSERT和DELETE放在同一个大事务中，锁持有时间过长，建议拆分为多个小事务分别提交，同时删除INSERT语句中无意义的ORDER BY语句，缩短执行时间：

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
  -- 归档数据插入，单独提交
  INSERT INTO abc.data_arch
      (col1,col2......)
  WITH cte_365days_older AS (
      SELECT * FROM abc.abc_data_365 
      WHERE abc_ts < dateadd(day, -365, current_date)
  )
  SELECT col1,col2...... 
  FROM cte_365days_older src 
  WHERE NOT EXISTS (
      SELECT 1 FROM abc.data_arch trgt WHERE src.abc_hkey = trgt.abc_hkey                          
  );
  COMMIT;
  
  -- 历史数据删除，单独提交
  DELETE FROM abc.abc_data_365 
  WHERE abc_ts < dateadd(day, -365, current_date);
  COMMIT;
  

• 3. 分批删除降低锁粒度 #

  如果表数据量很大，单条DELETE语句执行时间过长，可以改成小批量循环删除，每次删除1-10万条后提交，进一步降低锁持有时间：

  CREATE TEMP TABLE temp_del_hkeys AS 
  SELECT abc_hkey FROM abc.abc_data_365 
  WHERE abc_ts < dateadd(day, -365, current_date);
  
  <<del_loop>>
  LOOP
      DELETE FROM abc.abc_data_365 
      WHERE abc_hkey IN (SELECT abc_hkey FROM temp_del_hkeys LIMIT 10000);
      
      GET DIAGNOSTICS affected_rows = ROW_COUNT;
      COMMIT;
      
      IF affected_rows = 0 THEN
          EXIT del_loop;
      END IF;
  END LOOP del_loop;
  

• 4. 调整VACUUM执行时机 #

  VACUUM操作会持有表级排他锁，禁止和常规加载、归档作业并行执行。建议把VACUUM逻辑从当前归档脚本中剥离，单独调度到所有批量作业都结束后的更低峰时段执行。

• 5. 错峰调度（兜底方案） #

  如果以上优化后仍偶发冲突，直接调整归档作业的执行时间，和常规批量加载作业的时间窗口完全错开，从根源避免并行资源争抢。

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