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

SAP HANA®

Transactions in SAP HANA:

  • Beginning of transactions are implicit; two transactions are delimited by COMMIT or ROLLBACK.
  • DDL statements can be executed in transactions, but these will persist on rollback.
SAP HANA (version 2 SPS 04) supports savepoints with similar features as Informix SQL with:
  • SAVEPOINT name
  • ROLLBACK TO SAVEPOINT name
  • RELEASE SAVEPOINT name

Solution

The Informix behavior is simulated with an autocommit mode in the SAP HANA database interface. A switch to the explicit commit mode is done when a BEGIN WORK is performed by the BDL program. Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with SAP HANA.

Note:

Savepoints are not supported.

See also SELECT FOR UPDATE