Each thread should be assigned unique id while inserting

Question

I need to insert into database which has two columns-

ID Primary Key String
Data String

So that means ID should be unique every time , otherwise it will throw a duplicate row in unique index exception while inserting. And I need to choose ID in between this range 1-100000

So that means each thread should be using unique id always-

Below is the multithreaded program I wrote that will insert into database with different unique id's every time after getting from ArrayBlockingQueue.

So this program will be thread safe or not? Or is there any other better way to get the unique ID's every time for each thread? Or the below program can cause duplicate row in unique index ?

private static LinkedList<Integer> availableExistingIds = new LinkedList<Integer>();

public static void main(String[] args) {

        for (int i = 1; i <= 100000; i++) {
            availableExistingIds.add(i);
        }

        BlockingQueue<Integer> pool = new ArrayBlockingQueue<Integer>(200000, false, availableExistingIds);

        ExecutorService service = Executors.newFixedThreadPool(10);

            for (int i = 0; i < noOfTasks * noOfThreads; i++) {
                service.submit(new ThreadTask(pool));
            }
}   

class ThreadTask implements Runnable {

    private BlockingQueue<Integer> pool;
    private int id;

    public ThreadTask(BlockingQueue<Integer> pool) {
        this.pool = pool;
    }

    @Override
    public void run() {

         try {
            dbConnection = getDBConnection();
            preparedStatement = dbConnection.prepareStatement(INSERT_SQL);

            id = pool.take();

            preparedStatement.setString(1, String.valueOf(id));
            preparedStatement.setString(2, ACCOUNT);

            preparedStatement.executeUpdate();

       } finally {
         pool.offer(id);
        }

    }
}

I am using a new Database named XpressMP. Can you explain me by identity what do you mean? — AKIWEB, Feb 06 '13 at 01:38

assylias · Accepted Answer · 2013-02-06T01:46:46.087

2

The pool.offer(id) means you put a used id back in the queue - so it can be reused later on by another thread. That is probably going to be an issue (since the queue is FIFO you will get a duplicate ID on the 100,001st insertion).

In any case, it seems very complicated when a static AtomicInteger would do the same thing without having to use a queue:

class ThreadTask implements Runnable {

    private final AtomicInteger id;

    ThreadTask(AtomicInteger id) {
        this.id = id; //in your main thread: id = new AtomicInteger(minId);
    }

    @Override
    public void run() {
        dbConnection = getDBConnection();
        preparedStatement = dbConnection.prepareStatement(INSERT_SQL);

        preparedStatement.setString(1, String.valueOf(id.getAndIncrement()));
        preparedStatement.setString(2, ACCOUNT);

        preparedStatement.executeUpdate();
    }
}

Note: as commented, your database can probably assign unique IDs for you.

edited Feb 06 '13 at 01:46

answered Feb 06 '13 at 01:33

assylias

321,522
82
660
783

Thanks assylias for the suggestion. So in the example you mentioned above. It will increment the id right everytime starting from 1 right? And what about if I need to get the unique id from a particular range? – AKIWEB Feb 06 '13 at 01:42
@Nevzz03 You can either change the initialisation to `id = new AtomicInteger(lowerBoundOfYourRange);` or have a constructor that takes an AtomicInteger and manage the id range from your main code. – assylias Feb 06 '13 at 01:45
I have updated with an example where the id is passed to the constructor - make sure you use the same for all the tasks. – assylias Feb 06 '13 at 01:47
Thanks assylias. I was trying your first suggestion before the edit you made. And at some point it is also throwing `duplicate row in unique index: ` exception. Can you think of when that is possible? – AKIWEB Feb 06 '13 at 02:14
by construction, an atomicinteger getAndIncrement method is atomic and can't return the same number twice. So the only options I can think of are: (i) you use more than one atomicinteger (ii) you reset the one you use (iii) there already are some entries in your DB. You should add some logging to see what ID is a supposed duplicate and investigate if it is already in your DB or not. – assylias Feb 06 '13 at 02:17
Yeah. I figured out that problem. Now it's working fine for me. Now I am trying to pass the id from the command prompt and I will be using that ID only to start the `Atomic Integer value`. So I can pass that ID in the constructor right? and construct the Atomic Integer? One more quick question, in your current example, you commented out something like this `in your main thread: id = new AtomicInteger(minId);`. I believe by mistake you wrote that right? It should be something like this- `id = new ThreadTask(minId);` right? – AKIWEB Feb 06 '13 at 23:29
No - you create one atomicinteger in your main thread and you pass it to all your threadtasks so they share the same id generator. – assylias Feb 06 '13 at 23:32
That ensures that 2 tasks can't create the same id. – assylias Feb 06 '13 at 23:33
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/24077/discussion-between-nevzz03-and-assylias) – AKIWEB Feb 06 '13 at 23:36

score 1 · Answer 2 · answered Feb 06 '13 at 01:46

An identity column is one to which the database assigns a new unique value on each insert. See here

I don't know this XpressMP database but you should search documentation for an identity or auto-increment type. You can also use a guid type at slightly more cost.

Remember that you will have to start after the last assigned ID when the program restarts. But you still don't need a queue, just a IdProvider class with a synchronized nextId method that increments and returns a private variable that has been initialized to the last assigned id.

Each thread should be assigned unique id while inserting

2 Answers2