Spring Boot手动提交事务 [英] Spring boot manually commit transaction

问题描述

在我的Spring启动应用程序中，我要通过一次事务删除大量数据并将其插入到MySQL数据库中.理想情况下，我只想最后将结果提交到数据库中，所以全部或全部不提交.我遇到的问题是，在插入之前将执行删除操作，因此在此期间，对数据库的任何调用都不会返回任何数据(不好).有没有办法手动提交交易?

In my Spring boot app I'm deleting and inserting a large amount of data into my MySQL db in a single transaction. Ideally, I want to only commit the results to my database at the end, so all or nothing. I'm running into issues where my deletions will be committed before my insertions, so during that period any calls to the db will return no data (not good). Is there a way to manually commit transaction?

我的主要逻辑是:

    @Transactional
    public void saveParents(List<Parent> parents) {

        parentRepo.deleteAllInBatch();
        parentRepo.resetAutoIncrement();

//I'm setting the id manually before hand
String sql = "INSERT INTO parent " +
                    "(id, name, address, number) " +
                    "VALUES ( ?, ?, ?, ?)";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    Parent parent = parents.get(i);

                    ps.setInt(1, parent.getId());
                    ps.setString(2, parent.getName());
                    ps.setString(3, parent.getAddress());
                    ps.setString(4, parent.getNumber());
                }

                @Override
                public int getBatchSize() {
                    return parents.size();
                }
            });
    }

ParentRepo

ParentRepo

@Repository
@Transactional
public interface ParentRepo extends JpaRepository<Parent, Integer> {
    @Modifying
    @Query(
            value = "alter table parent auto_increment = 1",
            nativeQuery = true
    )
    void resetAutoIncrement();
}

所以我改变了

    parentRepo.deleteAllInBatch();
    parentRepo.resetAutoIncrement();

到

    jdbcTemplate.update("DELETE FROM output_stream");
    jdbcTemplate.update("alter table output_stream auto_increment = 1");

为了尝试避免jpa的事务，但是无论我尝试什么，每个操作似乎都是分别提交的.我已经尝试过TransactionTemplate并实现PlatformTransactionManager(参见此处)，但我似乎无法让这些操作一起提交.

in order to try avoiding jpa's transaction but each operation seems to be committing separately no matter what I try. I have tried TransactionTemplate and implementing PlatformTransactionManager (seen here) but I can't seem to get these operations to commit together.

看来我遇到的问题与alter table相同，因为它将始终提交.

It seems the issue I was having was with the alter table as it will always commit.

推荐答案

我遇到的问题是，在插入之前删除将被提交，因此在此期间，对数据库的任何调用都将不返回任何数据

I'm running into issues where my deletions will be committed before my insertions, so during that period any calls to the db will return no data

您是否配置了JPA和JDBC来共享事务?

Did you configure JPA and JDBC to share transactions?

如果没有，那么您基本上是在使用两种不同的机制来访问数据(EntityManager和JdbcTempate)，它们各自维护与数据库的独立连接.可能发生的情况是，只有EntityManager参与了@Transactional创建的事务. JdbcTemplate操作可以在没有事务上下文的情况下(在AUTOCOMMIT模式下读取:)执行，也可以在单独的事务中执行.

If not, then you're basically using two different mechanisms to access the data (EntityManager and JdbcTempate), each of them maintaining a separate connection to the database. What likely happens is that only EntityManager joins the transaction created by @Transactional; the JdbcTemplate operation executes either without a transaction context (read: in AUTOCOMMIT mode) or in a separate transaction altogether.

请参见这个问题.它有点旧，但是再次使用JPA和Jdbc并不是完全常见的用例.另外，请查看

See this question. It is a little old, but then again, using JPA and Jdbc together is not exactly a common use case. Also, have a look at the Javadoc for JpaTransactionManager.

这篇关于Spring Boot手动提交事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！