0005150: TransactionIsolationLevel for ManagedConnectionImpl

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0005150

[Resin]

major

always

07-23-12 15:57

07-30-12 10:51

Reporter

alex

View Status

public

Assigned To

ferg

Priority

normal

Resolution

fixed

Status

closed

Product Version

4.0.29

Summary

0005150: TransactionIsolationLevel for ManagedConnectionImpl

Description

rep by. Ryan Johnson

We recently tried to update Resin from version 4.0.23 to version 4.0.29 but had to rollback due to an issue with database connection isolation levels. We have a few locations in our code that explicitly switch the isolation level on a connection from the default level of repeatable read to read committed and then reset the level back to repeatable read prior to calling the close method on the connection. What we are seeing is that future requests that use the same connection are executing as read committed despite not changing the isolation level. They should be executing at the default level of repeatable read.

After digging through the Resin code base, I?ve managed to determine that the ManagedConnectionImpl class is not correctly tracking the default isolation level. If the isolation level of a connection is changed only once prior to the close method being called, Resin behaves as expected and the isolation level is correctly reset back to the default. The error occurs when the isolation level is changed more than once prior to calling the close method. During the second isolation level change, the value of the _oldIsolation field in the ManagedConnectionImpl class is overwritten with the value from the first isolation level change which, if the value was different from the connection?s original isolation level, means that when the connection is closed and reset by Resin, Resin will use the value of the first isolation level change and not the value that the connection had when it was first created.

Here is an example of what I was seeing while debugging the issue.

ManagedConnectionImpl class property values after calling getConnection

_oldIsolation = -1
_isolation = -1

ManagedConnectionImpl class property values after calling setTransactionIsolation(READ_COMMITTED)

_oldIsolation = 4
_isolation = 2

ManagedConnectionImpl class property values after calling setTransactionIsolation(REPEATABLE_READ)

_oldIsolation = 2
_isolation = 4

ManagedConnectionImpl class property values after calling close on the connection

_oldIsolation = 2
_isolation = 2

I?ve attached a JSP that reproduces the error. The JSP assumes that the default isolation level is repeatable read. The JSP has a dummy JNDI datasource name so it will need to be updated with a real one before use. On my machine, I am using MySQL 5.5.24 and version 5.1.18 of the Connector/J driver. The driver class that I?m using is com.mysql.jdbc.Driver.

The first time the JSP loads, it should show this:

The initial isolation level is 4
Switch to isolation level 1
The isolation level is now 1
Switch to isolation level 2
The isolation level is now 2
Closing the database connection.

After refreshing, it should show this:

The initial isolation level is 1
Switch to isolation level 1
The isolation level is now 1
Switch to isolation level 2
The isolation level is now 2
Closing the database connection.

I?m not entirely sure what has changed between 4.0.23 and 4.0.29, but I did notice that in 4.0.23 the connections that had their isolation level modified were destroyed immediately after they were closed. On 4.0.29, the connections are added back to the pool. I narrowed it down to the toIdle method in the com.cuacho.env.dbpool.ConnectionPool class and the change to line 1177 but I?m not sure what the change did, exactly.

Additional Information

<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
    out.println("<html><head><title>TEST</title></head><body>");
    Connection con = null;
    try{

        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/<datasource-name>");

        con = ds.getConnection();
        /* Default isolation level is assumed to be repeatable read (4).*/
        out.println("The initial isolation level is " + con.getTransactionIsolation() + "
");

        out.println("Switch to isolation level " + Connection.TRANSACTION_READ_UNCOMMITTED + "
");
        con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        out.println("The isolation level is now " + con.getTransactionIsolation() + "
");

        out.println("Switch to isolation level " + Connection.TRANSACTION_READ_COMMITTED + "
");
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        out.println("The isolation level is now " + con.getTransactionIsolation() + "
");

    }catch(Exception e){
        out.println("Error");
        e.printStackTrace();
    }finally{
        try{
            if(con != null){
                out.println("Closing the database connection.");
                con.close();
            }
        }catch(SQLException e){
            out.println("Error while closing connection.");
            e.printStackTrace();
        }
    }
    out.println("</body></html>");
%>

Attached Files

Relationships

Notes
(0005990) ferg 07-30-12 10:51	env/111a

Issue History
Date Modified	Username	Field	Change
07-23-12 15:57	alex	New Issue
07-23-12 15:58	alex	Severity	minor => major
07-23-12 21:04	rjohnson	Issue Monitored: rjohnson
07-30-12 10:51	ferg	Note Added: 0005990
07-30-12 10:51	ferg	Assigned To	=> ferg
07-30-12 10:51	ferg	Status	new => closed
07-30-12 10:51	ferg	Resolution	open => fixed
07-30-12 10:51	ferg	Fixed in Version	=> 4.0.30

Mantis 1.0.0rc3[^]

31 total queries executed.
27 unique queries executed.