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 as READ COMMITTED.
-
REPEATABLE READ: Automatically interpreted as 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 or START 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 use COPY. This step is optional for DDL statements, which are auto-committed.