0

I'm new to AWS Redshift and I wanted to know How i can implement my use-case.

I have a Redshift table which gets lot of Inserts/Selects. In some cases I need to delete some data from that table. I don't want my Delete to impact other writes and reads.

Can someone suggest what is the best locking mode available in Redshift to implement my use case.

I don't want to end up with dadlocks and some locks hanging in my application.

I tried without locks and I'm observing the following violation:

Serializable isolation violation on table - 156300, transactions forming the cycle are: 6416378, 6416913 (pid:3770);

Also I went through the Redshift cxplicit locks but couldn't find any Official documentation for the same.

I know that Redshift is a wrapper on PostgreSQL, so can I use that documentation for implementing?

2
  • Redshift is not a "wrapper" for Postgres. It's a fork of a very, very old version of Postgres (and a completely different product in the meantime) Dec 9, 2019 at 12:20
  • So can i use postgresql documentation about explicit locking which I Couldn't find in RedShift documentation? Dec 9, 2019 at 13:29

2 Answers 2

0

Assuming that it works like PostgreSQL in this case:

This is a serialization error that you get because you are using the SERIALIZABLE transaction isolation level.

That is nothing to worry about, just repeat the transaction.

3
  • Can you suggest any Locking mode ? Which doesn't impact my INSERT/SELECT's in one transaction(T1) while I'm deleting data in another transaction (T2) Dec 9, 2019 at 13:26
  • There shouldn't be any conflicts, unless maybe the file is empty or locks are escalating. Does the error happen every time? Dec 9, 2019 at 13:33
  • as of now we are not using any locks on the table and when mulitple writes (Deletes,Inserts) are happening in parallel we are seeing this error Dec 10, 2019 at 6:10
-2

In order to avoid the above mentioned error, you can use the LOCK command to explicitly lock in that transaction.

1
  • Which explicit locks? Please provide an example. Apr 15, 2021 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.