SqlSession / Opening & Closing Connection

[Daniel Stieger]

Hi MyBatis-Group,

first of all a big compliment to the mybatis team. We are using the
mybatis library in a code generator for a top level ORM Domain
Specific Language (http://www.modellwerkstatt.org/videos). The best
feature of mybatis: no magic, only mapping!

I have a question regarding the managment of sql sessions. Seems that
i do the handling in a wrong manner. The situation is this:

Log4J reports various "created connection" task like:

DEBUG [AWT-EventQueue-0] - Created connection 31374579.
DEBUG [AWT-EventQueue-0] - ooo Connection Opened
DEBUG [AWT-EventQueue-0] - ==>  Executing: SELECT iRoot.KEY_POS
[.....]
DEBUG [AWT-EventQueue-0] - ==> Parameters: 19709896(Integer)
DEBUG [AWT-EventQueue-0] - <==    Columns: RM16COL0, RM16COL1, [....]
DEBUG [AWT-EventQueue-0] - <==        Row: 83414892, 19709896, 0, 10,
143949, null, null, 2016, 12, .9454, 1,
DEBUG [AWT-EventQueue-0] - ==>  Executing: SELECT iRoot.ARTIKEL [...]
DEBUG [AWT-EventQueue-0] - ==> Parameters: 143949(Integer)
DEBUG [AWT-EventQueue-0] - <==    Columns: S3COL0, S3COL1, S3COL2,
S3COL3,
DEBUG [AWT-EventQueue-0] - <==        Row: 143949, Napoli
Schnittenblock, 16441, 1118, 12, 1, .7929, 1
DEBUG [AWT-EventQueue-0] - <==        Row: 83414893, 19709896, 0, 40,
166902, null, null, 576,

and a little bit later another "connection open" is reported ...


DEBUG [AWT-EventQueue-0] - Created connection 7647268.
DEBUG [AWT-EventQueue-0] - ooo Connection Opened
DEBUG [AWT-EventQueue-0] - ==>  Executing: SELECT iRoot.KEY_POS [...]
DEBUG [AWT-EventQueue-0] - ==> Parameters: 19724652(Integer)
DEBUG [AWT-EventQueue-0] - <==    Columns: RM16COL0, RM16COL1,
RM16COL2, RM16COL3, [...]
DEBUG [AWT-EventQueue-0] - <==        Row: 83487929, 19724652,
83414901, 140, 188191, null, null, -5376, 16 DEBUG [AWT-EventQueue-0]
- ==>  Executing: SELECT iRoot.ARTIKEL [....]
DEBUG [AWT-EventQueue-0] - ==> Parameters: 188191(Integer)
DEBUG [AWT-EventQueue-0] - <==    Columns: S3COL0, S3COL1, S3COL2,
S3COL3, S3COL4, S3COL5, S3COL6,

and then very later i get ...

DEBUG [AWT-EventQueue-0] - Claimed overdue connection 7647268.

and something hangs. So obviously i do not handle the connection
correctly. But connection is not a connection to the Oracle DB,
wright? It s kind of internal mybatis connection? I m using POOLED
datasource.

What i have done in my repository class is the following:

class MyRepository {
   private SqlSession Manager manager;
   private IRekoMapper mapper;

  @Inject
  public MyRepository(SqlSessionManager m) {
      manager = m;
      mapper = manager.getMapper(IRekoRepo.class);
 }

  public List<RechnungZeile> findeRechZeilenZuRechung(int idRechnung)
{
    return mapper.findeRechZeilenZuRechung(idRechnung);
  }

  public void insertRechnungZeile(RechnungZeile boObject) {
    mapper.insertRechnungZeile(boObject);
  }
}

So is it reccommended to set the connection POOL to size 1 or should i
call open/close on the mapper or on the manager ?  Or is it better to
remove the SqlSessionManager

Dan

[Jose María Zaragoza]

>
> So is it reccommended to set the connection POOL to size 1 or should i
> call open/close on the mapper or on the manager ?  Or is it better to
> remove the SqlSessionManager
>
> Dan

IMHO, I guess it's better open session/close session

[Daniel Stieger]

Hi Chema,

so of course i open / close a session when doing a transaction. so
kind of

start transaction = SqlSessionManagerInstance.startManagedSession()
... save this
... save that
close transaction = commit() and SqlSessionManagerInstance.close()

but when only query-ing the database with, e.g.  select, should i also
use a manageSession (per call??)

Best,
Dan



On 27 Jan., 10:48, Chema <[hidden email]> wrote:
> > So is it reccommended to set the connection POOL to size 1 or should i
> > call open/close on the mapper or on the manager ?  Or is it better to
> > remove the SqlSessionManager
>
> > Dan
>
> IMHO, I guess it's better open session/close session

[Jose María Zaragoza]

I use myBatis and openSession() gets a Connection from DataSource and
starts transaction ( if NOT autocommit )
When close() session , you stop transaction and return Connection to the pool

So,

- I always make openSession() & close() session ( at least, you need
to get a Connection object )
- You can try to call openSession(true) with SELECT queries. Maybe ( I
dont know ) performance is better
- After retrieve a Session with openSession() method, you can execute
may query . When you ends , execute close() and Connection is returned
to pool.

You wrote "and then very later i get...".  If you have a connection
idle for a long time, maybe it's returned to the pool ( take a look at
these timers )  and when your code to try use it , an error happens
...





2012/1/27 Daniel Stieger <[hidden email]>:

> Hi Chema,
>
> so of course i open / close a session when doing a transaction. so
> kind of
>
> start transaction = SqlSessionManagerInstance.startManagedSession()
> ... save this
> ... save that
> close transaction = commit() and SqlSessionManagerInstance.close()
>
> but when only query-ing the database with, e.g.  select, should i also
> use a manageSession (per call??)
>
> Best,
> Dan
>
>
>
> On 27 Jan., 10:48, Chema <[hidden email]> wrote:
>> > So is it reccommended to set the connection POOL to size 1 or should i
>> > call open/close on the mapper or on the manager ?  Or is it better to
>> > remove the SqlSessionManager
>>
>> > Dan
>>
>> IMHO, I guess it's better open session/close session

[Daniel Stieger]

Thanks very much Chema for your explanations.
I ll check that in my code ....

Best,
Dan

On 27 Jan., 11:45, Chema <[hidden email]> wrote:

> I use myBatis and openSession() gets a Connection from DataSource and
> starts transaction ( if NOT autocommit )
> When close() session , you stop transaction and return Connection to the pool
>
> So,
>
> - I always make openSession() & close() session ( at least, you need
> to get a Connection object )
> - You can try to call openSession(true) with SELECT queries. Maybe ( I
> dont know ) performance is better
> - After retrieve a Session with openSession() method, you can execute
> may query . When you ends , execute close() and Connection is returned
> to pool.
>
> You wrote "and then very later i get...".  If you have a connection
> idle for a long time, maybe it's returned to the pool ( take a look at
> these timers )  and when your code to try use it , an error happens
> ...
>
> 2012/1/27 Daniel Stieger <[hidden email]>:
>
>
>
>
>
>
>
> > Hi Chema,
>
> > so of course i open / close a session when doing a transaction. so
> > kind of
>
> > start transaction = SqlSessionManagerInstance.startManagedSession()
> > ... save this
> > ... save that
> > close transaction = commit() and SqlSessionManagerInstance.close()
>
> > but when only query-ing the database with, e.g.  select, should i also
> > use a manageSession (per call??)
>
> > Best,
> > Dan
>
> > On 27 Jan., 10:48, Chema <[hidden email]> wrote:
> >> > So is it reccommended to set the connection POOL to size 1 or should i
> >> > call open/close on the mapper or on the manager ?  Or is it better to
> >> > remove the SqlSessionManager
>
> >> > Dan
>
> >> IMHO, I guess it's better open session/close session

[Jose María Zaragoza]

You're welcome.
It' would be interesting that you give us a feedback when you finish your tests

Regards

2012/1/27 Daniel Stieger <[hidden email]>:

> Thanks very much Chema for your explanations.
> I ll check that in my code ....
>
> Best,
> Dan
>
> On 27 Jan., 11:45, Chema <[hidden email]> wrote:
>> I use myBatis and openSession() gets a Connection from DataSource and
>> starts transaction ( if NOT autocommit )
>> When close() session , you stop transaction and return Connection to the pool
>>
>> So,
>>
>> - I always make openSession() & close() session ( at least, you need
>> to get a Connection object )
>> - You can try to call openSession(true) with SELECT queries. Maybe ( I
>> dont know ) performance is better
>> - After retrieve a Session with openSession() method, you can execute
>> may query . When you ends , execute close() and Connection is returned
>> to pool.
>>
>> You wrote "and then very later i get...".  If you have a connection
>> idle for a long time, maybe it's returned to the pool ( take a look at
>> these timers )  and when your code to try use it , an error happens
>> ...
>>
>> 2012/1/27 Daniel Stieger <[hidden email]>:
>>
>>
>>
>>
>>
>>
>>
>> > Hi Chema,
>>
>> > so of course i open / close a session when doing a transaction. so
>> > kind of
>>
>> > start transaction = SqlSessionManagerInstance.startManagedSession()
>> > ... save this
>> > ... save that
>> > close transaction = commit() and SqlSessionManagerInstance.close()
>>
>> > but when only query-ing the database with, e.g.  select, should i also
>> > use a manageSession (per call??)
>>
>> > Best,
>> > Dan
>>
>> > On 27 Jan., 10:48, Chema <[hidden email]> wrote:
>> >> > So is it reccommended to set the connection POOL to size 1 or should i
>> >> > call open/close on the mapper or on the manager ?  Or is it better to
>> >> > remove the SqlSessionManager
>>
>> >> > Dan
>>
>> >> IMHO, I guess it's better open session/close session

[Daniel Stieger]

Hi Chema,

i forgot to give feedback regarding my sqlsession problems. The
problem was that i forgot to close the managedSession in a  try{}
finally {} operation! So after a some time passes, i get those
problems described.

However, no everything works perfect. On a oracle 11g mybatis is
incredible fast!

Best,
Dan


On 27 Jan., 17:10, Chema <[hidden email]> wrote:

> You're welcome.
> It' would be interesting that you give us a feedback when you finish your tests
>
> Regards
>
> 2012/1/27DanielStieger<[hidden email]>:
>
>
>
>
>
>
>
> > Thanks very much Chema for your explanations.
> > I ll check that in my code ....
>
> > Best,
> > Dan
>
> > On 27 Jan., 11:45, Chema <[hidden email]> wrote:
> >> I use myBatis and openSession() gets a Connection from DataSource and
> >> starts transaction ( if NOT autocommit )
> >> When close() session , you stop transaction and return Connection to the pool
>
> >> So,
>
> >> - I always make openSession() & close() session ( at least, you need
> >> to get a Connection object )
> >> - You can try to call openSession(true) with SELECT queries. Maybe ( I
> >> dont know ) performance is better
> >> - After retrieve a Session with openSession() method, you can execute
> >> may query . When you ends , execute close() and Connection is returned
> >> to pool.
>
> >> You wrote "and then very later i get...".  If you have a connection
> >> idle for a long time, maybe it's returned to the pool ( take a look at
> >> these timers )  and when your code to try use it , an error happens
> >> ...
>
> >> 2012/1/27DanielStieger<[hidden email]>:
>
> >> > Hi Chema,
>
> >> > so of course i open / close a session when doing a transaction. so
> >> > kind of
>
> >> > start transaction = SqlSessionManagerInstance.startManagedSession()
> >> > ... save this
> >> > ... save that
> >> > close transaction = commit() and SqlSessionManagerInstance.close()
>
> >> > but when only query-ing the database with, e.g.  select, should i also
> >> > use a manageSession (per call??)
>
> >> > Best,
> >> > Dan
>
> >> > On 27 Jan., 10:48, Chema <[hidden email]> wrote:
> >> >> > So is it reccommended to set the connection POOL to size 1 or should i
> >> >> > call open/close on the mapper or on the manager ?  Or is it better to
> >> >> > remove the SqlSessionManager
>
> >> >> > Dan
>
> >> >> IMHO, I guess it's better open session/close session

[Jose María Zaragoza]

2012/2/15 Daniel Stieger <[hidden email]>:
> Hi Chema,
>
> i forgot to give feedback regarding my sqlsession problems. The
> problem was that i forgot to close the managedSession in a  try{}
> finally {} operation! So after a some time passes, i get those
> problems described.
>
> However, no everything works perfect. On a oracle 11g mybatis is
> incredible fast!

Thanks for you feedback

Regards