Session.selectList hits database even with caching working using non-pooled DataSource

classic Classic list List threaded Threaded
2 messages Options
Yan
Reply | Threaded
Open this post in threaded view
|

Session.selectList hits database even with caching working using non-pooled DataSource

Yan
Hi,

With ibatis-2.3.0.677 it was possible for select result caching to not
touch the database at all when the result was in the cache. Using
mysql it is possible to check the number of statements that have been
executed using "show status like 'Questions'". I built a test around
this to confirm caching was working. This test started failing with
MyBatis 3.0.2.

With MyBatis a database connection is acquired before the test for
caching is performed which means connections are being acquired
unnecessarily. This occurs in
DefaultSqlSessionFactory.openSessionFromDataSource,

  Connection connection = dataSource.getConnection();
  ...
  return new DefaultSqlSession(...);

In my test the DataSource is Spring's DriverManagerDataSource so the
database is accessed every time a connection is requested, which would
not happen with a connection pool.

Although the effect of this would be lower in an app server with
connection pooling it would be better if the connection was only
acquired if the result was not in cache. Or is that already possible?
This is how I am acquring and using SqlSession,

  SqlSession session = ssf.openSession();
  try {
     ..  = session.selectList(...);
   } finally {
    session.close();
   }

Is there some way to prevent a connection from being acquired if the
result is in the cache?

-Janek
Yan
Reply | Threaded
Open this post in threaded view
|

Re: Session.selectList hits database even with caching working using non-pooled DataSource

Yan
Hi,

I switched my test to use a pooling DataSource
(com.mchange.v2.c3p0.ComboPooledDataSource). The database activity was
reduced but each acquisition of a connection still results in MySQL
database activity namely,

  SET autocommit=0
  SET autocommit=1

My ideal would be for a cache hit to not touch the database at all,
which ibatis-2.3.0.677 achieves.

-Janek

On Sep 29, 5:39 pm, Yan <[hidden email]> wrote:

> Hi,
>
> With ibatis-2.3.0.677 it was possible for select result caching to not
> touch the database at all when the result was in the cache. Using
> mysql it is possible to check the number of statements that have been
> executed using "show status like 'Questions'". I built a test around
> this to confirm caching was working. This test started failing with
> MyBatis 3.0.2.
>
> With MyBatis a database connection is acquired before the test for
> caching is performed which means connections are being acquired
> unnecessarily. This occurs in
> DefaultSqlSessionFactory.openSessionFromDataSource,
>
>   Connection connection = dataSource.getConnection();
>   ...
>   return new DefaultSqlSession(...);
>
> In my test the DataSource is Spring's DriverManagerDataSource so the
> database is accessed every time a connection is requested, which would
> not happen with a connection pool.
>
> Although the effect of this would be lower in an app server with
> connection pooling it would be better if the connection was only
> acquired if the result was not in cache. Or is that already possible?
> This is how I am acquring and using SqlSession,
>
>   SqlSession session = ssf.openSession();
>   try {
>      ..  = session.selectList(...);
>    } finally {
>     session.close();
>    }
>
> Is there some way to prevent a connection from being acquired if the
> result is in the cache?
>
> -Janek