When I run concurrent Amazon Redshift operations in different sessions, I get the message "ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift." How do I resolve this error?
Concurrent write operations in Amazon Redshift must be serializable. This means that it must be possible for the transactions to run serially in at least one order that would produce the same results as if the transactions were run concurrently. For more information, see Serializable Isolation.
Use one or both of the following methods to resolve serializable isolation errors:
- Move operations that don't have to be in the same transaction for atomicity outside of the transaction
- Force serialization by having each session lock all tables
Move operations that don't have to be in the same transaction for atomicity outside of 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 each start a transaction:
Session1_Redshift = # BEGIN;
Session2_Redshift = # BEGIN;
The result of the SELECT statement in each transaction could be affected by the INSERT statement 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 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 (that is, the atomicity of the operations in the transactions isn't important), move the SELECT statements outside of their transactions, as shown 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 these examples, there are no cross-references in the transactions. The two INSERT statements 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 locking all tables in each session
The LOCK command blocks operations that would result in serializable isolation errors. When you use the LOCK command, be 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 that operations are performed in.
- Lock all tables at the beginning of the transaction, before performing any operations.