spring mysql自动提交事务,Spring启动手动提交事务

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?

My main logic is:

@Transactional

public void saveParents(List 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

@Repository

@Transactional

public interface ParentRepo extends JpaRepository {

@Modifying

@Query(

value = "alter table parent auto_increment = 1",

nativeQuery = true

)

void resetAutoIncrement();

}

EDIT:

So I changed

parentRepo.deleteAllInBatch();

parentRepo.resetAutoIncrement();

to

jdbcTemplate.update("DELETE FROM output_stream");

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

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.

EDIT: 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

Did you configure JPA and JDBC to share transactions?

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.

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.