Mantis Bugtracker
  

Viewing Issue Simple Details Jump to Notes ] View Advanced ] Issue History ] Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0005150 [Resin] major always 07-23-12 15:57 07-02-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-02-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-02-12 10:51 ferg Note Added: 0005990
07-02-12 10:51 ferg Assigned To  => ferg
07-02-12 10:51 ferg Status new => closed
07-02-12 10:51 ferg Resolution open => fixed
07-02-12 10:51 ferg Fixed in Version  => 4.0.30


Mantis 1.0.0rc3[^]
Copyright © 2000 - 2005 Mantis Group
31 total queries executed.
27 unique queries executed.
Powered by Mantis Bugtracker