Distributed transaction cannot be committed because AdoNetWithDistributedTransactionFactory tries to write data by using locked sqlConnection
Description
Environment
is related to
Activity
Frédéric DelaporteSeptember 7, 2017 at 8:15 AM
https://nhibernate.jira.com/browse/NH-4011#icft=NH-4011 has introduced a considerable rework of system transaction handling. It now uses a dedicated connection for flushing changes from transaction notifications, for db supporting this.
Without a dedicated test case, we are not sure your case is covered, but I close this nonetheless.
Frédéric DelaporteJune 29, 2017 at 10:38 AMEdited
Your example does not reproduce the issue alone, we need need more informations. See here for directly contributing a test case, or here (some obsolete things are to be adapted) for providing it as a separated project. The used data provider and database seem crucial to reproducing those kind of issues, please make sure of letting us know which are they. (Versions included. Your example code implies SQL Server, but which version?)
Frédéric DelaporteMay 15, 2017 at 10:24 AM
@Mihail Iwanow, may you provide a unit test reproducing the issue please? And tell us with which database do you have the issue. And maybe add the stacktrace of your case, for ascertain where the lock happen.
The prepare phase is used by NHibernate with the EnlistDuringPrepareRequired option, so using there a connection to perform additional database work should be ok, and works in our testes.
Now maybe for some databases is it required to use a new connection rather than the one already acquired by the session.
Alex ZaytsevMarch 23, 2017 at 11:45 PM
Thanks for that, @Mihail Iwanow. Do you have any ideas how to fix the issue?
We use NHibernate, NServiceBus, Envers and in our case the problem occurs when AdoNetWithDistributedTransactionFactory triggers BeforeTransactionCompletion event (at that point Enverse tries to write audit logs to database). But the same problem appears in simpler cases.
Here is example:
using (var sessionToFirstDb = sessionFactory.OpenSession()) { using (var transactionScope = new TransactionScope()) { var entity = new Entity(); sessionToFirstDb.Save(entity); // at this point AdoNetWithDistributedTransactionFactory enlists to ambient transaction sessionToFirstDb.Flush(); using (var sqlConnectionToSecondDb = new SqlConnection(connectionString)) { sqlConnectionToSecondDb.Open(); // promote to distributed transaction var command = new SqlCommand(insertCommandSql, sqlConnectionToSecondDb); command.ExecuteNonQuery(); sqlConnectionToSecondDb.Close(); } var entity2 = sessionToFirstDb.Get<User>(id); entity2.CreatedOn = DateTime.Now; sessionToFirstDb.Update(entity2); transactionScope.Complete(); } }When commit process is started, DTC sends "prepare" notifications to all registered resource managers. It is important to note that each IEnlistmentNotification.Prepare(...) method can be run in different thread.
AdoNetWithDistributedTransactionFactory in IEnlistmentNotification.Prepare(PreparingEnlistment preparingEnlistment) method calls sessionImplementor.Flush() by using sql connection which (it seems to me) has already prepared for commit and is locked. As a result, we receive sql connection timeout exception (by default 15 sec), transaction is in doubt state.