Transaction isolation level set via Connection#setTransactionIsolation is lost,
when statement are executed on such a connection that trigger an MaxAllowedPacketException.
Although the connection is still usable, it silently switches back to the default isolation behaviour,
Transaction isolation level set via {{Connection#setTransactionIsolation}} is lost,
when statement are executed on such a connection that trigger an MaxAllowedPacketException.
Although the connection is still usable, it silently switches back to the default isolation behaviour,
Testcase:
{code}
public class IsolationLevelLostOnReconnect {
@Test
public void isolationLevelResets() throws SQLException {
try (Connection c = DriverManager.getConnection("jdbc:mariadb://localhost:3306/test", "test", "test")) {
long max = maxPacket(c);
if (max > Integer.MAX_VALUE - 10) {
fail("max_allowed_packet to high for this test");
}
c.prepareStatement("create table if not exists foo (x longblob)").execute();
c.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
assertThat(level(c), is("READ-UNCOMMITTED"));
try (PreparedStatement st = c.prepareStatement("insert into foo (?)")) {
st.setBytes(1, data((int) (max + 10)));
st.execute();
fail();
} catch (SQLException e) {
assertThat(e.getMessage(), containsString("max_allowed_packet"));
// we still have a working connection
assertThat(maxPacket(c), is(max));
// but our isolation level changed:
assertThat(level(c), is("READ-UNCOMMITTED"));
}
}
}
private long maxPacket(Connection c) throws SQLException {
try (ResultSet rs = c.prepareStatement("select @@max_allowed_packet").executeQuery()) {
rs.next();
return rs.getLong(1);
}
}
private byte[] data(int size) {
byte[] data = new byte[size];
Arrays.fill(data, (byte) 'a');
return data;
}
}
{code}
Javadoc on {{Connection#setTransactionIsolation}} reads (excerpt):
{code}
* Attempts to change the transaction isolation level for this
* <code>Connection</code> object to the one given.
{code}
Clearly, this does not hold in this situation.
Transaction isolation level set via {{Connection#setTransactionIsolation}} is lost,
when statement are executed on such a connection that trigger an MaxAllowedPacketException.
Although the connection is still usable, it silently switches back to the default isolation behaviour,
Testcase:
{code:java}
public class IsolationLevelLostOnReconnect {
@Test
public void isolationLevelResets() throws SQLException {
try (Connection c = DriverManager.getConnection("jdbc:mariadb://localhost:3306/test", "test", "test")) {
long max = maxPacket(c);
if (max > Integer.MAX_VALUE - 10) {
fail("max_allowed_packet to high for this test");
}
c.prepareStatement("create table if not exists foo (x longblob)").execute();
c.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
assertThat(level(c), is("READ-UNCOMMITTED"));
try (PreparedStatement st = c.prepareStatement("insert into foo (?)")) {
st.setBytes(1, data((int) (max + 10)));
st.execute();
fail();
} catch (SQLException e) {
assertThat(e.getMessage(), containsString("max_allowed_packet"));
// we still have a working connection
assertThat(maxPacket(c), is(max));
// but our isolation level changed:
assertThat(level(c), is("READ-UNCOMMITTED"));
}
}
}
private long maxPacket(Connection c) throws SQLException {
try (ResultSet rs = c.prepareStatement("select @@max_allowed_packet").executeQuery()) {
rs.next();
return rs.getLong(1);
}
}
private byte[] data(int size) {
byte[] data = new byte[size];
Arrays.fill(data, (byte) 'a');
return data;
}
}
{code}
Javadoc on {{Connection#setTransactionIsolation}} reads (excerpt):
{code:java}
* Attempts to change the transaction isolation level for this
* <code>Connection</code> object to the one given.
{code}
I did run mvn test locally and there were no differences in the outcome (severel tests failed and several skipped, I assume because of my environment).
Matthias Pretzer
added a comment - The following patch fixes the test case by ensuring non-default isolation levels are applied again upon reconnecting.
diff --git a/src/main/java/org/mariadb/jdbc/internal/protocol/AbstractConnectProtocol.java b/src/main/java/org/mariadb/jdbc/internal/protocol/AbstractConnectProtocol.java
index 95fd170..88c6445 100644
--- a/src/main/java/org/mariadb/jdbc/internal/protocol/AbstractConnectProtocol.java
+++ b/src/main/java/org/mariadb/jdbc/internal/protocol/AbstractConnectProtocol.java
@@ -451,6 +451,9 @@ public abstract class AbstractConnectProtocol implements Protocol {
sessionOption += "," + options.sessionVariables;
}
executeQuery("set session " + sessionOption);
+ if (getTransactionIsolationLevel() != 0) {
+ setTransactionIsolation(getTransactionIsolationLevel());
+ }
}
private void handleConnectionPhases() throws QueryException {
I did run mvn test locally and there were no differences in the outcome (severel tests failed and several skipped, I assume because of my environment).
Bug
Major
The following patch fixes the test case by ensuring non-default isolation levels are applied again upon reconnecting.
diff --git a/src/main/java/org/mariadb/jdbc/internal/protocol/AbstractConnectProtocol.java b/src/main/java/org/mariadb/jdbc/internal/protocol/AbstractConnectProtocol.javaindex 95fd170..88c6445 100644--- a/src/main/java/org/mariadb/jdbc/internal/protocol/AbstractConnectProtocol.java+++ b/src/main/java/org/mariadb/jdbc/internal/protocol/AbstractConnectProtocol.java@@ -451,6 +451,9 @@ public abstract class AbstractConnectProtocol implements Protocol {sessionOption += "," + options.sessionVariables;}executeQuery("set session " + sessionOption);+ if (getTransactionIsolationLevel() != 0) {+ setTransactionIsolation(getTransactionIsolationLevel());+ }}private void handleConnectionPhases() throws QueryException {I did run mvn test locally and there were no differences in the outcome (severel tests failed and several skipped, I assume because of my environment).