Question Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT in Technology Q&A

Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Fri, 05 Aug 2016 11:32:34 GMT

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,

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Fri, 05 Aug 2016 13:19:23 GMT

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?

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Fri, 05 Aug 2016 13:48:27 GMT

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.

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Sun, 07 Aug 2016 23:25:01 GMT

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().)

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Mon, 08 Aug 2016 18:04:31 GMT

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

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Tue, 09 Aug 2016 10:25:45 GMT

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.

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Breck_Carter — Tue, 09 Aug 2016 13:50:38 GMT

> ...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?

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Fri, 27 Oct 2017 15:50:03 GMT

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?

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

VolkerBarth — Thu, 02 Nov 2017 08:22:06 GMT

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.

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Thu, 02 Nov 2017 16:27:16 GMT

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

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

VolkerBarth — Thu, 02 Nov 2017 17:59:00 GMT

You can find the read me files online here:

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

I took the information from there...

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

chris_keating — Fri, 03 Nov 2017 10:59:16 GMT

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

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

VolkerBarth — Fri, 03 Nov 2017 11:21:05 GMT

Could you tell more about that workaround and its effect?

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Fri, 03 Nov 2017 13:17:26 GMT

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

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

chris_keating — Fri, 03 Nov 2017 14:00:47 GMT

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.

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Fri, 03 Nov 2017 15:02:06 GMT

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

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

chris_keating — Fri, 03 Nov 2017 15:04:46 GMT

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

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

chris_keating — Wed, 08 Nov 2017 20:13:39 GMT

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.

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

Former Member — Wed, 08 Nov 2017 21:52:53 GMT

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.

Re: Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

chris_keating — Thu, 09 Nov 2017 14:58:21 GMT

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.