Teradata Secure Transactions

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.
  1. MultiStatement
  2. BEGIN / END Transaction (BT / ET)
  3. 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.

• 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
  

• 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.
  

• 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.