The following topics describe how the database processes statements, requests, and transactions.
Statement Processing
- Statements require locks on accessed database objects to make sure of a level of isolation.
- Statements can be submitted individually as requests.
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.
- Statements can be submitted as components of a single-statement request or a multiple-statement request.
Depending on the circumstances, a multiple-statement request can also be functionally equivalent to a database transaction.
- Statements can be components of a macro.
- Statements 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 multiple-statement request. A request with one SQL statement is called a single-statement request.
A request is sent to the database and 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, only when the transaction completes.
For example, consider the following multiple-statement request:
SELECT FROM employee ; UPDATE employee SET salary_amount = salary_amount * 1.1;
The SELECT statement in this multiple-statement 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 multiple-statement request, the system applies the WRITE lock to the multiple-statement request for both the SELECT and the UPDATE statement components of the multiple-statement request.
Processing multiple-statement 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 multiple-statement 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;
- Multiple-statement requests can only include DML statements.
DCL and DDL statements cannot be components of a multiple-statement request.
This property distinguishes multiple-statement requests from macros, which can contain a single DDL statement as the last statement in the request.
- Multiple-statement requests cannot include CALL statements.
CALL is a disallowed DML statement in a multiple-statement request.
- The database runs the statements within a multiple-statement request in the order 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 multiple-statement INSERT requests the outcome of a multiple-statement 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 multiple-statement INSERT requests that use statement independence, only those statements that fail are rolled back, and the successful statements in the multiple-statement request are committed.
See the appropriate Teradata Tools and Utilities documentation to determine whether a client API supports statement independence.
- A multiple-statement 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.
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 may. 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 differentiates the two modes.
The operations of committing or rolling back changes to data are what constitute transaction processing.