I understand that the CREATE FUNCTION statement implies an "Automatic commit", but I have a situation where simply calling the function automatically commits the transaction.

It seems to be related to the fact that the function calls the Java EXTERNAL ENVIRONMENT. For example, with the Java code as:

public class Test {
    public static String jtest() {
        return "jTEST";
    }
    public static void main(String[] args) {
        System.out.println( jtest() );
    }
}
and the SQL code as:
INSTALL JAVA UPDATE FROM FILE 'D:/eclipse_workspace/TEST/bin/Test.class';

CREATE OR REPLACE FUNCTION jtest() RETURNS LONG VARCHAR EXTERNAL NAME 'Test.jtest()Ljava/lang.String;' LANGUAGE JAVA;

you can run the test:
CREATE OR REPLACE TABLE TEST(
    VAL LONG VARCHAR
);
SELECT * FROM TEST;
INSERT INTO TEST VALUES(123);
SELECT * FROM TEST;
SELECT jtest();
ROLLBACK;
SELECT * FROM TEST;
I would expect the last SELECT to return no rows; however, the inserted row has been committed.

If you run the test without the calling the external environment function (i.e., comment out the SELECT jtest(); statement), the transaction is rolled back as expected.

I'm running SQL Anywhere 17.0.4.2053 on Windows 7.

Can anyone explain this behaviour? What am I missing?

Thanks,

asked 05 Aug '16, 11:32

Terry%20Wilkinson's gravatar image

Terry Wilkinson
531202337
accept rate: 28%

I can reproduce this ... and will look into it more ...

I suspect this was also possible in 17.0.0 GA ... can you confirm what you're experience there was?

(05 Aug '16, 13:19) Nick Elson S...

Sorry Nick - don't have that version available.

However, I just installed SA 16.0.0.2043 and tried it there and it works properly, i.e., the ROLLBACK in fact rolls back the transaction even when doing the call to jtest(). Seems the behaviour change happened after that version.

(05 Aug '16, 13:48) Terry Wilkinson

Nick, I realized I had a linux version of 17.0.0.1358 available under virtualbox and tried it there - it worked properly there too ( i.e., the ROLLBACK in fact rolls back the transaction even when doing the call to jtest().)

(07 Aug '16, 23:25) Terry Wilkinson

Thanks for letting us know ... With the limited cycles here, having fewer things to narrow down goes a log way ...

(08 Aug '16, 18:04) Nick Elson S...
2

This looks like this is most likely tied to the server-side "auto_commit" option; a new feature of version 17

I'm still researching how that is actually working under the covers and will cycle back around when I find out more.

(09 Aug '16, 10:25) Nick Elson S...
Replies hidden
1

> ...server-side "auto_commit"; a new feature...

At least The Watcom Rule is in force (the default is 'Off') but seriously?

Does the world really need another way to autocommit?

(09 Aug '16, 13:50) Breck Carter

Nick, I know it's been a while, but I just installed version 17.0.8.4046 and it seems this issue still exists (I had been using version 17.0.0.1358 for development which does not have the issue).

Has there been a resolution, or am I stuck with installing an older version in production too?

(27 Oct, 15:50) Terry Wilkinson

Have you tried to use the new ClientAutocommit connection parameter introduced in build 17.0.4.2087?

Note, I don't know if this is related to your issue at all, it's just a very wild guess. Please refer to the changes to auto commit mode made in that build.

(02 Nov, 08:22) Volker Barth

I can't seem to find docs on that parameter. Do you have the link to the DocCommentXchange description?

(02 Nov, 16:27) Terry Wilkinson

You can find the read me files online here:

http://sqlasupport.sap.com/readme/index.html

I took the information from there...

(02 Nov, 17:58) Volker Barth
More comments hidden
showing 5 of 10 show all flat view

To workaround this issue, issue a START EXTERNAL ENVIRONMENT JAVA rather than having it started as needed.

permanent link

answered 03 Nov, 10:59

Chris%20Keating's gravatar image

Chris Keating
3.2k1956
accept rate: 29%

converted 09 Nov, 06:50

Could you tell more about that workaround and its effect?

(03 Nov, 11:21) Volker Barth

When should this be done? When database is started? On each connection?

(03 Nov, 13:17) Terry Wilkinson
2

I am still investigating the root cause. I believe it may be related to a change made on or about the release of 17.0.4 in which a fix introduced an internal commit in the external environment. Technically, the STOP EXTERNAL ENVIRONMENT is what we need to avoid to workaround the issue and that can be done by explicitly starting the external environment versus allowing the server to invoke the external environment as needed. The workaround will result in a longer lifetime for the given external environment.

The JAVA external environment is launched as one-per-database. I would add the workaround in an DatabaseStart event so that in the future it could be easily disabled and removed when and if it is no longer needed. A one per database external environment is terminated when the database is stopped so there is not a need to stop the environment manually.

if your server must avoid the longer lifetime, you could narrow the scope to the transactions where the external environment is invoked. This would be more involved and potentially prone to more errors.

(03 Nov, 14:00) Chris Keating
Replies hidden

Thanks Chris. I tried executing START EXTERNAL ENVIRONMENT JAVA in Database Start event but it doesn't seem to fix it - I tried starting the environment in a User Connect event, still no luck - I then tried it as part of by TEST script - still does a commit.

However, I did see one thing I might have missed before. If I run the test script twice, the first run seems to automatically commit as in the OP, but the second and subsequent runs seem to rollback as desired.

I'm running these tests on version SQL Anywhere version 17.0.8.4046 on Ubuntu 14.04.5 LTS

(03 Nov, 15:02) Terry Wilkinson

Let me look into this further. The workaround appears to work consistently for me but I had not specifically tested the implementation I suggested.

(03 Nov, 15:04) Chris Keating
2

I have tested with the following event:

create event WorkaroundExtEnvCommit type DatabaseStart handler begin message '*** Issue START EXTERNAL ENVIRONMENT JAVA as a workaround'; start external environment java end;

and it does workaround the issue. I use the message to confirm that the event was fired (it is sent to the engine console output).

I am running Windows 17.0.8.4075 but I do not think that this is build or platform specific. I will try on a Linux environment to confirm the behavior.

(08 Nov, 20:13) Chris Keating
Replies hidden
1

Well, I don't know what I did wrong when I tried it before, but I used your code now and it worked perfectly. Sorry for that. It's nice to have a work-around, especially one as simple as that! Thanks for your efforts. If you change this to an answer, I can then accept it.

(08 Nov, 21:52) Terry Wilkinson

We have been digging into this issue and are finding the workaround not be as reliable as initially thought. Rather than create a discussion here on options, I would like to propose that you contact me by email so that I can offer other suggestions and once we find a suitable solution post a complete workaround method to close this thread. My email is {first}.{last}@sap.com.

(09 Nov, 14:58) Chris Keating

This issue has been fixed by Engineering Case# 811905 which will be in a future SP build: 16.0 Build 2602 or newer; 17.0 Build 4099 or newer.

(3 hours ago) Chris Keating
More comments hidden
showing 5 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×91
×65
×48
×22
×6

question asked: 05 Aug '16, 11:32

question was seen: 478 times

last updated: 3 hours ago