BEGIN TRANSACTION
Purpose
Defines the beginning of an explicit logical transaction in Teradata session mode.
An explicit Teradata session mode transaction must always be terminated with an END TRANSACTION statement. You must always specify both BEGIN TRANSACTION and END TRANSACTION statements to define the limits of an explicit transaction in Teradata session mode.
Syntax
ANSI Compliance
BEGIN TRANSACTION is a Teradata extension to the ANSI SQL:2011 standard.
The statement is valid only in Teradata session mode. If you submit a BEGIN TRANSACTION statement in an ANSI mode session, the system returns an error.
For ANSI session mode transaction control statements, see “COMMIT” on page 343 and “ROLLBACK” on page 484.
Other SQL dialects support similar non‑ANSI standard statements with names such as the following:
Required Privileges
None.
Rules for Embedded SQL
The following rules apply to the use of BEGIN TRANSACTION in embedded SQL:
Explicit Transactions
An explicit, or user‑generated, transaction is a single set of BEGIN TRANSACTION/END TRANSACTION statements surrounding one or more additional SQL statements.
A BTEQ .LOGOFF command following a BEGIN TRANSACTION statement and one or more additional statements also forms an explicit transaction as well as terminating the current session.
Note that a .LOGOFF command aborts the transaction and rolls back the processing results of all the statements that occurred between BEGIN TRANSACTION and .LOGOFF.
All other Teradata session mode transactions are implicit.
Implicit Transactions
An implicit, or system‑generated, transaction, is typically one of the following.
Rules for Transactions Containing DDL Statements
A transaction can be any solitary SQL statement, including a DDL statement.
Note that when a request contains multiple statements, a DDL statement is allowed only if the following things are true:
Teradata Database Transaction Handling Protocol
Teradata Database manages transactions to maintain valid, consistent, and available data for all users. A transaction may consist of one or more requests which are sent one at time and processed as each is received. Locks are applied as needed for each request. Various locks are placed on its associated database objects according to the types of statements contained in the request.
If a statement does not complete successfully or causes processing to time out for some reason, such as a statement error, deadlock, privilege violation, table constraint violation, or premature logoff, the system performs the following transaction management steps.
1 The entire transaction is aborted.
Abort processing performs the following actions.
a Performs an implicit END TRANSACTION.
b Backs out any changes made to the database by the transaction.
c Releases any usage locks associated with the transaction.
d Discards any partially accumulated results (spool files) generated by the transaction.
2 A failure or time-out response is returned to the requestor.
Most statement errors resulting from multistatement INSERT statements abort the entire transaction.
Statement independence is a method of handling multistatement INSERT errors by only rolling back the individual statements that failed, not the entire transaction. For details, see “Rules for Multistatement and Iterated INSERT Requests” on page 383.
Note: The client software you are using must support statement independence to prevent all multistatement requests and iterated INSERT statements in a transaction or multistatement request from being rolled back. Refer to the documentation for your client software for information on support for statement independence.
Specifying SQL Request Modifiers With Explicit Transactions
When the Parser receives a BEGIN TRANSACTION statement, it immediately looks for an SQL statement keyword in the SQL text that follows. Keep this in mind when determining the placement of request modifiers such as EXPLAIN, LOCKING, NONTEMPORAL, and USING.
For example, if the first statement in an explicit transaction is associated with a USING request modifier, that USING request modifier must precede the BEGIN TRANSACTION statement.
Nested BEGIN TRANSACTION/END TRANSACTION Pairs
When BEGIN TRANSACTION/ET pairs are nested, Teradata Database checks to ensure that each BEGIN TRANSACTION statement has a matching END TRANSACTION statement.
The outermost BEGIN TRANSACTION/END TRANSACTION pair defines the explicit transaction; the inner BEGIN TRANSACTION/END TRANSACTION pairs have no effect on the transaction because Teradata Database does not support transaction nesting.
Any embedded multistatement requests and macro executions are considered part of the outermost BEGIN TRANSACTION/END TRANSACTION explicit transaction and are not considered to be implicit transactions in this context.
In a multistatement request, there can be no more than one BEGIN TRANSACTION statement, and if you do specify a BEGIN TRANSACTION statement, it must be the first statement in a request.
For example, the following series of requests is treated as one explicit transaction.
BEGIN TRANSACTION;
SELECT …;
UPDATE …
EXEC a(3,4);
BEGIN TRANSACTION;
UPDATE …;
INSERT …
;INSERT …;
END TRANSACTION;
INSERT …;
END TRANSACTION;
If an error occurs in the middle of a nested BEGIN TRANSACTION/END TRANSACTION, everything rolls back to the initial BEGIN TRANSACTION.
Scenarios
The following scenarios illustrate the use of BEGIN TRANSACTION/END TRANSACTION.
Scenario
Assuming that the department table contains an emp_count column, the following explicit transaction could be used to remove a row from the employee table and then decrement a departmental head count in the department table.
BEGIN TRANSACTION;
DELETE FROM employee
WHERE name = 'Reed C';
UPDATE department
SET emp_count = emp_count -1
WHERE dept_no = 500;
END TRANSACTION;
Scenario
The following example illustrates an explicit transaction in which each INSERT statement is associated with a USING request modifier.
BEGIN TRANSACTION ;
USING ssnumfile (INTEGER)
INSERT INTO employee (soc_sec_no) VALUES (:ssnumfile) ;
USING ssnumfile (INTEGER)
INSERT INTO employee (soc_sec_no) VALUES (:ssnumfile) ;
USING ssnumfile (INTEGER)
INSERT INTO employee (soc_sec_no) VALUES (:ssnumfile) ;
END TRANSACTION ;
Scenario
The following examples illustrate the use a DDL statement in an explicit transaction. These transactions create a volatile table, perform an aggregate operation on the result of another aggregate operation, and then drop the volatile table that was created in the first transaction.
Two transactions are used because a DDL statement must be either the only statement in a transaction or the last statement in a transaction
BEGIN TRANSACTION;
CREATE VOLATILE TABLE dept_sum_sal NO LOG (
dept_no SMALLINT FORMAT '999' BETWEEN 100 AND 900 NOT NULL,
sum_sal DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99')
PRIMARY INDEX(dept_no),
ON COMMIT DELETE ROWS;
END TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO dept_sum_sal
SELECT dept_no, SUM(salary)
FROM employee
GROUP BY dept_no;
SELECT AVG(sum_sal)
FROM dept_sum_sal;
DROP VOLATILE TABLE dept_sum_sal;
END TRANSACTION;
Scenario : Implicit Transaction (BTEQ)
The following example is structured as a BTEQ multistatement request, so it is processed as a single implicit transaction.
Note the placement of the USING modifier and the semicolons. With this construct, the failure of any WHERE conditions causes the transaction to abort and all completed insert and update operations to be rolled back.
USING var1(CHARACTER),
var2(CHARACTER),
var3(CHARACTER)
INSERT INTO test_tab_u (c1) VALUES (:var1)
; INSERT INTO test_tab_u (c1) VALUES (:var2)
; INSERT INTO test_tab_u (c1) VALUES (:var3)
; UPDATE test_tab_u SET c2 = c1 + 1
WHERE c1 = :var1
; UPDATE test_tab_u SET c2 = c1 + 1
WHERE c1 = :var2