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 BEGIN TRANSACTION (BT) statement is not valid. |
A transaction initiates when no transaction is currently 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 Teradata Vantage™ - SQL Fundamentals, B035-1141 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. For more information, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145. 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, the database returns an Error for the non-valid request, but does not roll back the transaction. Although they are technically DCL statements, the 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, the 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:
|
| 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. For more information, see Teradata Vantage™ - Database Introduction, B035-1091. |
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. For more information, see Teradata Vantage™ - Database Introduction, B035-1091. |
| 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. The 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. For more information, see Teradata Vantage™ - Database Utilities, B035-1102. 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. The 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. For more information, see Teradata Vantage™ - Database Utilities, B035-1102. |