Hana features unknown - rollback a truncate table statement

[werner_daehn]

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?