commit in stored procedure ?

[Former Member]

Hi,

Can I use „commit“ in a stored procedure?

I am using SAP HANA SPS5 Rev52v3.0.

I get the error message: SAP DBTech JDBC: [7]: feature not supported

For  example:

create procedure xxx

as

begin

update table_a set …….;

commit;

call long_time_calculation();

end;

Regards,

YH

[Former Member]

Hi John,

I tested your code. The test steps and the result look like the following:

Step	Database connection 1 with your code	Database connection 2	connection 2 result	My comment
1	drop table "_JobPool_RS"
2	create row table "_JobPool_RS"
3	create index "IX_JobPool_RS_JobID"
4	drop table interim_results
5	create row table interim_results
6	drop procedure "JobPool_Create"
7	create procedure "JobPool_Create"
8	CALL "JobPool_Create"()
9	drop procedure "JobPool_Run"
10	create procedure "JobPool_Run"
11	call "JobPool_Run"
12	/* 1000000 inserts and updates inside procedure "JobPool_Run" and procedure "JobPool_Run" is still running. */	select count(*) from interim_results	0	inserts and updates don't commit immediately. Autocommit doesn't work here inside procedure  JobPool_Run .
13		select count(*) from interim_results	0	...
14		select count(*) from interim_results	0	...
15		......		...
16	/* call "JobPool_Run" is finished */	select count(*) from interim_results	100000	Autocommit works here after the end of the procedure JobPool_Run

Now you can test please once again this with exec 'commit' inside procedure. You will immediately see the committed data in the table "interim_results" with connection 2.

Now the result:

1) Your said "The UPDATE command will always commit when it is run"

=> This is not correct. Autocommit doesn't work inside a procedure.

2) My question again: Were the changes inside the procedure really committed before the end of the procedure call ?

=> Please your answer.

3) exec 'commit' is a workaround for autocommit for insert/update inside a procedure.

=> Please your test result.

Regards

YH

[Former Member]

Yes, it all works perfectly on my system including the auto commit. I am not sure why you get different results so you should open a support call with SAP.

John

[Former Member]

No you can't. What are you trying to achieve? The UPDATE command will always commit when it is run.

John

[Former Member]

No, it looks not so, that insert/update in procedure are committed immediately. I suspect that commit is executed at the end of the procedure (after call procedure). Unfortunately the other connection can not see the changes from insert/update before the end of the procedure.

But I've already found the solution. I can use exec ‘commit’ instead commit in procedure. Now I don’t get error message and commit works in procedure immediately after insert/update. The problem is only that it is very slow.

You can try this example:

1) Run this script in the first connection session with and without exec ‘commit’

set schema "MY_SCHEMA";

-----------------------------------------------------------

drop table T1;

create row table T1 (A integer, B varchar(20));

-----------------------------------------------------------

drop procedure COMMIT_TEST;

create procedure COMMIT_TEST

as

begin

  declare vID integer;

  declare vTestCount integer := 0;

  while :vTestCount < 10000

  do

    vTestCount := :vTestCount + 1;

    insert into T1 (A) values (:vTestCount);

    update T1 set B = 'changed' where A = :vTestCount;

    exec 'commit';

    -- This statement min(A) simulates only a "long time calculation"

    select min(A) into vID  from T1;

  end while;

end;

-----------------------------------------------------------

-- Run

call COMMIT_TEST;

2) Run these selects in the second connection session before the end of call COMMIT_TEST in the first session

set schema "MY_SCHEMA";

select count(*) from T1;

select * from T1;

With exec ‘commit’ in procedure you can see the data immediately. Without exec ‘commit’ you will see the data only after the end of the procedure call.

Regards

YH

[Former Member]

Hi Yujun,

Could I recommend that you stop here and read the SQLScript Reference Manual. I wouldn't normally recommend this but all the problems you are describing are covered off here.

The short version is: don't use Dynamic SQL. It is very slow. As I said in your other thread, you have to unnest your loop. That will will solve all your problems.

I am interested though because the data should be visible.

John

[Former Member]

Hi John,

I've already read the  SQLScript Reference Manual. But I didn’t find the solution for commit in procedure. With exec ‘commit’ is my workaround. It works. I hope still a better solution.

But is your answer (The UPDATE command will always commit when it is run) from the SQLScript Reference Manual? Does it work in procedure? No, it doesn’t work in a procedure! 

Why should I stop here? Please ensure first, whether your answer correctly or not. I wish here only the technical discussion, please!

Regards

YH

[Former Member]

So I have rewritten your code and written a wrapper to generate N lines of dummy data. Some design notes:

- We don't use any dynamic SQL and we don't need it - the commit() is implicit and runs correctly on my system.

- Note that I unnested a lot of the code in JobPool_Run and removed all the scalar variables. Scalar variables are bad design in HANA stored procedures.

- Note that I now use a cursor rather than a while loop. This allows a single SELECT statement to obtain all the jobs to run now which is much more efficient.

Some performance notes:

- Your JobPool_Run procedure is very slow and takes 12 seconds for 10k rows on my system. This is because of the use of scalar variables and the WHILE loop.

- The JobPool_Create procedure takes about 0.7 seconds for 10k rows, 6.7 seconds for 100k rows, 67 seconds for 1m rows. Given that this code only runs in one thread, that feels roughly right and it is exactly linear.

- The table interim_results is always populated with the right start time and no commit() is required. Can you please test this on your system.

- The JobPool_Run procedure takes around 2 seconds for 10k rows, 20 seconds for 100k rows, 206 seconds for 1m rows including the write to the interim_results table. Note that this is 30k writes, 300k writes and 3m writes respectively.

- If we move the write to interim_results, this becomes 1.5 seconds for 10k rows, 13.6 seconds for 100k rows, 136 seconds for 1m rows. Note that this is 20k writes, 200k writes and 2m writes respectively.

- Note that in all cases this is approx. 15k individual inserts/sec/core in a loop.

I noticed you said you are running on AWS with cc2.8xl. This is based on an Intel E5-2670 CPU and should be just as fast as the on-premise E7-8870 system I am using for a single core. However the AWS instance may be much worse for writes and this may be part of your problem.

In addition, you mentioned you are running HANA 1.0 SP05 Rev.53. This is a very old revision and there are a lot of performance enhancements to HANA since then. I am running HANA 1.0 SP06 Rev.68 for these tests. It is also possible that there is a bug in HANA which is causing your commit() problem.

Can you update to Rev.68 and rerun these tests.

John

drop table "_JobPool_RS";

create row table "_JobPool_RS"

(

          "JobID" INT CS_INT NOT NULL,

          "JobStart" LONGDATE CS_LONGDATE,

          "JobEnd" LONGDATE CS_LONGDATE,

          "JobParameters" VARCHAR(100) CS_STRING

--          PRIMARY KEY ( "JobID" )

);

-- use PRIMARY KEY ("JobID") or INDEX "IX_JobPool_RS_JobID"

create index "IX_JobPool_RS_JobID" on "_JobPool_RS"("JobID");

drop table interim_results;

create row table interim_results (

          "JobID" INT CS_INT NOT NULL,

          "JobStart" LONGDATE CS_LONGDATE,

          "JobEnd" LONGDATE CS_LONGDATE,

          "JobParameters" VARCHAR(100) CS_STRING

);

drop procedure "JobPool_Create";

create procedure "JobPool_Create"

as

begin

          declare          vJobID integer;

          declare vJobParameters nvarchar(100);

          declare vTestCount integer := 0;

          while :vTestCount < 100000

          do

                    vTestCount := :vTestCount + 1;

                    -- Build a unique set of JobIDs with null Start and End time

                    insert into "_JobPool_RS" ("JobID", "JobStart", "JobEnd", "JobParameters") VALUES (:vTestCount, null, null, 'TEST');

          end while;

end;

CALL "JobPool_Create"();

drop procedure "JobPool_Run";

create procedure "JobPool_Run"

as

begin

-- Get an ordered list of jobs, by JobID in ascending order, where there is no start time

DECLARE CURSOR c_jobs FOR SELECT "JobID", "JobParameters" from "_JobPool_RS" where "JobStart" is null ORDER by "JobID";

FOR row AS c_jobs DO

                    update "_JobPool_RS" set "JobStart" = now() where "JobID" = row."JobID";

--                    call DoJob(cur_job.JobID, cur_job.JobParameters);

                                        select * from "_JobPool_RS" where "JobID" = row."JobID" into interim_results;

                    update "_JobPool_RS" set "JobEnd" = now() where "JobID" = row."JobID";

END FOR;

end;

call "JobPool_Run"();

[Former Member]

Thank you very much for your help! I will update HANA to new version.

Regards

YH

[Former Member]

Can you run that code with 10, 100k, 1m and let us know the timings.

[Former Member]

I would like to keep to the topic “commit in stored procedure”. The topic about performance with the aggregation function min() can be discussed in the other thread, please.

I upgraded HANA to Rev66 (Version 1.00.66.382664). But the commit in procedure still doesn't work with Rev66.  All changes will be committed after the end of the procedure call, but not immediately after insert/update in procedure.

John Appleby wrote:
- We don't use any dynamic SQL and we don't need it - the commit() is implicit and runs correctly on my system.

Were the changes in procedure really committed before the end of the procedure call ?

[Former Member]

You can see my test code - did you use it? There, we update, then select into another table. On my system it works great and the latest timestamp is always inserted into the interim table.

By the way I tested with Rev.52 and Rev.66 and both run the code I wrote great. Can you run the code that I wrote and test, please?

John

[Former Member]

See my test result in next post, please.