Transactions

When in multiple user sessions concurrently access the same data, session-scoped isolation levels determine what data each transaction can access.

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

  • 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.