Retry execution of a query once

[Jose María Zaragoza]

Hello:

I'm using Mybatis 3 

I would like to implement retries for every query to database when it fails (SQLException )

I need to do it once ( I mean, call fails -> retry --> call fais or not ---> return or SQLException )

At the first, I don't want to make it wrapping the call into  try/catch for retrying

I would like to do it with MyBatis interceptors

Is it possible ?

I think the most difficult is to keep the number of invocation between calls ( the first one or the second one )

I don't know if I could do it with ThreadLocal field into the interceptor 

Any ideas/suggestions ?

Thanks and regards

[Eduardo Macarron]

I would proxy the datasource that would proxy the connection and the
statements. Include a counter in the statement so it knows how many
times it retried.

2013/4/29 Jose María Zaragoza <demab...@gmail.com>:

> --
> You received this message because you are subscribed to the Google Groups
> "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mybatis-user...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Jose María Zaragoza]

Thanks. But I can use Interceptor as proxy executor , right ?

If I define a interceptor class like 

@Intercepts({@Signature(type= Executor.class, method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})

with next method ( I didn't test it )   Would it work ?

public Object intercept(Invocation invocation) throws Throwable

 {

     try

{

    return invocation.proceed();

}

catch(SQLException e1)

{

      if (threadLocal.get() == 0 )

         try

         {

           threadLocal.set(1) 

          return invocation.proceed();

         }

         catch(SQLException e2)

         {

               threadLocal.set(0) 

                throw e;

         }

 }

2013/4/29 Eduardo Macarron <eduardo....@gmail.com>

[Jose María Zaragoza]

I was a fool 

Indeed, I think that  I don't need a ThreadLocal variable, because I guess that  invocation.proceed() is not intercepted by this interceptor

Easier then

[Eduardo Macarron]

Probably yes.

But, what do you need the thread local for?

[Eduardo Macarron]

Ok. :)

2013/4/29 Eduardo Macarron <eduardo....@gmail.com>:

[Jose María Zaragoza]

:-)

But now I've got another problem : I need that the retry goes for a new connection . 

It's not easier like I thought

I'll take a look to JDBC interceptors ...

2013/4/29 Eduardo Macarron <eduardo....@gmail.com>

[Eduardo Macarron]

Believe me and proxy the datasoruce. It is really easy to do and will
work on all circumstances.

[Jose María Zaragoza]

Thanks.

If I'm honest  , I don't know how to start

I could to override getConnection method of org.apache.tomcat.jdbc.pool.DataSource , returning a custom Connection ( wrapper) .

But I looking for something more "pluggable" 

I see that you don't trust so much in your own interceptors :-) , but I want something like that.

I going to check if when  invocation.proceed() throws an SQLException, the underlying connection is invalidated and the new  invocation.proceed()  call gets a new Connection .

In this case, it would work for me.

If not, I should do a Datasource proxy ( have you got an example, please ? ) or try with JDBC interceptors 

Thanks and regards

2013/4/30 Eduardo Macarron <eduardo....@gmail.com>

[Eduardo Macarron]

Note that this is just me :) there is no such thing as an official
mybatis position.

Probably you can use interceptors, but I never used them, and... I do
not like them because they expose the internals. I would have prefered
to have interception points with contracts (interfaces).

Proxing a DataSource is quite easy. You can do it by two means.
- Create your DataSourceFactory that builds a custom RetryDataSource
(a bean that extends Datasoruce). This bean will get a connection for
the inner (real) datasource and retry once. Then wrap the connection
in a custom RetryConnection that will do the same with the Statement
(RetryStatement).
- Or do the same using proxys. For example
https://code.google.com/p/mybatis/issues/detail?id=382

Proxies are needed to implement an interface you do not know. This is
not the case so you can just implement the interfaces.

[Jose María Zaragoza]

Thanks a lot , Eduardo

Regards

2013/4/30 Eduardo Macarron <eduardo....@gmail.com>

[Jose María Zaragoza]

FYI

Really works. But I'll try with datasource proxy. :-)

Below I paste the code

Only I 've got a doubt 

My application running on Tomcat and Tomcat uses a thread pool to attend requests.

Looks like Connection is attatched to thread 

When does SqlSessionTemplate return the underlying connection to the connection pool ? ( when close() , I mean ) 

I've got a query validation ( queryValidation in Tomcat a.k.a pingQuery ) to check if the connection is already valid where is borrowed from the connection pool.

But if the connection is attached to thread and that thread reused between call , I lost the advantage of validationQuery check

@Intercepts({@Signature(type= Executor.class, method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})}) 

public class QueryInterceptor implements Interceptor 

{ 

    

/*

* Tomcat usa un pool de thread para atender las peticiones. 

* Si la conexion es ligada al thread ¿ cuando se retorna al pool ? 

* 

*/

        

public Object intercept(Invocation invocation) throws Throwable

{

try

{

return invocation.proceed();

}

catch (InvocationTargetException ie)

{

try

{

throw ie.getTargetException();

}

catch(SQLException sqle)

{

try

{

return invocation.proceed();

}

catch (Exception e)

{

throw e;

}

}

}

}

2013/4/30 Jose María Zaragoza <demab...@gmail.com>

[Eduardo Macarron]

SqlSessionTemplate works different depending on whether you are using
spring tx or not.

If not, it creates a new session for each statement and closes it.
When closing the session, conn.close() is called and that instance
goes back to the pool.

If using tx, it is Spring who calls conn.close() when the TX scope has
ended. Probably when you go out of a @Transactional method.

2013/4/30 Jose María Zaragoza <demab...@gmail.com>:

[Jose María Zaragoza]

Hi:

If not, it creates a new session for each statement and closes it.
When closing the session, conn.close() is called and that instance
goes back to the pool.

This is my case. I only perform queries

About your comments, I always would get a connection from pool. And this connecton are checked before be borrowed.

But I've observed that when I simulate a link down to database , the next request get a connection closed

And a connection closed cannot be retrieved from pool

So, 

- session is not closed after each statement 

- validationQuery doesn't work, but I tested it 

I'll investigate it

Thanks 

[Jose María Zaragoza]

This is the error stacktrace 

I don't see any "getConnection()"  ¿?

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server no devolvió una respuesta. Se ha cerrado la conexión.

at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1352)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1339)

at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:3700)

at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:5022)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:57)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:98)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:73)

2013/4/30 Jose María Zaragoza <demab...@gmail.com>

Hi:

[Eduardo Macarron]

The 1st time a connection is needed (to execute something) mybatis
calls getConnection() and stores the instance in the transaction
instance inside the session. All further statements will use the same
connection.

So the getConnection was performed before executing the query and it
succeeded, so you do not see it.


2013/4/30 Jose María Zaragoza <demab...@gmail.com>:

[Jose María Zaragoza]

OK. Thanks Eduardo. 

But I would like to have the behaviour you told me before : "to create a new session for each statement and closes it."

I'm using Spring-MyBatis , but I don't defineTX Manager like this

<bean id="txmanager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

<property name="dataSource" ref="dataSource"/>

</bean>

All my statement are SELECT queries and I don't want to start any transaction 

When Spring-MyBatis execute SELECT queries , does it always create a transactions ?

Is it required to define txManager for SELECT queries ? 

So, do I need to use @Transactional with SELECT queries ?

Read Only transactions could be a solution ?

I don't want to have attached Connections to Tomcat threads and reuse it between calls.

Thanks and regards

2013/4/30 Eduardo Macarron <eduardo....@gmail.com>

The 1st time a connection is needed (to execute something) mybatis

[Eduardo Macarron]

Yep. That is right, in that case just do not define any
DataSourceTransactionManager.

[Jose María Zaragoza]

2013/4/30 Eduardo Macarron <eduardo....@gmail.com>

Yep. That is right, in that case just do not define any
DataSourceTransactionManager.

But I don't have defined any  DataSourceTransactionManager 

You told before "The 1st time a connection is needed (to execute something) mybatis

calls getConnection() and stores the instance in the transaction

instance inside the session"

But I don't define any  DataSourceTransactionManager 

I don't understand, sorry.

I'll activate debug level for logging in org.springframework.jdbc.datasource

and I'll what happens

Thanks, Eduardo

[Eduardo Macarron]

Sorry Jose, this is difficult to explain.

Let me try it again.

For mybatis, a session and a transaction is the same thing. A session
is like a connection for Jdbc. You change data, and once you are done
you resolve the tx with commit/rollback and close it. Same for
MyBatis.

In Spring a TX is an "scope". A tx is opened and any work you do with
any resource should be attached to that TX. When the TX finishes, all
resources finish with it (with commit or rollback).

Note that when using Spring it is spring who manages the lifecycle of
all objects. There is no session.open() or session.close(). If you try
that methods you will get an exception that tells you that this an
Spring task.

So basically mybatis-spring opens a session when Spring creates a
Transaction and closes it when Spring finishes the transaction.

And.. what happens if there is no transaction and you call mybatis.
Ok, then we open a session, execute the statement and close it. There
is no other option, because there is no place to store that session
and there is not any API to let the user manage it (open, close,
commit). Not a bug! This how we want it to be!

So far so good?

Then, when are connections opened? It depends on mybatis version but
from 3.1 connection opening is delayed untile the fist statement is
executed. Why? Because mybatis uses caches and if all data comes from
the cache there is no point in opening a connection for not using it.

So, if you are not using TX. When you call two statements mybatis does:

- open a session
- open a connection
- execute statement 1
- close a connection (with commit)
- close session
- open a session
- open a connection
- execute statement 2
- close a connection (with commit)
- close session

If using TX

- spring opens a TX
- open a session and attach it to TX
- open a connection
- execute statement 1
- execute statement 2
- spring closes a TX (with commit)
- close session (flush)
- close a connection (with commit)

2013/4/30 Jose María Zaragoza <demab...@gmail.com>:
>

[Jose María Zaragoza]

Thank you very much. Very glad from yours

It's hard to explain myself, I know

My scenario is using Spring-MyBatis *without defining a txManager*

I guess that I'm on the first case : "if you are not using TX" , right ?

But looks like if Spring-MyBatis ( I repeat, without txManager ) would be creating a Transacion (with a Connection attached to ), 

because , *in the same thread* ,  if I execute  a mapper.getData()  method for second time , I see that the Connection object used to execute the query  *is not  retrieved from the pool*. So, I guess, that Connection object is retrieved from current Transaction  ( but I'm not using txManager ! )

Does this make any sense for you ?

How does Spring-MyBatis works **without txManager** ? Does it create an implicit transaction ? 

If it does , what is the scope for that transaction ? If it doesn't , why a Connection is not retrieved  from pool between calls *in the same thread* ?

Regards

2013/5/1 Eduardo Macarron <eduardo....@gmail.com>

. 

[Eduardo Macarron]

Nope. It should not work like that.

If there is no tx and you are using mybatis-spring as expected, then
each statement will use a new session that will ask the ds for a new
connection.

Turning logging on will bring a lot of light :)

[Jose María Zaragoza]

Hi:

Well, I've turned logging on and you're right : without Tx manager, connections are used and returned to the pool 

You won :-)

The problem was  the way that I simulated a network crash : I performed it so fast , so validationQuery ( a.k.a pingQuery )  returned OK ( I don't know why does that , yet ) and it was borrowed to my application. When my application would like to use that connection, a "Closed connection" exception was thrown 

Thanks and regards

2013/5/1 Eduardo Macarron <eduardo....@gmail.com>