This is the multi-page printable view of this section.
Click here to print.
Return to the regular view of this page.
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:
Transaction isolation levels READ COMMITTED and SERIALIZABLE differ as follows:
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.
1 - Rollback
Transaction rollbacks restore a database to an earlier state by discarding changes made by that transaction. Statement-level rollbacks discard only the changes initiated by the reverted statements. Transaction-level rollbacks discard all changes made by the transaction.
With a ROLLBACK statement, you can explicitly roll back to a named savepoint within the transaction, or discard the entire transaction. Vertica can also initiate automatic rollbacks in two cases:
-
An individual statement returns an ERROR message. In this case, Vertica rolls back the statement.
-
DDL errors, systemic failures, dead locks, and resource constraints return a ROLLBACK message. In this case, Vertica rolls back the entire transaction.
Explicit and automatic rollbacks always release any locks that the transaction holds.
2 - Savepoints
A savepoint is a special marker inside a transaction that allows commands that execute after the savepoint to be rolled back. The transaction is restored to the state that preceded the savepoint.
Vertica supports two types of savepoints:
-
An implicit savepoint is automatically established after each successful command within a transaction. This savepoint is used to roll back the next statement if it returns an error. A transaction maintains one implicit savepoint, which it rolls forward with each successful command. Implicit savepoints are available to Vertica only and cannot be referenced directly.
-
Named savepoints are labeled markers within a transaction that you set through SAVEPOINT statements. A named savepoint can later be referenced in the same transaction through RELEASE SAVEPOINT, which destroys it, and ROLLBACK TO SAVEPOINT, which rolls back all operations that followed the savepoint. Named savepoints can be especially useful in nested transactions: a nested transaction that begins with a savepoint can be rolled back entirely, if necessary.
3 - READ COMMITTED isolation
When you use the isolation level READ COMMITTED, a SELECT query obtains a backup of committed data at the transaction's start.
When you use the isolation level READ COMMITTED, a SELECT query obtains a backup of committed data at the transaction's start. Subsequent queries during the current transaction also see the results of uncommitted updates that already executed in the same transaction.
When you use DML statements, your query acquires write locks to prevent other READ COMMITTED transactions from modifying the same data. However, be aware that SELECT statements do not acquire locks, so concurrent transactions can obtain read and write access to the same selection.
READ COMMITTED is the default isolation level. For most queries, this isolation level balances database consistency and concurrency. However, this isolation level can allow one transaction to change the data that another transaction is in the process of accessing. Such changes can yield nonrepeatable and phantom reads. You may have applications with complex queries and updates that require a more consistent view of the database. If so, use SERIALIZABLE isolation instead.
The following figure shows how READ COMMITTED isolation might control how concurrent transactions read and write the same data:
READ COMMITTED isolation maintains exclusive write locks until a transaction ends, as shown in the following graphic:
See also
4 - SERIALIZABLE isolation
SERIALIZABLE is the strictest SQL transaction isolation level.
SERIALIZABLE is the strictest SQL transaction isolation level. While this isolation level permits transactions to run concurrently, it creates the effect that transactions are running in serial order. Transactions acquire locks for read and write operations. Thus, successive SELECT commands within a single transaction always produce the same results. Because SERIALIZABLE isolation provides a consistent view of data, it is useful for applications that require complex queries and updates. However, serializable isolation reduces concurrency. For example, it blocks queries during a bulk load.
SERIALIZABLE isolation establishes the following locks:
-
Table-level read locks: Vertica acquires table-level read locks on selected tables and releases them when the transaction ends. This behavior prevents one transaction from modifying rows while they are being read by another transaction.
-
Table-level write lock: Vertica acquires table-level write locks on update and releases them when the transaction ends. This behavior prevents one transaction from reading another transaction's changes to rows before those changes are committed.
At the start of a transaction, a SELECT statement obtains a backup of the selection's committed data. The transaction also sees the results of updates that are run within the transaction before they are committed.
The following figure shows how concurrent transactions that both have SERIALIZABLE isolation levels handle locking:
Applications that use SERIALIZABLE must be prepared to retry transactions due to serialization failures. Such failures often result from deadlocks. When a deadlock occurs, any transaction awaiting a lock automatically times out after 5 minutes. The following figure shows how deadlock might occur and how Vertica handles it:
Note
SERIALIZABLE isolation does not apply to temporary tables. No locks are required for these tables because they are isolated by their transaction scope.
See also Vertica