Fixing Oracle Stored Procedure Rollback Issues in Java Transactions

In a community I participate in, a developer recently ran into a frustrating data integrity issue: they had a Spring-managed transaction calling two methods. The first method executed an Oracle Stored Procedure (SP) to insert data, and the second method performed a standard JPA save. Even though the second method threw a RuntimeException, the data inserted by the Oracle SP remained in the database.

If you are seeing “partial commits” where your Java code fails but your Oracle SP changes persist, you are likely dealing with a transaction boundary mismatch.

Short Answer (The “TL;DR”)

The most common reason an Oracle Stored Procedure fails to roll back alongside a Java transaction is that the SP contains an explicit COMMIT statement or uses PRAGMA AUTONOMOUS_TRANSACTION.

In Oracle, a COMMIT inside a procedure persists all changes in that session up to that point, effectively “breaking” the atomicity managed by Spring’s @Transactional. To fix this, remove the COMMIT from the PL/SQL code and let the Java Transaction Manager (JTA or DataSourceTransactionManager) handle the lifecycle.

---

Deep Dive: Why the Rollback Fails

When you use @Transactional in Java, the Spring Framework instructs the JDBC connection to set autoCommit(false). It then waits for the method to complete before sending a COMMIT or ROLLBACK command over that same connection.

However, Oracle Stored Procedures can override this behavior if not written with external transaction management in mind.

Solution 1: Remove Explicit Commits in PL/SQL

If your PL/SQL code looks like the example below, it will bypass your Java transaction logic.

The Problematic Code (Oracle PL/SQL):

CREATE OR REPLACE PROCEDURE insert_user_log(p_msg IN VARCHAR2) AS
BEGIN
  INSERT INTO user_logs (log_msg) VALUES (p_msg);
  COMMIT; -- <--- THIS IS THE CULPRIT
END;

The Fix (Oracle PL/SQL): Remove the COMMIT. The Java application container is responsible for the transaction lifecycle.

-- Illustrative example — verify in your environment
CREATE OR REPLACE PROCEDURE insert_user_log(p_msg IN VARCHAR2) AS
BEGIN
  INSERT INTO user_logs (log_msg) VALUES (p_msg);
  -- No COMMIT here; let the caller decide
END;

Why this works: By removing the COMMIT, the INSERT remains “pending” in the current database session. When the second Java write fails and Spring issues a ROLLBACK via JDBC, Oracle reverts the pending insert from the SP as part of the same transaction.

Solution 2: Detect and Remove Autonomous Transactions

Sometimes, developers use PRAGMA AUTONOMOUS_TRANSACTION to perform logging that should persist even if the main transaction fails. If your primary business logic is wrapped in this pragma, it will never roll back with the main Java transaction.

The Problematic Code (Oracle PL/SQL):

CREATE OR REPLACE PROCEDURE process_order(p_id IN NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION; -- <--- THIS SEPARATES THE TRANSACTION
BEGIN
  UPDATE orders SET status = 'PROCESSED' WHERE id = p_id;
  COMMIT;
END;

The Fix: If the SP is part of the atomic business unit, remove the Pragma.

Version Note: This applies to all modern Oracle versions (11g, 12c, 19c, 21c).

Solution 3: Verify Spring Transaction Configuration

If the SP code is “clean” (no commits), the issue might be how Java handles the exception. By default, Spring only rolls back on Unchecked Exceptions (RuntimeException and Error).

The Fix (Java 17+ / Spring Boot 3.x): Ensure your @Transactional annotation accounts for checked exceptions if your 2nd DB write throws one (like IOException or a custom Exception).

// Illustrative example — Spring 6.x / Java 17
@Transactional(rollbackFor = Exception.class)
public void complexBusinessProcess(Data data) {
    // 1. Calls Oracle SP via JdbcTemplate
    userRepository.callStoredProcedure(data.getLog());

    // 2. This throws a checked Exception
    fileService.saveReport(data);
}

---

Prevention Checklist

1. Database Side:
   • Search PL/SQL source code for COMMIT, ROLLBACK, or PRAGMA AUTONOMOUS_TRANSACTION.
   • Ensure no DDL statements (like CREATE TABLE) are in the SP, as DDL causes an implicit commit in Oracle.
2. Java Side:
   • Verify the DataSource used by the SP caller is the same DataSource used by the JPA/Hibernate repository. Transactions cannot span different DataSources without a JTA coordinator (like Atomikos).
   • Ensure you aren’t using “Self-Invocation” (calling a @Transactional method from another method within the same class), which bypasses the proxy and the transaction entirely.
   • Check that connection.setAutoCommit(false) is being set by your connection pool (standard behavior for most pools like HikariCP).

---

Related Questions

Does the JDBC driver version matter? Generally, no. This is a logic/protocol issue rather than a driver bug. However, ensure you are using the ojdbc8.jar or ojdbc11.jar compatible with your Oracle DB version to ensure the transaction state is correctly communicated.

What if I actually NEED the SP to commit independently? If the SP is for auditing/logging where you want the record to stay even if the main purchase fails, then PRAGMA AUTONOMOUS_TRANSACTION is correct. The “error” in that case is a misunderstanding of the architectural requirement, not a technical bug.

Can Checked Exceptions cause this? Yes. If the second write fails with a checked Exception and your annotation is just @Transactional, Spring will commit the first write (the SP) because it assumes the exception was handled. Always use rollbackFor = Exception.class for maximum safety.