SET SESSION CHARACTERISTICS AS TRANSACTION

Sets the isolation level and access mode of all transactions that start after this statement is issued.

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.

Syntax

SET SESSION CHARACTERISTICS AS TRANSACTION setting[, setting]

ISOLATION LEVEL Arguments

The ISOLATION LEVEL clause determines what data the transaction can access when other transactions run concurrently. You cannot change the isolation level after the first query (SELECT) or DML statement (INSERT, DELETE, UPDATE) if a transaction has run.

Set ISOLATION LEVEL to one of the following arguments:

SERIALIZABLE

Sets the strictest level of SQL transaction isolation. This level emulates transactions serially, rather than concurrently. It holds locks and blocks write operations until the transaction completes.

Applications that use SERIALIZABLE must be prepared to retry transactions in the event of serialization failures. This isolation level is not recommended for normal query operations.

Setting the transaction isolation level to SERIALIZABLE does not apply to temporary tables. Temporary tables are isolated by their transaction scope.
REPEATABLE READ

Automatically converted to SERIALIZABLE.
READ COMMITTED

The default setting, allows concurrent transactions.
READ UNCOMMITTED

Automatically converted to READ COMMITTED.

READ WRITE/READ ONLY

You can set the transaction access mode with one of the following:

READ WRITE Default
READ ONLY

 Disallows SQL statements that require write access:

  • INSERT, UPDATE, DELETE, and COPY operations on any non-temporary table.
  • CREATE, ALTER, and DROP
  • GRANT, REVOKE
  • EXPLAIN if the SQL statement to explain requires write access.

Setting the transaction session mode to read-only does not prevent all write operations.

Privileges

None

Viewing Session Transaction Characteristics

SHOW TRANSACTION_ISOLATION and SHOW TRANSACTION_READ_ONLY show the transaction settings for the current session:

=> SHOW TRANSACTION_ISOLATION;
         name          |   setting
-----------------------+--------------
 transaction_isolation | SERIALIZABLE
(1 row)

=> SHOW TRANSACTION_READ_ONLY;
         name          | setting
-----------------------+---------
 transaction_read_only | true
(1 row)