15.10 - Transactions, Requests, and Statements - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

The following topics describe how Teradata Database processes statements, requests, and transactions.

In Teradata SQL, statements have the following properties:

  • They require locks on the database objects they access in order to ensure a level of isolation.
  • See “Database Locks, Two-Phase Locking, and Serializability” on page 669.

  • They can be submitted individually as requests.
  • In this case, an SQL statement is functionally equivalent to a Teradata request.

    Depending on the circumstances, an SQL statement can also be functionally equivalent to a database transaction.

  • They can be submitted as components of a single-statement request or a multistatement request.
  • Depending on the circumstances, a multistatement request can also be functionally equivalent to a database transaction.

  • They can be components of a macro.
  • They can be components of an SQL procedure.
  • A request is composed of zero or more SQL statements and other information. A request that contains more than one SQL statement is called a multistatement request. A request with one SQL statement is called a single-statement request.

    A request is sent to Teradata Database. It is processed and a response is returned. Then another request can be sent.

    The following segments of system input analysis (see Chapter 1: “Request Parsing” for details) are performed with one or more SQL statements for a request.

  • SQL statement syntax checking (see “Syntaxer” on page 45).
  • SQL statement syntax annotation (see “Resolver” on page 48).
  • Privilege checking (see “Security Checking” on page 52).
  • Request optimization (see “Optimizer” on page 58 and Chapter 2: “Query Rewrite and Optimization”).
  • The most restrictive locks required by the SQL statements in a request are acquired as early as possible. This is fundamental to the two‑phase locking protocol (see “Database Locks, Two-Phase Locking, and Serializability” on page 669). Locks are never released within a request or transaction. They are released when the transaction completes.

    For example, consider the following multistatement request.

        SELECT * 
        FROM employee
        ; UPDATE employee
          SET salary_amount = salary_amount * 1.1;

    The SELECT statement in this multistatement request only requires a READ lock, but the UPDATE statement requires a WRITE lock. Because the WRITE lock is the most restrictive lock required by the multistatement request, the system applies it to the multistatement request for both the SELECT and the UPDATE statement components of the multistatement request.

    A Teradata request terminates with a SEMICOLON character at the end of a line. A semicolon placed at any other point in the request does not terminate it.

    You can use this property to specify multiple SQL statements within a single request either by placing intermediate semicolons at the beginning of a subsequent line or in the middle of a line.

    For example, both of the following requests are valid multistatement requests:

        SELECT * FROM employee; UPDATE employee SET
        salary_amount=salary_amount * 1.1;
        SELECT * FROM employee
        ;UPDATE employee SET salary_amount=salary_amount * 1.1;

    Multistatement requests have the following properties.

  • They can only include DML statements.
  • DCL and DDL statements cannot be components of a multistatement request.

    This property distinguishes multistatement requests from macros, which can contain a single DDL statement as long as it is the last statement in the request.

  • Teradata Database executes the statements within a multistatement request in the order in which they are specified with knowledge of dependencies.
  • The most exclusive locks required by any individual statement within the request are held for the entire length of the request and the transaction.
  • With the exception of multistatement INSERT requests, the outcome of a multistatement request, like the outcome of a transaction in Teradata session mode, is all or nothing.
  • If one statement in the request fails, the entire request fails and Teradata Database rolls back the transaction.

    For multistatement INSERT requests that use statement independence, only those statements that fail are rolled back, and the successful statements in the multistatement request are committed.

    Consult the appropriate Teradata Tools and Utilities documentation to determine whether a client API supports statement independence.

    For more information on statement independence, see “INSERT/INSERT … SELECT” in SQL Data Manipulation Language for details.

  • A multistatement request can be committed either implicitly by a line‑ending SEMICOLON character in Teradata session mode or explicitly with a COMMIT request in ANSI session mode or an END TRANSACTION request in Teradata session mode.
  • You can only submit an END TRANSACTION request to terminate an explicit transaction that was initiated with a BEGIN TRANSACTION request. See “BEGIN TRANSACTION” and “END TRANSACTION” in SQL Data Manipulation Language for details.

    The most commonly used example of a transaction is a debit-credit transaction at a bank ATM. Suppose you withdraw 10 dollars from your checking account and deposit it in your savings account. This is a two‑part transaction: withdrawal of money from the checking account (debit phase) and depositing it in the savings account (credit phase). This is an oversimplification. The classic debit-credit transaction has numerous components, but from a user perspective, it is a withdrawal of funds from one account and their deposit into another.

    Suppose the debit phase of the transaction completes successfully, but the credit phase does not. Do those 10 dollars just disappear? Without proper transaction management, they just might. In the scenario presented here, the system rolls back the withdrawal when the deposit fails, so that no money is lost. This transaction is atomic because it is all‑or‑nothing. It cannot perform only a subset of its work.

    Statement and request processing are essentially identical when operating in Teradata or ANSI modes, implicitly or explicitly. The conditions under which changes are applied is what differentiates the two modes from one another.

    The operations of committing or rolling back changes to data are what constitute transaction processing.