Database deadlock and foreign key conflicts

 

Overview

Repetition of certain SQL error messages indicate problems, such as database referential integrity violations, that we can prevent by using the CMP sequence grouping feature.

 

Exceptions resulting from foreign key conflicts due to violations of database referential integrity

A database referential integrity (RI) policy prescribes rules for how data is written to and deleted from the database tables to maintain relational consistency. Run-time requirements for managing bean persistence, however, can cause an EJB application to violate RI rules, which can cause database exceptions.Your EJB application is violating database RI if we see an exception message in the WAS trace or log file that is similar to one of the following messages (which were produced in an environment running DB2):

The insert or update value of the FOREIGN KEY table1.name_of_foreign_key_constraint is not equal to any value of the parent key of the parent table.

...or...

A parent row cannot be deleted because the relationship table1.name_of_foreign_key_constraint is not equal to any value of the parent key of the parent table.

To prevent these exceptions, designate the order in which entity beans update relational database tables by defining sequence groups for the beans.

 

Exceptions resulting from deadlock caused by optimistic concurrency control schemes

Additionally, sequence grouping can minimize transaction rollback exceptions for entity beans that are configured for optimistic concurrency control. Optimistic concurrency control dictates that database locks be held for minimal amounts of time, so that a maximum number of transactions consistently have access to the data. In such a highly available database, concurrent transactions can attempt to lock the same table row and create deadlock. The resulting exceptions can generate messages similar to the following (which was produced in an environment running DB2):

Unsuccessful execution caused by deadlock or timeout.

Use the sequence grouping feature to order bean persistence so that database deadlock is less likely to occur.

---

Related concepts

Sequence grouping for container-managed persistence
Concurrency control

 

Related tasks

Set the run time for CMP sequence groups