Calling an EXTERNAL ENVIRONMENT FUNCTION seems to automatically do a COMMIT

[former_SQLA_member1694873]

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,

[Former Member]

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?

[former_SQLA_member1694873]

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.

[former_SQLA_member1694873]

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

[Former Member]

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

[Former Member]

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.

[Breck_Carter]

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

[former_SQLA_member1694873]

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?

[VolkerBarth]

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.

[former_SQLA_member1694873]

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

[VolkerBarth]

You can find the read me files online here:

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

I took the information from there...

[chris_keating · Accepted solution]

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

[VolkerBarth · Accepted solution]

Could you tell more about that workaround and its effect?

[former_SQLA_member1694873 · Accepted solution]

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

[chris_keating · Accepted solution]

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.

[former_SQLA_member1694873 · Accepted solution]

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

[chris_keating · Accepted solution]

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

[chris_keating · Accepted solution]

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.

[former_SQLA_member1694873 · Accepted solution]

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.

[chris_keating · Accepted solution]

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.

[chris_keating · Accepted solution]

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.