We are running sql server 2016 enterprise edition. We have a linked server configured to db2. We do a distributed transaction between mssql 2016 and db2 using MS DTC. SQL Server controls the distributed transaction. Application is running in Java using JDBC. We are using MS JDBC driver. Java does nothing in terms of distributed transaction. From Java's perspective, it is only a local transaction with MSSQL.
Everything works fine and has been running 2+ years.
Transactions are started and committed, or rolled back on error on both mssql and db2
However we now experience a weird problem. If we run into a duplicate key (or any other error from an SQL statement), an error is thrown in the form of a SQLException (java exception). When this occurs, it seems the transaction in progress is paused and a new transaction is started. The new transaction commits, however everything that happened before in the paused transaction does not commit (nor rollback). It just hangs.
When this happens enough times, the tables lock, and we can neither read nor write from them.
There is nothing done in the application that will start a new transaction at exception.
The reason for why we have not had this issue in 2+ years is because this is the first time we expect an error to happen (duplicate key) and we therefore do some logic and continue. Previously we would always rollback immediately on exception.
I'm not sure how to troubleshoot this. Is this a documented behavior ? Where can I see the "transaction id" so I can confirm the above ? How do I proceed ?
--- Update ---
xact_abort is on and therefore the duplicate key error rollsback the transaction (but the rollback is not complete, somehow the transaction is left hanging). This explains why the commit works for the work performed after the error. Also this is relevant: https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on#18829211
The question now changes to, how to deal with statements that may throw an error but should not roll back an exception