Change Transaction Isolation Levels
By default, Vertica uses the READ COMMITTED
isolation level for all sessions. You can change the default isolation level for the database or for a given session.
A transaction retains its isolation level until it completes, even if the session's isolation level changes during the transaction. Vertica internal processes (such as the Tuple Mover and refresh operations) and DDL operations always run at the SERIALIZABLE
isolation level to ensure consistency.
Database Isolation Level
The configuration parameter TransactionIsolationLevel
specifies the database isolation level, and is used as the default for all sessions. Use ALTER DATABASE
to change the default isolation level.For example:
=> ALTER DATABASE DEFAULT SET TransactionIsolationLevel = 'SERIALIZABLE'; ALTER DATABASE => ALTER DATABASE DEFAULT SET TransactionIsolationLevel = 'READ COMMITTED'; ALTER DATABASE
Changes to the database isolation level only apply to future sessions. Existing sessions and their transactions continue to use their original isolation level.
Use SHOW CURRENT
to view the database isolation level:
=> SHOW CURRENT TransactionIsolationLevel; level | name | setting ----------+---------------------------+---------------- DATABASE | TransactionIsolationLevel | READ COMMITTED (1 row)
Session Isolation Level
SET SESSION CHARACTERISTICS AS TRANSACTION
changes the isolation level for a specific session. For example:
=> SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET
Use SHOW
to view the current session's isolation level:
=> SHOW TRANSACTION_ISOLATION;