尝试将重复记录插入数据库时出现java sql异常

Question

我使用下面的方法检查用户是否存在，如果不存在，则将其添加到数据库中

@Transactional(isolation = Isolation.READ_COMMITTED)
public Optional<User> create(User user) {
Optional<User> userOptional = userRepository.findByEmail(user.getEmail());
if (userOptional.isPresent()) {
    return Optional.empty();
}
User savedUser = userRepository.save(user);
return Optional.of(savedUser);

}

两个线程t1和t2试图用相同的电子邮件保存同一用户(example@example.com)。出现以下情况：

t1找不到用户

t2也找不到用户

t1将用户添加到数据库中

t2尝试将用户添加到数据库，但失败，因为记录已存在，并引发以下错误：

2019-10-02 18:28:43  WARN UKPC000029 --- [nio-8090-exec-2] 
o.h.e.j.s.SqlExceptionHelper             : SQL Error: 1062, SQLState: 23000 
2019-10-02 18:28:43 ERROR UKPC000029 --- [nio-8090-exec-2] 
o.h.e.j.s.SqlExceptionHelper             : Duplicate entry 'example@example.com' for key 'UK_tcks72p02h4dp13cbhxne17ad' 
2019-10-02 18:28:43 ERROR UKPC000029 --- [nio-8090-exec-2] o.h.i.ExceptionMapperStandardImpl        : HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement] 
2019-10-02 18:28:43 DEBUG UKPC000029 --- [nio-8090-exec-2] o.s.o.j.JpaTransactionManager            : Initiating transaction rollback after commit exception    
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [UK_tcks72p02h4dp13cbhxne17ad]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement   
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:296)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:536)

为了避免这个错误，我尝试使用可序列化隔离级别 @事务性（隔离=隔离。可序列化）

但是，在相同的场景中，我得到了以下错误：

2019-10-02 18:52:56  WARN UKPC000029 --- [nio-8090-exec-2] o.h.e.j.s.SqlExceptionHelper             : SQL Error: 1213, SQLState: 40001  
2019-10-02 18:52:56 ERROR UKPC000029 --- [nio-8090-exec-2] o.h.e.j.s.SqlExceptionHelper             : Deadlock found when trying to get lock; try restarting transaction    
2019-10-02 18:52:56 ERROR UKPC000029 --- [nio-8090-exec-2] o.h.i.ExceptionMapperStandardImpl        : HHH000346: Error during managed flush [org.hibernate.exception.LockAcquisitionException: could not execute statement] 
2019-10-02 18:52:56 DEBUG UKPC000029 --- [nio-8090-exec-1] o.s.o.j.JpaTransactionManager            : Not closing pre-bound JPA EntityManager after transaction 
2019-10-02 18:52:56 DEBUG UKPC000029 --- [nio-8090-exec-2] o.s.o.j.JpaTransactionManager            : Initiating transaction rollback after commit exception    
org.springframework.dao.CannotAcquireLockException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute statement   
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:287)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:536)

我的印象是，两项交易都会一项接一项地进行

以下链接

isolation explained

可序列化隔离级别是所有隔离级别中限制最严格的。事务在所有级别（读、范围和写锁定）都使用锁定执行，因此它们看起来就像是以序列化方式执行的。这导致了这样一种情况，即上面提到的任何问题都不会发生，但从另一方面来说，我们不允许事务并发，因此会带来性能损失

但是，即使第一个事务尚未完成，两个线程仍在检查用户电子邮件？处理这个问题的最好办法是什么？在这种情况下使用java锁是否可以接受