ejbSelect / ejbStore Bug!!!

[jcraig]

Hi

I think I have found a serious 'bug'. I want to enforce that the value of a cmp field/column is unique across all instances of an enitity bean. This field is not my primary key.

Therefore before setting the cmp fields value via its accessor method, I call a custom ejbSelect which checks whether any other entity bean has a field with the specified value. If I get a finder exception, meaning no entity object has that field set to the value in question, I use the accessor method and update the value. The problem seems to come in with using the ejbSelect method directly before using the accessor method. It seems as if ejbStore does not get called consistantly. The entity objects state always changes but it doesn't always get persisted. If I comment out the ejbSelect bits everything works fine... Could the usage of the ejbSelect method be affecting when the ejbStore method gets called?

I have posted the bug, with example source, on SourceForge request ID (551404).

Has anybody had the same problem. It still happens with JBoss 3 RC2...

Thanks

[dsundstrom]

> I think I have found a serious 'bug'. I want to
> enforce that the value of a cmp field/column is
> unique across all instances of an enitity bean. This
> field is not my primary key.
>
> Therefore before setting the cmp fields value via its
> accessor method, I call a custom ejbSelect which
> checks whether any other entity bean has a field with
> the specified value. If I get a finder exception,
> meaning no entity object has that field set to the
> value in question,

This won't work because two entities could execute the quey at the same time and neither would detect that the other was about the set the value. You have to use a database constraint, but this means that you will not be able to handle the exception; SQLExceptions are black boxes.

> I use the accessor method and
> update the value. The problem seems to come in with
> using the ejbSelect method directly before using the
> accessor method. It seems as if ejbStore does not get
> called consistantly. The entity objects state always
> changes but it doesn't always get persisted. If I
> comment out the ejbSelect bits everything works
> fine... Could the usage of the ejbSelect method be
> affecting when the ejbStore method gets called?
>
> I have posted the bug, with example source, on
> SourceForge request ID (551404).

That is definately a bug. Thanks for the report. Just for your info, before a select or finder is invoked we are required to synchronize all entities in the transaction, so this code could easily mess up the stores state.

[jcuen]

Thanks for the feedback and advice.

You are correct that this approach will not work. After careful consideration we have decided to go the root that you suggest.

There are two aspects of using a database constraint to enforce column uniqueness that I do not like. From a conceptual point of view a uniqueness constraint is a business rule - and I do not like the idea of embedding business rules in the database tier. From a bean developers point of view, I am starting to lose ease of portability between databases. I believe that constraints such as this should be defined in the deployment descriptor (perhaps it should be addressed for EJB 3).

I haven't tested it as yet but if one has a unique key constraint on a database column and a entity update fails due to a violation of it - jBoss should not throw SQLException but DuplicateKeyException.

There is a misconception in some areas that this should only be done for Primary keys, however, according to the EJB 2 spec this is not the case - please refer to section 10.5.8.2 of the specification.

I will test this on jBoss and if jBoss is acting incorrectly I will post a bug on SourceForge

Thanks Again!

[dsundstrom]

> There are two aspects of using a database constraint
> to enforce column uniqueness that I do not like.
> From a conceptual point of view a uniqueness
> constraint is a business rule - and I do not like
> the idea of embedding business rules in the database
> tier. From a bean developers point of view, I am
> starting to lose ease of portability between
> databases. I believe that constraints such as this
> should be defined in the deployment descriptor
> (perhaps it should be addressed for EJB 3).

That is an interesting idea, but I don't think it is possible to enforce a uniqueness constraint from the application layer. It would require the ability to lock the entire table, check for uniqueness, and if unique insert/update and unlock the table. It would be useful for other constraints such as value in range.

Also it could be used as a two phase check. Check in the app level as best a we can, but enforce it in the db level. If it fails in the db level because of concurency that would be an acceptable exceptional case. This is how create works today.

> I haven't tested it as yet but if one has a unique
> key constraint on a database column and a entity
> update fails due to a violation of it - jBoss should
> not throw SQLException but DuplicateKeyException.

DuplicateKeyException is only for primary keys in the create method. I would create a new JBoss exception which extends EJBException, something like ConstraintViolationException.

> There is a misconception in some areas that this
> should only be done for Primary keys, however,
> according to the EJB 2 spec this is not the case -
> please refer to section 10.5.8.2 of the
> specification.

Dam, ignore some of the above comment; DuplicateKeyException is still only allowed for the create method.

> I will test this on jBoss and if jBoss is acting
> incorrectly I will post a bug on SourceForge

You can post a bug, but there is no way for JBoss to check for this in the application level. Given that SQLException are black boxes there is no way for JBoss to determine if this is an duplicate key problem or something else.

[jcuen]

> > There are two aspects of using a database constraint
> > to enforce column uniqueness that I do not like.
> > From a conceptual point of view a uniqueness
> > constraint is a business rule - and I do not like
> > the idea of embedding business rules in the database
> > tier. From a bean developers point of view, I am
> > starting to lose ease of portability between
> > databases. I believe that constraints such as this
> > should be defined in the deployment descriptor
> > (perhaps it should be addressed for EJB 3).
>
> That is an interesting idea, but I don't think it is possible to enforce
> a uniqueness constraint from the application layer. It would require the
> ability to lock the entire table, check for uniqueness, and if unique
> insert/update and unlock the table. It would be useful for other
> constraints such as value in range.

I did not mean for the uniqueness to be enforced from an application level but then I did not elaborate. How I see such a thing working (if supported by future EJB specifications) is as follows: If a uniqueness constraint was defined in the deployment descriptor, the app server could create the constraint at deployment time, very much how I imagine it currently works for primary keys.

> Also it could be used as a two phase check. Check in the app level as
> best a we can, but enforce it in the db level. If it fails in the db
> level because of concurency that would be an acceptable exceptional
> case. This is how create works today.
>
> > I haven't tested it as yet but if one has a unique
> > key constraint on a database column and a entity
> > update fails due to a violation of it - jBoss should
> > not throw SQLException but DuplicateKeyException.

I went through the EJB2 spec again and discovered the following: The app server may, but is not required to, throw DuplicateKeyException.

I would like to make a comment on this. I know that originally it was required (The revision history - section E.10 Public Draft - states that this requirement was removed) however if it is not required then why is it in the spec? One of the golden rules for drawing up specifications is testability. If a specification is not testable then why have it? So I would propose that if this is no longer required then it should be removed altogether.

> DuplicateKeyException is only for primary keys in the create method. I
> would create a new JBoss exception which extends EJBException, something
> like ConstraintViolationException.
>
> > There is a misconception in some areas that this
> > should only be done for Primary keys, however,
> > according to the EJB 2 spec this is not the case -
> > please refer to section 10.5.8.2 of the
> > specification.
>
> Dam, ignore some of the above comment; DuplicateKeyException is still
> only allowed for the create method.

Yes, you are right, DuplicateKeyException extends CreateException - so in that way it only makes sense for it to be used for the create method (Although a create method may set fields other than the PK).

> > I will test this on jBoss and if jBoss is acting
> > incorrectly I will post a bug on SourceForge
>
> You can post a bug, but there is no way for JBoss to check for this in
> the application level. Given that SQLException are black boxes there is
> no way for JBoss to determine if this is an duplicate key problem or
> something else.

This is not a bug as it is no longer required - so I will not post it.
By the way, how does JBoss currently determine weather or not a SQLException is a thrown because of a duplicate key problem with primary keys?