I'm receiving "ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift" when performing operations in a transaction concurrently with another session. How do I resolve this?
Transactions are serializable if two transactions could be run serially in some order that would produce the same result as when run concurrently.
You can resolve most serializable isolation errors by doing one or both of the following things:
Move any operations that don't have to be in the same transaction for atomicity outside the transaction
This applies when individual operations inside two transactions cross-reference each other in a way that could affect the outcome of the other transaction. For example, assume two sessions have each started a transaction:
Session1_Redshift = # BEGIN;
Session2_Redshift = # BEGIN;
The result of the select statement in each transaction could be affected by the insert in the other. If run serially, in any order, the result of one select statement 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 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);
Session2_redshift =# select * from tab2;
Serializable isolation errors look similar to the following:
DETAIL: Serializable isolation violation on table - [table_id], transactions forming the cycle are: [transaction_id], [transaction_id] (pid:[process_id])
To avoid the error, if the result of the select statements isn't important (that is, the atomicity of the operations in these transactions isn't important), you can move the select statements outside of their transactions, as in the following examples:
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;
In this example, there is no cross-reference inside each respective transaction; the two INSERTs do not affect each other. Because there is at least one order in which the transactions can run serially and produce the same result as if run concurrently, the transactions are serializable.
Force serialization by having each session lock all tables
You can use the LOCK command to force operations to occur serially by blocking operations to tables that would result in serializable isolation errors.
To force serialization while avoiding deadlock, make sure to do the following:
- Lock all tables affected by the transaction, including those affected by read-only SELECT statements inside the transaction.
- Lock tables in the same order regardless of the order of operations to be performed.
- Lock all tables at the beginning of the transaction, before performing any operations.
Redshift, 1023, serializable
Did this page help you? Yes | No
Back to the AWS Support Knowledge Center
Need help? Visit the AWS Support Center
Published: 2017-03-03
