如何使存储过程中的查询知道 Spring 事务? [英] How to make the queries in a stored procedure aware of the Spring Transaction?

问题描述

在对 DB 执行一堆查询后，我从 Spring 事务(标有 @Transactional 的 Spring 服务)调用存储过程.

entityManager.createNativeQuery("调用stored_procedure()");query.executeUpdate();

为了在java代码抛出异常时回滚存储过程的查询(或者java代码中对DB的修改因为存储过程抛出异常回滚)，我已经将mysql服务器的自动提交变量设置为false.这解决了我的部分问题.我现在拥有数据库事务的所有代码.我的问题是存储过程的查询不知道在 java 代码中所做的修改(对数据库).IE.存储过程中的选择读取 Spring 事务启动之前数据库中的值.

我的问题是(标题中所述的问题)如何使存储过程中的查询知道在调用存储过程(从该事务中)之前对 Spring 事务中的数据库所做的修改?

不幸的是，我现在不能将数据库设置为脏读.获取 Spring 在 mysql 上打开的事务的 ID，并在该事务中运行存储过程的查询是否可行?

谢谢

解决方案

原来我需要在实体管理器上调用flush，以便将持久化上下文同步到底层数据库.我还从 SP 中删除了任何事务管理指令，它按预期工作；存储过程中的查询现在是 Spring 管理的事务的一部分.

After I execute a bunch of queries on the DB, I'm calling a stored procedure from a Spring Transaction (a Spring Service marked with @Transactional).

entityManager.createNativeQuery("call stored_procedure()");
query.executeUpdate();

In order to make the queries of the stored procedure rollback when an exception is thrown by the java code (or the modifications of the DB made in the javacode rolled back because of an exception thrown in the stored procedure), I've set the autocommit variable of the mysql server to false. This fixes part of my problem. I now have all the code to the DB transactional. My problem is that the queries of the stored procedure are not aware of the modifications (to the DB) made in the java code. I.e. the selects from the stored procedure reads the values that were in the DB before the Spring transaction started.

My question is (the one stated in the title) How to make the queries in a stored procedure aware of the modifications made to the DB in the Spring Transaction prior to the stored procedure being called (from that transaction)?

Unfortunately setting the DB to dirty read is not an option for me now. Would it be possivle to get the ID of the transaction that Spring opens on mysql, and run the queries of the stored procedure inside that transaction?

Thanks

解决方案

It turned out I need to call flush on the Entity Manager, in order to synchronise the persistence context to the underlying database. Also I removed any transaction management instruction from the SP, and it worked as expected; the queries inside the Stored Procedure are now part of the transaction managed by Spring.

这篇关于如何使存储过程中的查询知道 Spring 事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！