Cancelling long running select queries

[charlesdidonato]

I am using MyBatis 3.06 and have specified the timeout value on a select in the mapper as seen below:

<select id="getSummaryDisplayDataOptimized" parameterType="HashMap" 
                     resultMap="SummaryDisplayData" timeout="240">    

This works if the user does not force a connection reset (Eg hitting stop in their browser). The issue occurs when the user performs a connection reset. I can catch the connection reset as follows but I don't have any way to cancel the long running select. See my filter code below

public void doFilter(ServletRequest request, ServletResponse response,
            FilterChain chain) throws IOException {
        HttpServletRequest req = (HttpServletRequest) request;
        HttpServletResponse res = (HttpServletResponse) response;
        String servletName = req.getServletPath();
        String servletPath = req.getContextPath() + servletName;
        SqlSession myMbSession = null;
        theLog.debug("Inside StateFilter.doFilter()");
        try {
            Reader reader = Resources.getResourceAsReader(mbResource);
            SqlSessionFactory sessionFactory = null;      
            theLog.debug("Inside SPCFilter.doFilter()...");
            sessionFactory = new SqlSessionFactoryBuilder()
                .build(reader, "post_jndi");  
            myMbSession = sessionFactory.openSession(false);
            req.setAttribute("MBSession", myMbSession);
            theLog.debug("Passed in MBSession.");
            chain.doFilter(req, res);
            req.removeAttribute("Connection");
        } catch (Throwable t) {
            // If an exception is thrown somewhere down the filter chain,
            // we still want to execute our after-processing, and then
            // rethrow the problem after that.
            String cause = t.getCause().getMessage();
            theLog.error("Cause in Catch throwable:"+cause);
            theLog.error("class:"+t.getClass().getName());
            if (cause.indexOf("Connection reset") > -1){
                theLog.error("Connection reset detected...cancelling statement");
                        //I would like to programmatically cancel the long running query here
                        //and I don't think that rollback will do anything on a select
                myMbSession.rollback();
                theLog.error("Rollback complete.");
            }
            String errorMessage = UtilityMethods.getStackTrace(t);
            theLog.error(errorMessage);
            PrintWriter out = res.getWriter();
            displayAccessDeniedHTMLPage(out, errorMessage);
        } finally {
                if(myMbSession!=null) 
                    myMbSession.close();
        }

[emacarron]

AFAIK there is not API in JDBC to cancel a running statement. So there is nothing we can do :(

Let me know if I am wrong!

[charlesdidonato]

A method on java.sql.Statement is cancel()
Which is inherited by PreparedStatement

Charlie DiDonato
NTT Data Inc.
NYSDOH ECLRS and SYNDSURV Technical Lead
518-474-2735

From: Eduardo Macarron [mailto:notifications@github.com]
Sent: Sunday, January 19, 2014 10:44 AM
To: mybatis/mybatis-3
Cc: Charles DiDonato
Subject: Re: [mybatis-3] Cancelling long running select queries (#123)

AFAIK there is not API in JDBC to cancel a running statement. So there is nothing we can do :(

Let me know if I am wrong!

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/123#issuecomment-32711129.

[emacarron]

Hi Charlie, Sorry for my silly question. I have been working with DBs for 10 years and I did not know that method existed!

It makes a lot sense adding a cancel() to SqlSession. The change sounds easy but implies a change in the API and we should change the minor version number.

I will have a look at this and tell you back.

Thanks for the contribution.

[charlesdidonato]

I see that this issue is closed and am wondering if it was implemented in a new version.

Note from Eduardo Macarron was
"It makes a lot sense adding a cancel() to SqlSession. The change sounds easy but implies a change in the API and we should change the minor version number.

I will have a look at this and tell you back.

Thanks for the contribution."

[emacarron]

Hi! Sorry you are right. In fact, I just forget to re-open it.

I already have a look at it and I think we should add this feature for sure. The problem is that it will require a minor version upgrade, that is 3.3. And we are still preparing 3.2.6.

Thank you for pointing.

[charlesdidonato]

Hi Eduardo, just checking on the status of my feature request to cancel long running select queries programatically using Statement.cancel(). I see it was removed from the 3.3.0 milestone. Does this mean the feature request is not going to be implemented in a future version?

[rhleone]

Hi Eduardo, Like Charles, I want to know if this feature is not going to be part of a new version.
Recently I came across with a similar issue and I think It would be really helpful.

[dfeingol]

I am also interested in this feature and would like to know what version this is going to be part of.
In the meantime, is their any workarounds to achieve this.

[vladhid]

Are there any news on this subject?

[kislaykishore]

Hi, just checking for any progress here.