The following topics describe how Teradata Database processes statements, requests, and transactions.
In Teradata SQL, statements have the following properties:
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.
Depending on the circumstances, a multistatement request can also be functionally equivalent to a database transaction.
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.
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.
; 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.
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.
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.
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.