title: Spring data - "ERROR: current transaction is aborted": how to rollback/commit transaction when DataIntegrityViolationException is catched?
body: <p>If I have some instances to insert or update in DB, like:</p>
  
  <pre><code>@Data
  @NoArgsConstructor
  @AllArgsConstructor
  @Entity
  @EqualsAndHashCode
  @Table(name="user_purchases")
  public class UserPurchase implements Serializable, Persistable&lt;String&gt; {
      @Id
      @Column(name="id")
      @NotNull
      private String id; // an UUID
  
      @Column(name="user_id")
      @NotNull
      private String userId; // in user_info: "sub"
  
      /**
       * Seconds since epoch: when the last purchase happened.
       */
      @Column(name="last_date")
      private Date lastBuyDate;
  
      @Transient
      private boolean isNewObject;
  
  
      // Add UUID before persisting
      @PrePersist
      public void prepareForInsert() {
          this.id = UUID.randomUUID().toString();
      }
  
  
      @Override
      public boolean isNew() {
          return isNewObject;
      }
  
      // This part for Persistable is not required, because getId(), by accident,
      // is the getter for the "id" field and returns a String.
      //@Override
      //public getId() {
      //    return id;
      //}
  }
  </code></pre>
  
  <p>We know that <code>id</code> is surrogate id and will be generated before persisting. And <code>userId</code> is unique in DB.</p>
  
  <p>To know more about the interface <code>Persistable&lt;ID&gt;</code>, check <a href="https://stackoverflow.com/questions/40605834/spring-jparepositroy-save-does-not-appear-to-throw-exception-on-duplicate-save/40608937#answer-45732869">this answer</a>.</p>
  
  <p>Now, when we have an instance without id, the <code>userId</code> could or not be duplicated in DB, and there is no way to tell if we are persisting or updating in DB. </p>
  
  <p>I want to save a full-table scanning before <em>every</em> persisting/updating, so I am trying to catch <code>DateIntegrationViolationException</code> after the first try of <code>repository.save(entity)</code>.</p>
  
  <pre><code>@Transactional(rollbackFor = DataIntegrityViolationException.class)
  public UserPurchase saveUserPurchase(UserPurchase purchase) throws RuntimeException {
      UserPurchase saved = null;
      try {
          saved = repository.saveAndFlush(purchase);
          log.debug("UserPurchase was saved/updated with id {}, last buy time: {}", saved.getId(),
                  DateTimeUtil.formatDateWithMilliPart(saved.getLastBuyDate(), false));
      } catch (DataIntegrityViolationException e) {
          log.info("Cannot save due to duplication. Rolling back..."); // we don't distinguish userId and id duplication here.
          UserPurchase oldPurchase = repository.findByUserId(purchase.getUserId());  // &lt;--------- here we cannot proceed
          if (oldPurchase != null) {
              purchase.setId(oldPurchase.getId()); // set the existent ID to do updating
              purchase.setNewObject(false);  // for Persistable&lt;String&gt;
              saved = repository.saveAndFlush(purchase); // now should be updating
  
          } else {
              log.error("Cannot find ID by user id");
          }
      }
      return saved;
  }
  </code></pre>
  
  <p>This gives me the error of:</p>
  
  <pre><code>ERROR: current transaction is aborted, commands ignored until end of transaction 
  </code></pre>
  
  <p>Because I am doing two things in one transaction, where the transaction shoule be rolled back. </p>
  
  <p>OK, so I throw the exception, and tried to do the operation of updating outside(because Spring shall rollback automatically when it sees an exception is thrown, or so I read):</p>
  
  <pre><code>@Transactional(rollbackFor = DataIntegrityViolationException.class)
  public UserPurchase saveUserPurchase(UserPurchase purchase) throws RuntimeException {
      UserPurchase saved = null;
      try {
          saved = repository.saveAndFlush(purchase);
          log.debug("UserPurchase was saved/updated with id {}, last buy time: {}", saved.getId(),
                  DateTimeUtil.formatDateWithMilliPart(saved.getLastBuyDate(), false));
      } catch (DataIntegrityViolationException e) {
          log.info("Cannot save due to duplication. Rolling back..."); // we don't distinguish userId and id duplication here.
          throw e; // or throw new RuntimeException(e); is the same
      }
      return saved;
  }
  </code></pre>
  
  <p>At where I call <code>save()</code>:</p>
  
  <pre><code>try {
      userPurchaseService.saveUserPurchase(purchase);
  } catch (DataIntegrityViolationException e) {
      log.info("Transaction rolled back, updating...");
      // ... 1. select 2. getId() 3.setId() and save again
  }
  </code></pre>
  
  <p>But, it fails again.</p>
  
  <p>Now, with Spring Data, we have no <code>EntityManager</code> to <code>rollback()</code>. </p>
  
  <p>What to do now? Must I do a manual <code>findByUserId()</code> before every insert/update? My approach of lazy-selection wouldn't work under any circumstance?</p>