15.00 - ROLLBACK - Teradata Database

Teradata Database SQL Data Manipulation Language

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1146-015K

ROLLBACK

Purpose  

Terminates and rolls back the current transaction.

For information about the temporal form of ROLLBACK, see Temporal Table Support.

Syntax  

where:

 

Syntax Element …

Specifies …

WORK

an optional keyword.

‘abort_message’

the text of the message to be returned when the transaction is terminated.

FROM_clause

a clause that is required only if the WHERE clause includes subqueries. You can code scalar subqueries as an expression within a derived table in FROM clause conditions in the same way you can specify them for a SELECT request. See “Scalar Subqueries” on page 142 and “Rules for Using Scalar Subqueries in ABORT Statements” on page 304. Note, however, that you cannot code a derived table as a scalar subquery.

The contents of the FROM option are described in “FROM Clause” on page 96.

WHERE_clause

a clause that introduces a conditional expression whose result must evaluate to TRUE if the transaction is to be rolled back. The expression can specify aggregate operations and scalar subqueries. See “Scalar Subqueries” on page 142 and “Rules for Using Scalar Subqueries in ABORT Statements” on page 304 for the rules and restrictions.

If the WHERE clause is omitted, termination is unconditional. See “WHERE Clause” on page 119.

The WHERE condition specifies an expression whose result must evaluate to TRUE if termination is to occur. If the result is FALSE, transaction processing continues.

ANSI Compliance

ROLLBACK is ANSI SQL:2011-compliant.

The following ROLLBACK options are Teradata extensions to the ANSI standard:

  • abort_message
  • FROM_clause
  • WHERE_clause
  • Other SQL dialects support similar non‑ANSI standard statements with names such as the following:

  • ROLLBACK TRANSACTION
  • ROLLBACK WORK
  • Required Privileges

    None.

    Definition and Termination of ANSI Transactions

    In ANSI session mode, the first SQL request in a session initiates a transaction. The transaction is terminated by issuing either a COMMIT or a ABORT/ROLLBACK request. Request failures do not cause a rollback of the transaction, only of the request that causes them.

    ROLLBACK Is Explicit

    There are no implicit transactions in ANSI session mode. More accurately, each ANSI transaction is initiated implicitly, but always completed explicitly. A COMMIT or ABORT/ROLLBACK request must always be explicitly stated and be the last request of a transaction in order for the transaction to terminate successfully.

    In ANSI session mode, you must issue an ABORT/ROLLBACK (or COMMIT) request even when the only request in a transaction is a SELECT or SELECT AND CONSUME.

    In the case of a SELECT request, it makes no difference whether you issue a COMMIT or an ABORT/ROLLBACK.

    In the case of a SELECT AND CONSUME, there is a difference in the outcomes between issuing a COMMIT or ABORT/ROLLBACK because an ABORT or ROLLBACK request reinstates the subject queue table of the request to its former status, containing the rows that were pseudo‑consumed by the aborted SELECT AND CONSUME request.

    Rules for Embedded SQL

    ROLLBACK observes the following rules:

  • ROLLBACK cannot be performed as a dynamic SQL statement.
  • ROLLBACK is not valid when you specify the TRANSACT(2PC) option to the preprocessor.
  • ROLLBACK and ABORT Are Synonyms

    With the exception of the presence of the WORK keyword, ROLLBACK is a synonym for ABORT.

    The COMMIT statement also causes the current transaction to be terminated, but with commit rather than rollback.

    See “ABORT” on page 301 and “COMMIT” on page 343.

    Actions Performed by ROLLBACK

    ROLLBACK performs the following actions:

    1 Backs out changes made to the database as a result of the transaction.

    2 Deletes spooled output for the request.

    3 Releases locks associated with the transaction.

    4 If the transaction is in the form of a macro or a multistatement request, bypasses execution of the remaining statements.

    5 Returns a failure response to the user.

    Actions Performed by ROLLBACK With Embedded SQL

    ROLLBACK performs the following additional actions when used within an embedded SQL application:

    1 See “Actions Performed by ROLLBACK” on page 486.

    2 Discards dynamic SQL statements prepared within the current transaction.

    3 Closes open cursors.

    4 Cancels database changes made by the current transaction.

    5 Sets SQLCODE to zero when ROLLBACK is successful.

    6 Sets the first SQLERRD element of the SQLCA to 3513 or 3514, depending on whether abort_message is specified.

    7 Returns an abort message in the SQLERRM field of the SQLCA if a WHERE_clause is specified.

    8 Terminates the application program connection (whether explicit or implicit) to Teradata Database if the RELEASE keyword is specified.

     

    IN this environment …

    IF no LOGON/CONNECT request precedes the next SQL request, THEN the preprocessor …

    Mainframe-attached

    attempts to establish an implicit connection.

    Workstation-attached

    issues a No Session Established error.

    ROLLBACK With A WHERE Clause

    ROLLBACK tests each value separately, so a WHERE clause should not introduce both an aggregate and a nonaggregate value.

    The aggregate value becomes, in effect, a GROUP BY value, and the mathematical computation is performed on the group aggregate results.

    For example, assuming that the following things are true, then the ROLLBACK statement that follows incorrectly terminates the transaction.

  • The table Test contains several rows,
  • The sum of Test.colA is 188, and
  • Only one row contains the value 125 in Test.colB
  •      ROLLBACK 
         WHERE (SUM(Test.colA) <> 188) 
         AND (Test.ColB = 125);

    The preceding request is processed first by performing an all‑rows scan with the condition (ColB  = 125), which selects a single row and then computes intermediate aggregate results with the condition (SUM(ColA) <> 188).

    The condition evaluates to TRUE because the value of ColA in the selected row is less than 188.

    If ROLLBACK … WHERE is used and it requires READ access to an object for execution, then user executing the ROLLBACK statement must have SELECT right to the data being accessed.

    The WHERE condition of a ROLLBACK can include subqueries. The subqueries require FROM clauses and the ROLLBACK request should also have a FROM clause if you want the scope of a reference in a subquery to be the ROLLBACK condition.

    ROLLBACK With a UDT In Its WHERE Clause

    ROLLBACK supports comparisons of UDT expressions in a WHERE clause. A UDT expression is any expression that returns a UDT value.

    If you specify a UDT comparison, then the UDT must have a defined ordering. See CREATE ORDERING in SQL Data Definition Language Syntax and Examples. See “Example 1: ROLLBACK With a UDT In The WHERE Clause” on page 489.

    Rules for Using a Scalar UDF in a ROLLBACK Request

    You can invoke a scalar UDF in the WHERE clause of a ROLLBACK request if the UDF returns a value expression. The scalar UDF must be invoked from within an expression that is specified as the search condition. See “Example 2: Using an SQL UDF in the WHERE Clause of a ROLLBACK Request” on page 489.

    Rules for Using Correlated Subqueries in a ROLLBACK Request

    The following rules apply to correlated subqueries used in a ROLLBACK request:

  • A FROM clause is required for a ROLLBACK request if the ROLLBACK references a table. All tables referenced in a ROLLBACK request must be defined in a FROM clause.
  • If an inner query column specification references an outer FROM clause table, then the column reference must be fully qualified.
  • Correlated subqueries, scalar subqueries, and the EXISTS predicate are supported in the WHERE clause of a ROLLBACK request.

    Also see “Rules for Using Scalar Subqueries in ROLLBACK Requests” on page 488 and “SAMPLE Clause” on page 175.

    Rules for Using Scalar Subqueries in ROLLBACK Requests

    You can specify a scalar subquery in the WHERE clause of a ROLLBACK request in the same way you can specify one for a SELECT request.

    You can also specify a ROLLBACK request with a scalar subquery in the body of a trigger. However, Teradata Database processes any noncorrelated scalar subquery you specify in the WHERE clause of a ROLLBACK statement in a row trigger as a single‑column single‑row spool instead of as a parameterized value.

    Multiple ROLLBACK Requests

    If a macro or multistatement request contains multiple ROLLBACK requests, those requests are initiated in the order they are specified, even if the expressions could be evaluated immediately by the parser because the request does not access any tables (see “Two Types of ROLLBACK Requests” on page 488). However, the system can process ROLLBACK requests in parallel.

    If the system executes the ROLLBACK requests in parallel, and one of the requests actually does roll back, then the system reports that rollback regardless of its specified sequence in the macro or multistatement request.

    You can examine the EXPLAIN report for the macro or multistatement request to determine whether the system is executing the ROLLBACK requests in parallel or not.

    Two Types of ROLLBACK Requests

    There are two categories of ROLLBACK requests, those that can be evaluated by the parser and do not require access to a table and those that require table access.

    If ROLLBACK expressions are processed that do not reference tables, and if their order of execution is not important relative to the other requests in a multistatement request or macro, then they should be placed ahead of any requests that reference tables so that the rollback operation can be done at minimum cost.

    In the following example, the first two ROLLBACK requests can be evaluated by the parser and do not require access to tables. The third ROLLBACK request requires access to a table.

         CREATE MACRO macro_name (
           p1 INTEGER, 
           p2 INTEGER)
         AS. . .
         ROLLBACK 'error' WHERE :p1 < 0;
         ROLLBACK 'error' WHERE :p2 < 0;
         SELECT. . .
         ROLLBACK 'error' WHERE tab.c1 = :p1;

    ROLLBACK With BTEQ

    If you use ROLLBACK in a BTEQ script with either the .SESSION or the .REPEAT command, you must send the ROLLBACK statement and the repeated SQL statement as one request.

    If you send the repeated request without the ROLLBACK, one of the requests is eventually blocked by other sessions and the job hangs because of a deadlock.

    Example : ROLLBACK With a UDT In The WHERE Clause

    The following examples show correct use of UDT expressions in the WHERE clause of a ROLLBACK request:

         ROLLBACK WHERE (tab1.euro_col < CAST(0.0 AS euro));
     
         ROLLBACK WHERE (tab1.cir_col.area() < 10.0);

    Example : Using an SQL UDF in the WHERE Clause of a ROLLBACK Request

    The following ROLLBACK request specifies an SQL UDF in its WHERE clause search condition.

         ROLLBACK FROM t1 
         WHERE a1 = test.value_expression(2,3);

    Related Topics

  • “ABORT” on page 301
  • “COMMIT” on page 343
  • SQL Request and Transaction Processing
  • Temporal Table Support