You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Tom Henricksen <To...@A-t-g.com> on 2008/03/04 22:32:22 UTC
queryForList/Map/Object... rollbacks
We are performance monitoring our application and my DBA let me know
that we had over 3 MILLION rollbacks in a single day. We are using
WebSphere to DB2 on Win2k3 servers.
I looked in our code and could not find anything. Used p6spy and found
that every time queryForXXX is called I received a rollback. Stepping
through the queryForList code in SqlMapExecutorDelegate.java the
autoStartTransaction starts a transaction if none is present and since
we don't have commitRequired set, the autoEndTransaction will cause a
rollback.
I assume there is a good reason to start the transaction here. It seems
like extra work creating a transaction that will always be rolled back.
Can someone explain why this is good to me so I can forward the
information to my DBA? Any links supporting the argument would be
great. Is there a way to use the queryForXXX without creating a
transaction? I assume it is better to not have commitRequired?
Thanks for your time.
Tom Henricksen
Consultant
Advanced Technologies Group, Inc.
Re: queryForList/Map/Object... rollbacks
Posted by Michael Schall <mi...@gmail.com>.
I would like to make this configurable by adding a property and code
something like the following...
Thoughts?
Mike
<transactionManager ... autoTransaction="false">
public List queryForList(SessionScope sessionScope, String id, Object
paramObject, int skip, int max) throws SQLException {
List list = null;
MappedStatement ms = getMappedStatement(id);
Transaction trans = getTransaction(sessionScope);
boolean autoStart = sessionScope.getAutoTransaction() && trans == null;
try {
trans = autoStartTransaction(sessionScope, autoStart, trans);
StatementScope statementScope = beginStatementScope(sessionScope, ms);
try {
list = ms.executeQueryForList(statementScope, trans, paramObject,
skip, max);
} finally {
endStatementScope(statementScope);
}
autoCommitTransaction(sessionScope, autoStart);
} finally {
autoEndTransaction(sessionScope, autoStart);
}
return list;
}
On Tue, Mar 4, 2008 at 6:40 PM, Clinton Begin <cl...@gmail.com>
wrote:
> Actually, it is the default behaviour. :-/
>
> It's a combination of both the iBATIS transaction manager default
> configuration and SimpleDataSource.
>
> To make a long story short, in the good old days, some JDBC drivers
> literally required a rollback to "reset" the connection. *cough* Sybase
> *cough* But other drivers hissed and booed at the excessive rollbacks.
>
> Thus we added a flag to the iBATIS transaction manager, called
> commitRequired (because commits are equally as aggressive). Try this:
>
> <transactionManager ... commitRequired="false">
>
> That combined with a container managed DataSource should eliminate all
> unnecessary rollbacks. If it was already set to false, it is entirely
> possible that SimpleDataSource was entirely responsible for the aggressive
> rollbacks -- thus we should probably make that configurable too.
>
> Clinton
>
> -----Original Message-----
> From: Christopher Lamey [mailto:clamey@localmatters.com]
> Sent: March-04-08 5:07 PM
> To: user-java@ibatis.apache.org
> Subject: Re: queryForList/Map/Object... rollbacks
>
> That is not the default iBATIS behavior, I'm guessing your container is
> getting involved.
>
> From what I understand, the SIMPLE datasource type is generally for
> standalone programs, not for apps running in a container.
>
> On 3/4/08 4:20 PM, "Tom Henricksen" <To...@A-t-g.com> wrote:
>
> > Currently we are using JDBC with simple datasource.
> >
> >
> >
> > <transactionManager type="JDBC">
> >
> > <dataSource type="SIMPLE">
> >
> >
> >
> > But we are transitioning to JNDI datasource.
> >
> >
> >
> > This is just a web application. I will take a look at the Wiki.
> >
> >
> >
> > So is this default behavior of iBatis to create a transaction and roll
> > it back for even a select?
> >
> >
> >
> > Would JTA change this?
> >
> >
> >
> > Thanks,
> >
> > Tom
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Jeff Butler [mailto:jeffgbutler@gmail.com]
> > Sent: Tuesday, March 04, 2008 3:53 PM
> > To: user-java@ibatis.apache.org
> > Subject: Re: queryForList/Map/Object... rollbacks
> >
> >
> >
> > What transaction manager are you using?
> >
> > Is this an EJB application with CMT, or just a web application?
> >
> > Have you read the information in the WIKI about configuring iBATIS on
> > WebSphere?
> >
> >
> >
> > http://opensource.atlassian.com/confluence/oss/display/IBATIS/Environmen
> > t+Specific+Information
> >
> >
> >
> > Jeff Butler
> >
> >
> >
> >
> >
> > On Tue, Mar 4, 2008 at 3:32 PM, Tom Henricksen <To...@a-t-g.com> wrote:
> >
> > We are performance monitoring our application and my DBA let me know
> > that we had over 3 MILLION rollbacks in a single day. We are using
> > WebSphere to DB2 on Win2k3 servers.
> >
> > I looked in our code and could not find anything. Used p6spy and found
> > that every time queryForXXX is called I received a rollback. Stepping
> > through the queryForList code in SqlMapExecutorDelegate.java the
> > autoStartTransaction starts a transaction if none is present and since
> > we don't have commitRequired set, the autoEndTransaction will cause a
> > rollback.
> >
> > I assume there is a good reason to start the transaction here. It seems
> > like extra work creating a transaction that will always be rolled back.
> > Can someone explain why this is good to me so I can forward the
> > information to my DBA? Any links supporting the argument would be
> > great. Is there a way to use the queryForXXX without creating a
> > transaction? I assume it is better to not have commitRequired?
> >
> > Thanks for your time.
> >
> >
> >
> > Tom Henricksen
> > Consultant
> > Advanced Technologies Group, Inc.
> >
> >
> >
> >
> >
>
>
RE: queryForList/Map/Object... rollbacks
Posted by Clinton Begin <cl...@gmail.com>.
Actually, it is the default behaviour. :-/
It's a combination of both the iBATIS transaction manager default
configuration and SimpleDataSource.
To make a long story short, in the good old days, some JDBC drivers
literally required a rollback to "reset" the connection. *cough* Sybase
*cough* But other drivers hissed and booed at the excessive rollbacks.
Thus we added a flag to the iBATIS transaction manager, called
commitRequired (because commits are equally as aggressive). Try this:
<transactionManager ... commitRequired="false">
That combined with a container managed DataSource should eliminate all
unnecessary rollbacks. If it was already set to false, it is entirely
possible that SimpleDataSource was entirely responsible for the aggressive
rollbacks -- thus we should probably make that configurable too.
Clinton
-----Original Message-----
From: Christopher Lamey [mailto:clamey@localmatters.com]
Sent: March-04-08 5:07 PM
To: user-java@ibatis.apache.org
Subject: Re: queryForList/Map/Object... rollbacks
That is not the default iBATIS behavior, I'm guessing your container is
getting involved.
>From what I understand, the SIMPLE datasource type is generally for
standalone programs, not for apps running in a container.
On 3/4/08 4:20 PM, "Tom Henricksen" <To...@A-t-g.com> wrote:
> Currently we are using JDBC with simple datasource.
>
>
>
> <transactionManager type="JDBC">
>
> <dataSource type="SIMPLE">
>
>
>
> But we are transitioning to JNDI datasource.
>
>
>
> This is just a web application. I will take a look at the Wiki.
>
>
>
> So is this default behavior of iBatis to create a transaction and roll
> it back for even a select?
>
>
>
> Would JTA change this?
>
>
>
> Thanks,
>
> Tom
>
>
>
>
>
> -----Original Message-----
> From: Jeff Butler [mailto:jeffgbutler@gmail.com]
> Sent: Tuesday, March 04, 2008 3:53 PM
> To: user-java@ibatis.apache.org
> Subject: Re: queryForList/Map/Object... rollbacks
>
>
>
> What transaction manager are you using?
>
> Is this an EJB application with CMT, or just a web application?
>
> Have you read the information in the WIKI about configuring iBATIS on
> WebSphere?
>
>
>
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/Environmen
> t+Specific+Information
>
>
>
> Jeff Butler
>
>
>
>
>
> On Tue, Mar 4, 2008 at 3:32 PM, Tom Henricksen <To...@a-t-g.com> wrote:
>
> We are performance monitoring our application and my DBA let me know
> that we had over 3 MILLION rollbacks in a single day. We are using
> WebSphere to DB2 on Win2k3 servers.
>
> I looked in our code and could not find anything. Used p6spy and found
> that every time queryForXXX is called I received a rollback. Stepping
> through the queryForList code in SqlMapExecutorDelegate.java the
> autoStartTransaction starts a transaction if none is present and since
> we don't have commitRequired set, the autoEndTransaction will cause a
> rollback.
>
> I assume there is a good reason to start the transaction here. It seems
> like extra work creating a transaction that will always be rolled back.
> Can someone explain why this is good to me so I can forward the
> information to my DBA? Any links supporting the argument would be
> great. Is there a way to use the queryForXXX without creating a
> transaction? I assume it is better to not have commitRequired?
>
> Thanks for your time.
>
>
>
> Tom Henricksen
> Consultant
> Advanced Technologies Group, Inc.
>
>
>
>
>
Re: queryForList/Map/Object... rollbacks
Posted by Christopher Lamey <cl...@localmatters.com>.
That is not the default iBATIS behavior, I'm guessing your container is
getting involved.
>From what I understand, the SIMPLE datasource type is generally for
standalone programs, not for apps running in a container.
On 3/4/08 4:20 PM, "Tom Henricksen" <To...@A-t-g.com> wrote:
> Currently we are using JDBC with simple datasource.
>
>
>
> <transactionManager type="JDBC">
>
> <dataSource type="SIMPLE">
>
>
>
> But we are transitioning to JNDI datasource.
>
>
>
> This is just a web application. I will take a look at the Wiki.
>
>
>
> So is this default behavior of iBatis to create a transaction and roll
> it back for even a select?
>
>
>
> Would JTA change this?
>
>
>
> Thanks,
>
> Tom
>
>
>
>
>
> -----Original Message-----
> From: Jeff Butler [mailto:jeffgbutler@gmail.com]
> Sent: Tuesday, March 04, 2008 3:53 PM
> To: user-java@ibatis.apache.org
> Subject: Re: queryForList/Map/Object... rollbacks
>
>
>
> What transaction manager are you using?
>
> Is this an EJB application with CMT, or just a web application?
>
> Have you read the information in the WIKI about configuring iBATIS on
> WebSphere?
>
>
>
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/Environmen
> t+Specific+Information
>
>
>
> Jeff Butler
>
>
>
>
>
> On Tue, Mar 4, 2008 at 3:32 PM, Tom Henricksen <To...@a-t-g.com> wrote:
>
> We are performance monitoring our application and my DBA let me know
> that we had over 3 MILLION rollbacks in a single day. We are using
> WebSphere to DB2 on Win2k3 servers.
>
> I looked in our code and could not find anything. Used p6spy and found
> that every time queryForXXX is called I received a rollback. Stepping
> through the queryForList code in SqlMapExecutorDelegate.java the
> autoStartTransaction starts a transaction if none is present and since
> we don't have commitRequired set, the autoEndTransaction will cause a
> rollback.
>
> I assume there is a good reason to start the transaction here. It seems
> like extra work creating a transaction that will always be rolled back.
> Can someone explain why this is good to me so I can forward the
> information to my DBA? Any links supporting the argument would be
> great. Is there a way to use the queryForXXX without creating a
> transaction? I assume it is better to not have commitRequired?
>
> Thanks for your time.
>
>
>
> Tom Henricksen
> Consultant
> Advanced Technologies Group, Inc.
>
>
>
>
>
RE: queryForList/Map/Object... rollbacks
Posted by Tom Henricksen <To...@A-t-g.com>.
Currently we are using JDBC with simple datasource.
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
But we are transitioning to JNDI datasource.
This is just a web application. I will take a look at the Wiki.
So is this default behavior of iBatis to create a transaction and roll
it back for even a select?
Would JTA change this?
Thanks,
Tom
-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Tuesday, March 04, 2008 3:53 PM
To: user-java@ibatis.apache.org
Subject: Re: queryForList/Map/Object... rollbacks
What transaction manager are you using?
Is this an EJB application with CMT, or just a web application?
Have you read the information in the WIKI about configuring iBATIS on
WebSphere?
http://opensource.atlassian.com/confluence/oss/display/IBATIS/Environmen
t+Specific+Information
Jeff Butler
On Tue, Mar 4, 2008 at 3:32 PM, Tom Henricksen <To...@a-t-g.com> wrote:
We are performance monitoring our application and my DBA let me know
that we had over 3 MILLION rollbacks in a single day. We are using
WebSphere to DB2 on Win2k3 servers.
I looked in our code and could not find anything. Used p6spy and found
that every time queryForXXX is called I received a rollback. Stepping
through the queryForList code in SqlMapExecutorDelegate.java the
autoStartTransaction starts a transaction if none is present and since
we don't have commitRequired set, the autoEndTransaction will cause a
rollback.
I assume there is a good reason to start the transaction here. It seems
like extra work creating a transaction that will always be rolled back.
Can someone explain why this is good to me so I can forward the
information to my DBA? Any links supporting the argument would be
great. Is there a way to use the queryForXXX without creating a
transaction? I assume it is better to not have commitRequired?
Thanks for your time.
Tom Henricksen
Consultant
Advanced Technologies Group, Inc.
Re: queryForList/Map/Object... rollbacks
Posted by Jeff Butler <je...@gmail.com>.
What transaction manager are you using?
Is this an EJB application with CMT, or just a web application?
Have you read the information in the WIKI about configuring iBATIS on
WebSphere?
http://opensource.atlassian.com/confluence/oss/display/IBATIS/Environment+Specific+Information
Jeff Butler
On Tue, Mar 4, 2008 at 3:32 PM, Tom Henricksen <To...@a-t-g.com> wrote:
> We are performance monitoring our application and my DBA let me know that
> we had over 3 MILLION rollbacks in a single day. We are using WebSphere to
> DB2 on Win2k3 servers.
>
> I looked in our code and could not find anything. Used p6spy and found
> that every time queryForXXX is called I received a rollback. Stepping
> through the queryForList code in SqlMapExecutorDelegate.java the
> autoStartTransaction starts a transaction if none is present and since we
> don't have commitRequired set, the autoEndTransaction will cause a rollback.
>
> I assume there is a good reason to start the transaction here. It seems
> like extra work creating a transaction that will always be rolled back. Can
> someone explain why this is good to me so I can forward the information to
> my DBA? Any links supporting the argument would be great. Is there a way
> to use the queryForXXX without creating a transaction? I assume it is
> better to not have commitRequired?
>
> Thanks for your time.
>
>
>
> *Tom Henricksen*
> Consultant
> Advanced Technologies Group, Inc.
>
>
>