How do we resolve Primary key violation exception, when we have our primary key Id being created by a sequence generator?
When we work in a cluster server environment, for example, in Weblogic server we have Admin server, managed1, managed2 and so on, there are multiple requests being handled simultaneously at the same time. This is generally done for to enable you to balance loads and provide fail over protection for critical applications.
When we have our Enterprise application or a Web Application being deployed in such a clustered environment, which have its database transaction handled by JPA specification for persistence, instead of a plane JDBC. Here, we usually concentrate on business requirement implementation and rely on the server and the framework to handle our database transaction.
Cause:
Creating a new record in a particular table requires creation of a new entity instance in the persistence context. A fully populated object would be in its persistent state and then after a commit would be in a detached state. We have a sequence generator, usually for our primary key Id generation instead of a manual Id creation ,to generate the value for Id, Weblogic does its own caching in persistent context.
In a cluster environment there are separate instances of persistence context per managed server. There could be problem when multiple new object created and remain in persistent state. This could result in the Id having the same value increment, and thus result a Primary key violation exception.
Solution:
There are two steps to resolve this issue:
1) In your Entity class, while writing a "sequenceGenerator" for your primary key ID generation, mention the attribute "allocationSize=0". By assigning Allocation size to zero disable the generation of Id from persistence context cache and forces to query to fetch the latest Id value from database and then increment it to generate a new value. if we do not specify the allocation size attribute the default size for allocation size is 50.
For example, as below:
@Entity
public class Employee implements Serializable {
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="empSeq")
@SequenceGenerator(name="empSeq",sequenceName="EMP_SEQ", allocationSize=0)
private long id;
- In @GeneratorValue attribute generator refers to the @SequenceGenerator mentioned below.
- In @SequenceGenerator the sequenceName attribute the name of the sequence created and defined in database.
2) This is the second step, and may be optional too. Required if you are still not able to resolve the issue after performing step one.
Also, modify your existing SQL or create SQL query for EMP_SEQ sequence creation in database, in following manner:CREATE SEQUENCE EMP_SEQ START WITH start_num, INCREMENT BY increment_num, NOCYCLE, NOCACHE;
- The start_num mentioned above for START WITH must be a value greater than the maximum record's Id value present in data base.
- The increment_num could be any value to be any positive integer. This is the difference between two contiguous Id value.
- NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value.
- CACHE cache_num specifies the number of integers to keep in memory. NOCACHE specifies no integers are to be stored.
Hopefully with above two mentioned resolutions we can overcome the primary key/ unique constraint violation exception when we are working in a weblogic clustered server environment and the persistence layer is implemented with JPA.
Note: Do not forget to clear your weblogic cache and restarting the server after performing the above steps.
