The problem background

  • Unique_format_content_language (format_content_id, LANGUage_code) is a federated index for an old table to ensure data uniqueness during development. After adding a unique index, there is an MQ linkage business scenario where consumption keeps reporting errors:
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [unique_format_content_language] ...
Copy the code
  • The basic logic for MQ consumption in this business scenario (the following steps are in a transaction) :
    • MQ to receive changes to data
    • Incremental translation data
    • Delete old translations
    • Save the new translation
  • The DAO uses spring-data-jPA as follows:
@Repository
public interface KnowledgeContentTranslationRepository extends JpaRepository<ContentTranslationItem.String>{}Copy the code

Problem locating process and solution

  • Try to use debug mode, hit a breakpoint in the error area, from the data there is no problem.
    • KnowledgeContentTranslationRepository. Delete (deleteContentTranslationItems) deleted resources of translation
    • KnowledgeContentTranslationRepository. Save (addContentTranslationItems) added translation of resources

  • However, with hibernate SQL logs, you can see that the INSERT statement is executed first. The DELETE method is not executed before the INSERT statement is executed

  • So you can assume that there are some features of the JPA framework that are responsible
  • Search for the corresponding problem on Github by Google keyword searchissue
    • Many people in this issue also encountered the problem of unique index error when delete then Save
  • By checking the discussion record of this issue, you can find the corresponding two solutions (both of which are verified to be feasible)
    • Use deleteInBatch instead of DELETE
    • After delete, call the flush method manually

conclusion

  • Spring-data-jpa does not execute delete statements first, but inserts statements directly when a transaction commits when the delete method is called first and then the save method is called
    • In this case, a unique index conflict is possible if the table has a unique index. Exception information is as follows:
    org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [unique_format_content_language] ...
Copy the code
  • For delete then Save, where a unique index error occurs, consider the following two scenarios to avoid the error
    • Use deleteInBatch instead of DELETE
      • Recommend it. Delete from [table_name] where id=? or id=? . SQL to execute. When deleting data is too large, there might be a Java. Lang. StackOverflowError anomalies
    • After delete, call the flush method manually
      • Not really. Because the flush method synchronizes all data prior to the method to DB, there may be performance issues

legacy

  • Spring-data-jpa delete then save Why is JPA designed this way?