AWS Redshift Serializable Isolation Violation Error With Snapshot Isolation

Question

I am receiving intermittent ```Serializable isolation violation on xid``` errors from a stored proc in Redshift so I followed the guidance in the aws docs (https://docs.aws.amazon.com/redshift/latest/dg/c_serial_isolation.html) and changed the db isolation level to Snapshot Isolation with the understanding that this would fix the issue, but unfortunately I am still receiving them (albeit at a lower frequency).

Should these errors still be happening with Snapshot Isolation mode?

There is only one proc that works on the table affected so I am guessing that the order of statements is not relevant here as the sql being run will always be the same, but there could be multiple instances of the proc running at the same time which is presumably the cause.

Mounika_K · Answer

Hello,

Both SERIALIZABLE and SNAPSHOT isolation are types of serializable isolation levels i.e. dirty reads, non-repeatable reads, and phantom reads are prevented according to the SQL standard. Where, SNAPSHOT isolation allows higher concurrency of transactions compared to SERIALIZABLE isolation type.
Ref - https://repost.aws/articles/ARLIduLsAMSgGnG1CjjQ4IUw/understanding-redshift-isolation-levels-serializable-vs-snapshot

In your case, I suspect there are multiple queries or procedures attempting to perform DDL/DML operations on a table leading to the issue. Kindly consider adding a statement to query the database isolation level using [stv_db_isolation_level](https://docs.aws.amazon.com/redshift/latest/dg/r_STV_DB_ISOLATION_LEVEL.html) system table and save it in form of information so that for every Stored Procedure invocation we can see the isolation level using [SVL_STORED_PROC_MESSAGES](https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_STORED_PROC_MESSAGES.html).

Further, I recommend to reach to AWS Support Engineering team for Amazon Redshift service along with below details, so that an expert can look into the transactions and queries being run on cluster to identify the reason for Serializable Isolation error with SNAPSHOT isolation level on transactions.
1. Complete Stored Procedure definition
2. Complete error message being observed
3. QueryID / XID / Process ID of the stored procedure run

Thank you.

AWS Redshift Serializable Isolation Violation Error With Snapshot Isolation

답변 추가

관련 콘텐츠