16.10 - Comparison of Transactions in ANSI and Teradata Session Modes - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

ANSI session mode is a state in which transaction processing semantics adhere to the rules defined by the ANSI SQL:2011 specification. Teradata (or BTET) session mode is a state in which transaction processing follows a set of rules defined by Teradata. Teradata session mode provides a means for conducting transaction processing by legacy applications.

In ANSI session mode, each transaction consists of one or more requests, each of which can consist of one or more SQL statements (see Transactions, Requests, and Statements) in ANSI session mode.

Multistatement requests are treated as a single atomic unit; either all the work done by all the statements in a multistatement request is committed or none of it is.

Apart from transaction semantics, you can write SQL code with explicit specifications to override defaults so that it performs identically in both ANSI and Teradata session modes.

Rule ANSI Session Mode Teradata Session Mode
Transaction initiation A transaction initiates when no transaction is active and an SQL request is performed.

A transaction is opened by one of the following.

  • The first SQL request executed in a session.
  • The next request performed in the session following the close of a transaction in ANSI session mode.

The BEGIN TRANSACTION (BT) statement is not valid.

A transaction initiates when no transaction is currently active and one of the following occurs:
  • An SQL request is executed (an implicit transaction).
  • There is a BEGIN TRANSACTION request (an explicit transaction).

    Unless initiated by BEGIN TRANSACTION (BT), the system treats each request as an implicit transaction (see Transactions, Requests, and Statements).

Transaction termination The transaction terminates with either a COMMIT [WORK] or ROLLBACK [WORK] (or ABORT) statement, or a failure, times out, logoff, or system restart.

The END TRANSACTION statement is not valid.

  • An implicit transaction terminates at the end of the request, ROLLBACK or ABORT statement, a failure, times out, or a system restart.

    An implicit transaction terminates when it either completes successfully (Success response) or causes a Failure response. (See SQL Fundamentals for information about success, warning, error, and failure responses.)

  • An explicit transaction terminates with an END TRANSACTION statement, ROLLBACK or ABORT statement, failure, times out, logoff, or system restart.

    In the case of nested transactions, it is not the first END TRANSACTION request encountered that terminates the transaction, but the last.

The COMMIT request is not valid.

You can mix both explicit and implicit transactions within the same script.

When a transaction commits, Teradata Database discards its Transient Journal (see Using the Transient Journal to Roll Back Transactions) and closes any open cursors.

If a transaction is not successfully committed, either explicitly or by an END TRANSACTION statement, Teradata Database rolls back all your requests for the transaction.

Cursors Defaults to being positioned. Defaults to being not positioned.
Error behavior Errors roll back only the request that causes them, not the entire transaction.

See SQL Fundamentals for information about success, warning, error, and failure responses.

This means that ANSI mode transactions are not universally atomic because they do not roll back the entire transaction when an error response occurs. As a result, they do not support the A property of ACID transactions (see ACID Properties of Transactions) in all circumstances.

To ensure that your transactions are always handled as intended, it is critical to code your applications with logic to handle any situation that only rolls back an error-generating request rather than the entire transaction of which it is a member.

Control of character truncation of trailing non-blank characters causes errors in ANSI session mode.

Use the SUBSTRING function to prevent such errors (see SQL Functions, Operators, Expressions, and Predicates ).

Locks placed by erroneous requests are not released and the Transient Journal is not deleted from the dictionary.

Not applicable.

Something that would be an error in ANSI mode is treated as a failure in Teradata session mode.

Failure behavior Failures roll back the entire transaction.

The Transient Journal is first applied, then deleted from the dictionary, and then all locks are released.

When a transaction fails, the system first rolls it back automatically, discards its Transient Journal, and closes any open cursors. There is no need to perform an ABORT or ROLLBACK request to force the rollback to occur in Teradata session mode.

Failure responses roll back the entire transaction, not just the request that evokes them. Error responses do not occur, that is, any response that would be treated as an error in ANSI session mode is treated as a failure in Teradata Session mode.

Default attribute for character comparisons CASESPECIFIC NOT CASESPECIFIC
Default TRIM behavior TRIM(BOTH FROM …) TRIM(BOTH FROM …)
Control of character truncation errors Error response for truncation problems with trailing non-pad characters. Truncation problems never cause failures; the truncation is silently applied.
Default table type semantics MULTISET

This means that duplicate rows are allowed when updating or inserting rows into tables.

SET

This means that duplicate rows are not allowed when updating or inserting rows into tables.

DDL statement placement Must be the only statement in the last request in the transaction, other than an optional COMMIT statement.

If you attempt to perform another DDL request before you issue a COMMIT request, Teradata Database returns an Error for the non-valid request, but does not roll back the transaction.

Although they are technically DCL statements, Teradata Database treats the DATABASE and SET SESSION statements as DDL statements for the purposes of handling transactions.

Must be the only statement in the last request in the transaction, other than the END TRANSACTION for an explicit transaction.

If you attempt to perform another DDL request before you commit the transaction, the system returns a failure response for the non-valid request, and then rolls back the transaction.

Although they are technically DCL statements, Teradata Database treats the DATABASE and SET SESSION statements as DDL statements for the purposes of handling transactions.

Statements performed through a logon startup string Not applicable. Must follow standard rules for the following:
  • Transaction definition
  • Transaction termination
  • DDL request placement
Logoff behavior If you log off prior to committing your work, then the system rolls back all your transaction requests.  
Two-phase commit (2PC) Not supported.

Two-Phase Commit is a method of ensuring that updates in a distributed database management system either commit to all target nodes in the transaction or all roll back.

See Introduction to Teradata.

Supported.

Two-Phase Commit is a method of ensuring that updates in a distributed database management system either commit to all target nodes in the transaction or all roll back.

See Introduction to Teradata.

Locks The most exclusive locks (READ, WRITE, EXCLUSIVE) are retained at the highest level (rowkey, rowhash, partition, table, and so on) and are not released until a transaction is committed or rolled back.

Teradata Database checks the number of control blocks used for Rowhash locks in a transaction and aborts it if the number exceeds the threshold set for the DBS Control field MaxRowHashBlocksPercent (see Utilities for details).

Any locks that are placed for a request that is rolled back are not released because of an error response.

The most exclusive locks (READ, WRITE, EXCLUSIVE) are retained at the highest level (rowkey, rowhash, partition, table, and so on) and are not released until a transaction is committed.

Teradata Database checks the number of control blocks used for rowhash locks in a transaction and aborts it if the number exceeds the threshold set for the DBS Control field MaxRowHashBlocksPercent (see Utilities for details).