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

Short description

Concurrent write operations in Amazon Redshift must be serializable so that transactions can run after one another in some order. The serial execution must also produce the same results as when the transactions run concurrently. For more information, see Serializable isolation.

Resolution

To resolve serializable isolation errors, use one of the following methods.

Move non-atomic operations 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.

When the results don't need to be atomic with other operations, move SELECT statements outside their transactions .

The following example moves the SELECT statements outside 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;

The preceding transactions are serializable. If you run the transactions in sequence, then the results are the same as when you concurrently run them.

Lock all tables in each session to force serialization

The LOCK command blocks operations that might result in serializable isolation errors. When you run the LOCK command, complete the following steps:

• Lock all tables that the transaction affects, including tables that read-only SELECT statements inside the transaction affect.
• Lock tables in the same order regardless of the order that the operations are performed in.
• Before you perform operations, lock all tables at the beginning of the transaction.

Use snapshot isolation for concurrent transactions

Serializable isolation implements strict serialization. A transaction might fail when Amazon Redshift can't map the result to a serial order of the concurrently running transactions.

Snapshot isolation allows higher concurrency so that concurrent modifications to different rows in the same table can complete successfully.

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

You set snapshot isolation on the database, and include the ISOLATION LEVEL parameter in the CREATE DATABASE or ALTER DATABASE command.

To view the concurrency model that your database uses, run the following 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;

When you alter the isolation level of a database, use the following best practices:

• To change the database isolation level, you must have the superuser or CREATE DATABASE permission.
• 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 when other users connect to the database.
• The alter isolation level command can alter the isolation level settings of the current session.

Related information

Managing concurrent write operations