Database Transactions, Requests, and Statements | Teradata Vantage - 17.10 - Database Transactions - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Processing in Vantage is transaction-based. The principal purpose of transaction management is to optimize concurrency: to ensure that as many sessions as possible can access the information in the database concurrently without compromising the consistency or integrity of the data.

Definition of a Database Transaction

A transaction is a sequence of n actions that are treated both as a unit of work and as a unit of recovery, though for the purposes of this discussion, it is viewed primarily as a unit of work.
  • As a unit of work, a transaction defines a set of SQL operations to be performed on a database. Either all of these operations must be performed or none of them 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, because it takes 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 as it undergoes 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 are never seen by other users. Another way of saying this is that any two operations that relate to the same database object must appear to execute in some serial order. See Serializability Defined.

Definitions of Statements and Requests in Teradata SQL

It is important to understand the definitions of a request and a statement in Teradata SQL.

Request is a Teradata-specific term used to describe a minimal unit of work that is transmitted from a client system to the database in a single message. Among the components of the message are the following.
  • 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.

The following topics describe the differences between statements and requests in the database in more detail.

Definitions of a Transaction in Teradata SQL

The definition of a transaction in Teradata SQL depends in part on the session mode in which it is submitted.

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 an implicit transaction unless it is preceded by a BEGIN TRANSACTION statement, in which case the transaction becomes 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.

Teradata session mode explicit transactions are completed by one of the following:
  • 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.

When the Parser receives a BEGIN TRANSACTION statement, it 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

In ANSI session mode, a transaction begins when a request is submitted and there is not an uncompleted transaction. ANSI mode transactions are completed by one of the following:
  • 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 several 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.

Because of this, locks are placed as each request is received. Locks are held until one of the following events completes, depending on the session mode, but regardless of when the user receives the data. (For example, a spool might exist beyond the end of the transaction.)
  • The outermost END TRANSACTION statement in Teradata session mode.
  • Submission of a COMMIT or ROLLBACK statement in ANSI session mode.

    You must explicitly commit or rollback all transactions in ANSI session mode; otherwise they continue until you do commit them or roll them 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
To ensure that your transactions are always handled as intended, it is critical to code your applications with logic to handle any situations that only roll back an error-generating request rather than the entire transaction of which it is a member.

Note that 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.

Note that errors do not complete ANSI session mode transactions, but failures do.

ACID Properties of Transactions

The general concept of transaction processing is encapsulated by their so-called ACID properties. ACID is an acronym for the following set of properties that characterize a transaction:
  • 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 it does not. No matter how many component SQL operations are specified within the boundaries of a transaction, they must all complete successfully and commit or they must all fail and rollback. There are no partial transactions.

Note that 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.

See Levels of Isolation Defined.

Durability Once a commit has been made, the new consistent state of the database survives even if the underlying system crashes.

Durability is a synonym for persistence.

It should be clear that not only are these four factors not rectangular, the degree of shared variance among them varies considerably. Atomicity and Consistency are very close to being subtle restatements of one another, and neither is possible without Isolation.

Furthermore, the importance of the various ACID guarantees depends on whether a transaction is read-only or if it also performs write operations. In the case of a read-only transaction, for example, Atomicity and Durability are irrelevant, but Isolation remains critically important for the reasons why this is true.

Note that 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 it either commits or rolls back.

Using the Transient Journal to Roll Back Transactions

Because transactions sometimes 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 for any reason, the system rolls back its updates using the before-change copies of any rows touched by the failed transaction. It does this by writing the appropriate Transient Journal before-change rows over the updated after-change rows.