How do I resolve the error "ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift"?

Short description

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 produces the same results as if the transactions were run concurrently. For more information, see Serializable isolation.

Use one or all of the following methods to resolve serializable isolation errors:

• Move operations that don't have to be in the same transaction for atomicity so that they are outside the transaction
• Force serialization by locking all tables in each session
• Use snapshot isolation for concurrent transactions

Resolution

Move operations that don't have to be in the same transaction for atomicity so that they are outside the transaction

Use this method when individual operations inside two transactions cross-reference each other in a way that might affect the outcome of the other transaction. For example, assume that two sessions each start a transaction:

Session1_Redshift = # BEGIN;

Session2_Redshift = # BEGIN;

The result of the SELECT statement in each transaction might 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 where the operations can be run serially that can produce the same result as when run concurrently, the last operation that is run 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;

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 so that they are outside their transactions. For example:

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 don't affect each other. Because there's at least one order where 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 might 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.

Use snapshot isolation for concurrent transactions

The SERIALIZABLE option implements strict serialization, where a transaction might fail if the result can't be mapped to a serial order of the concurrently running transactions.

The SNAPSHOT ISOLATION option allows higher concurrency, where concurrent modifications to different rows in the same table can complete successfully.

Transactions continue to operate on the latest committed version, or a snapshot, of the database.

Snapshot isolation is set on the database using the ISOLATION LEVEL parameter in the CREATE DATABASE or ALTER DATABASE command.

To view the concurrency model that your database is using, run the following example STV_DB_ISOLATION_LEVEL query:

SELECT * FROM stv_db_isolation_level;
The database can then be altered to SNAPSHOT ISOLATION:
ALTER DATABASE sampledb ISOLATION LEVEL SNAPSHOT;

Consider the following when altering the isolation level of a database:

• You must have the superuser or CREATE DATABASE privilege for the current database to change the database isolation level.
• You can't alter the isolation level of the DEV database environment.
• You can't alter the isolation level within a transaction block.
• The alter isolation level command fails if other users are connected to the database.
• The alter isolation level command can alter the isolation level settings of the current session.

---

Related information

Managing concurrent write operations