-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Constraint violation due to Sequence generator in Oracle
PostPosted: Wed Sep 21, 2016 5:08 am 
Newbie

Joined: Wed Sep 21, 2016 4:50 am
Posts: 2
Hi Team,

I am using spring data+oracle+MQ in my project. My application is hosted on 2 data centers of cloud foundry. And my application is deployed on both the data centers, and any of the app can pick up the msg from the queue.
I have a table where the primary key is being generated by a sequence.
There is an xml message which is being pushed on to a queue twice, my application picks up that file, parse it and map it to an object and tries to insert it in a db.

Now, problem which i am facing is:


Suppose an X message was pushed twice onto the queue,
1) Datacenter1 picks it up at 9:20:45:789 and datacenter2 picks it at 9:20:45:850.
2) Sample of my entity class:
Code:
   @Id
   @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="share_id_generator")
   @SequenceGenerator(name="share_id_generator", sequenceName="SHARE_ID_SEQ", allocationSize=1)
   private Long ShareId;

   @Column(unique=true)
   private String testID;

   @ManyToOne()
   @JoinColumn(name = "empId")
   @JsonBackReference
   private Emp empId;


3) Since both the data centers have picked up the message at a time difference of milli seconds, both might have generated the same sequence number, and both try to insert in the db.

4) The exception I am getting is:-

Code:
a52a3649-b267-4aa5-b040-e83992119d3d [APP] - - 06:22:58.615 [paasEnv=RTE-NP-Datacenter1 o] [1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1, SQLState: 23000
346 <14>1 2016-09-14T06:22:58.616473+00:00 loggregator a52a3649-b267-4aa5-b040-e83992119d3d [APP] - - 06:22:58.615 [ [1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-00001: unique constraint (TXNSRV.SHARE_PK) violated

353 <14>1 2016-09-14T06:22:58.636602+00:00 loggregator a52a3649-b267-4aa5-b040-e83992119d3d [APP] - - 06:22:58.636 [ [1] INFO  o.h.e.j.b.internal.AbstractBatchImpl - HHH000010: On release of batch it still contained JDBC statements


5) I have a common database which is being used by both the data center. Both are connecting using the same user.

Queries on this:-

1) How can same sequence number is being generated!?
2) If it is throwing a primary key constraint violation exception, that would have meant that one entry of the msg [either from datacenter1 or datacenter2] would have been there in the db, so when it tried to insert again [using the same sequence number] it gave this violation exception---
But this is not the scenario here, i cannot see even a single entry in the db, which means , none of the entry [either from datacenter1 or datacenter2] could insert into the db, as both the msgs have failed on both the data centers with the same violation exception.


Can anyone please help me on this.... :( I am stuck with this issue from a long time, it may affect my prod if I could not find the actual root cause.. Please help!!!


Thanks,


Top
 Profile  
 
 Post subject: Re: Constraint violation due to Sequence generator in Oracle
PostPosted: Wed Sep 21, 2016 5:45 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
This is very odd!

The mapping for the identifier is fine:

Code:
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="share_id_generator")
@SequenceGenerator(name="share_id_generator", sequenceName="SHARE_ID_SEQ", allocationSize=1)
private Long ShareId;


I suggest you enable the SQL-level logs and see what value is used for the entity identifier. Try pasting the table constraints here as well. Maybe the SHARE_PK constraint is related to testId and it's just badly named, who knows?

Are you using multi-master replication on your environment?


Top
 Profile  
 
 Post subject: Re: Constraint violation due to Sequence generator in Oracle
PostPosted: Thu Sep 22, 2016 2:15 am 
Newbie

Joined: Wed Sep 21, 2016 4:50 am
Posts: 2
Oh ok.. Thats a good suggestion.. let me try printing the sql queries , that may help in identifying the culprit sequence numbers


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.