How do I resolve the "Serializable isolation violation on table" or "Relation does not exist" errors in Amazon Redshift?

Short description

Concurrent write operations in Amazon Redshift must be serializable so that transactions can run subsequently. 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.

Retry canceled transactions

If Amazon Redshift detects that a concurrent workload isn't serializable, then there might be gaps in the application logic. Retry the canceled transaction that caused the error.

Use intermediate commits

When a commit or rollback is issued, the transaction completes. If a transaction commits before the delete from table operation runs, then a new transaction is created and serializable isolation is preserved.

The following example uses an intermediate COMMIT command:

DELETE FROM XXXXX WHERE date = XXXXX';
→COMMIT;
BEGIN TRANSACTION;
DELETE FROM XXXXX WHERE date = XXXXX';

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, and include 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 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.

For more information, see How to fix serializable isolation errors.

Related information

ERROR:1023 DETAIL: Serializable isolation violation on a table in Redshift

ERROR:1018 DETAIL: Relation does not exist

Managing concurrent write operations