Using the Transaction Model
Vertica supports conventional SQL transactions with standard ACID properties. Specifically:
- Vertica supports ANSI SQL 92 style implicit transactions. You do not need to execute a BEGIN or START TRANSACTION command.
- Vertica does not use a redo/undo log or two-phase commit.
- The COPY command automatically commits itself and any current transaction. Vertica recommends that you COMMIT or ROLLBACK the current transaction before using COPY.
Session-Scoped Isolation Levels
Vertica supports a subset of the standard SQL isolation levels and access modes for a user 
session as described in SERIALIZABLE Isolation and READ COMMITTED Isolation. These modes determine what data a transaction can access when other transactions are running concurrently. You can change the default isolation level for a user session using the SET SESSION CHARACTERISTICS command.
A SQL session is an occurrence of a user interacting with a database through the use of SQL statements. A session can be invoked using vsql or a JDBC application. In Vertica, the scope of a session is the same as that of a connection.
JDBC (Java Database Connectivity) is a call-level API that provides connectivity between Java programs and data sources (SQL databases and other non-relational data sources, such as spreadsheets or flat files). JDBC is included in the Java 2 standard and enterprise editions.
vsql is the Vertica implementation of psql, a character-based, interactive, front-end that is part of PostgreSQL and used by other database management systems. It allows you to type in SQL statements and see the results. It also provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.
Session-scoped isolation levels do not apply to temporary tables, which are scoped to the current transaction. They do not take table locks, are only visible to one user, and their contents are always visible regardless of advancing epochs and COMMITs.
Query-Scoped Isolation Levels
Snapshot Isolation (historical queries) are part of the Vertica query syntax:
Vertica can execute a query from a snapshot of the database taken at a specific date and time. The syntax is:
AT TIME 'timestamp' SELECT...
The command queries all data in the database up to and including the epoch representing the specified date and time without holding a lock or blocking write operations.
An epoch represents committed changes to the data stored in a database between two specific points in time. In other words, an epoch contains all COPY, INSERT, UPDATE, and DELETE operations that have been executed and committed since the end of the previous epoch.
[ AT EPOCH LATEST ] | [ AT TIME 'timestamp' ] SELECT ...
AT EPOCH LATEST allows queries to access all historical data up to but not including the current epoch . It does not hold locks and does not block write operations. This provides a profound query performance advantage. It does not apply to temporary tables.
An epoch represents committed changes to the data stored in a database between two specific points in time. In other words, an epoch contains all COPY, INSERT, UPDATE, and DELETE operations that have been executed and committed since the end of the previous epoch.
Automatic Rollback
When an error occurs or a user session is disconnected, the current transaction automatically rolls back. This behavior may be different from other databases.
