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.
Multiple-statement requests are treated as a single atomic unit. That is, all the work done by all the statements in a multiple-statement request is committed or none is.
Apart from transaction semantics, you can write SQL code with explicit specifications to override defaults so that the code 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 BEGIN TRANSACTION (BT) statement is not valid. |
A transaction initiates when no transaction is active and one of the following occurs:
|
| 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. |
The COMMIT request is not valid. You can mix both explicit and implicit transactions within the same script. When a transaction commits, Vantage 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, the 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 Success Response, Warning Response (ANSI Session Mode Only), Error Response (ANSI Session Mode Only), and Failure Response (ANSI Session Mode Only). ANSI mode transactions do not roll back the entire transaction when an error response occurs, and therefore are not universally atomic and do not support the A property of ACID transactions (see ACID Properties of Transactions) in all circumstances. To make sure that your transactions are 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. Locks placed by erroneous requests are not released and the Transient Journal is not deleted from the dictionary. |
Not applicable. 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 rolls the transaction back, 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 only 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 Therefore, duplicate rows are allowed when updating or inserting rows into tables. |
SET Therefore, 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 try to perform another DDL request before you issue a COMMIT request, the database returns an Error for the non-valid request, but does not roll back the transaction. The database treats the DATABASE and SET SESSION statements (which are technically DCL 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 try 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. The database treats the DATABASE and SET SESSION statements (which are technically DCL 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:
|
| Logoff behavior | If you log off before 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. |
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. |
| 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. 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. |