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,
Login
FAQ
Search
