What is a good approach for safe concurrent updates in a relational database?

Question

I'm writing a Java app to update a relational database (currently H2). I have a process which does the following:

1. Query an external system for certain data items
2. Check whether or not those items have already been imported into our system by checking an import log table. If not:
3. Import the new data items into various tables.
4. Write a new record into the import log table.

This process might be run concurrently in different threads. I'd like to avoid the problem where two threads might both check the import log, find nothing there, and then both attempt to insert the data items.

What might be a good approach? I've been considering:

1. Using SERIALIZABLE transaction isolation.
2. Relying on a unique index constraint in the import log to error and rollback one of the transactions.
3. Confining the process to a single thread in the Java app.

None of the above seem very appealing, for various reasons -- is there another approach that might work better?

Answer 1

The SERIALIZABLE transaction isolation certainly is the most certain way to achieve your goal but it could mean that performance will suffer.

There is one option you have not considered and that is to build your own semaphore.

You could create a static ConcurrentHashMap of items currently being processed and (at the start of each insert process - put a record and when done delete it.

Then each Thread process could consult this semaphore before starting inserts.