PostgreSQl correct lock on creation of new entity

Question

I have two concurrent transactions which check if appropriate PostgreSQL table record exists and if no - try to insert a new one.

I have the following Spring Data repository method:

@Lock(LockModeType.PESSIMISTIC_WRITE)
TaskApplication findByUserAndTask(User user, Task task);

As you may see I have added @Lock(LockModeType.PESSIMISTIC_WRITE) there. Inside of my service method I check if entity exists and if no, create a new one:

@Transactional
public TaskApplication createIfNotExists(User user, Task task) {
    TaskApplication taskApplication = taskApplicationRepository.findByUserAndTask(user, task);
    if (taskApplication == null) {
        taskApplication = create(user, task);
    }
}

I also added unique constraint on tasks_applications (user_id, task_id) fields.

ALTER TABLE public.task_applications 
ADD CONSTRAINT "task_applications-user_id_task_id_unique" 
UNIQUE (user_id, task_id)

automatically created corresponding unique index:

CREATE UNIQUE INDEX "task_applications-user_id_task_id_unique" 
ON public.task_applications 
USING btree (user_id, task_id)

Unfortunately in case of two concurrent transactions with same user_id and task_id, the second one always fail with the following exception:

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "task_applications-user_id_task_id_unique"
  Key (user_id, task_id)=(1, 1) already exists.

What am I doing wrong and how to fix it in order to be able to handle such situation in my service method?

UPDATED

I don't understand why the following method doesn't block the execution of second transaction until first transaction will be committed or rolled back:

TaskApplication taskApplication = taskApplicationRepository.findByUserAndTask(user, task);

I'm not sure about PostgreSQL, but typically it should block the execution based on unique index in case of no record.

How to achieve it?

UPDATED 2

The sequence of SQL commands generated during execution:

select * from task_applications taskapplic0_ where taskapplic0_.user_id=? and taskapplic0_.task_id=? for update of taskapplic0_
insert into task_applications values (?,...)

Answer 1

@Lock(LockModeType.PESSIMISTIC_WRITE)
TaskApplication findByUserAndTask(User user, Task task);

will obtain pessimitic lock only on entites returned by the query (row-level lock). In situation when result set is empty, no lock is being obtained and findByUserAndTask doesn't block transaction.

There are few ways how you could handle concurrent inserts:

1. Use unique index to prevent adding duplicates and handle exception proper to your application needs
2. If your database supports it, obtain table-level lock on the table where you want to insert data. JPA doesn't support it.
3. Emulate table-level lock using row-level lock on new entity & table dedicated for storing locks. This new table should have a row per each table on which you want to obtain pessimistic lock on insert

    public enum EntityType {
        TASK_APPLICATION
    } 

    @Getter
    @Entity
    public class TableLock {
        @Id
        private Long id

        @Enumerated(String)
        private EntityType entityType;
    }

    public interface EntityTypeRepository extends Repository<TableLock, Long> {
        
        @Lock(LockModeType.PESSIMISTIC_WRITE)
        TableLock findByEntityType(EntityType entityType);
    }

Having such setup you just need to obtain lock:

@Transactional
public TaskApplication createIfNotExists(User user, Task task) {
    TaskApplication taskApplication = taskApplicationRepository.findByUserAndTask(user, task);

    if (taskApplication == null) {
        findByEntityType(EntityType.TASK_APPLICATION);
        taskApplication = taskApplicationRepository.findByUserAndTask(user, task);
        
        if (taskApplication == null) {
            taskApplication = create(user, task);
        }
    }

    return taskApplication;
}

For most cases first approach (unique index) is the best and most efficient. Obtaining table lock (native / emualated) is heavy and should be used with care.

I'm not sure about PostgreSQL, but typically it should block the execution based on unique index in case of no record.

Presence of indices does not affect if select / insert statement are blocking or not. Such behaviour is controlled by pessimistic locks.