Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 114 115 116 117 118 119 < 120 > 121 122 123 124 125 126 .. 241 >> Next

END TRANSACTION Closes a transaction and may be at the end of the query or in a later query.

An explicit, or user-generated, transaction is a single set of BEGIN ExPlicit Transad:ions TRANSACTION/END TRANSACTION statements surrounding

one or more requests.

A LOGOFF command following a BEGIN TRANSACTION statement and one or more requests also forms an explicit transaction; however, this construct aborts the transaction, and the processing results of all the requests between BEGIN TRANSACTION and LOGOFF are rolled back.

All other transactions are implicit.

An implicit, or system-generated, transaction, is typically a macro, a Implicit Transactions 8 data manipulation statement that affects one or more table rows, or

a multistatement request that is not part of an explicit transaction and for which the system automatically supplies “BEGIN/END TRANSACTION” statements.

8-38

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

BEGIN/ END TRANSACTION

Rules for Transactions Containing DDL Statements

Teradata RDBMS Transaction Handling Protocol

A transaction may be any solitary SQL statement, including a DDL statement. However, when a request contains multiple statements, a DDL statement is allowed only if the following are true:

• The transaction is explicit (bracketed by BEGIN TRANSACTION and END TRANSACTION statements).

• The DDL statement is the last statement in the transaction (immediately followed by the END TRANSACTION statement).

The Teradata RDBMS handles transactions to maintain valid, consistent, and available data for all users. When a transaction is received, various locks are placed on the data according to the types of requests contained in the transaction. Then, either all the requests in the transaction are processed, or none of them are processed.

If for some reason, such as a statement error, deadlock, access rights violation, table constraint violation, or premature logoff, a request does not complete successfully or causes processing to time out, the following occurs:

Stage

Process

The entire transaction is aborted.

Abort processing performs the following actions.

1

Stage Process
1 Performs an implicit END TRANSACTION.
2 Backs out any changes made to the database by the transaction.
3 Releases any usage locks associated with the transaction.
4 Discards any partially accumulated results (spool files) generated by the transaction.

2

A failure or time-out response is returned to the user.

When the Teradata RDBMS receives a BEGIN TRANSACTION statement, it looks for a statement keyword. The user should be aware of this when determining the organization of statement modifiers. For example, if the first statement in an explicit transaction is associated with a USING clause, the USING clause must precede the BEGIN TRANSACTION statement.

The abbreviation for BEGIN TRANSACTION statement is BT.

The abbreviation for END TRANSACTION statement is ET.

Teradata RDBMS for UNIX SQL Reference

8-39
Teradata SQL Syntax Guide

BEGIN/ END TRANSACTION

Nested BT/ETs

BT/ET pairs can be nested and the Teradata RDBMS checks to ensure that every BT has a matching ET.

The outermost BT/ET pair defines the explicit transaction; the inner BT/ET pairs, other than to validate that they match, have no effect on the transaction. Also, any embedded multi-statement requests and macro executions are considered part of the outermost BT/ET explicit transaction, and are not considered as implicit transactions in this context.

For example, the following is considered one explicit transaction:

BT;

SELECT ...;

UPDATE ....

EXEC A(3,4);

BT;

UPDATE ... ;

INSERT ...

;INSERT ...;

ET;

INSERT ...;

ET;

If an error occurs in the middle of a nested BT/ET, everything rolls back to the initial BT.

Scenarios

The following scenarios illustrate the use of BEGIN/END TRANSACTION.

Scenario l

Assuming that the Department table contains an EmpCount 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 EmpCount = EmpCount -1 WHERE DeptNo = 500;

END TRANSACTION;

To prevent the UPDATE statement from being executed in the event that the DELETE statement fails, or completes without removing a row, the user must check the DELETE return code.

If a “No rows removed” condition is indicated, the user must take action to bypass the update. Otherwise, the count result for department 500 is incorrect. Alternatively, the statements could be defined in a macro containing a conditional ABORT to protect the count.

8-40

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

BEGIN/ END TRANSACTION

The following example illustrates an explicit transaction in which Scenario 2 each statement, including the first, is associated with a USING
Previous << 1 .. 114 115 116 117 118 119 < 120 > 121 122 123 124 125 126 .. 241 >> Next