This topic presents several case studies concerning ANSI session mode transaction semantics. Also see Teradata Session Mode Transaction Processing Case Studies).
Successful ANSI Mode Transaction Example
The following transaction is an example of a successful ANSI mode transaction:
INSERT INTO employee SELECT * FROM employee; *** Insert completed. 26 rows added. |
This single request begins an ANSI mode explicit transaction. WRITE locks are held on employee. |
UPDATE employee SET department_number = 400 WHERE department_number = 401 ;DELETE FROM employee WHERE department_number = 401; *** Update completed. 7 rows changes. *** Delete completed. No rows removed. |
This request is composed of two statements. WRITE locks are still held on employee. |
SELECT * FROM employee WHERE department_number = 401; *** Query completed. No rows found. |
This single statement request is a check to ensure that all employees in department_number 401 were deleted in the previous request. Notice that even though this is a SELECT request, which only requires a READ (or ACCESS) lock, the transaction continues to hold a WRITE lock on employee. |
COMMIT; *** COMMIT done. *** Total elapsed time was 1 second. |
This request terminates the transaction by committing all changes. All locks are released and the Transient Journal is dropped from the dictionary. The next request entered begins another ANSI transaction. |
ANSI Session Mode Error and Failure Responses
When ANSI transaction semantics are in effect, SQL Error responses do not cause a rollback, while failure responses do.
The following example shows how error responses do not roll back a transaction:
INSERT INTO employee SELECT * FROM customer_service.employee; *** Insert completed. 26 rows added. |
This INSERT … SELECT request begins an ANSI mode transaction. |
SELECT * FRM employee; *** Error 3706 Syntax error; SELECT * must have a FROM clause. |
Syntaxer problems are errors, not failures in an open transaction. The inserts from the previous request remain, and all locks continue to be in force. |
SELECT * FROM employee; *** Query completed. 26 rows found. 9 columns returned. |
Correcting the SELECT syntax produces a successful request that indicates the inserted rows from the first request remain in place. The transaction is still not committed. |
SELECT * FROM employee WHERE emp_num = 1010; *** Error 5628 Column emp_num not found in Employee. |
Resolver problems are neither always errors, nor always failures. In this case, the problem evokes an Error response, not a Failure response. The inserts from the first request remain, and all locks continue to be in force. |
SELECT * FROM employee; *** Query completed. 26 rows found. 9 columns returned. |
Repeating the selection of all columns from the table proves that the inserted rows from the first request remain. The transaction is still not committed. |
COMMIT; |
The transaction and the rows inserted in the first request are committed. |
SELECT * FRM employee; *** Failure 3706 Syntax error; SELECT * must have a FROM clause. |
A failure response is returned since there is no open transaction. Note that above, an error response is returned, since a transaction was successfully opened. |
INSERT INTO employee SELECT * FROM customer_service.employee; *** Insert completed. 26 rows added. |
This INSERT … SELECT request begins an ANSI mode transaction. |
CREATE TABLE tbl_1 ( col_1, col_2 INTEGER); *** Error 3739 The user must give a data type for . |
Syntaxer problems are errors, not failures in an open transaction. The inserts from the previous request remain, and all locks continue to be in force. |
SELECT * FROM employee; *** Query completed. 26 rows found. 9 columns returned. |
Selecting all columns from the table proves that the inserted rows from the first request remain. The transaction is still not committed. |
CREATE TABLE tbl_1 ( col_1 INTEGER, col_2 INTEGER); *** Failure 3802 Table ‘tbl_1’ already exists. |
This resolver problem evokes a Failure response, which terminates the transaction. The transaction rolls back and the rows inserted into employee after the commit above are deleted. |
SELECT * FROM employee; *** Query completed. No rows found. |
This request begins an ANSI mode transaction. Selecting all columns from the table proves that the previously inserted rows have all been deleted. |
Placing DDL Requests Within ANSI Mode Transactions
Like Teradata mode transactions, there can only be 1 DDL request in a transaction, and it must be the last sequential request other than a COMMIT request.
Unlike Teradata mode transactions, an ANSI mode transaction does not roll back if you attempt to submit another DDL request before committing the transaction. Instead, it continues to respond with Error responses until the requestor either issues a COMMIT request or a ROLLBACK/ABORT request.
CREATE TABLE table_3 ( col_1 INTEGER); *** Table has been created. |
This single-statement DDL request begins an ANSI mode transaction. No further requests are valid within the boundaries of the current transaction. |
SHOW TABLE table_3; *** Error 3722 Only a COMMIT WORK or null statement is legal after a DDL Statement. |
This single-statement request evokes an Error response because the previous CREATE TABLE request cannot be followed by any other requests within the boundaries of the current transaction. The transaction does not roll back. |
COMMIT; *** COMMIT done. |
A COMMIT request commits the transaction, which is now complete. |
SHOW TABLE table_3; CREATE MULTISET TABLE DB.table_3 ,NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL ( col_1 INTEGER) PRIMARY INDEX ( col_1 ); |
A new ANSI transaction begins with this SHOW TABLE request, which reports the DDL used to create the table named table_3. |
ANSI Mode Transactions and DDL: Multistatement Requests
Like Teradata mode transactions, you cannot mix DDL and DML statements with a single request in ANSI session mode.
The following multistatement request and macro, which have the identical semantics, both fail. An ANSI mode Failure response rolls back the transaction.
SELECT * FROM table_1 ;SELECT * FROM table_2 ;CREATE TABLE table_5 ( col_1 INTEGER); *** Failure 3576 Data definition not valid unless solitary. Statement# 1, Info =0 CREATE MACRO mac_1 AS ( SELECT * FROM table_1; SELECT * FROM table_2; CREATE TABLE table_5 ( col_1 INTEGER);); *** Failure 3576 Data definition not valid unless solitary. Statement# 1, Info =0
ANSI Mode DELETE Performance for Different Transaction Structures
Depending on how you structure a transaction that contains a DELETE request, it can either create a Transient Journal entry for each row deleted from a table or create only one Transient Journal entry for the entire transaction.
The following DELETE is a single request followed by another request to commit the transaction. It writes a Transient Journal entry for each deleted row, so its performance is poor, particularly for large tables.
DELETE FROM table_1; COMMIT;
The following multistatement request contains the same two statements as the transaction above, but because they are packaged as a multistatement request, both statements are seen by the system together. The system does not write a Transient Journal entry for each row deleted from the table, but instead writes a single Transient Journal entry. The multistatement request’s performance is very good.
DELETE FROM table_1 ;COMMIT;
In the first case, the system knows what the next request is and that it needs to be prepared to roll back the transaction. In the second case, the system can see that the delete is to be committed, and therefore the system knows that it does not need to roll back the transaction.