When you run concurrent Amazon Redshift operations in different sessions, there is a probability of getting “ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift.” In Amazon Redshift Concurrent write operations must be serializable. This means that for the transactions to run serially in at least one order that would produce the same results as if the transactions were run concurrently.
Move operations that don’t have to be in the same transaction for atomicity outside of the transaction.
Consider there are two sessions each start a transaction
Session1_Redshift = # BEGIN;
Session2_Redshift = # BEGIN;
The result of the SELECT in each of the transaction could be affected by the INSERT statement in the other. If we run serially, in any order, the result of one SELECT always returns one more row than if the transactions run concurrently. Because there is no order in which the operations could be run serially that would produce the same result as when run concurrently, the last operation that is executed results in a serializable isolation error:
Session1_redshift =# select * from tab1; Session1_redshift =# insert into tab2 values (1); Session2_redshift =# insert into tab1 values (1); Session1_redshift =# select * from tab2;
If the result of the SELECT statements isn’t important , then you have to move the SELECT statements outside of their transactions.
Session1_Redshift=# BEGIN; Session1_Redshift = # insert into tab1 values (1) Session1_Redshift = # END; Session1_Redshift # select * from tab2; Session2_Redshift # select * from tab1; Session2_Redshift =# BEGIN; Session2_Redshift = # insert into tab2 values (1) Session2_Redshift = # END;
Here there are no cross-references in the transactions and two INSERT statements do not affect each other.
