Mantis - Resin
Viewing Issue Advanced Details
ID: Category: Severity: Reproducibility: Date Submitted: Last Update:
5150 major always 07-23-12 15:57 07-30-12 10:51
Reporter: alex Platform:  
Assigned To: ferg OS:  
Priority: normal OS Version:  
Status: closed Product Version: 4.0.29  
Product Build: Resolution: fixed  
Projection: none      
ETA: none Fixed in Version: 4.0.30  
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.
 
Steps To Reproduce:
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>");
%>
Relationships
Attached Files:

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