ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Schall" <mike.sch...@gmail.com>
Subject Re: queryForList/Map/Object... rollbacks
Date Fri, 07 Mar 2008 20:58:18 GMT
I am happy to report that switching over to JDNI has removed the excessive
rollbacks.  Thanks for all you help.

Mike

On Wed, Mar 5, 2008 at 1:50 PM, Michael Schall <mike.schall@gmail.com>
wrote:

> We have the commitRequired="false" so the code does not explicitly call
> rollback, however when the transaction is ended, since the transaction is
> not committed, the txManager rolls back the transaction.
>
>   public void endTransaction(SessionScope session) throws SQLException {
>     try {
>       try {
>         sqlExecutor.cleanup(session);
>       } finally {
>         txManager.end(session); // <-- rollback shows in p6spy log here
>       }
>     } catch (TransactionException e) {
>       throw new NestedSQLException("Error while ending transaction.
> Cause: " + e, e);
>     }
>   }
>
> I'm not sure if this is "fixed" with when not using the Simple Data
> Source.  We are testing this right now...
>
> As far as your example, each query is in it's own transaction unless you
> start one...
>
> Your pseudocode should look like
>
> int n = sqlmap.queryForObject(select count(*) from categories;)
>
> List list = sqlmap.queryForList(select * from categories;)
> Both queryForObject and queryForList start their own transaction.  Right?
>
>
> Creating and tearing down transactions are expensive, and most cases (for
> me at least) don't need this type of isolation.  I would like to opt in to
> this type of behavior when needed or at least have a way to opt out.
>
> I need to implement the opt out and would like to commit it back so it is
> part of the project.  Would that be beneficial?  Do you like how I proposed
> the feature?  Any input before I start?
>
> Thanks for your help.  I love active open source communities!!!
>
> Mike
>
>
> On Wed, Mar 5, 2008 at 12:30 PM, Clinton Begin <clinton.begin@gmail.com>
> wrote:
>
> >  The rollback was necessary for Sybase and other databases at the time,
> >  but possibly none anymore... Regardless, it is solved by setting If you set
> > commitRequired="false". In the next major version of iBATIS we'll probably
> > change the default setting.
> >
> >
> >
> > ***BUT***
> >
> >
> >
> > Creating the transaction in the first place has nothing to do with
> > Sybase.  It's a common misconception that transactions are only useful for
> > updates.
> >
> >
> >
> > If you are running multiple selects against a transactional database,
> > you will want to run them in a transaction scope to ensure that you're
> > isolated from other partial changes to the database (depends on isolation
> > level of course).
> >
> >
> >
> > For example (pseudocode):
> >
> >
> >
> > int n = select count(*) from categories;
> >
> > List list = select * from categories;
> >
> >
> >
> > n == list.size() is only guaranteed if both statements were run in an
> > isolated transaction scope.
> >
> >
> >
> > Clinton
> >
> >
> >
> > *From:* Michael Schall [mailto:mike.schall@gmail.com]
> > *Sent:* March-05-08 10:49 AM
> >
> > *To:* user-java@ibatis.apache.org
> > *Subject:* Re: queryForList/Map/Object... rollbacks
> >
> >
> >
> > I saw the commitRequired, but why are we creating the transaction in the
> > first place? Or is this a Sybase requirement that the rest of us have had to
> > live with?
> >
> > On Wed, Mar 5, 2008 at 11:44 AM, Christopher Lamey <
> > clamey@localmatters.com> wrote:
> >
> > Doh!  Forgot about "commitRequired" - been using Spring for everything
> > lately.
> >
> > Sorry about that.
> >
> >
> >
> > On 3/4/08 7:46 PM, "" <> 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" <TomH@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 <TomH@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.
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> >
> >
> >
>
>

Mime
View raw message