I'm really upset with Hibernate!
I have a database table (mysql) that holds parent-child relationships that allow me to build a tree of categories. I have multiple threads which can try to get and if not there create the category path (with several parent-child rows implied) at roughly the same time.
The problem is that I'm using TRANSACTION_READ_COMMITTED only, and so race conditions can occur where a thread can create a parent-child for a category subpath because it didn't find it, and then (lo!) another thread did this at the same time. To try to solve this problem, I put a unique constraint on the parent/child ids and a unique constraint on the full category path. Then, I hoped that in my session, I would catch the hibernate ConstraintViolationException, and knowing that another thread wrote the new relationship for me, I query for the row that the other thread wrote in the catch clause. And try to continue in that thread doing all the things it needs to do with the session.
This is the ONLY way I can think of to solve the problem of multiple threads doing the work of get/creating the same long category path (with several sub parent-child relationship rows) at the same time, and ensuring that the unique constraint is upheld.
But hibernate invalidates the session on ConstraintViolationException and ultimately throws an assertion exception ("null id in com.stagirite.bean.Category entry (don't flush the Session after an exception occurs)") so my solution is unviable.
How am I going to solve this throughout my application for "get/create" models that don't create duplicate rows, without using a pessimistic lock?
Andy
Since I am not sure of how you have modelled your data (Self referential table or marterialized path) and the corresponding hibernate mapping, I am going to attempt answering the part where two threads are trying to change the same thing. Have you tried using the hibernate versioning feature. In short with versioning if one thread has already updated a category, the version number is incremented. The other thread which may have started the update but did not finish it will see the version change and rollback. It would help if you could post your data model.
Hope that helps.