0

So, I use Hibernate, Spring, PostgreSQL and Quartz.

Quartz my have many concurrent jobs in many threads, so each job insert some data to DB and do it in transaction. Each row have serial attribute, which is getting from DB sequence.

I need a strange thing - all rows, inserted in one job (and, so in one transaction) must going sequentially by numbers from sequence (like, "1,2,3" - to first concurrent transaction and only "4,5,6" to second).

I know, that if we have two or more concurrent transactions - they may get numbers mixed...

How can I solve this?

Namelles.One
  • 78
  • 8
  • What is assigning the sequence? If the database assigns it, wouldn't it just assign the sequence in the order it receives the request, which is what you want? – slevin May 24 '14 at 16:43
  • As I know inserts from two concurrent transactions may mixed, so first transaction may assign 1,4,5 and second - 2,3,6. I don't want this. – Namelles.One May 24 '14 at 16:54
  • 1
    You could lock the table to avoid concurrency, but your application will become much slower. But why do you care about these numbers? After a rollback, your sequence won't rollback and you end up with a gab in the numbers. How important is this number, what does it do/mean? – Frank Heikens May 24 '14 at 21:28
  • I think you need to use the [Hi-lo id generation algorithm](http://stackoverflow.com/questions/282099/whats-the-hi-lo-algorithm) – Vlad Mihalcea May 25 '14 at 04:51

1 Answers1

0

You may have to handle the sequence generation programmatically then. On way is to use a sequence table.

CREATE SEQUENCE my_sequence INCREMENT BY 3

Or whatever works for you. Then select that for your key, and within your transaction, increment by one.

Or you can use ThreadLocal to obtain a value that is specific to each thread.

 int seq = 1;

 private static final ThreadLocal<Integer> sequence = new ThreadLocal<Integer>() {
     @Override protected Integer initialValue() {
         return seq+=3;
     }
     @Override protected Integer get() {
         return seq+=3;
     }     
};

Then you can use sequence.get() to obtain the next value for each thread without worrying about whether each thread is getting a different value.

slevin
  • 308
  • 1
  • 8