SpringDataJPA笔记(9)-使用transation注解引发的唯一索引冲突的问题
在业务中使用JPA的时候,因为业务逻辑比较复杂,使用了@Transactional 注解来确保事务一致性
在实际使用的时候却遇到了问题,最终发现 Hibernate 在实际执行SQL语句时并没有按照代码的顺序执行,而是按照 INSERT, UPDATE, DELETE的顺序执行的
原因是Hibernate 为了性能优化,不会直接将SQL语句提交给数据库,而是先放在缓存中,等commit的时候一起提交,利用batch操作提高数据库的性能,而这就导致了SQL的执行顺序与实际的代码顺序不一致,直接导致了代码抛出异常
复现错误过程如下
首先需要几个关键类
实体
@Data
@Entity
@Table(name = "nine_tb", uniqueConstraints = {
@UniqueConstraint(name = "name", columnNames={"firstName", "lastName"})
})
public class NineEntity implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String firstName;
private String lastName;
private String remarks;
}
Repository
public interface NineRepository extends BaseRepository<NineEntity, Long>{
}
Service
@Slf4j
@Service
public class NineService {
@Autowired
private NineRepository nineRepository;
public void add(NineEntity nineEntity) {
nineRepository.save(nineEntity);
}
private List<NineEntity> setNewList(List<NineEntity> list) {
String remarks = UUID.randomUUID().toString();
log.info("remarks: {}", remarks);
return list.stream().map(nineEntity -> {
NineEntity newEntity = new NineEntity();
newEntity.setFirstName(nineEntity.getFirstName());
newEntity.setLastName(nineEntity.getLastName());
newEntity.setRemarks(remarks);
return newEntity;
}).collect(Collectors.toList());
}
@Transactional
public void update() {
List<NineEntity> list = nineRepository.findAll();
List<NineEntity> newList = setNewList(list);
nineRepository.deleteAll(list);
nineRepository.saveAll(newList);
}
@Transactional
public void update1() {
List<NineEntity> list = nineRepository.findAll();
List<NineEntity> newList = setNewList(list);
nineRepository.deleteAll(list);
nineRepository.flush();
nineRepository.saveAll(newList);
}
}
Controller
@Slf4j
@RestController
@RequestMapping("/chapter/nine")
public class ChapterNineController {
@Autowired
private NineService nineService;
@PostMapping("/add")
public void add(@RequestBody NineEntity nineEntity) {
nineService.add(nineEntity);
}
@GetMapping("/error")
public void error() {
nineService.update();
}
@GetMapping("/right")
private void right() {
nineService.update1();
}
}
首先调用add新增一条数据
查看数据库,新增成功
再调用error接口对数据进行update操作,则会产生报错
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'kobe-bryant' for key 'name'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1109) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1057) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1377) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1042) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.2.0.jar:na]
查看日志 SQL执行顺序
首先执行select语句
Hibernate: select nineentity0_.id as id1_2_, nineentity0_.first_name as first_na2_2_, nineentity0_.last_name as last_nam3_2_, nineentity0_.remarks as remarks4_2_ from nine_tb nineentity0_
再执行了insert语句
Hibernate: insert into nine_tb (first_name, last_name, remarks) values (?, ?, ?)
然后insert报了冲突
而代码中应该是先执行select,然后执行delete,最后才是insert
调用right接口来验证这个问题
查看调用right接口的日志
可以看到,先执行select,然后执行delete,最后才是insert,没有报错
Hibernate: select nineentity0_.id as id1_2_, nineentity0_.first_name as first_na2_2_, nineentity0_.last_name as last_nam3_2_, nineentity0_.remarks as remarks4_2_ from nine_tb nineentity0_
Hibernate: delete from nine_tb where id=?
Hibernate: insert into nine_tb (first_name, last_name, remarks) values (?, ?, ?)
查看数据库
数据确实更新了