Transactions in Teradata

Here is a quick test to show how transactions work in Teradata.

CREATE TABLE t1 (
    Col1    INT 
);
 
INSERT INTO t1 VALUES (1); 
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
 
SELECT Col1 AS AfterInsert FROM t1;

BEGIN TRANSACTION;

UPDATE t1 SET Col1 = Col1 * 10;

SELECT Col1 AS AfterUpdate FROM t1;

ROLLBACK;
--ABORT;

SELECT Col1 AS AfterRollback FROM t1;

Running this query in Teradata SQL Assistant creates two results panes and highlights the ROLLBACK. The message at the bottom states, "ROLLBACK Failed. 3514: User-generated transaction ABORT."

Running this same query in Nexus creates three results panes.

AfterInsert
-----------
1
2
3

Nexus adds a tab here with no results. It appears to correspond to the ROLLBACK.

AfterUpdate
-----------
20
30
10

Neither returns results for the "AfterRollback" query.

Running that query after running the whole script returns the expected (rolled back) results.

AfterInsert
-----------
1
2
3

There is clearly more to understand here. I will update this post as I learn more.

Update 1 (2011-01-07)

Our system uses Teradata transaction semantics. Look here for a quick explanation of transaction semantics.

To see which mode you are running in a session, use HELP SESSION and look for the column "Transaction Semantics".

(Many thanks to my friend and colleague Joshua Skarphol for the details.)