Understanding Redshift Isolation Levels: SERIALIZABLE vs SNAPSHOT

The choice between SERIALIZABLE and SNAPSHOT isolation in Redshift depends on the specific requirements of your application, the nature of your data.

SERIALIZABLE isolation is the strictest level, ensuring that transactions execute in a way that is equivalent to running them serially, one after the other. This provides the highest level of data consistency but may come at the cost of reduced concurrency and could have locking issues.

SNAPSHOT isolation provides a more relaxed level of isolation. In this mode, each transaction sees a consistent snapshot of the data as it existed at the time the transaction began, regardless of changes made by other concurrent transactions.

both SERIALIZABLE and SNAPSHOT isolation are types of serializable isolation levels. That is, dirty reads, non-repeatable reads, and phantom reads are prevented according to the SQL standard. Both isolation levels guarantee that a transaction operates on a snapshot of data as it exists when the transaction begins, and that no other transaction can change that snapshot.

Both SERIALIZABLE and SNAPSHOT isolation prevents

• Dirty reads - A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits the update. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed, i.e reading uncommitted rows that can be rolled back
• Non repeatable reads - A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete. If transaction 1 rereads the row, it retrieves different row values or discovers that the row has been deleted.
• Phantoms – A phantom is a row that matches the search criteria but is not initially seen. For example, suppose transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 generates a new row (through either an update or an insert) that matches the search criteria for transaction 1. If transaction 1 reexecutes the statement that reads the rows, it gets a different set of rows.

SERIALIZABLE vs SNAPSHOT isolation

You can view which concurrency model your database is running as follows

SELECT * FROM stv_db_isolation_level;

You can also get the Database snapshot details by Query the PG_DATABASE_INFO view

SELECT datname, datconfig FROM pg_database_info;
Null – Default setting 
concurrency_model=1 – SNAPSHOT
concurrency_model=2 – SERIALIZABLE

How to change Isolation level

Isolation level is set per-database and in order to change isolation level for Database, it should not have any user connection. Set DB connection limit to 0 to not allow any new connection.

ALTER DATABASE < Your db>  CONNECTION LIMIT 0 -- this will prevent any new connections
SELECT process FROM STV_SESSIONS WHERE dbname='<'db_name'>  AND user != <'your_user_id'> 
--terminate active pids with select pg_terminate_backend(pid)
ALTER DATABASE db ISOLATION LEVEL SNAPSHOT -- May require Restart
ALTER DATABASE db CONNECTION LIMIT UNLIMITED -- allow accept new connections