Netezza Transactions BEGIN, COMMIT, ROLLBACK

Reading Time: 2 minutes

Netezza  transactions can be summarized using the following :

  • Transactions start with BEGIN WORK.
  • Transactions are validated with COMMIT WORK.
  • Transactions are canceled with ROLLBACK WORK.
  • Statements executed outside of a transaction are automatically committed.
  • If an SQL error occurs in one of the statements executed inside the transaction, Netezza cancels the entire transaction 
  • DDL statements can be executed (and canceled) in transactions.
  • A transaction must only contain INSERTs if you want concurrent processes to insert rows at the same time (UPDATEs/DELETEs lock the whole table).
  • Only the SERIALIZABLE isolation level is implemented by Netezza.
BEGIN WORK
INSERT INTO existingtable ( 1, 'abc' )   -- successful sql
SELECT FROM unexisting WHERE key = 123   -- unexisting table = sql error
COMMIT WORK

The table will remain empty after executing this piece of code, because the server will rollback the whole transaction.

Solution

Since Netezza is not designed for OLTP applications, review any code doing complex data modifications to ensure it doesn’t error out. To get the same behavior in case of error when connected to a different database than Netezza, you must issue a ROLLBACK to cancel all the SQL statements that succeeded in the transaction, for example with a TRY/CATCH block.

TRY
  BEGIN WORK
  ...
  COMMIT WORK
CATCH
  ROLLBACK WORK
END TRY

BEGIN/COMMIT/ROLLBACK commands takes the optional input parameters, WORK or TRANSACTION – and have no effect on the transactions.

RollBack

Four Isolation Levels

   
Isolcation Level   
   
Uncommited Data   
   
Non Repeatable   Data   
   
Phantom Data   
   
Uncommitted   
   
Y   
   
Y   
   
Y   
   
Committed   
   
N   
   
Y   
   
Y   
   
Repeatable Read   
   
N   
   
N   
   
Y   
   
Serializable   
   
N   
   
N   
   
N   
Isolation levels

Uncommitted Data: Transaction can see data from another transaction which is still not committed

Non Repeatable Data: When a transaction tries to read the same data, the data is changed by another transaction but committed after the previous read.

Phantom Data: When a transaction tries to read the same data, the data previously read is not changed but new rows has been added which satisfies the previous query criteria i.e. new rows has been added to the data rows.