인프런 커뮤니티 질문&답변

[질문 내용]

안녕하세요. JPA 를 활용하여 개발 중 해결이 안되서 문의 남깁니다.

상황

복수개의 DB 에서 데이터를 읽어와 Main DB 에 넣는 상황

특이사항으로는 Main 에서 @ID 로 사용할 값이 원본 DB에서는 복수개의 데이터가 있는 상황

ex) Main DB 에서는 IP 를 PK 로 사용하려고 Entity 를 만들었고, 각 원본 DB(이기종으로 Cubrid, MS-SQL, Postrgre 등)에서는 IP 가 PK 가 아님

10.10.10.10 데이터가 2~5개 있음

Main DB 에서는 10.10.10.10 을 PK 로 만들어서 1개 row 만 있기를 희망함

질문

(개발환경)

H2 사용

문제없이 JPA 에서 자동으로 마지막 것으로 업데이트 후 1개의 ROW 만 INSERT 됨

NULL인 경우는 자동으로 제거됨

(운영환경)

MySQL 사용

개발환경에서 문제없던 코드가 javax.persistence.OptimisticLockException 예외 발생됨

에러부분 전체

2023-05-24 16:16:53.311 ERROR 33726 --- [ main] o.s.batch.core.step.AbstractStep : Encountered an error executing step powerpackBatchStep in job chunkProcessingJob

javax.persistence.OptimisticLockException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update tba_powerpack_agent set agent_version=?, ccyn=?, ccyndt=?, creation_time=?, dept_name=?, drdt=?, dridno=?, last_scan_time=?, last_update_time=?, updrdt=?, updridno=?, user_id=? where CRDT=? and IP=?

at org.hibernate.internal.ExceptionConverterImpl.wrapStaleStateException(ExceptionConverterImpl.java:238) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:93) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1411) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1394) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.springframework.batch.item.database.JpaItemWriter.write(JpaItemWriter.java:94) ~[spring-batch-infrastructure-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:193) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:159) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:294) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:217) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:77) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:407) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:331) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.3.26.jar:5.3.26]

at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:273) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375) ~[spring-batch-infrastructure-4.3.8.jar:4.3.8]

at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-4.3.8.jar:4.3.8]

at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145) ~[spring-batch-infrastructure-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:258) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:208) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:152) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:413) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:136) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:320) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:149) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) ~[spring-core-5.3.26.jar:5.3.26]

at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:140) ~[spring-batch-core-4.3.8.jar:4.3.8]

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]

at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.26.jar:5.3.26]

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.3.26.jar:5.3.26]

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.26.jar:5.3.26]

at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:128) ~[spring-batch-core-4.3.8.jar:4.3.8]

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.26.jar:5.3.26]

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.26.jar:5.3.26]

at jdk.proxy2/jdk.proxy2.$Proxy119.run(Unknown Source) ~[na:na]

at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.execute(JobLauncherApplicationRunner.java:199) ~[spring-boot-autoconfigure-2.7.10.jar:2.7.10]

at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.executeLocalJobs(JobLauncherApplicationRunner.java:173) ~[spring-boot-autoconfigure-2.7.10.jar:2.7.10]

at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.launchJobFromProperties(JobLauncherApplicationRunner.java:160) ~[spring-boot-autoconfigure-2.7.10.jar:2.7.10]

at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:155) ~[spring-boot-autoconfigure-2.7.10.jar:2.7.10]

at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:150) ~[spring-boot-autoconfigure-2.7.10.jar:2.7.10]

at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:759) ~[spring-boot-2.7.10.jar:2.7.10]

at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:749) ~[spring-boot-2.7.10.jar:2.7.10]

at org.springframework.boot.SpringApplication.run(SpringApplication.java:314) ~[spring-boot-2.7.10.jar:2.7.10]

at org.springframework.boot.SpringApplication.run(SpringApplication.java:1303) ~[spring-boot-2.7.10.jar:2.7.10]

at org.springframework.boot.SpringApplication.run(SpringApplication.java:1292) ~[spring-boot-2.7.10.jar:2.7.10]

at io.kabang.excavator.ExcavatorApplication.main(ExcavatorApplication.java:12) ~[classes/:na]

Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update tba_powerpack_agent set agent_version=?, ccyn=?, ccyndt=?, creation_time=?, dept_name=?, drdt=?, dridno=?, last_scan_time=?, last_update_time=?, updrdt=?, updridno=?, user_id=? where CRDT=? and IP=?

at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:67) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:54) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:47) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3571) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3438) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3870) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:202) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:721) ~[na:na]

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:344) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1407) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]

… 46 common frames omitted