CODEWITHSUNDEEP
javaconcurrencytransactionsjvmoptimistic-locking
DeepInJava
DeepInJava

Reputation: 1961

Concurrent transaction issue on clustered environment

I have came across one scenario in web application which runs on clustered environment.

We have 2 nodes(both has JVM)

  1. Node1
  2. Node2

I am running one database transaction in my application where sequence_no is read and later we increment it with plus 1 like 

select sequence from some_table;

nextsequence = sequence + 1; // incrementing sequence 

update some_table set sequence = nextsequence;  // set the next sequence

Now what happened request goes to Node1 and it increments the sequence no. but was commited slowly to database(takes 1 minute) due to outofmemoryerror on node1. meanwhile another request is gone to Node2 and take sequence no. from database and update it. So both the request got same sequence which we don't wont as we want unique sequence no. for all the requests.

We cannot synchronize transactions as it will not help because it runs on different JVM.

I am wondering what to do next ? Any help is greatly appriciated.

Upvotes: 0

Views: 658

Answers (1)

xpa1492
xpa1492

Reputation: 1973

If you cannot control this within the database as many of the comments suggest, your next option is to implement some form of simple versioning.

One approach is to just change your update statement to:

update some_table set sequence = nextsequence where sequence = [sequence you just read];

and then see how many record were updated (PreparedStatement's executeUpdate() will give you this value for example). If the result is 0, then another instance of your process beat you to it, so you handle it somehow (throw and error, select another sequence number, ...)

Upvotes: 1

Related Questions