SpringDataJPA笔记(9)-使用transation注解引发的唯一索引冲突的问题

港控/mmm° 2023-07-17 11:15 19阅读 0赞

在业务中使用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新增一条数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpbmd6aWlzbWU_size_16_color_FFFFFF_t_70]

查看数据库，新增成功

![在这里插入图片描述][20200320221152828.png]

再调用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

### 解决这个问题的方法就是再delete语句后面执行一次flush ###

调用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 (?, ?, ?)

查看数据库  
![在这里插入图片描述][20200320221015508.png]

数据确实更新了

欢迎关注微信交流  
![在这里插入图片描述][70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpbmd6aWlzbWU_size_16_color_FFFFFF_t_70]: /images/20230528/54ad9166a8c3404ea4685daf29e6ffd0.png
[20200320221152828.png]: /images/20230528/e39652d2555947e9b6a0f955fae4d44e.png
[20200320221015508.png]: /images/20230528/f083bf3f58234bba81e10fc56bd9919c.png
[70]: /images/20230528/64f343e1218d43c189d2f7836d4d4a43.png