Where to LOCK on to have atomic get-or-create of entity-row

Question

I want to have an getOrCreateFoobar(id) method that either gets the already existing Foobar by id or creates/inserts a new Foobar with id. This method would be called in a highly concurrent fashion, so I have to serialize/synchronize access on this atomic get-or-create so that not two concurrent requests create the Foobar with the same id twice.

Since the entity may not exist yet, there is no row yet on which I could lock to synchronize. additionally, a table-wide lock seems to be a bad idea either - potential recipe for deadlock-disaster(?)

There is this pattern to create artificial entities like a LockEntity which may have an property like Name. For each UseCase, I provide a row/instance like Name=GET_OR_CREATE_FOOBAR. Which is then used in a repository with a PESSIMISTIC_WRITE lock:

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT e FROM LockEntity e WHERE name = :name")
    LockEntity obtainLock(@Param("name") String name);

which can then be used like:

    obtainLock("GET_OR_CREATE_FOOBAR")
    // now we hold the lock and are synchronized
    get()
    if (doesNotExistYet) create()
    commit / release-lock

Is there a better / more lightweight way?

Answer 1

Since the parameter is id, and since you only expect one value back, it would be safe to assume that id is unique, so create a UNIQUE INDEX on the column (if it isn't already e.g. the Primary Key), and it'll be impossible for two concurrent requests to create the Foobar with the same id twice.

One of them will fail, and it then has to wait long enough for the first one to commit the newly created record, then read it from database.

loop:
   get()
   if found:
      return it
   insert()
   if ok:
      return it
   if failure is not duplicate value error:
      fail
   short wait
end loop