How to implement partial commits in a Spring Boot service when using spring repositories

Implementing checkpoint or partial commits within a database transaction in a Spring Boot service can be tricky since Spring transactions are designed to be atomic: they either fully commit or fully roll back. However, you can achieve a similar effect by managing transactions explicitly in your service layer and leveraging Spring’s PlatformTransactionManager or nested transactions with @Transactional.

Here are some approaches:

1. Explicit Transaction Management

You can manually manage transactions using TransactionTemplate or PlatformTransactionManager.

Steps:

Autowire PlatformTransactionManager into your service.
Use it to create and commit/rollback individual transactions for each “checkpoint.”

Example:

@Service
public class CheckpointService {

    @Autowired
    private PlatformTransactionManager transactionManager;

    @Autowired
    private MyRepository myRepository;

    public void processWithCheckpoints(List<MyEntity> entities) {
        for (MyEntity entity : entities) {
            TransactionStatus status = transactionManager.getTransaction(new DefaultTransactionDefinition());

            try {
                // Perform operations for this checkpoint
                myRepository.save(entity);

                // Commit this transaction (checkpoint)
                transactionManager.commit(status);
            } catch (Exception ex) {
                // Rollback this checkpoint on failure
                transactionManager.rollback(status);
                throw new RuntimeException("Transaction failed at checkpoint", ex);
            }
        }
    }
}

2. Nested Transactions

Nested transactions allow you to commit or roll back parts of a transaction independently. This requires a database that supports savepoints and Spring’s @Transactional annotation.

Steps:

Use @Transactional with propagation = Propagation.NESTED.
Use try-catch blocks within the main transaction to create checkpoints.

Example:

@Service
public class CheckpointService {

    @Autowired
    private MyRepository myRepository;

    @Transactional
    public void processWithCheckpoints(List<MyEntity> entities) {
        for (MyEntity entity : entities) {
            try {
                processEntityWithCheckpoint(entity);
            } catch (Exception ex) {
                // Log or handle the failure, but the main transaction won't roll back
                System.err.println("Failed to process entity: " + entity.getId());
            }
        }
    }

    @Transactional(propagation = Propagation.NESTED)
    public void processEntityWithCheckpoint(MyEntity entity) {
        myRepository.save(entity);
        // Additional operations for this entity
    }
}

Notes:

Ensure your database supports nested transactions or savepoints (e.g., PostgreSQL, Oracle, etc.).
Use this approach carefully, as it can introduce complexity.

3. Chunk-Based Processing

If you’re processing a large dataset, you can batch the data into smaller chunks, commit each chunk as an individual transaction, and avoid a single large transaction.

Steps:

Divide the data into chunks.
Use @Transactional for each chunk.

Example:

@Service
public class CheckpointService {

    @Autowired
    private MyRepository myRepository;

    public void processInChunks(List<MyEntity> entities, int chunkSize) {
        int total = entities.size();
        for (int i = 0; i < total; i += chunkSize) {
            List<MyEntity> chunk = entities.subList(i, Math.min(i + chunkSize, total));
            saveChunk(chunk);
        }
    }

    @Transactional
    public void saveChunk(List<MyEntity> chunk) {
        myRepository.saveAll(chunk);
    }
}

4. Savepoints in Native JDBC

If you need finer control over transactions, you can use native JDBC with savepoints. This requires direct interaction with the DataSource and may bypass Spring’s transaction management.

Example:

@Service
public class CheckpointService {

    @Autowired
    private DataSource dataSource;

    public void processWithSavepoints(List<MyEntity> entities) throws SQLException {
        try (Connection connection = dataSource.getConnection()) {
            connection.setAutoCommit(false);

            Savepoint savepoint = null;

            try {
                for (MyEntity entity : entities) {
                    // Save entity
                    String sql = "INSERT INTO my_table (id, name) VALUES (?, ?)";
                    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                        pstmt.setLong(1, entity.getId());
                        pstmt.setString(2, entity.getName());
                        pstmt.executeUpdate();
                    }

                    // Create a savepoint
                    savepoint = connection.setSavepoint();

                    // Simulate error
                    if (entity.getId() % 2 == 0) {
                        throw new RuntimeException("Simulated failure");
                    }
                }

                // Commit the transaction
                connection.commit();
            } catch (Exception ex) {
                if (savepoint != null) {
                    connection.rollback(savepoint); // Rollback to the last savepoint
                } else {
                    connection.rollback(); // Full rollback
                }
                throw new RuntimeException("Transaction failed", ex);
            }
        }
    }
}

Which Approach to Use?

Explicit Transactions: Use when you need full control over individual transactions.
Nested Transactions: Use when your database supports savepoints and you want to leverage Spring’s abstraction.
Chunk Processing Transactions: Use for batch processing of large datasets.
JDBC Savepoints: Use for fine-grained control or legacy systems.

I’ve used this in a non-published version of the app https://programtom.com/dev/product/article-translator-spring-boot-vaadin-web-app/ for savign article keys in a batch. Let me know which one fits your needs or if you’d like further clarification!