Spring @Transactional is consistantly rolling back transaction upon exception

Question

Alrighty folks. I've read quite a bit of docs. And at my wits end on why this is happening.

Like many of the other people, we have a service interfacing with a oracle db. This is a pretty standard setup

@Service
public class DaoService {

    private JdbcTemplate jdbcTemplate;

    private SimpleJdbcInsert insertA;
    private SimpleJdbcInsert insertB;
    private SimpleJdbcInsert insertC;

    @Autowired
    public Dao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        // Assume all Inserts are initialized
    }

    @Transactional(rollbackFor = Exception.class)
    public void insertStuff(Stuff stuff) {
         insertA.execute(stuff.A);
         // Suppose a connection failed here
         insertB.executeBatch(stuff.B);
         insertC.executeBatch(stuff.C);
}

Now here lies our issue. For 99% of all rollbacks they are a-ok.

When a connection gets closed for unknown reasons is where our problems occur. The exception message being

 TransactionSystemException: Could not roll back JDBC transaction;
     nested exception is java.sql.SQLException: "The connection is closed"

See, it tries to rollback as it's supposed to. But the issue is that stuffA lingers in the DB will stuffB and stuffC is not. This happens only like 1% of the time. (aka, sometimes despite the rollback failure, no 'stuff' will be in the DB.

Am I understanding something wrong? I thought spring only commits at the end of a successful transaction? Anyone have an idea on how I can stop these partial commits despite making in @Transactional?

p.s. for what it's worth. autocommit is defaulted to being on. However I read that it's not taken into consideration when something is @Transactional

score 1 · Answer 1 · answered Sep 25 '18 at 22:43

According to this answer, Spring only offers you an interface for transaction manager and the implementation may vary. However, most transaction manager implementations will turn off autocommit during an @Transactional call then return it to the previous state after commit.

That being said, on some databases there are ways to execute autonomous transactions inside an outer transaction that may not be visible to your application or to Spring. I know Oracle allows this. Have you checked all of the triggers on the tables? I worked on one application that had an audit trigger that would force orphaned data into some of the tables in situations like this. Can you tell us which database you are using?

OP said he is using Oracle DB. – Gustavo Passini Sep 25 '18 at 22:56 — Gustavo Passini, Sep 25 '18 at 22:56

score 1 · Accepted Answer · answered Sep 25 '18 at 22:54

1

From the Database JDBC Developer's Guide:

If the auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is run.

Maybe this is the case you are running into.

answered Sep 25 '18 at 22:54

Gustavo Passini

2,348
19
25

This is probably the issue. You could solve this yourself, or you could simply use a higher-level API than jdbc that’s already solved the problem. – Arlo Guthrie Sep 25 '18 at 23:44

score 0 · Answer 3 · answered Sep 26 '18 at 04:10

0

try this :

@Transactional(propagation = Propagation.REQUIRES_NEW)

hope it will work

answered Sep 26 '18 at 04:10

kingGarfield

304
1
12

Spring @Transactional is consistantly rolling back transaction upon exception

3 Answers3