0

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.

DeepInJava
  • 1,871
  • 3
  • 16
  • 31
  • 2
    Which DB are you using ? any major DB support sequencing (either upon insert or by trigger etc) - you shouldn't implement it yourself! – Nir Alfasi Oct 16 '14 at 06:58
  • We are using Oracle 11g. I know it's bad approach to implement sequence by ourselves, but this is an existing code which i need to fix. – DeepInJava Oct 16 '14 at 07:00
  • 1
    Use a database sequence, or at least use a pessimistic lock when reading the sequence (select for update). – JB Nizet Oct 16 '14 at 07:01
  • 1
    Can't you implement an oracle sequence and replace the code to which retrieves the sequence to call that? You obviously have DB connectivity already. Your record primary keys do not need to be contiguous after all. You can have skipped values on them, so long as they are unique, and you would solve 2 issues. 1. poor implementation, 2. your concurrency problem :) – rurouni88 Oct 16 '14 at 07:03
  • @rurouni88 - i am not using this sequence to generate oracle sequence for some table. i am using this sequence to generate file name which is processed with help of this sequence no. File with lower sequence process first (from 1,2 .... N). By the way thanks a lot suggesting. – DeepInJava Oct 16 '14 at 07:10
  • 1
    Hmm, strange, but noted. If you're flat determined to go down this path, one technique you could use is Optimistic Concurrency Control. So you only update the sequence if it matches the sequence you're expecting, or you can achieve this by storing timestamps on when the sequence was last updated and doing a similar check. If they match, commit your changes, else thrown an exception, go back to your application and get it to retrieve a new sequence and re-attempt. – rurouni88 Oct 16 '14 at 07:35

1 Answers1

1

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, ...)

xpa1492
  • 1,953
  • 1
  • 10
  • 19