>> Your pseudocode should look like
No, my pseudocode had nothing to do with iBATIS. It was just an SQL/Java
mashup. My point stands true with or without iBATIS. I could demonstrate
it with two MySQL consoles...
Sorry it wasn't clear though. I'll stick to real code for the next example.
>> Both queryForObject and queryForList start their own transaction. Right?
>> Creating and tearing down transactions are expensive
Yes. So it's actually both _faster_ and _safer_ to explicitly demarcate a
separate transaction like:
try {
sqlmap.startTransaction();
int n = sqlmap.queryForObject("countAllCategories")
List list = sqlmap.queryForList("selectAllCategories")
} finally {
sqlmap.endTransaction();
}
>> 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.
You can opt out by managing the connection yourself by using
setUserConnection(). Otherwise, iBATIS always uses transactions. It gets
hairy dealing with this in a managed data source situation. Basically you
have to set autocommit to true, in which case there's still no guarantee
that your database won't use tranactions anyway (see the docs for your
driver). But worse, you'll have connections floating around with different
settings and you'll have to be sure to always check whether they're set to
autocommit or not. As a rule I just never use autocommit, unless it's
something weird like running DDL with a driver that only supports it in
autocommit mode (I've seen that too).
>> I need to implement the opt out and would like to commit it back so it is
part of the project.
>From my perspective I actually don't think it's a good idea. Using an
explicit transaction around multiple selects is faster and safer. Disabling
the transactions would only really be useful for read-only databases (which
usually have some other system writing batch updates periodically anyway).
But with a read-only databse your best performance gain is in replicating
the database and/or aggressively caching the data.
For what it's worth, iBATIS 3 will externalize the transaction control
entirely. So you, your framework or another framework like Spring can more
easily manage the transactions.
But iBATIS 2.x is designed around an assumption that is more heavily
dependent upon transactions. I would not recommend messing with that
assumption.
Clinton
From: Michael Schall [mailto:[EMAIL PROTECTED]
Sent: March-05-08 12:50 PM
To: user-java@ibatis.apache.org
Subject: Re: queryForList/Map/Object... rollbacks
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 <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
> 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" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
>> 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 <[EMAIL PROTECTED]> 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.
>>
>>
>>
>>
>>
>
