I'm writing a Java application to update a relational database (currently H2, but we'll likely switch to PostgreSQL at some point). I have a process which does the following:
- Poll an external system for a list of data items.
- For each item, check whether or not it has already been imported into our system by querying an IMPORT_LOG table. If not:
- Import the new data item, inserting data into various tables.
- Insert a new record into the IMPORT_LOG table.
This process might be run concurrently, either in different threads or scaled out over multiple machines.
I'd like to avoid the concurrency hazard where two threads might both start to import the same data item, check IMPORT_LOG, find nothing there, and then both insert the data items.
What might be a good approach? I've been considering:
- Using SERIALIZABLE transaction isolation.
- Relying on a UNIQUE constraint in IMPORT_LOG to error and rollback one of the transactions.
Are either of those workable? Is there something else that might work better?