15.10 - Database Transactions - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

Processing in Teradata Database 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 Teradata Database concurrently without compromising the consistency or integrity of the data.

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 chapter, 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” on page 670.

    In the case of system failures, you can use archived data, and the Before and After row images optionally recorded for data tables in the Permanent journal for their containing databases, to recover the database by either rolling back or rolling forward any number of previously performed actions. See Teradata Archive/Recovery Utility Reference for details.

    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 Teradata 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 Teradata Database in more detail.

  • “Statement Processing by the Teradata Database” on page 649
  • “Request Processing by the Teradata Database” on page 649
  • 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.

    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.

    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, Teradata 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.

    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, Teradata Database aborts the request and returns an error to the requestor.

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

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

    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” on page 672.

    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. As defined by Haerder and Reuter, for example, 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 (see “Levels of Isolation Defined” on page 672 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 (see “Definition of a Database Transaction” on page 644).

    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.