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

yingzi_code

发布于 2020-03-24 11:08:55

1.5K0

发布于 2020-03-24 11:08:55

文章被收录于专栏：生活不止眼前的代码生活不止眼前的代码

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

解决这个问题的方法就是再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 (?, ?, ?)

查看数据库