SAP HANA - Begin tran, Rollback, commit

Question

I've just started using SAP HANA. I'm coming from SQL Server environment. Is there an equivalent BEGIN TRAN in SAP HANA?

Looks like there are commands Rollback and Commit and I can't figure out when does it work and when is it auto-commit.

I wanted to know if there is something like BEGIN TRAN where I can specify that I don't want the update below to auto-commit

Answer 1

In SAP HANA (as well as in most other DBMS) all your commands are executed in an implicit transaction context. There is no need to explicitly state that you want to begin a transaction now.

When using HANA Studio one thing to note is that the default session setting is to use AUTOCOMMIT, which means, that every single command will be executed in its own transaction and immediately COMMITted. In order to bundle commands together into one transaction, you have to change the session parameter to AUTOCOMMIT=false.

Beyond that, there is the option to run code in an AUTONOMOUS TRANSACTION, which basically gives your procedure its own independent transaction.

The thing that SAP HANA doesn't provide (up to HANA 2 SP02) is "sub-transactions" or "local transaction" - which is what BEGIN TRAN in MS SQL Server does - that you can nest and COMMIT or ROLLBACK in a nested fashion.

This is, of course, documented here and here, so you might want to make yourself familiar with the transaction concepts.

A general remark: doing transaction management within your stored procedures often points towards "step-by-step" processing of data with explicit intermediate result sets (e.g. temporary tables). This is a very expensive approach and should be replaced by the more flexible "data flow" approach in HANA SQL Script. Instead of INSERTS/UPDATES into temp. tables, the results of SELECTs simply are assigned to variables. The SQL Script compiler figures out upon execution what the final (transitive) data transformation looks like, prunes unused variables and processing steps, parallelizes SELECTs where possible and provides the same output faster and/or with less resource usage.

What I'm saying is basically: when porting code from MS SQL Server directly, be aware that there might be better ways to achieve things in SAP HANA.

General remark II: obviously this is my opinion, but I find sub-transactions rather confusing when using stored procedures. Without actually knowing all the sub-transaction logic of a procedure call tree, the client application loses control over the transaction handling. All of a sudden the transaction the client thought was still un-committed has been committed or rolled back - without any indication to the client app. To me, that's a recipe for inconsistencies that should be avoided if possible.