The following topics describe how the database processes statements, requests, and transactions.
Statement Processing
- They require locks on the database objects they access in order to ensure a level of isolation.
- They can be submitted individually as requests.
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.
- They can be submitted as components of a single-statement request or a multistatement request.
Depending on the circumstances, a multistatement request can also be functionally equivalent to a database transaction.
- They can be components of a macro.
- They can be components of an SQL procedure.
Request Processing
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 the database. It is processed and a response is returned. Then another request can be sent.
- SQL statement syntax checking (see Syntaxer).
- SQL statement syntax annotation (see Resolver).
- Privilege checking (see Security Checking).
- Request optimization (see Optimizer and Query Rewrite, Statistics, and Optimization).
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). Locks are never released within a request or transaction. They are released when the transaction completes.
For example, consider the following multistatement request:
SELECT FROM employee ; 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.
Processing Multistatement Requests
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;
- They can only include DML statements.
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.
- They cannot include CALL statements.
CALL is a disallowed DML statement in a multistatement request.
- The database executes the statements within a multistatement request in the order in which they are specified with knowledge of dependencies.
- The most exclusive locks required by any individual statement within the request are held for the entire length of the request and the transaction.
- With the exception of multistatement INSERT requests the outcome of a multistatement request, like the outcome of a transaction in Teradata session mode, is all or nothing.
If one statement in the request fails, the entire request fails and the 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 and INSERT ... SELECT in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
- A multistatement request can be committed either implicitly by a line-ending SEMICOLON character in Teradata session mode or explicitly with a COMMIT request in ANSI session mode or an END TRANSACTION request in Teradata session mode.
You can only submit an END TRANSACTION request to terminate an explicit transaction that was initiated with a BEGIN TRANSACTION request (for more information, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).
Transaction Processing
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.