|
|
|
|
|
|
SET SESSION CHARACTERISTICS sets the transaction characteristics for subsequent transactions of a user 
session. These are the isolation level and the access mode (read/write or read-only).
Syntax
SET SESSION CHARACTERISTICS AS TRANSACTION
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ |
READ COMMITTED | READ UNCOMMITTED }
{ READ WRITE | READ ONLY }
Semantics
ISOLATION LEVEL |
determines what data the transaction can access when other transactions are running concurrently. It does not apply to temporary tables. The isolation level cannot be changed after the first query (SELECT) or DML statement (INSERT, DELETE, UPDATE) of a transaction has been executed. |
SERIALIZABLE REPEATABLE READ |
are identical in meaning. Both specify SERIALIZABLE isolation, which is the strictest level of SQL transaction isolation and the default in Vertica. This level emulates transactions executed one after another, serially, rather than concurrently. It holds locks, and blocks write operations and is thus not recommended for normal query operations. |
READ COMMITTED READ UNCOMMITTED |
are identical in meaning. Both specify READ COMMITTED isolation, which allows concurrent transactions and is the default in PostgreSQL and other databases. Use READ COMMITED isolation or Snapshot Isolation for normal query operations but be aware that there is a subtle difference between them (see below). |
READ WRITE READ ONLY |
determines whether the transaction is read/write or read-only. Read/write is the default. When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and COPY if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; GRANT, REVOKE, and EXPLAIN if the command it would execute is among those listed. This is a high-level notion of read-only that does not prevent all writes to disk. |
READ COMMITTED vs. Snapshot Isolation
By itself, AT EPOCH LATEST produces purely historical query behavior. However, with READ COMMITTED, SELECT queries return the same result set as AT EPOCH LATEST plus any changes made by the current transaction.
This is standard ANSI SQL semantics for ACID transactions. Any select query within a transaction should see the transactions's own changes regardless of isolation level.
Need side-by-side example.
Notes