[ShardingSphere + JPA] 之运行环境问题分析实例

运行环境：

spring-boot 2.5.2

shardingsphere 4.1.1

金仓数据库V8R6

分库字段：tenantid

生产环境有一个业务之前一直正常，这几天突然大批量报错：

could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statementorg.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:276)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)


Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:109)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3298)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3825)
	at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)


Caused by: org.postgresql.util.PSQLException: 错误: 重复键违反唯一约束"A_pkey"
  详细：键值"(id)=(xxx)" 已经存在
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$1.executeSQL(SQLExecuteCallbackFactory.java:45)
	at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$1.executeSQL(SQLExecuteCallbackFactory.java:41)
	at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
	at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
	at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97)
	at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.serialExecute(ExecutorEngine.java:82)
	at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76)
	at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68)
	at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:51)
	at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:129)
	at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.executeUpdate(PreparedStatementExecutor.java:123)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.executeUpdate(ShardingPreparedStatement.java:133)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
	... 106 more

从异常字面意义来理解：往A表插入主键为xxx的记录，违反唯一约束。实际查询了数据库，xxx记录在A表中确实存在。

按理说使用JPA的saveAndFlush，我们不需要显式的再去检查记录是否存在，JPA框架会自动插入或更新，不应该出现此问题。

于是选择将该记录备份，然后删除。发现xxx记录确实能正常插入到数据库了，但是后续又出现了yyy、zzz...记录违反唯一约束，所以这样治标不治本，还是得分析根因。

在开发环境未能复现该问题，再者说，程序最近一直没有更新，且之前都是正常运行的，所以问题大概率出现在数据上，于是又把生产环境的xxx这条数据拿过来分析，没想到还真揪出了这个臭虫：

生产环境的xxx记录，它的分库key字段值与当前分库不符合，导致调用saveAndFlush，先根据唯一标识+分库字段值查询记录是否存在时，该记录查不到，所以JPA决定走insert，插入xxx这条记录，但是数据库层面xxx记录又确实存在的，所以抛出了上述异常。

原因分析出来了，得统一排查各分库的A表中是否还有其他类似这样的问题数据，筛选出来之后，统一手动将其分库字段值更新为对应的分库即可，问题解决。