Hi,

since my question is of a rather fundamental nature, I'm pretty sure that it had been raised before by someone, but nevertheless I wasn't able to find anything in this or other related forums.

I'm working on an EJB3 application based on Hibernate EntityManager / Annotations and JBoss 4.0.4, backed by an Oracle 10g database.

Suppose we have some Entity "E". E has a UNIQUE constraint on some attribute, say "nickname".
Further suppose, we have a SLSB with a method "void doBusiness(E e)" with container managed transaction demarcation, that implements the "business logic".

In my business logic, I want to "opportunistically" persist e, that is, I want the following behavior:

- if no E exists in the database with the given nickname, persist the entity e.
- else, do nothing and continue work, including other database manipulation. (I do not consider this being an error, but rather a legitimate special case.)


At a first glance, there are two options:

Option A: Blindly persist e and catch (and discard) the potentially resulting PersistenceException, which signals the UNIQUE constraint violation

Option B: Do a search on the UNIQUE attribute before persisting, to check if an entity with the given nickname already exists.


Now for my problems:

Option A won't work: According to the EJB spec, the transaction is marked for rollback, so no further work can be done. (Moreover, according to the Hibernate docs, a Session - which after all is what does the work behind the scenes - may not be reused in case of an exception being thrown)

Option B seems to work at first, but is incorrect and fails in a highly concurrent scenario:
- Thread/Transaction ONE does the search and finds that an E with the given nickname doesn't exist.
- Thread/Transaction TWO does the search and finds that an E with the given nickname doesn't exist.
- Thread/Transaction ONE calls persist(), resulting in an insert and commits.
- Thread/Transaction TWO calls persist(), resulting in an UNIQUE constraint violation, resulting in a PersistenceException and leading us to the same problem as with Option A. (I'm aware that the INSERT yielding the Exception usually happens delayed, when the session is flushed, but this doesn't change the nature of the problem.)

I should mention that the database operates in isolation level READ COMMITED. I probably could make Option B work by using SERIALIZABLE instead, but I want to avoid this due to the potentially heavy impact on concurrency / performance.

My favorite solution would be to go with Option A, since in a non EJB environment this seems to be the most natural and best performing way. But this would require to tell the container somehow not to mark the TA for rollback in case of a constraint violation

Any ideas or comments?


Thank you for your assistance
Günter

REPEATABLE_READ should be enough I think
Or you can manually acquire a table lock

Hi Emmanuel, many thanks for your reply!


I don't think so, because REPEATABLE_READ only prevents from modification of already read records. This is not he case here, because the record doesn't exist before being inserted, of course.
Secondly, Oracle doesn't support REPEATABLE_READ.


Yes, this works, but unfortunately I couldn't find a way to do it RDBMS independently. Session.lock() seems to support row level locks only, right? Or did I miss something here?

Uhmm, by the way, does anybody know how I can find out which RDBMS I'm running on / which dialect is in effect? I know that I can retrieve the dialect from the Configuration. But how to get to the (Hibernate-)Configuration in a managed environment?

Günter

ah correct, you need SERIALIZABLE, which is a bit overkill

When you access the connection, you can query the JDBC metadata and know the database you are working on