Transactions handling

Informix®

With the Informix native mode (non ANSI):

  • Transactions blocks start with BEGIN WORK and terminate with COMMIT WORK or ROLLBACK WORK.
  • Statements executed outside a transaction are automatically committed.
  • DDL statements can be executed (and canceled) in transactions.
UPDATE tab1 SET ...   -- auto-committed
BEGIN WORK            -- start of TX block
UPDATE tab1 SET ...
UPDATE tab2 SET ...
...
COMMIT WORK           -- end of TX block
Informix version 11.50 introduces savepoints:
SAVEPOINT name [UNIQUE]
ROLLBACK [WORK] TO SAVEPOINT [name] ]
RELEASE SAVEPOINT name

Netezza®

  • Transactions are started with BEGIN WORK.
  • Transactions are validated with COMMIT WORK.
  • Transactions are canceled with ROLLBACK WORK.
  • Statements executed outside of a transaction are automatically committed.
  • DDL statements can be executed (and canceled) in transactions.
  • If an SQL error occurs in a transaction, the whole transaction is aborted.
  • 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.
Netezza cancels the entire transaction if an SQL error occurs in one of the statements executed inside the transaction. The following code example illustrates this difference:
CREATE TABLE tab1 ( k INT PRIMARY KEY, c CHAR(10) )
WHENEVER ERROR CONTINUE
BEGIN WORK
INSERT INTO tab1 ( 1, 'abc' )
SELECT FROM unexisting WHERE key = 123   -- unexisting table = sql error
COMMIT WORK

With Informix, the above code will leave the table with one row inside, since the first INSERT statement succeeded. With Netezza, the table will remain empty after executing this piece of code, because the server will rollback the whole transaction.

Solution

Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with Netezza: Informix transaction handling commands are automatically converted to Netezza instructions to start, validate or cancel transactions. However, since Netezza is not designed for OLTP applications, you must review any code doing complex data modifications. See the concurrency topic for more details.

You must review the SQL statements inside BEGIN WORK / COMMIT WORK instruction and check if these can raise an SQL error. 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