SET SESSION CHARACTERISTICS AS TRANSACTION

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

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 settings
settings
One or both of the following:

ISOLATION LEVEL argument

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
Default, 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, allows read/write access to SQL statements.
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.

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)