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;