Processing in Vantage is transaction-based. The principal purpose of transaction management is to optimize concurrency: to maximize the number of sessions that can access the information in the database concurrently without compromising the consistency or integrity of the data.
Definition of a Database Transaction
- As a unit of work, a transaction defines a set of SQL operations to be performed on a database. All these operations must be performed or none can be performed. This is called the atomic property of transactions.
- As a unit of recovery, a transaction defines a set of rollback operations to be performed on a database in order to change its state back to an earlier consistent state in the event that the transaction cannot successfully complete.
Beside its common definitions as a unit of work and a unit of recovery, a transaction can also be a unit of consistency, taking the database from one consistent state to another consistent state. In the course of a sequence of update operations, a database object may become transiently inconsistent while undergoing a change to a new consistent state. Transactions are devised to provide consistency in the face of potential transient inconsistency.
All transient inconsistencies may be isolated within the transaction and never seen by other users. In other words, any two operations that relate to the same database object must appear to run in a serial order. See Serializability Defined.
Definitions of Requests and Statements in Teradata SQL
- Request-level API options
- Zero or more SQL statements
- Request-related metadata
- Request-related data
ANSI SQL does not use the request concept and refers only to SQL statements, which do not define units of work.
Any SQL statement can be a single-statement request, but not every request is a single SQL statement.
Definitions of a Transaction in Teradata SQL
The definition of a transaction in Teradata SQL depends in part on session mode.
In Teradata session mode, a transaction can either be implicit or explicit. In ANSI session mode, there are only ANSI transactions.
Definition of an Implicit Transaction
Each request submitted in Teradata session mode is implicit unless preceded by a BEGIN TRANSACTION statement, in which case the transaction is explicit.
In Teradata session mode, because an implicit transaction is taken as a single request, the Optimizer can determine what kinds of locks are required by the entire transaction at the time the request is parsed. Before processing begins, the Optimizer can arrange any table locks in an ordered fashion to minimize deadlocks.
ANSI session mode does not have implicit transactions.
Definition of an Explicit Transaction
You can also define transactions explicitly in Teradata session mode by using the BEGIN TRANSACTION statement.
- An END TRANSACTION statement.
- An ABORT or ROLLBACK statement.
- A failure response for a request.
- The session is logged off, which aborts the transaction.
- The system restarts.
You cannot submit BEGIN TRANSACTION and END TRANSACTION statements in ANSI session mode. If you attempt to do so, the database aborts the request and returns an error or a failure response to the requestor.
Upon receiving a BEGIN TRANSACTION statement, the Parser immediately looks for an SQL statement keyword in the SQL text that follows. Keep this in mind when determining the placement of request modifiers such as EXPLAIN, LOCKING, NONTEMPORAL, and USING.
For example, if the first request in an explicit transaction is associated with a USING request modifier, that USING request modifier must precede, not follow, the BEGIN TRANSACTION request. In other words, the USING request modifier must be specified outside the BEGIN TRANSACTION boundary.
Definition of ANSI Transactions
- A COMMIT statement.
- An ABORT or ROLLBACK statement.
- A failure response for a request.
- The session is logged off, which aborts the transaction.
- The system restarts.
You cannot submit COMMIT requests in Teradata session mode. If you attempt to do so, the database aborts the request and returns an error to the requestor.
Locking for Explicit and ANSI Transactions
When multiple requests are submitted as an explicit or ANSI transaction, the requests are processed one at a time. This means that the Optimizer has no way of determining what locks are needed by the transaction as a whole.
- The outermost END TRANSACTION statement in Teradata session mode.
- Submission of a COMMIT or ROLLBACK statement in ANSI session mode.
In ANSI session mode, transactions continue to run until explicitly committed or rolled back.
- The system restarts.
Errors and Failures
The following table briefly describes the conditions under which the system rolls back a transaction.
| Response Code Type | Session Mode | Action Taken |
|---|---|---|
| Error | ANSI | Rolls back the error-generating
request only. Does not release locks placed on behalf of the rolled back request. |
| Teradata | Not applicable. The system does not return error codes in Teradata session mode; a failure code is returned instead. |
|
| Failure | ANSI | Rolls back the entire transaction that contains the error-generating request. |
| Teradata |
The system does not release any locks placed for a request that is rolled back because of an error-generating request. All such locks remain in effect until the transaction either commits or rolls back.
Errors do not complete ANSI session mode transactions, but failures do.
ACID Properties of Transactions
- Atomicity
- Consistency
- Isolation
- Durability
The specific meanings of these expressions in terms of transactions are defined in the following table.
| Term | Definition |
|---|---|
| Atomicity | A transaction either occurs or does not. All SQL operations specified within the boundaries of a transaction must complete successfully and commit or all must fail and rollback. ANSI transaction semantics do not necessarily satisfy this property. |
| Consistency | A transaction transforms one consistent database state into another. Intermediate inconsistencies in the database are not permitted. |
| Isolation | The operations of any transaction are concealed from all other transactions until that transaction commits. In practice, a transaction may not necessarily adhere to this property. |
| Durability | After a commit is made, the new consistent state of the database survives even if the underlying system fails. Durability is a synonym for persistence. |
These four factors are not rectangular. Their degree of shared variance varies. Atomicity and Consistency are close to being subtle restatements of one another, and neither is possible without Isolation.
The importance of the ACID guarantees depends on whether a transaction is read-only or also performs write operations. For a read-only transaction, Atomicity and Durability are irrelevant, but Isolation remains critically important for the reasons this is true.
Transactions are not always atomic in ANSI session mode, because when a request within a transaction fails with an Error response, only that request, and not the entire transaction, is rolled back. The remaining requests in the transaction continue until the transaction commits or rolls back.
Using the Transient Journal to Roll Back Transactions
Because transactions can fail and must be rolled back to a previous state, a mechanism must exist for preserving the before-change row set of a transaction. The before-change row images for a transaction are saved in the Transient Journal.
When a transaction fails, the system rolls back its updates using the before-change copies of any rows touched by the failed transaction. The system does this by writing the appropriate Transient Journal before-change rows over the updated after-change rows.