The transaction is not automatically rolled back on commit timeout

[mihaicodrean]

I'm using the typical exception handling with two using blocks, as detailed here.

Occasionally, I'm getting "Commit failed" during NHibernate.Transaction.AdoTransaction.Commit(): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

And still, the transaction is not automatically rolled back in this case.

Is this by design? In fact, I see that in AdoTransaction, Commit sets the commitFailed flag on exception, and then Rollback bails out if that is true.

To contrast that, I see here that hibernate (Java) supports rollback on timeout:

We have an option to allow rollback on timeout, but disabled by default because some XA drivers have issues with it (since the rollback happens in a different thread than the one that started it).
The transaction is marked for rollback when it times out. The application should cleanup, and commit will fail with rollback as a result.

[fredericDelaporte]

A commit failure is a wicked case: we cannot easily know if the transaction is still considered ongoing on the DB side or not. So, attempting a rollback could add an additional failure, which may hide the original one and prevent understanding what is the actual issue.

Any change here would have to be done carefully.

[mihaicodrean]

Valid points, although maybe attempting rollback would still succeed in some cases, thus reducing the chances of ending up with DB inconsistencies.

On the optional rollback, could it be as easy as allowing the commitFailed flag to be manually unset in a catch block? Similar to markRollbackOnly from hibernate - example here.

BTW, is there a complete example / unit-test for manually managing the ADO.NET transactions, as partially detailed in the official docs here? How does one obtain/create the ADO.NET transaction object in that case?

[fredericDelaporte]

although maybe attempting rollback would still succeed in some cases, thus reducing the chances of ending up with DB inconsistencies

Inconsistencies are not possible unless the database itself is utterly failing in its transaction management. A pending transaction will not finish with any of its parts committed without a successful commit. It can only end-up rollback-ed. This happens automatically with most if not all databases, due to their transaction timeout.

BTW, is there a complete example / unit-test for manually managing the ADO.NET transactions, as partially detailed in the official docs here ? How does one obtain/create the ADO.NET transaction object in that case?

With user supplied connections, the transaction can be manually managed, since the application provides and manages itself the connection. But NHibernate will be unaware of the transaction, which will prevent some features to work as intended. (Flush on commit, second level cache, to name a few.)

[mihaicodrean]

Inconsistencies are not possible unless the database itself is utterly failing in its transaction management. [...]

Ah, no, I'm referring to ending up with duplicated data, for example, say when re-running the same insert code, if assuming failure on the first attempt.

With user supplied connections, the transaction can be manually managed [...]

Thanks for clarifying on that and pointing out the downsides!

[fredericDelaporte]

Ah, no, I'm referring to ending up with duplicated data, for example, say when re-running the same insert code, if assuming failure on the first attempt.

Then we are back to "wicked case": we cannot assume anything in case of a commit failure. By example, is it a network issue before reaching the DB (so transaction still ongoing), or after, on receiving its response? (So transaction actually commited but the client has not received the acknowledgment.) No way to know, excepted querying the database (after having dropped the faulted connection and session). Attempting a rollback will not rollback anything in the second case, and re-running the insert would then still cause duplicates.

Dropping the connection and session (disposing of it, so closing it) would cause the database to rollback any pending transaction. Querying it would then allow to correctly assess the situation. But of course, that is not something that can be generically handled. It would need dedicated code in the application for each case, which would be quite a burden to implement. A more usual pattern is to let the user handle this by telling her/him to check the data after the failure.

Documentation:

If the ISession throws an exception you should immediately rollback the transaction, call ISession.Close() and discard the ISession instance. Certain methods of ISession will not leave the session in a consistent state.

That does apply to any NHIbernate interface obtained from a session and bound to it, like the ITransaction, querying objects, ...
So, you are already rollbacking the NHibernate transaction (which does nothing in your case currently). But are you also discarding the session? If yes, that would cause the database to rollback the transaction if it was still ongoing.

[mihaicodrean]

Thanks for the detailed reply.

Querying it would then allow to correctly assess the situation. But of course, that is not something that can be generically handled. It would need dedicated code in the application for each case, which would be quite a burden to implement.

Right, I was afraid that that might be the only reliable way.

But are you also discarding the session?

Yes, given the "using".

If yes, that would cause the database to rollback the transaction if it was still ongoing.

Even without an explicit Rollback call on the ADO transaction object? Because the NHibernate transaction object won't do that, given that the commitFailed flag was set on exception.

[fredericDelaporte]

If the connection is actually closed, the DbTransaction is rollbacked. (Note that with transaction scopes, that is another subject.)

But it seems that connection pooling may not guard against ongoing transaction when putting back a connection in the pool according to this SO answer, which is unexpected for me. I know the pooling mechanism do some "reset" operation on the connection to ensure that on next get, it is in its default state. That surprises me that it would not handle the possibility of ongoing transactions to rollback them too.

The Microsoft documentation looks unhelpful to me in this regard, it only discusses the case of ambient transactions (transaction scopes).

According to this blog post, things are "not so bad": the connection can be returned to the pool with an ongoing transaction, but its next use will reset it, causing a transaction rollback. Still its next reuse (or actual closing due to inactivity) can be long to come, which can cause issues.
The blog post mainly deals with the issue of query timeouts without explicit rollback, and advocates the use of the xact_abort setting to always cause a transaction rollback in such cases (which may cause any explicit rollback done afterward to fail, but it also provides a way to avoid that).
About your case, a commit failure, I have no idea if this setting may be of any help.

And all this is only valid for SQL Server. NHibernate addresses many other databases, which may have different issues with this subject.

[mihaicodrean]

Thanks again for the detailed reply.
I ended up checking for existing data at the beginning of the DB jobs, and bail out if that's the case.