Is Stored Procedure a Single Transaction in HANA?

[Former Member]

Hi ,

For a HANA project, we plan to use stored procedures to update some master data tables, which are used to break down one structured sales article into component articles (for example, one carton of beer can be broken down into 24 cans of beer ) for stock on hand calculation.

Inside those procedures we have some DELETE and INSERT statements.

For example in one of the procedures:

• DELETE FROM Table_A;
• INSERT INTO Table_A;

And we do concern about if there will be a dirty read of ‘Table_A’ between DELETE and INSERT.

So I did a testing to let the procedure wait for a few seconds after the SQL statements.

It shows that all changes to tables will only be committed after the whole procedure is finished.

Then can we say that Stored Procedure is a single transaction in HANA and we can use store procedure to combine a few transactions (SQL statements) into one transaction?

We are working with HANA 1.0 rev 85 right now and will upgrade to rev 97 later on. Any idea about changes in the new version?

Could anyone help to confirm if a procedure is one single transaction?

Any comment will be highly appreciated.

Thanks and regards,

Matt

[former_member186082 · Accepted solution]

Hi Matt,

Yes, your understanding is correct. Commit will be performed only on completing the procedure. Till then data is not committed to database.

For example, in your procedure DELETE and INSERT completes successfully and as part of next dirty SQL, if the procedure fails(due to divide by zero error - overflow) etc, DELETE and INSERT are also not committed. It works like DO ALL or NOTHING.

To have intermediate saves to database, you will have to use COMMIT in the procedure.

Regards,

Chandra.

[Former Member · Accepted solution]

Hi Chandra,

Thanks for your reply!

Is transaction management statement like COMMIT only available at later version of HANA?

I tried in my HANA system (1.0 rev 85), got syntax error says it's not support yet.

Regards,

Matt

[former_member186082 · Accepted solution]

Hi Matt,

It is possible with previous versions as well. We have to use Dynamic SQL to COMMIT in between the procedure.

Regards,
Chandra.

[Former Member]

This message was moderated.

[former_member182302]

Hi Matt,

Adding to Chandra's comments.

Yes it is a single transaction, It is possible with Dynamic SQL to commit but it is not supported by SAP.

Have a check on this link

Regards,

Krishna Tangudu

[thomas_jung]

>Yes it is a single transaction, It is possible with Dynamic SQL to commit but it is not supported by SAP.

Not true as of SPS 10.

COMMIT and ROLLBACK - SAP HANA SQLScript Reference - SAP Library

[former_member182302]

Hi

Thanks for the sharing this update. I will update this link to the blog as well. Have been looking forward for this.One more reason to upgrade to SP10.

Just wanted to know if TRUNCATE is also getting supported for tables ? As of now on SP8 , I understand it is only supported on temporary tables and we cannot achieve this by dynamic SQL as well. Let me know if I need to create a separate discussion for the same.

Regards,

Krishna Tangudu