MySQL Bugs: #70850: fetch the data persisted by another thread fails in concurrent scenarios

Bug #70850	fetch the data persisted by another thread fails in concurrent scenarios
Submitted:	7 Nov 2013 5:45	Modified:	7 Dec 2013 17:57
Reporter:	Sachin Francis	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
i am facing the same problems as discussed here.

http://stackoverflow.com/questions/16055324/hibernate-mysql-concurrency-issue
http://stackoverflow.com/questions/15806893/threading-issues-with-hibernate?lq=1

When I persist something to the database using one thread and try to fetch it using another thread, Hibernate does not always find the entity. 

And this is happening only in concurrent scenarios, from mysql 5.5.

It works fine with mysql 5.1, postgress, oracle etc

the problem is there even with mysql 5.6.

So i believe the problem is with mysql.

Any Help

How to repeat:
http://stackoverflow.com/questions/16055324/hibernate-mysql-concurrency-issue
http://stackoverflow.com/questions/15806893/threading-issues-with-hibernate?lq=1

Hi Sachin,

Thanks for your bug report, but unfortunately, there's far too little information supplied to indicate that this is a bug.

The default transaction isolation for InnoDB is repeatable-read.  See the following page for details:

http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_repeatable-read

This can cause concurrent transactions to not see data committed in other transactions.  You can consider changing the transaction isolation to READ COMMITTED, but you should understand the implications:

http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed

So why would it be different in 5.1 than 5.5?  Best guess here is that the table creation doesn't specify a storage engine, and the default in 5.1 is the non-transactional, locking MyISAM storange engine, while 5.5 changes the default to InnoDB:

http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html

If you can provide a reproducible test case demonstrating behavior not explained by any of the above information, we'll re-evaluate as a defect.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".