Saturday, 30 November 2019 17:05

Hana features unknown – rollback a truncate table statement

Written by https://blogs.sap.com/2019/12/01/hana-features-unknown-rollback-a-truncate-table-statement/
Rate this item
(0 votes)

When deleting the entire table contents of a large table, either a delete can be used or a truncate table.

  • The delete takes a long time but deleting and inserting the data
    in one transaction does have the least impact to other users.
  • The truncate table is very fast but has an implicit commit.

At least, so I thought. But actually, a truncate is rollback-able:

set transaction autocommit ddl off; select count(*) from mytable; truncate table mytable; select count(*) from mytable; rollback; select count(*) from mytable;

I noted that when writing a procedure which does truncate the data and then reloads the data using an SDI FlowGraph. The load failed due to a lock, which I could not explain at first.

A simple example to proof the point:

CREATE PROCEDURE LOCKTEST ( ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN exec 'truncate table mytable'; begin autonomous transaction insert into mytable(pk, col1) values (1, 'Hello World'); end; END;

The procedure will not succeed because the truncate table statement creates an exclusive lock in the procedure session, the autonomous transaction runs in another session but cannot insert the record, because of the still held lock from the truncate table. A Flowgraph is just another example of an insert statement running in another transaction and therefore facing the same issue.

A commit after the truncate table solves this, obviously.

I did not expect that a procedure has this autocommit ddl flag off by default. In Hana 2.0 SP4 this is explained and the create procedure statement got a new setting

AUTOCOMMIT DDL { ON | OFF }

see help.sap.com for details

In order to make sure I got it right I added an output table variable to the procedure and returned the m_session_context for the current session. Indeed the auto_commit_dll was set to off when checked inside the procedure, even in Hana 1.0 SP12.

Interesting, isn’t it?

Read 20 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.