CODEWITHSUNDEEP
javamultithreadingjdbc
user1251589
user1251589

Reputation: 21

JBDC - execute SELECT and INSERT atomically across concurrent threads

I have searched the web for simple examples to this but to no avail. I need to run a select and insert operation as an atomic unit in Java, using JDBC against an Oracle database.

Effectively I need to do the following:

  1. Select code from users
  2. Go through all codes until I find one that is not used (as users can be deleted there may be codes available in the middle of the range)
  3. Insert new user with that available code

This is a simple operation normally, but as my application is multi-threaded I'm not sure how to go about this. As concurrent threads running at the same time could both try and insert using the same value for code.

There are a couple workarounds or hacks that I can think of to do the job but in general how can I lock the table to make this operation atomic? Most of what I've seen involves row locks but as I'm not updating I don't see how this applies.

Upvotes: 2

Views: 1380

Answers (3)

beny23
beny23

Reputation: 35018

How about:

insert into usertable (
  id, 
  code, 
  name
) values (
  user_id_sequence.nextval,
  (
      select min(newcode) 
        from usertable, (
           select level newcode 
             from dual 
          connect by level <= (select max(code)+1 from usertable))
       where not exists (select 1 from usertable where code = newcode)
  ),
  'mynewusername'
)

EDIT:

changed to max(code) + 1, so if there is no gap available, there is a new code available.

Upvotes: 1

Glenn
Glenn

Reputation: 9150

I would lean toward putting the logic in a stored procedure. Use "select for update" to lock, then commit to unlock.

You can add a filter to your insert statement and retry logic on the client end, I guess:

  • determine an available code (proposed code)
  • perform the insert with a filter determine the number of rows from the executeUpdate result (0 means a concurrent thread grabbed this code, try again)

The insert would look something along these lines where 3 is your new id, 'Joe' your new user, and proposedCode the one you think is available: 

INSERT INTO users
  SELECT 3, :proposedCode, 'Joe' 
    FROM dual
    WHERE :proposedCode NOT IN (SELECT code FROM users)

Upvotes: 1

Gray
Gray

Reputation: 116878

This is a tough problem to do entirely in SQL. Any solution is going to have race condition problems. If I was going to do it entirely in SQL I'd use a deleted code table. When users then get deleted you'd use some service to add their code to the deleted table. If the deleted code table is empty threads would use a sequence number to get their new code. Getting a code from the deleted would need to be in a synchronized block because of the get and then set nature with multiple SQL operations. I don't think SQL transactions are going to help there. They may keep the data consistent but if two threads use the same code then one of the two commits is going to throw an exception.

I think a better, and faster, mechanism would be to have a separate thread manage these deleted codes. It could write it in a database but also keep a BlockingQueue of deleted codes for the other threads to consume. If there must be no holes and you are worried about crashing then it will need to validate the list of available holes by querying the user table at launch. It would not need to synchronize or do any SQL transactions because only it would be deleting from the deleted code table.

Hope this helps.

Upvotes: 1

Related Questions