INSERT followed by SELECT does not return newly added row

[Brian Barnett]

Using MyBatis 3.0.4. After I do an INSERT into tableX I then do a SELECT
from tableX but the newly inserted row does not come back in the results.
Looks as if the cache is being used for the SELECT statement. The INSERT and
SELECT are performed within the same SqlSession object.

Can someone provide some guidance on how best to handle this scenario. I
would like the newly inserted row to come back in the SELECT statement. I'm
just using the default caching in the mapper.xml file, i.e., <cache/>.

Thanks,
Brian

[Paulo Soares]

Please, repeat the same INSERT in any visual SQL tool and execute
again the SELECT.

I think that this problem can to be a trigger or constraint of database.

Paulo Soares

[Brian Barnett]

The row gets inserted for sure. When I refresh the web page, it then shows up, but I want it to show up in the web page when the user is first presented the page, not after a page refresh.

Sequence:
1. User enters data and clicks "Save".
2. On the server, a SQLSession is opened.
3. The INSERT statement is executed.
4. The SELECT statement is executed, but does not pull the newly inserted row. It uses what is in the cache.
5. Html response is packaged up to send back to user.
6. SQLSession is committed and then closed.
7. Response sent back to user.

[Richard Yee]

Try committing right after the insert.

Richard

Sent from my iPhone

[Jeff Butler]

1. If you are doing select, insert, select in the same SqlSession,
then the SqlSession cache is causing this issue. You will need to
clear the cache manually after the insert: sqlSession.clearCache().

2. If the first select is in a different session, then you are using
the global cache and will need to configure MyBatis to clear the cache
on an insert:

<insert flushCache="true" ...>
</insert>

#2 is default behavior, so this leads me to believe that #1 is your issue.

Jeff Butler

[Brian Barnett]

I was under the impression that using <cache/> in the sql map file caused
INSERTS, UPDATES and DELETES to automatically clear the cache?? Anyway I
added a manual call to sqlSession.clearCache() and that did not resolve the
problem.

It was my impression that the standard way a database transaction was
supposed to function was that anything that happened within the transaction,
or "session", was supposed to be visible within the transaction. In other
words, if I INSERT 10 rows, UPDATE 5 rows, and DELETE 2 rows all in the same
transaction, and then perform a SELECT prior to committing the session,
i.e., still in the same session, the rows returned by the SELECT should
reflect the INSERTS, UPDATES and DELETES that just occurred in that still
uncommitted transaction. Is this incorrect thinking?

It was also my impression that if there were a separate transaction
(session), that it would NOT be able to see the results of the still
uncommitted transaction. This functionality appears to work like this. It's
just the part where it all happens within the same transaction that is not
working as I had anticipated.

The only way I could get it to work is to call sqlSession.commit(), as was
suggested by Richard Yee, in a previous response, but I didn't want to have
to manually call commit() like that in these instances. I'm handling the
commit/rollback in a filter class. Didn't want to have to sprinkle commits
throughout the code.

Thanks,
Brian

[Jeff Butler]

You're right - inserts, updates and deletes will clear the cache by
default (not the SqlSession cache, the high level MyBatis cache).
clearCache() is for the SqlSession cache which doesn't appear to be
the issue here. There are two separate caches in MyBatis so it can
get confusing.

As to what can and can't be seen in transactions and by other
transactions, this is controlled by the transaction isolation settings
of the transaction - there is not a one size fits all answer here, it
really depends on the behavior of your specific database and how the
transaction isolation level is set. You should do some research about
the transactional behavior of your database and possibly adjust the
isolation level of your MyBatis transactions.

Jeff Butler

[Brian Barnett]

I added the defaultTransactionIsolation="READ_UNCOMMITTED" to my jdbc setup
file (context.xml), but this did not solve the problem.

Example:
<Resource name="jdbc/myDbConnectionPool" auth="Container"
type="javax.sql.DataSource"
maxActive="20" maxIdle="10" maxWait="10000"
username="XXXX" password="XXXX"
driverClassName="com.mysql.jdbc.Driver"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
url="jdbc:mysql://XXX:1234/xxxx"
defaultTransactionIsolation="READ_UNCOMMITTED"/>

I then removed this and tried specifying the transaction level in the
SqlSesssion itself like this:

public static void open() {

session.set(GlobalStore.getInstance().getSqlSessionFactory().openSession(Tra
nsactionIsolationLevel.READ_UNCOMMITTED));
return;
}

This did not solve the problem either. Not sure what to think at this point.
The READ_UNCOMMITTED value is supposed to let you do "dirty reads" as they
are called. In other words, I should be able to read data that is
uncommitted.

It must be a cache issue. Here is a console output snippet:

2011-04-10 20:32:49,517 DEBUG [java.sql.PreparedStatement:27] ==>
Executing: INSERT INTO discussion_item ( customer_id, meeting_id, notes,
decision ) values ( ?, ?, ?, ? )
2011-04-10 20:32:49,533 DEBUG
[com.wss.ldsminutes.model.service.BaseService:34]
DiscussionItemService.selectDiscussionItemsForMeeting
2011-04-10 20:32:49,533 DEBUG
[org.apache.ibatis.cache.decorators.LoggingCache:27] Cache Hit Ratio
[com.wss.ldsminutes.model.dao.DiscussionItemMapper]: 0.42857142857142855

Notice the insert, then a select,
DiscussionItemService.selectDiscussionItemsForMeeting executes a select
statement, and then the cache hit ratio notification.

I tried it with and without a call to SqlSession.clearCache() before the
select statement. Neither worked.

Is there a bug somewhere? Do I not have something configured properly?

[Jeff Butler]

There's definitely a problem somewhere - I'm not sure where yet but
I'm looking into it. In my test, if I disable the cache everything
works. Weird. I'll keep looking.

Jeff Butler

[Brian Barnett]

Jeff,
Were you able to find out what the problem is on this one?
Thanks.

Jeff Butler

> on(Tra nsactionIsolationLevel.READ_UNCOMMITTED));

[Jeff Butler]

Well...it's complex. This behavior is due to MyBatis, not to the DB.
But I think MyBatis is doing the "right" thing.

Since the MyBatis cache is a global cache for all sessions, the cache
is not updated until a commit. This makes sense to me because if
something went wrong and the transaction was rolled back, then you
would not want the cache to reflect a different state than the
database. To make it work without the commit(), we would have to
somehow implement a commit/rollback strategy in the cache - and that
seems like overkill for something as simple as MyBatis.

Consider this sequence:

1. select from foo (populates the cache)
2. insert into foo
3. select from foo (reads from cache)

If steps 2 and 3 are in the same session, then you will not see the
newly inserted record in the second select. This can be easily cured
in two ways:

1. commit after the insert
2. configure your session for auto commit and do the second select in
a second sql session

In summary, I think MyBatis is working correctly here. Your use case
is a bit unusual - I think that adding a commit is not too much
trouble.

Jeff Butler

[Brian Barnett]

Thanks Jeff. I'll add the manual commits.
~ Brian

Consider this sequence:

Jeff Butler

>> i on(Tra nsactionIsolationLevel.READ_UNCOMMITTED));

[Koka Kiknadze]

Why is step 3 necessary at all? Imho all of the data you expect to get with step 3 is already available from steps 1-2. I'd try avoiding manual transaction control if possible.

[Brian Barnett]

Yes, that was my intent – avoid manual transaction control, but it looks like it is the easiest option.

 

Other options:

1.       Manually add the new row into the grid (java List object) prior to returning html response. How is this any better than just manually committing?

2.       Send a subsequent html request (after transaction has committed normally) to get a refreshed List control for the grid.

 

Do you have another suggestion?

[AleBu]

Hi, I am new to mybatis, but this topic is very interesting. What if
global cache will be turned off? In this way step 3 of your example
should display inserted row or not?

[Jeff Butler]

This behavior does not happen when the global (or transactional) cache
is disabled. With no transactional cache, you will always get fresh
records from the database in this scenario - so step 3 will show the
inserted row.

You might know that there is another cache in MyBatis - the SqlSession
cache. That cache is always cleared on an update/insert/delete so it
is not a contributor to this behavior.

Jeff Butler

[Brian Barnett]

What is the benefit of the global/transactional cache? If I wanted to turn
it off, how would I do it? If there is a good place to read about it, please
let me know. I didn't find anything about a global or transactional cache in
the User Guide.

Wondering if the SqlSession cache is sufficient for me?? Where can I read
about what these two caches provide, how they differ, how to configure them,
etc.?

Thanks,
Brian Barnett

Jeff Butler

>> >> essi on(Tra nsactionIsolationLevel.READ_UNCOMMITTED));

[Jeff Butler]

The global/transactional cache is the cache you read about in the
user's guide...<cache/> tags, etc. There are lots of settings for the
cache described in the user's guide. Benefits are far better
performance for repeated queries. Generally you don't have to worry
too much about how the cache works - but you uncovered a scenario
where you need to be aware of its behavior.

The SqlSession cache cannot be configured - it just IS. That cache
only exists for the duration of a single SqlSession - and each
SqlSession gets a new instance of a session cache. The SqlSession
cache is not about performance. It's purpose is to help MyBatis deal
with queries that have circular references. Generally, you don't have
to think about the SqlSession cache and it doesn't give you the kind
of behavior you would normally expect from a cache - it's really a
MyBatis internals thing.

Jeff Butler

[gogobu]

I happened to encounter the same problem just now... and have to
disagree on whether MyBatis is working "correctly". Base on the
current discussion, in order to flush the global cache I have to
manually commit the transaction, which I don't think it's the right
thing to do as I'd rather have my transaction manager doing it when
it's appropriate.

IMHO, the default behavior of MyBatis should clear its global cache as
soon as an INSERT/UPDATE/DELETE happens even if it's not yet
committed. I'd rather have my global cache mistakenly cleared from
time to time than having a wrong resultset in my SELECT.

I agree this depends on the usage scenario, but for the "default"
behavior, correctness is far more important than efficiency.

Cheers,
Michael

> >>> >>> To:...
>
> read more »

[Brian Barnett]

Yes, this was my opinion as well, but after the discussion, I figured my
scenario was considered an uncommon one. I personally didn't think it was
very uncommon?? I had expected the global cache to function as Michael
described here, clear after an INSERT/UPDATE/DELETE, especially after
reading the documentation. The docs say this:

flushCache: Setting this to true will cause the cache to be flushed whenever
this statement is called

But apparently should say this:

flushCache: Setting this to true will cause the cache to be flushed AFTER
THE TRANSACTION IS COMMITTED whenever this statement is called

Is it really an uncommon scenario to INSERT/UPDATE/DELETE and then do a
SELECT within the same HTTP request, i.e., the same SQLSession? I would
think it would be much more efficient than doing a separate HTTP round trip
request to do the SELECT, just so the commit could happen "normally".

[Jeff Butler]

I think it is kind of uncommon to insert/update/delete and then select
in the same HTTP request. Plus it opens you up to all kinds of issues
with the back and refresh buttons. I'm much more of a fan of the
"redirect after post" pattern where you'll never have to worry about
unintended side effects from a browser refresh.

But there's more to think about here. Suppose MyBatis worked the way
you are suggesting...

Transaction 1:
- delete record (flush cache, but no commit yet)

Transaction 2:
- select (refills cache, and includes deleted record because of db
transaction isolation)

Transaction 1:
- select (uses cache which includes deleted record, displays wrong data to user)
- close/commit (database delete is committed, but MyBatis cache
includes deleted record)

Now the cache is wrong and there's no way of knowing it. Not acceptable.

Jeff Butler

[gogobu]

Hmm... with this scenario I've to agree that as long as cache was
enabled, all SELECTs could only be trusted after the transaction is
committed. I guess we'll just have to be extra careful if we want to
truly enjoy the benefit of cache.

Nevertheless, I'd also suggest putting a reminder like Brian said in
the document just to avoid confusion.

Cheers,
Michael

> ...
>
> read more »

[Brian Barnett]

Thank you Jeff. Your warnings, example and explanation have been very
helpful.

[Hugh Ross]

Jeff,

In that example, unless the Transaction 2 select used a dirty read,
I'd expect a either Timeout error or row-not-found.

In the original scenario outlined by Brian, if the object is not found
in the Cache, I'd expect MyBatis to go to the DB for it (and then
cache it).

??

Hugh

[DaveyBob]

I absolutely agree. If MyBatis requires my code to commit to satisfy the
cache, then I think we have a design flaw. The cache should stay out of the
way of commit/rollback semantics. The presence of the cache should be
invisible to the user.

Maybe there need's to be a "cache sensitivity" setting...so that if you are
doing queries of updates within the same transaction, you can cause the
updates to flush immediately.

--
View this message in context: http://mybatis-user.963551.n3.nabble.com/INSERT-followed-by-SELECT-does-not-return-newly-added-row-tp2801268p3684634.html
Sent from the mybatis-user mailing list archive at Nabble.com.

[DaveyBob]

Well, as fate would have it, I have just bumped into this problem first hand.

(All this within one transaction)

One part of the code queries to see if a particular row exists. It doesn't,
and so the cache gets populated with an empty result list. Then we do an
insert into the table. Later we go through some complicated logic, and
query again for the row. It just got created, of course, in the
transaction, but the cache hands back an empty list.

This actually causes a NullPointerException in our code...we expect that
since we just added the row, we should be able to get it back again, and we
index the array at [0]...and the NPE happens.

It seems to me that we really need way to indicate that all caches (or
specific caches) should aggressively clear the cache (immediately, on
insert/update/delete) so that it's correct all the time.

I can't defeat my transaction semantics (by adding commits to satisfy this
caching issue) and risk leaving orphaned rows around.

--
View this message in context: http://mybatis-user.963551.n3.nabble.com/INSERT-followed-by-SELECT-does-not-return-newly-added-row-tp2801268p3688916.html

[Eduardo]

Hi Davey. I am afraid that separating cache from transactions will
cause much more issues.

for example:
open session
insert row
select row -> gets cached
rollback
close session
open session
select row -> got from cache

Should row be able to be retrieved? I would say no because it was
never in the DB.

I cannot understand what was the problem that Bryan had.

INSERT into tableX
SELECT from tableX

That select can never be cached previously (insert does not update the
cache). So I do not know why the commit fixed the issue.

On 24 ene, 14:15, DaveyBob <psyn...@yahoo.com> wrote:
> I absolutely agree.  If MyBatis requires my code to commit to satisfy the
> cache, then I think we have a design flaw.  The cache should stay out of the
> way of commit/rollback semantics.  The presence of the cache should be
> invisible to the user.
>
> Maybe there need's to be a "cache sensitivity" setting...so that if you are
> doing queries of updates within the same transaction, you can cause the
> updates to flush immediately.
>
> --

> View this message in context:http://mybatis-user.963551.n3.nabble.com/INSERT-followed-by-SELECT-do...

[Eduardo]

If you are doing this on the same SqlSession
select
insert
select

The insert should flush the SqlSession so there shoud be no problem
with it.

Global cache will also be updated with the first select, so the second
will be retrieved from the cache. That should also be solved adding
the flushCache="true" property.

Try with that and post the results.