I had 3 jobs in my Redshift cluster. First of them was querying two tables A and B and then inserting data to table B. Second transaction was removing and inserting to table A. Third transaction was just reading A and B.
The timeline was:

  1. First transaction reads
  2. Second transaction deletes, inserts and commits
  3. First transaction inserts
  4. Third transaction reads

The result was that the third transaction was always killed because of serializable isolation violation.

Let’s reproduce this:

Table creation:

First transaction:

Second transaction:

Third transaction

Okay, it crashes. If you try it out with MS SQL Server 2019 using SERIALIZABLE SNAPSHOT isolation level, it works. If you try it with PostgreSQL 12.1, it works. So there is definitely something weird with Redshift “MVCC”.

Solution in my case was: add a LOCK locks_tests, locks_tests2 to third transaction. This way two other transactions are not blocked but the third one waits. Third transaction wasn’t that important for me (it was just reporting) so it was okay.