Serializable isolation violation on table X with Redshift using upsert

[Falydoor]

Describe the bug

I have a lambda that reads a file from S3 and then insert it to Redshift. I noticed that when multiple lambdas are inserting in the same table, the first one inserts things correctly but the others are raising the error below:

{'S': 'ERROR', 'C': 'XX000', 'M': '1023', 'D': 'Serializable isolation violation on table - 119371, transactions forming the cycle are: 1745830, 1745762 (pid:15021)', 'F': '/home/ec2-user/padb/src/sys/xen_execute.cpp', 'L': '9797', 'R': 'pg_throw'}

To Reproduce

Lambda layer 2.7.0 for Python 3.8

Code used to insert to RS (let me know if more informations are required):

con = wr.redshift.connect_temp(cluster_identifier=CLUSTER, user=USER, database='dev', auto_create=False)

wr.redshift.copy(df=DF,
path=S3_PATH,
con=con,
schema=SCHEMA,
table=TABLE,
mode='upsert',
iam_role=IAM_ROLE,
primary_keys=PKS)

[kukushking]

Concurrent COPY commands break serial isolation.

I suggest to add a parameter to wrangler to be able to call LOCK before COPY. While this is being added, as a quick fix you could set up retries on your lambdas.

[jaidisido]

Thank you @Falydoor for raising this and @kukushking for the quick fix. Now merged into main and can be tested via:

pip uninstall awswrangler -y
pip install git+https://github.com/awslabs/aws-data-wrangler.git@main

until it's available in the next release

[Falydoor]

Awesome! Thanks @kukushking @jaidisido

[jaidisido]

Now covered in 2.8.0.