Insert row if query condition met JPA and spring

Question

I think this question is similar to Data base pessimistic locks with Spring data JPA (Hibernate under the hood) but thought I would ask separately as not exactly the same.

I have a multi threaded/node springboot application on top of a mariadb database with a table like

CREATE TABLE job (
id      INT PRIMARY KEY AUTO_INCREMENT,
owner   VARCHAR(50),
status  VARCHAR(10) );

Have a Job domain class as you'd expect.

Have a JobRepository interface which extends CrudRepository<Job,Integer> and a service class.

The application rule is we cannot insert a new job if same owner and set of status values. e.g. If this was old school native sql I would just:

START TRANSACTION;
INSERT INTO job (owner, status)
SELECT 'fred', 'init' FROM DUAL
WHERE NOT EXISTS
(   SELECT 1 FROM job
    WHERE owner = 'fred' AND status IN ('init', 'running')
);
COMMIT;

But how to I do this in JPA/CrudRepository.

I split into DB operations. Defined a repository method:

@Lock(LockModeType.READ)
long countByOwnerAndStatusIn(String owner, List<String> status);

And then had a service method like:

@Transactional
public Job createJob(Job job) {
    if (jobRepository.countByOwnerAndStatusIn(job.getOwner(), Job.PROGRESS_STATUS) == 0) {
        // Sleeps just to ensure conflicts
        Thread.sleep(1000);
        Job newJob = jobRepository.save(job);
        Thread.sleep(1000);
        return newJob
    }
    else {
        return null;
    }
}

But with this I do not get the desired effect.

LockModeType of READ and WRITE allow duplicates to be created.

LockModeType of PESSIMISTIC_READ and PESSIMISTIC_WRITE can result in deadlock errors.

So I guess I am after one of two options:

• Is there a way to make get the INSERT...SELECT WHERE NOT EXISTS into a JPA/CrudRepository method?
• Is there a way to get the serivce method to effectively wrap the count check and the insert in the same lock?

If there is no way to do either I guess I'll try and get access to the underlying jdbc connection and explicity run a LOCK TABLE statement (or the insert...select, but I don't like the idea of that, keeping it JPA like is probably better).

Hope I have explained myself properly. Thanks in advance for your help.