Aborted transactions in Redshift due to Serializable isolation level 2

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:

First transaction reads
Second transaction deletes, inserts and commits
First transaction inserts
Third transaction reads

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

Let’s reproduce this:

Table creation:

-- 0

DROP TABLE IF EXISTS locks_tests;
CREATE TABLE locks_tests(k INT);
INSERT INTO locks_tests(k) VALUES (1), (2);

DROP TABLE IF EXISTS locks_tests2;
CREATE TABLE locks_tests2(k INT);
INSERT INTO locks_tests2(k) VALUES (1), (2);

-- 0

DROP TABLE IF EXISTS locks_tests;

CREATE TABLE locks_tests(k INT);

INSERT INTO locks_tests(k) VALUES (1), (2);

DROP TABLE IF EXISTS locks_tests2;

CREATE TABLE locks_tests2(k INT);

INSERT INTO locks_tests2(k) VALUES (1), (2);

First transaction:

-- 1
BEGIN TRANSACTION;
SELECT L1.* FROM locks_tests L1, locks_tests2 L2;

-- 3
INSERT INTO locks_tests2(k) VALUES (3);

-- 1

BEGIN TRANSACTION;

SELECT L1.* FROM locks_tests L1, locks_tests2 L2;

-- 3

INSERT INTO locks_tests2(k) VALUES (3);

Second transaction:

-- 2
BEGIN TRANSACTION;
DELETE FROM locks_tests WHERE k = 1;
INSERT INTO locks_tests(k) VALUES (4);
COMMIT;

-- 2

BEGIN TRANSACTION;

DELETE FROM locks_tests WHERE k = 1;

INSERT INTO locks_tests(k) VALUES (4);

COMMIT;

Third transaction

-- 4
BEGIN TRANSACTION;
SELECT L1.* FROM locks_tests L1, locks_tests2 L2;

-- 4

BEGIN TRANSACTION;

SELECT L1.* FROM locks_tests L1, locks_tests2 L2;

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.