Transactions
When transactions in multiple user sessions concurrently access the same data, session-scoped isolation levels determine what data each transaction can access.
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.
The Vertica query parser supports standard ANSI SQL-92 isolation levels as follows:
READ COMMITTED
(default)READ UNCOMMITTED
: Automatically interpreted asREAD COMMITTED
.REPEATABLE READ
: Automatically interpreted asSERIALIZABLE
SERIALIZABLE
Transaction isolation levels READ COMMITTED
and SERIALIZABLE
differ as follows:
Isolation level | Dirty read | Non-repeatable read | Phantom read |
---|---|---|---|
READ COMMITTED
|
Not Possible | Possible | Possible |
SERIALIZABLE
|
Not Possible | Not Possible | Not Possible |
You can set separate isolation levels for the database and individual transactions.
Implementation Details
Vertica supports conventional SQL transactions with standard ACID properties:
- ANSI SQL 92 style-implicit transactions. You do not need to run a
BEGIN
orSTART TRANSACTION
command. - No redo/undo log or two-phase commits.
- The
COPY
command automatically commits itself and any current transaction (except when loading temporary tables). It is generally good practice to commit or roll back the current transaction before you useCOPY
. This step is optional for DDL statements, which are auto-committed.