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:

  1. Poll an external system for a list of data items.
  2. For each item, check whether or not it has already been imported into our system by querying an IMPORT_LOG table. If not:
  3. Import the new data item, inserting data into various tables.
  4. 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:

  1. Using SERIALIZABLE transaction isolation.
  2. 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?

  • 1
    I think both are workable (although SERIALIZABLE might work better in Postgres). If you are thinking about Postgres, using advisory locks could also help. – a_horse_with_no_name Mar 4 '14 at 10:25
  • So, with Postgres advisory locks, assuming each data item has a unique URL, I could hash (SHA etc) the URL to a bigint, then have each process do a SELECT pg_try_advisory_xact_lock(urlhash) to avoid overlapping. Does that sound right? – user3364825 Mar 4 '14 at 11:07
  • Why would SERIALIZABLE work better in Postgres than H2? – user3364825 Mar 4 '14 at 11:08
  • The Postgres docs say about advisory locks, "a flag stored in a table could be used for the same purpose" -- do you know how I could emulate pg_try_advisory_lock that way in H2? – user3364825 Mar 4 '14 at 11:09
  • I have no idea if advisory locks could be simulated in H2. The reason why I think SERIALIZABLE works better in Postgres, is that I am not sure that H2 implemented it as efficiently as Postgres: wiki.postgresql.org/wiki/Serializable and drkp.net/papers/ssi-vldb12-slides.pdf – a_horse_with_no_name Mar 4 '14 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.