Cohort generation on RedShift - serializable isolation violation on table Error: 1023

Expected behavior

When generating > 1 cohort on a single data source, the cohort generation processes finish without error on RedShift.

Actual behavior

Generating > 1 cohort on RedShift using WebAPI v2.10.0 generates the following exception:

org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE #Codesets  (codeset_id int NOT NULL,
  concept_id bigint NOT NULL
)
DISTSTYLE ALL; INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 4 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
( 
  select concept_id from cdm_truven_ccae_v1676.CONCEPT where concept_id in (45774435,44816332,40170911,1583722,793143,44506754)
UNION  select c.concept_id
  from cdm_truven_ccae_v1676.CONCEPT c
  join cdm_truven_ccae_v1676.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (45774435,44816332,40170911,1583722,793143,44506754)
  and c.invalid_reason is null

) I
LEFT JOIN
(
  select concept_id from cdm_truven_ccae_v1676.CONCEPT where concept_id in (36410293,36410289,1718706,1718711,1718604,1718712,1718713,1718705,1718709,21174087,1718605,1718606,44191345,36055923,36055919)
UNION  select c.concept_... [truncated] ... sr.inclusion_rule_mask = POWER(cast(2 as bigint),RuleTotal.total_rules)-1
) FC
; TRUNCATE TABLE #best_events; DROP TABLE #best_events; TRUNCATE TABLE #inclusion_rules; DROP TABLE #inclusion_rules; TRUNCATE TABLE #strategy_ends; DROP TABLE #strategy_ends; TRUNCATE TABLE #cohort_rows; DROP TABLE #cohort_rows; TRUNCATE TABLE #final_cohort; DROP TABLE #final_cohort; TRUNCATE TABLE #inclusion_events; DROP TABLE #inclusion_events; TRUNCATE TABLE #qualified_events; DROP TABLE #qualified_events; TRUNCATE TABLE #included_events; DROP TABLE #included_events; TRUNCATE TABLE #Codesets; DROP TABLE #Codesets]; 

SQL state [XX000]; error code [0]; ERROR: 1023
  Detail: Serializable isolation violation on table - 5840179, transactions forming the cycle are: 77581433, 77582607 (pid:21190); nested exception is com.amazon.redshift.util.RedshiftException: ERROR: 1023
  Detail: Serializable isolation violation on table - 5840179, transactions forming the cycle are: 77581433, 77582607 (pid:21190)

Steps to reproduce behavior

Generate > 1 cohort on RedShift using WebAPI v2.10.0.

Additional Notes

As part of the v2.10.0 release, we updated the RedShift JDBC drivers: https://github.com/OHDSI/WebAPI/pull/1925/files. As a test, we can try to roll back to using a 1.2.x build to see if that allows us to work-around this problem while we do a deeper dive.

Start with the files changed in PR #1925 and the RedShift JDBC driver update used by WebAPI v2.10.0. Reproduce the issue by generating more than one cohort on a single data source, then compare against a 1.2.x driver build; done means concurrent cohort generation completes without SQL state XX000 or error 1023.