Network driver setTransactionIsolation() causes a commit, but does not complete it locally

• Key: DERBY-638
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Bernt M. Johnsen
• Reporter: Deepa Remesh
• Component/s: Network Client
• Affects Version/s: 10.1.3.1, 10.2.1.6, 10.3.1.4
• Fix Version/s: 10.1.3.2, 10.2.2.0, 10.3.1.4

Description

When autocommit is set to false, a call to setTransactionIsolation using client driver does not end the transaction when the method exits. When a close() is called on the conection, it throws an exception.

Running the code below:

       conn.setAutoCommit(false);
       conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
       try{
               conn.close();
       }catch(SQLException se){
               System.out.println("Got exception when closing the connection");
               se.printStackTrace();
       }

with client driver gives:
Got exception when closing the connection
org.apache.derby.client.am.SqlException: java.sql.Connection.close() requested while a transaction is in progress on the connection.The transaction remains active, and the connection cannot be closed.

with embedded driver, it works okay and does not throw any exception.

Comments

[Deepa Remesh]

Attaching a repro for this. To run with embedded driver, use "java d638". To run with client driver, start network server on port 1528 and use "java d638 client". Client driver throws an exception. It should not throw this exception and should match the behaviour of embedded driver.

[Satheesh Bandaram]

I think this bug needs to be cross linked to DERBY-310, that tracks all known differences between Derby client and embedded drivers.

http://issues.apache.org/jira/browse/DERBY-310

[Bernt M. Johnsen]

Simple patch for this case. Ideally setTransactionIsolation should have been done with some DRDA mechanism piggybacking the next query/update and (e.g. like setQueryTimeout were done), but this would do as long as we're using statement.execute("SET CURRENT ISOLATION...");

[Bernt M. Johnsen]

After some investigation, I have found that this is actually three separate issues:

1) There is a difference between the embedded driver where setTransacionIsolation does not cause a commit and the nework client where setTransactionIsolation causes a commit (Side effect of using SET CURRENT ISOLATION).

2) setTransactionIsolation in the network client does not do the proper householding activity wrt. this is an implicit commit and that Statement.execute("SET CURRENT ISOLATION...") is used to implement it, and hence you get the exception documented in the description

3) (Small) The SQLState and error message is different when Connection.close() is done on an active transaction

Suggest that 1) & 3) is placed into separate issues while this issue is related to 2) which is the short term fix for the reported exception.

BTW: I think the attached patch si not sufficient in the general case, where there may be some open transaction when setTranascationIsolation is called, and that transaction is implicitely committed.

[Bernt M. Johnsen]

Simpler, cleaner, more correct.

[Bryan Pendleton]

Hi Bernt, I haven't read your code, just trying to follow the comments, but: are you proposing
to change the client to match the embedded behavior, or are you proposing to change
embedded to match the client behavior (or maybe you're proposing some 3rd thing entirely)?

[Deepa Remesh]

Thanks Bernt for looking at this issue.

Patch1 seems better to me and will match more with the embedded implementation. With patch1, we do not commit any other statements which could be executed in the meantime. But with patch2, client driver will commit everything executed before the call to setTransactionIsolation. This, I think is not expected when we have explicitly set the connection's auto-commit to false.

I am attaching a modified repro 'd638_repro2.java' which executes some statements after setting auto-commit to false and before the call to setTransactionIsolation. With patch1, both embedded and client driver show uncommitted transactions. With patch2, client driver commits everything.

However, it is not clear to me what the correct behaviour should be as the JDBC spec only has recommendations for the implementation of setTransactionIsolation method. But I think it will be good to keep embedded and client behaviour similar.

[Bernt M. Johnsen]

Bryan: I propose that we on the short term (and in 10.2 and possibly 10.1) fix the misleading exception which this report is about, but keep the difference. And that the different behaviour will be fixed e.g. for 10.3

Deepa: The current implementation will commit the active transaction. The exception is misleading, since there is no active transaction after setTransactionIsolation in the client. Try out d638_repro3.java. I agree that trying to make embedded and client behaviour similar is ideal, but neither of my patches will actually fix that.

The JDBC spec just says that "If this method is called during a transaction, the result is implementation-defined."

[Deepa Remesh]

I now see the current implementation will commit the transaction in all cases. The exception was indeed misleading and DERBY-638-v2.diff is more correct. I agree it will be good to fix this misleading exception and open separate JIRAs for the other two issues. It will be good if the repro can be included as a test case in the patch.

[Bernt M. Johnsen]

Uploaded new patch (V3) whith test change that will catch changes in wether setTransactionIsolation commits or not.

Committed revision 474720.

[Bernt M. Johnsen]

Committed revision 475651

[Bernt M. Johnsen]

Backport for 10.1 branch

[Bernt M. Johnsen]

Backported from trunk.
Committed revision 475707

[Andrew McIntyre]

This issue has been resolved for over a year with no further movement. Closing.