Проблема вставки / обновления параллельной загрузки Spring Boot EntityManager
У меня есть таблица ниже,
-------------------------------------------------------
root_case_id | wf_param_name |wf_param_value | version |
-------------------------------------------------------
123 |test1 |value1 |1 |
-------------------------------------------------------
123 |test2 |value2 |1 |
-------------------------------------------------------
Весь код проекта в gitHub
Я запускаю несколько потоков одновременно, чтобы вставить или обновить.
Мне нужно, если wf_param уже существует, затем обновить значение, если нет, то создать новое. Из-за параллелизма я получаю ниже:
org.hibernate.exception.ConstraintViolationException.
Теперь вопрос в том, когда поток 1 и поток 2 входят в поток. и T1, и T2 запрашивают в БД проверку существующего объекта. оба не получат никакой сущности, поскольку БД не имеет никакой записи. Но T1 входит и вставляется в БД и сбрасывается как приведенный ниже код
/**
*
* @author
*/
@Controller
@RequestMapping("/oneDispatcher/workflowParam")
public class MainController {
@Autowired
private OdsWorkflowParamService odsWorkflowParamService;
@PersistenceContext
private EntityManager entityManager;
/**
* @param request
* @author Newt
*/
@RequestMapping(value = "/createOrUpdate",method = RequestMethod.POST,
consumes = MediaType.APPLICATION_JSON_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
@ApiOperation(value = "Create or Update WF Param", notes = "Create or Update WF Param")
@ApiResponses(value = {
@ApiResponse(code = 200, message = "Successfully Added or updated WF Param"),
@ApiResponse(code = 400, message = "Invalid input provided"),
@ApiResponse(code = 404, message = "Workflow Param does not exist") })
@ResponseBody
@Transactional
public void createOrUpdate(@RequestBody WfParamRequestDTO request) {
if (!CollectionUtils.isEmpty(request.getWorkflowParams())) {
for (WorkflowParam param : request.getWorkflowParams()) {
try {
updateWorkflowParam(Integer.valueOf(request.getRootCaseId()), param.getParamName(), param.getParamValue());
} catch (Exception e) {
System.out.println("BOOM BOOM #### CONTROLLER "+e.getMessage());
}
}
}
}
/**
* @param rootCaseId
* @param paramName
* @param paramValue
* @return OdsWorkflowParams
*/
@RequestMapping(value = "/updateWorkflowParam",method = RequestMethod.POST, consumes = MediaType.APPLICATION_JSON_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
@ResponseBody
public void updateWorkflowParam(Integer rootCaseId , String paramName,String paramValue) {
odsWorkflowParamService.createOrUpdate(rootCaseId, paramName, paramValue);
}
}
@Service
public class OdsWorkflowParamService {
@Autowired
OdsWorkflowParamDao dao;
@PersistenceContext
private EntityManager entityManager;
@Transactional(propagation=Propagation.REQUIRES_NEW,isolation=Isolation.READ_UNCOMMITTED)
public void createOrUpdate(Integer rootCaseId,String paramName,String paramValue){
OdsWorkflowParams dbObject = null;
OdsWorkflowParams newObj = null;
System.out.println("Root caseId:::"+rootCaseId);
OdsWorkflowParamsId pk = new OdsWorkflowParamsId(rootCaseId,paramName);
dbObject= entityManager.find(OdsWorkflowParams.class,pk);
System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$FOUND$$$$$$$$$$$$$$$$$$$$$$"+dbObject);
try{
if (dbObject != null) {
dbObject.setWfParamValue(paramValue);
}else{
newObj = new OdsWorkflowParams(pk, paramValue);
entityManager.persist(newObj);
entityManager.flush();
}
entityManager.close();
}catch (Exception e) {
dbObject= entityManager.find(OdsWorkflowParams.class,pk);
if (dbObject != null) {
System.out.println("INSERTED RECORD FOUND");
dbObject.setWfParamValue(paramValue);
}
System.out.println("BOOM BOOM #### SERVICE "+e.getMessage());
}
}
}
Here when in the catch block I can see its getting the inserted record for Thread T2 and trying to update too.
But same time I get both exception and a Rollback too. also I see in the DB no increment in version column.
How to avoid the exception and make sure version is updated. When I add synchronized block for the createOrUpdate method it works perfectly fine. I just dont want to do that. What other solution we have? any reference code base to achieve the same?
LOGS::::::::
-------------------
Hibernate: select odsworkflo0_.root_case_id as root_cas1_0_0_, odsworkflo0_.wf_param_name as wf_param2_0_0_, odsworkflo0_.version as version3_0_0_, odsworkflo0_.wf_param_value as wf_param4_0_0_ from tbl_wf_decision_params odsworkflo0_ where odsworkflo0_.root_case_id=? and odsworkflo0_.wf_param_name=?
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$FOUND$$$$$$$$$$$$$$$$$$$$$$null
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$FOUND$$$$$$$$$$$$$$$$$$$$$$null
Hibernate: insert into tbl_wf_decision_params (version, wf_param_value, root_case_id, wf_param_name) values (?, ?, ?, ?)
Hibernate: insert into tbl_wf_decision_params (version, wf_param_value, root_case_id, wf_param_name) values (?, ?, ?, ?)
2017-12-22 10:53:08.847 WARN 4992 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1062, SQLState: 23000
2017-12-22 10:53:08.847 ERROR 4992 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : Duplicate entry '5295-param1' for key 'PRIMARY'
2017-12-22 10:53:08.848 INFO 4992 --- [nio-8080-exec-2] o.h.e.j.b.internal.AbstractBatchImpl : HHH000010: On release of batch it still contained JDBC statements
2017-12-22 10:53:08.850 WARN 4992 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Warning Code: 1062, SQLState: 23000
2017-12-22 10:53:08.850 WARN 4992 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : Duplicate entry '5295-param1' for key 'PRIMARY'
INSERTED RECORD FOUND
BOOM BOOM #### SERVICE org.hibernate.exception.ConstraintViolationException: could not execute statement
BOOM BOOM #### CONTROLLER Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction marked as rollbackOnly
Root caseId:::5295
Hibernate: select odsworkflo0_.root_case_id as root_cas1_0_0_, odsworkflo0_.wf_param_name as wf_param2_0_0_, odsworkflo0_.version as version3_0_0_, odsworkflo0_.wf_param_value as wf_param4_0_0_ from tbl_wf_decision_params odsworkflo0_ where odsworkflo0_.root_case_id=? and odsworkflo0_.wf_param_name=?
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$FOUND$$$$$$$$$$$$$$$$$$$$$$null
Hibernate: insert into tbl_wf_decision_params (version, wf_param_value, root_case_id, wf_param_name) values (?, ?, ?, ?)
[enter image description here][1]
[1]: https://stackru.com/images/33335b9e34c1f50e6b8e883b8af435ba30008bfe.jpg