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

[Chema]

>
> 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

[Chema]

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 <daniel....@modellwerkstatt.org>:

[Daniel Stieger]

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

Best,
Dan

On 27 Jan., 11:45, Chema <demablo...@gmail.com> 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 <daniel.stie...@modellwerkstatt.org>:

[Chema]

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

Regards

2012/1/27 Daniel Stieger <daniel....@modellwerkstatt.org>:

[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 <demablo...@gmail.com> wrote:
> You're welcome.
> It' would be interesting that you give us a feedback when you finish your tests
>
> Regards
>

> 2012/1/27DanielStieger<daniel.stie...@modellwerkstatt.org>:

>
>
>
>
>
>
>
> > Thanks very much Chema for your explanations.
> > I ll check that in my code ....
>
> > Best,
> > Dan
>
> > On 27 Jan., 11:45, Chema <demablo...@gmail.com> 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<daniel.stie...@modellwerkstatt.org>:

[Chema]

2012/2/15 Daniel Stieger <daniel....@modellwerkstatt.org>:

> 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