I am getting a "Serializable isolation violation on table" error from Redshift, with "transactions forming the cycle are: tx1, tx2, tx3".
After running select * from stl_query where xid in (tx1, tx2, tx3) I found that the transaction that actually aborted had only a single SELECT statement.
How can a transaction be in conflict if it does not insert/update/delete any rows? The impression I got from Redshift documentation is that this happens when you have circular dependencies between concurrent transactions: tx1 reads T1 and updates T2, while tx2 reads T2 and updates T1.
But when one transaction only has a SELECT, why wouldn't the result be equivalent as if it were serialized to run before any uncommitted transactions?
In my case, I have tx1 updating T1, tx2 reading T1 and updating T2, and tx3 reading both T1/T2. tx3 is the one that fails. tx1 and tx2 overlap, and tx2 and tx3 overlap, so I don't understand why tx3 fails instead of producing the same result as if serialized after tx1 and before tx2.
Although this question is specific to Redshift, I suppose you could encounter a similar issue on other DBs with serializable isolation.
