title: Insert row if query condition met JPA and spring
body: <p>I think this question is similar to <a href="https://stackoverflow.com/questions/48116330/data-base-pessimistic-locks-with-spring-data-jpa-hibernate-under-the-hood">Data base pessimistic locks with Spring data JPA (Hibernate under the hood)</a> but thought I would ask separately as not exactly the same.</p>
  
  <p>I have a multi threaded/node springboot application on top of a mariadb database with a table like</p>
  
  <pre><code>CREATE TABLE job (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  owner   VARCHAR(50),
  status  VARCHAR(10) );
  </code></pre>
  
  <p>Have a <code>Job</code> domain class as you'd expect.</p>
  
  <p>Have a <code>JobRepository</code> interface which extends <code>CrudRepository&lt;Job,Integer&gt;</code> and a service class.</p>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>But how to I do this in JPA/CrudRepository.</p>
  
  <p>I split into DB operations. Defined a repository method:</p>
  
  <pre><code>@Lock(LockModeType.READ)
  long countByOwnerAndStatusIn(String owner, List&lt;String&gt; status);
  </code></pre>
  
  <p>And then had a service method like:</p>
  
  <pre><code>@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;
      }
  }
  </code></pre>
  
  <p>But with this I do not get the desired effect.</p>
  
  <p>LockModeType of READ and WRITE allow duplicates to be created.</p>
  
  <p>LockModeType of PESSIMISTIC_READ and PESSIMISTIC_WRITE can result in deadlock errors.</p>
  
  <p>So I guess I am after one of two options:</p>
  
  <ul>
  <li>Is there a way to make get the <code>INSERT...SELECT WHERE NOT EXISTS</code>
  into a JPA/CrudRepository method?</li>
  <li>Is there a way to get the serivce
  method to effectively wrap the count check and the insert in the same
  lock?</li>
  </ul>
  
  <p>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 <code>LOCK TABLE</code> statement (or the insert...select, but I don't like the idea of that, keeping it JPA like is probably better).</p>
  
  <p>Hope I have explained myself properly. Thanks in advance for your help.</p>