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 Serge Sozonoff <se...@sozonoff.com> on 2008/02/09 15:09:57 UTC
Ibatis caching Empty ResultSet
Hi All,
I am experiencing some strange behavior with the iBatis cache (iBatis
version 2.3.x)
It seems like iBatis is caching empty ResultSets/ResultMap, below is a
select statement for a user which does not exist in the database.
Notice the last line iBatis is caching something but its not a User
object which is what it would be for a select with an existing user as
seen in the second log extract.
Any thoughts anyone?
This is a problem for me in the following scenario.
select user to see if he/she exists
insert user because does not exist
.... later ... select the user which now exists
In the above scenario the third statements returns a null object because
it is returning the "null" object which was cached from the first statement!
To make this work I would have to flush the cache every time I insert
the new user which to me makes no sense at all.
Thanks,
Serge
Output for select of non existing user with a strange cache behavior.
09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
java.sql.PreparedStatement - {pstm-100004} Executing
Statement: SELECT * FROM users WHERE username = ?
09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
java.sql.PreparedStatement - {pstm-100004} Parameters: [usera]
09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
java.sql.PreparedStatement - {pstm-100004} Types: [java.lang.String]
09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG java.sql.ResultSet -
{rset-100005} ResultSet
09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
com.ibatis.sqlmap.engine.cache.CacheModel - Cache 'User.user-cache':
stored object 'java.lang.Object@5b02a6'
Output for a select of an existing user
09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
java.sql.PreparedStatement - {pstm-100009} Executing
Statement: SELECT * FROM users WHERE username = ?
09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
java.sql.PreparedStatement - {pstm-100009} Parameters: [userc]
09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
java.sql.PreparedStatement - {pstm-100009} Types: [java.lang.String]
09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
- {rset-100010} ResultSet
09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
- {rset-100010} Header: [id, username, password_hash,
pwd_reminder_email, active]
09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
- {rset-100010} Result: [7, userc, w1vwW2KgIeg=, email@email.com, true]
09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
com.ibatis.sqlmap.engine.cache.CacheModel - Cache 'User.user-cache':
stored object 'com.echovox.sso.domain.User@14492fb'
Re: Ibatis caching Empty ResultSet
Posted by Larry Meadors <lm...@apache.org>.
On Feb 10, 2008 1:25 AM, Serge Sozonoff <se...@sozonoff.com> wrote:
> > caches are generally agnostic about the content of the cache.
> Agreed, the cache can be agnostic about its content but then the real
> question is why is iBatis inserting an empty result set into the cache
> in the first place.
Hey, lighten up, I think I wrote that code. :-)
It's by design. Why wouldn't you cache it? It's every bit as valid as
a result set with data in it.
> > This would solve your issue and ensure that your cache is always in a
> > "clean" state. I know you claim that this "makes no sense" but it
> > does :) As long as you are issues more then one select between inserts
> > which is normally the case.
> Yes I know that will fix the problem but its totally stupid to flush the
> entire cache upon insert of a new record.
Based on your comments, I don't think you understand how caching is
intended to work in iBATIS.
It's not an object cache, it's a query cache. We do not cache each
object, we cache query results.
What that means is that if you don't flush the entire cache on an
insert, you're pretty much hosing the entire cache.
In my experience, I have learned three things about caching:
1) Unless you are the only process that touches the database (rare for
me), caching of individual objects is of no value.
2) Caching for more than a few minutes is dangerous.
3) With a good database server with fast disk and enough RAM, a decent
connection pool, and optimized SQL, you can generally achieve adequate
performance with live data.
Larry
RE: Ibatis caching Empty ResultSet
Posted by "Shannon, Bryan" <BS...@Tribune.com>.
Thanks, Larry.
That might be all we need to work around what we've done. That's a
great help, thanks!
I'll dig through version control myself.
We've also noticed a very Sybase-specific (but unrelated) problem that
can be worked around, and I'll be sure to add it to the wiki. (Since I
noticed there was at least ONE other sybase user out there!)
Thanks again!
- Bryan
-----Original Message-----
From: Larry Meadors [mailto:larry.meadors@gmail.com]
Sent: Thursday, July 10, 2008 10:43 PM
To: user-java@ibatis.apache.org
Subject: Re: Ibatis caching Empty ResultSet
Yes, that behavior did change, but I'm not positive when...it's been a
while.
Larry
On Thu, Jul 10, 2008 at 4:34 PM, Shannon, Bryan <BS...@tribune.com>
wrote:
> Can someone please confirm whether or not iBatis used to cache the
> empty resultset in previous versions?
>
> We have a similar situation to Serge, in his slightly older thread, in
> which our code relied on iBatis not returning the null result for the
> second SELECT. (ie NOT caching an empty result)
>
> Upon upgrading from 2.0.9B to the latest version of iBatis, we
> discovered that our code which used to assume that the db query would
> be re-run the second time (after the insert, similar to Serge) and
> find the newly inserted value...
>
> I understand that it DOES make sense to cache the empty results, but
> did this logic change? (I'm fully prepared to look at the code diffs
> myself, but if someone could give me a head start, it'd be great!)
>
> If it is the case that we have to flush the cache with the INSERT of
> each new value, then it seems in these situations we'd only have about
> item in our cache, rendering it less useful.
>
> Any help would be greatly appreciated! ;-) -Bryan
>
>
> -----Original Message-----
> From: Serge Sozonoff [mailto:serge@sozonoff.com]
> Sent: Sunday, February 10, 2008 3:25 AM
> To: user-java@ibatis.apache.org
> Subject: Re: Ibatis caching Empty ResultSet
>
> Hi Nathan and Jeff,
>
>> The real question is why are you not clearing the cache on the insert
>> statement?
> I don't see why I would clear the cache for an insert statement. As
far
> as I am concerned an inserted record shouldn't already be in the cache
> before its been inserted.
>
>> caches are generally agnostic about the content of the cache.
> Agreed, the cache can be agnostic about its content but then the real
> question is why is iBatis inserting an empty result set into the cache
> in the first place.
>>
>> This would solve your issue and ensure that your cache is always in a
>> "clean" state. I know you claim that this "makes no sense" but it
>> does :) As long as you are issues more then one select between
inserts
>
>> which is normally the case.
> Yes I know that will fix the problem but its totally stupid to flush
the
> entire cache upon insert of a new record.
>
> Thanks,
> Serge
>
Re: Ibatis caching Empty ResultSet
Posted by Larry Meadors <la...@gmail.com>.
Yes, that behavior did change, but I'm not positive when...it's been a while.
Larry
On Thu, Jul 10, 2008 at 4:34 PM, Shannon, Bryan <BS...@tribune.com> wrote:
> Can someone please confirm whether or not iBatis used to cache the empty
> resultset in previous versions?
>
> We have a similar situation to Serge, in his slightly older thread, in
> which our code relied on iBatis not returning the null result for the
> second SELECT. (ie NOT caching an empty result)
>
> Upon upgrading from 2.0.9B to the latest version of iBatis, we
> discovered that our code which used to assume that the db query would be
> re-run the second time (after the insert, similar to Serge) and find the
> newly inserted value...
>
> I understand that it DOES make sense to cache the empty results, but did
> this logic change? (I'm fully prepared to look at the code diffs
> myself, but if someone could give me a head start, it'd be great!)
>
> If it is the case that we have to flush the cache with the INSERT of
> each new value, then it seems in these situations we'd only have about
> item in our cache, rendering it less useful.
>
> Any help would be greatly appreciated! ;-)
> -Bryan
>
>
> -----Original Message-----
> From: Serge Sozonoff [mailto:serge@sozonoff.com]
> Sent: Sunday, February 10, 2008 3:25 AM
> To: user-java@ibatis.apache.org
> Subject: Re: Ibatis caching Empty ResultSet
>
> Hi Nathan and Jeff,
>
>> The real question is why are you not clearing the cache on the insert
>> statement?
> I don't see why I would clear the cache for an insert statement. As far
> as I am concerned an inserted record shouldn't already be in the cache
> before its been inserted.
>
>> caches are generally agnostic about the content of the cache.
> Agreed, the cache can be agnostic about its content but then the real
> question is why is iBatis inserting an empty result set into the cache
> in the first place.
>>
>> This would solve your issue and ensure that your cache is always in a
>> "clean" state. I know you claim that this "makes no sense" but it
>> does :) As long as you are issues more then one select between inserts
>
>> which is normally the case.
> Yes I know that will fix the problem but its totally stupid to flush the
> entire cache upon insert of a new record.
>
> Thanks,
> Serge
>
RE: Ibatis caching Empty ResultSet
Posted by "Shannon, Bryan" <BS...@Tribune.com>.
Can someone please confirm whether or not iBatis used to cache the empty
resultset in previous versions?
We have a similar situation to Serge, in his slightly older thread, in
which our code relied on iBatis not returning the null result for the
second SELECT. (ie NOT caching an empty result)
Upon upgrading from 2.0.9B to the latest version of iBatis, we
discovered that our code which used to assume that the db query would be
re-run the second time (after the insert, similar to Serge) and find the
newly inserted value...
I understand that it DOES make sense to cache the empty results, but did
this logic change? (I'm fully prepared to look at the code diffs
myself, but if someone could give me a head start, it'd be great!)
If it is the case that we have to flush the cache with the INSERT of
each new value, then it seems in these situations we'd only have about
item in our cache, rendering it less useful.
Any help would be greatly appreciated! ;-)
-Bryan
-----Original Message-----
From: Serge Sozonoff [mailto:serge@sozonoff.com]
Sent: Sunday, February 10, 2008 3:25 AM
To: user-java@ibatis.apache.org
Subject: Re: Ibatis caching Empty ResultSet
Hi Nathan and Jeff,
> The real question is why are you not clearing the cache on the insert
> statement?
I don't see why I would clear the cache for an insert statement. As far
as I am concerned an inserted record shouldn't already be in the cache
before its been inserted.
> caches are generally agnostic about the content of the cache.
Agreed, the cache can be agnostic about its content but then the real
question is why is iBatis inserting an empty result set into the cache
in the first place.
>
> This would solve your issue and ensure that your cache is always in a
> "clean" state. I know you claim that this "makes no sense" but it
> does :) As long as you are issues more then one select between inserts
> which is normally the case.
Yes I know that will fix the problem but its totally stupid to flush the
entire cache upon insert of a new record.
Thanks,
Serge
Re: Ibatis caching Empty ResultSet
Posted by Serge Sozonoff <se...@sozonoff.com>.
Hi Nathan and Jeff,
> The real question is why are you not clearing the cache on the insert
> statement?
I don't see why I would clear the cache for an insert statement. As far
as I am concerned an inserted record shouldn't already be in the cache
before its been inserted.
> caches are generally agnostic about the content of the cache.
Agreed, the cache can be agnostic about its content but then the real
question is why is iBatis inserting an empty result set into the cache
in the first place.
>
> This would solve your issue and ensure that your cache is always in a
> "clean" state. I know you claim that this "makes no sense" but it
> does :) As long as you are issues more then one select between inserts
> which is normally the case.
Yes I know that will fix the problem but its totally stupid to flush the
entire cache upon insert of a new record.
Thanks,
Serge
Re: Ibatis caching Empty ResultSet
Posted by Nathan Maves <na...@gmail.com>.
The real question is why are you not clearing the cache on the insert
statement?
This would solve your issue and ensure that your cache is always in a
"clean" state. I know you claim that this "makes no sense" but it does :)
As long as you are issues more then one select between inserts which is
normally the case.
Nathan
On Feb 9, 2008 9:55 AM, Jeff Butler <je...@gmail.com> wrote:
> This behavior makes perfect sense to me - caches are generally agnostic
> about the content of the cache. They shouldn't behave one way for one kind
> of result and another way for others. An empty result set represents an
> accurate representation of the DB at the time the first query is performed.
> Suppose you never did the insert...you would want to cache the empty result
> to avoid re-querying the DB wouldn't you? This is the entire purpose of a
> cache.
>
> If you want to change this behavior, then you might be able to write your
> own cache controller to plugin to iBATIS, or perhaps you could make some
> configuration of OSCACHE that would work this way. I'm not sure about
> that. But the behavior you are experiencing seems like the way it *should*
> work to me.
>
> Jeff Butler
>
>
>
>
> On Feb 9, 2008 8:09 AM, Serge Sozonoff <se...@sozonoff.com> wrote:
>
> > Hi All,
> >
> > I am experiencing some strange behavior with the iBatis cache (iBatis
> > version 2.3.x)
> >
> > It seems like iBatis is caching empty ResultSets/ResultMap, below is a
> > select statement for a user which does not exist in the database.
> > Notice the last line iBatis is caching something but its not a User
> > object which is what it would be for a select with an existing user as
> > seen in the second log extract.
> >
> > Any thoughts anyone?
> >
> > This is a problem for me in the following scenario.
> >
> > select user to see if he/she exists
> > insert user because does not exist
> > .... later ... select the user which now exists
> >
> > In the above scenario the third statements returns a null object because
> > it is returning the "null" object which was cached from the first
> > statement!
> > To make this work I would have to flush the cache every time I insert
> > the new user which to me makes no sense at all.
> >
> > Thanks,
> > Serge
> >
> > Output for select of non existing user with a strange cache behavior.
> >
> > 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
> > java.sql.PreparedStatement - {pstm-100004} Executing
> > Statement: SELECT * FROM users WHERE username = ?
> > 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
> > java.sql.PreparedStatement - {pstm-100004} Parameters: [usera]
> > 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
> > java.sql.PreparedStatement - {pstm-100004} Types: [java.lang.String]
> > 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG java.sql.ResultSet -
> > {rset-100005} ResultSet
> > 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
> > com.ibatis.sqlmap.engine.cache.CacheModel - Cache 'User.user-cache':
> > stored object 'java.lang.Object@5b02a6'
> >
> >
> > Output for a select of an existing user
> >
> > 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
> > java.sql.PreparedStatement - {pstm-100009} Executing
> > Statement: SELECT * FROM users WHERE username = ?
> > 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
> > java.sql.PreparedStatement - {pstm-100009} Parameters: [userc]
> > 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
> > java.sql.PreparedStatement - {pstm-100009} Types: [java.lang.String]
> > 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
> > - {rset-100010} ResultSet
> > 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
> > - {rset-100010} Header: [id, username, password_hash,
> > pwd_reminder_email, active]
> > 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
> > - {rset-100010} Result: [7, userc, w1vwW2KgIeg=, email@email.com, true]
> > 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
> > com.ibatis.sqlmap.engine.cache.CacheModel - Cache 'User.user-cache':
> > stored object 'com.echovox.sso.domain.User@14492fb'
> >
>
>
Re: Ibatis caching Empty ResultSet
Posted by Jeff Butler <je...@gmail.com>.
This behavior makes perfect sense to me - caches are generally agnostic
about the content of the cache. They shouldn't behave one way for one kind
of result and another way for others. An empty result set represents an
accurate representation of the DB at the time the first query is performed.
Suppose you never did the insert...you would want to cache the empty result
to avoid re-querying the DB wouldn't you? This is the entire purpose of a
cache.
If you want to change this behavior, then you might be able to write your
own cache controller to plugin to iBATIS, or perhaps you could make some
configuration of OSCACHE that would work this way. I'm not sure about
that. But the behavior you are experiencing seems like the way it *should*
work to me.
Jeff Butler
On Feb 9, 2008 8:09 AM, Serge Sozonoff <se...@sozonoff.com> wrote:
> Hi All,
>
> I am experiencing some strange behavior with the iBatis cache (iBatis
> version 2.3.x)
>
> It seems like iBatis is caching empty ResultSets/ResultMap, below is a
> select statement for a user which does not exist in the database.
> Notice the last line iBatis is caching something but its not a User
> object which is what it would be for a select with an existing user as
> seen in the second log extract.
>
> Any thoughts anyone?
>
> This is a problem for me in the following scenario.
>
> select user to see if he/she exists
> insert user because does not exist
> .... later ... select the user which now exists
>
> In the above scenario the third statements returns a null object because
> it is returning the "null" object which was cached from the first
> statement!
> To make this work I would have to flush the cache every time I insert
> the new user which to me makes no sense at all.
>
> Thanks,
> Serge
>
> Output for select of non existing user with a strange cache behavior.
>
> 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
> java.sql.PreparedStatement - {pstm-100004} Executing
> Statement: SELECT * FROM users WHERE username = ?
> 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
> java.sql.PreparedStatement - {pstm-100004} Parameters: [usera]
> 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
> java.sql.PreparedStatement - {pstm-100004} Types: [java.lang.String]
> 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG java.sql.ResultSet -
> {rset-100005} ResultSet
> 09 Feb 2008 14:40:10,406 47078 [http-8080-2] DEBUG
> com.ibatis.sqlmap.engine.cache.CacheModel - Cache 'User.user-cache':
> stored object 'java.lang.Object@5b02a6'
>
>
> Output for a select of an existing user
>
> 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
> java.sql.PreparedStatement - {pstm-100009} Executing
> Statement: SELECT * FROM users WHERE username = ?
> 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
> java.sql.PreparedStatement - {pstm-100009} Parameters: [userc]
> 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
> java.sql.PreparedStatement - {pstm-100009} Types: [java.lang.String]
> 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
> - {rset-100010} ResultSet
> 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
> - {rset-100010} Header: [id, username, password_hash,
> pwd_reminder_email, active]
> 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG java.sql.ResultSet
> - {rset-100010} Result: [7, userc, w1vwW2KgIeg=, email@email.com, true]
> 09 Feb 2008 15:02:49,859 245953 [http-8080-1] DEBUG
> com.ibatis.sqlmap.engine.cache.CacheModel - Cache 'User.user-cache':
> stored object 'com.echovox.sso.domain.User@14492fb'
>