Categories

  • articles

Tags

  • db
  • mysql
  • java

This is a interesting issue I found using Hibernate. You have 2 independent application both using the same database. What I found that if one application submits a value to the database the second application does not see the updated/new entry. If you use direct SQL on the database the changed/new entry is visible and the same is true if you use the JDBC directly without hibernate.

My first though was that it was some caching issue so I disabled secondary cache, disabled query cache, cleared the session by calling session.clear() and many other obscure things. None of these solved the problem.

Finally found the problem to be database isolation. For those that are not aware about isolation levels it is how transaction integrity is visible to other users and systems. So when data becomes visible to others.

There are 4 main level supported by most databases:

  • 1 – TRANSACTION_READ_UNCOMMITTED: Allows dirty reads, non-repeatable reads, and phantom reads to occur.
  • 2 – TRANSACTION_READ_COMMITTED: Prevents dirty reads, but non-repeatable reads and phantom reads may occur.
  • 4 – TRANSACTION_REPEATABLE_READ: Prevents dirty and non-repeatable reads, but phantom reads may occur.
  • 8 – TRANSACTION_SERIALIZABLE: Prevent everything – phantom reads may not occur.

Default is usually 2 – Read Commited. But for some reason in the setup I had using Hibernate 4.3.5, MySQL connector 5.3.31 and JPA this was not the case. So a simple fix is to set the isolation property with:

<property name="hibernate.connection.isolation">2</property>

That solved all my problems.