0

I've multiples threads consulting a database at the same time in order to verify if an entry already exists. If no: insert a entry. If yes: do nothing.

My PK is serial, and the verification of consulting described before is doing by other 2 fields (operation-number, company-id).

The problem is that at the some times I'm caught in race condition, resulting in duplicated entries.

Please, the only way to avoid that is using a composite PK (serial-id, operation-number, company-id)? Am I right? Any thoughts?

PS: I'm using Java, Hibernate and PostgreSQL.

Thx.

D. Francis
  • 31
  • 1
  • 11
  • 1
    You can use an [`UPSERT`](http://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql) at query level to avoid race conditions completely. But I highly doubt that hibernate supports it. You *may* write a stored procedure for this & execute it with native query. – pozs Mar 27 '17 at 12:04
  • 2
    Alternative: use isolation level `SERIALIZABLE`. – Laurenz Albe Mar 27 '17 at 12:12
  • But using UPSERT I still need to lock the table in order to avoid race condition? http://www.the-art-of-web.com/sql/upsert/ – D. Francis Mar 27 '17 at 13:10
  • 1
    @D.Francis no, PostgreSQL's [`ON CONFLICT UPDATE` (a.k.a. `UPSERT`)](https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29) does not need any special lock. It will use predicate locking under the hood. – pozs Mar 27 '17 at 14:05
  • 1
    Add a unique constraint for combination of columns `operation-number` and `company-id`. Then try to insert and catch a duplicate key exception. – Dragan Bozanovic Mar 27 '17 at 14:08

0 Answers0