I have a scenario where I want to execute two statements in a transaction using Spring Transactional API. The DB statements roughly looks like this:
BEGIN TRANSACTION; – part of Spring
UPDATE MY_SCHEMA.MY_TABLE SET MY_COL = 5 WHERE MY_COL < 5;
INSERT INTO MY_SCHEMA.MY_TABLE (PK1, MY_COL) VALUES (1, 5), (1, 4);
COMMIT; – part of Spring
Since there is a primary key constraint on column PK1, this transaction should fail, i.e., neither the update nor the insert should be executed. However, checking the result of the query, the update is visible in the database after the transaction has been rolled back. The audit log looks as follows:
| SQL_TEXT | ERROR_TEXT | EXECUTION MODE | SUCCESS | ERROR_CODE |
|---|---|---|---|---|
| UPDATE … | PREPARE | true | ||
| UPDATE … | EXECUTE | true | ||
| INSERT … | PREPARE | true | ||
| INSERT … | constraint violation - primary key (…) | EXECUTE | false | 27002 |
| COMMIT | EXECUTE | true | ||
| ROLLBACK | EXECUTE | true |
I am using PreparedStatement.execute for the update and PreparedStatement.executeBatch for the insert. The executeBatch method does not throw an exception even though there is a violation. To the Spring Transactional interface, it looks like the transaction can be committed. While committing, the exception is raised and I suspect that this is finally triggering the rollback.
The commit should not have been attempted in the first place because the will be a constraint violation.
If I add another statement after the INSERT (e.g. SELECT MAX(PK1) FROM MY_SCHEMA.MY_TABLE), then the exception will be triggered upon trying to execute the SELECT statement.
When debugging and simultaneously checking the audit log after each statement while the transaction is still alive, the EXECUTE of the last statement is not visible. Maybe this is connected to the issue.
What is causing this behavior and how can it be fixed?
).