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

[anthonysena]

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.

[anthonysena]

I've done some testing with the v1.2 RedShift driver and that seems to be a work-around for now while we see what is happening with the v2 driver. We'll need to discuss putting this into v2.10.1 potentially.

[konstjar]

@anthonysena did you just rolled back to last version that was used or different one?

[anthonysena]

I ultimately went back to the last driver that we had used - the changes are here for now: https://github.com/OHDSI/WebAPI/tree/issue-1939-redshift-driver-downgrade

[konstjar]

@anthonysena @ssuvorov-fls worked on solution to fix the problem and go back to driver v1.0 but with IAM support. The functionality is available in "issue-1939-redshift-driver-downgrade-sdk" branch. The Redshift support was moved into separate profile "webapi-redshift", that's why you should mention in initialization and install steps.

We do the following steps in our environment

mvn initialize -Dredshift.classpath=/var/local/drivers/redshift-v1.2 -Pwebapi-redshift
mvn install -e -Dmaven.test.skip=true  -Dredshift.classpath=/var/local/drivers/redshift-v1.2 -Pwebapi-postgresql,webapi-redshift

For JDBC Driver, we used latest availabkle officially from AWS:
https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.55.1083/RedshiftJDBC42-1.2.55.1083.zip

Could you please check this branch in your environment?

[anthonysena]

@konstjar @ssuvorov-fls thank you both for investigating this problem and proposing the solution in the issue-1939-redshift-driver-downgrade-sdk branch. I had a few questions upon my review:

• The original need to update this driver was to support IAM authentication - do I have that right? If so, I was looking at the AWS docs and noted that For authentication using AWS Identity and Access Management (IAM) credentials or identity provider (IdP) credentials, use Amazon Redshift JDBC driver version 1.2.8.1005 or later. Just want to note that in this thread in case we have to revisit the driver change. The v1.2.55.1083 should work well.
• In the issue-1939-redshift-driver-downgrade-sdk branch, the com.amazon.redshift dependency was removed from the pom.xml in favor of using the maven command to install the profile instead. It appears that the webapi-redshift profile is doing 2 things: specifying the redshift.classpath property and also specify the redshift dependencies that are required for IAM support. Could we instead restore the 'com.amazon.redshift` dependency so that the driver is automatically downloaded and if someone wants to use IAM, they could follow the maven initalize/install steps above?

[konstjar]

@anthonysena

• You got it correctly in the first bullet. Amazon Redshift JDBC driver version 1.2.8.1005 or later should work well for IAM support.
• I think it should work this way and this will not break RedShift support for everyone in this case. But I would let @ssuvorov-fls to test this approach.

[ssuvorov-fls]

@anthonysena
The driver can't be automatically downloaded because it is not located in maven repository. Maven central has only versions started from 2.0.0.3
So it must be installed first.
If we will restore the com.amazon.redshift dependency and call mvn compile without setting the required profile then we'll get error