Teradata Secure Transactions
Khatri
Teradata Secure Transactions
- Secure transaction can be defined as if one statement in the sql request fails then the entire transaction must fails and the system must rolls back to original state.
- This property is called Atomicity and is one of the ACID property in DBMS.
- Secure transaction is also called Atomic transaction or implicit transaction.
- There are 3 ways to achieve implicit transactions in Teradata.
- MultiStatement
- BEGIN / END Transaction (BT / ET)
- MACRO
- Implicit statement is a series of DMLs defined considering above 3 ways.
- Teradata can execute some DMLs within implicit statement request(for MultiStatement & MACRO) in parallel, optimizer determines which steps within the request can be executed in parallel.
Note: Tables and data used in the below examples can be found here .
➠
MultiStatement
- A MultiStatement request is an implicit transaction.
- MultiStatement request is a sequence of DMLs with semi-colon(;) at the start of next DML(instead at the end of current DML) and so on.
- There should not be any DDL statement within MultiStatement request.
- Advantage is that dirty reads are not possible as the processed data will be available to other sessions only after all DMLS are successfully executed in MultiStatement request.
- Performance of multiple DMLs executed as multistatement will be better than running DMLs individually.
- Working example
DELETE FROM target_table WHERE EXISTS (SELECT 1 FROM temp_table_1 WHERE target_table.id = temp_table_1.id)
; INSERT INTO target_table
SELECT
id,name
FROM temp_table_1
WHERE NOT EXISTS (SELECT 1 FROM temp_table_1 WHERE temp_table_1.id = temp_table_1.id);
- Failing example: In the below MultiStatement request, as INSERT operation failed with Overflow error therefore delete operation was also rolled back.
DELETE FROM target_table WHERE EXISTS (SELECT 1 FROM temp_table_2 WHERE target_table.id = temp_table_2.id)
; INSERT INTO target_table
SELECT
id,name
FROM temp_table_2;
Failure Output:
*** Failure 2617 Overflow occurred computing an expression involving target_table.id
➠
BEGIN / END Transaction (BT / ET)
- Statements within BT / ET works as an implicit transaction but dirty reads are possible for other sessions.
- To start implicit transaction, BT;is executed first and then series of statements can be executed and finallyET;must be issued to commit the changes to database orROLLBACKcan be executed to rollback the changes.
- Disadvantage is that dirty reads are possible for the participating table, this means that intermediate data will be available as the current state of data to the other sessions.
- Working example
BT;
DELETE FROM target_table WHERE EXISTS (SELECT 1 FROM temp_table_1 WHERE target_table.id = temp_table_1.id);
INSERT INTO target_table
SELECT
id,name
FROM temp_table_1
WHERE NOT EXISTS (SELECT 1 FROM temp_table_1 WHERE temp_table_1.id = temp_table_1.id);
ET;
- Failing example: In the below BTET request, as INSERT operation failed with Overflow error therefore delete operation was also rolled back.
BT;
DELETE FROM target_table WHERE EXISTS (SELECT 1 FROM temp_table_2 WHERE target_table.id = temp_table_2.id);
INSERT INTO target_table
SELECT
id,name
FROM temp_table_2
WHERE NOT EXISTS (SELECT 1 FROM temp_Table_2 WHERE temp_Table_2.id = temp_table_2.id);
ET;
Failure Output:
*** Failure 2616 Numeric overflow occurred during computation.
➠
MACRO
- DMLs defined within MACRO works as an implicit transaction.
- Advantage is that dirty reads are not possible as the processed data will be available to other sessions only after all the statements are executed successfully in MACRO.
- Please visit Macro page to know more about MACRO's in Teradata and how it works.
- Working example
CREATE MACRO implicit_transaction
AS
(
DELETE FROM target_table WHERE EXISTS (SELECT 1 FROM temp_table_1 WHERE target_table.id = temp_table_1.id);
INSERT INTO target_table
SELECT
id,name
FROM temp_table_1
WHERE NOT EXISTS (SELECT 1 FROM temp_table_1 WHERE temp_table_1.id = temp_table_1.id);
)
- Failing example: In the below Macro example, as INSERT operation within MACRO failed with Overflow error therefore delete operation was also rolled back.
CREATE MACRO implicit_transaction
AS
(
DELETE FROM target_table WHERE EXISTS (SELECT 1 FROM temp_table_2 WHERE target_table.id = temp_table_2.id);
INSERT INTO target_table
SELECT
id,name
FROM temp_table_2
WHERE NOT EXISTS (SELECT 1 FROM temp_Table_2 WHERE temp_Table_2.id = temp_table_2.id);
)
Running Macro:
exec implicit_transaction;
Failure Output:
*** Failure 2616 Numeric overflow occurred during computation.