Questions regarding CloseAsync, DisposeAsync and Distributed transactions

[avidalcarrasco]

Hello all,
I would like to ask about why in the documentation is explicit about opening an async connection but not about closing and disposing it async, the examples also don't show this so I'm curious about it.

Another question I have is related to an error I'm experiencing since December and it is that I'm getting in my code XA_RBDEADLOCK while using oracle and mysql in the same transaction that I open like this:

using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
using (await _oracleConnectionManager.OpenConnection())
using (await _mySqlConnectionManager.OpenConnection())

While opening the connection I enlist the transaction

      if (Transaction.Current != null)
      {
           _connection.EnlistTransaction(Transaction.Current);
      }

And later when trying to dispose the connection I get XA_RBDEADLOCK .
It doesn't happens always but when it happens it also blocks Oracle tables.
It's very hard to test and actually I wasn't able to reproduce the error, so it just happens from time to time by itself.
I had this code running for 1 year without any issues and the error is always in the same path, so I don't think it's related to the library itself.

My question is the following: In the documentation it said that we should use MySqlConnection.BeginTransactionAsync, CommitAsync and RollbackAsync but if I'm using the TransactionScope this should be implicit in .NET Core code?

Could be a problem with a connection leaking, the transaction being block?

Thank you for your help!

[bgrainger]

I would like to ask about why in the documentation is explicit about opening an async connection but not about closing and disposing it async, the examples also don't show this so I'm curious about it.

The documentation hasn't been updated for those new methods being added to ADO.NET in .NET 6. (Practically speaking, it doesn't make any difference: "closing" a connection is simply returning it to the connection pool, which is a synchronous operation. I can update that documentation to make that more clear.)

Another question I have is related to an error I'm experiencing since December

Is it correlated with updating the version of MySqlConnector you're using? Or just happened to start then?

While opening the connection I enlist the transaction

That should be unnecessary. AutoEnlist = True is the default in the connection string, so MySqlConnection will automatically enlist itself on open. (I think calling it again is just a no-op.)

And later when trying to dispose the connection I get XA_RBDEADLOCK .

I found this advice online, and I would agree with it: "Your transaction is being rolled back because a deadlock was detected. You need to examine what resources were deadlocked, then try to figure out why. Remember that in RDBMSs deadlocks should be infrequent but not entirely unexpected (databases are shared resources after all). It might be that retrying the transaction will fix this. If a deadlock happens repeatedly in the same place you need to look at your database interactions to see if you can reorder them to avoid the deadlock. But step one is find out what is deadlocked and why."

As per https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks-handling.html, "Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again." I assume the same advice applies to distributed transactions as it does to regular MySQL transactions.

My question is the following: In the documentation it said that we should use MySqlConnection.BeginTransactionAsync, CommitAsync and RollbackAsync but if I'm using the TransactionScope this should be implicit in .NET Core code?

I don't believe that .NET Distributed Transactions support async commit/rollback, but this isn't something to worry about. Async I/O is recommended for MySqlConnector but it's also fine to use synchronous methods; the main potential downside is limiting scalability once you start getting up to thousands of simultaneous I/O operations.

[avidalcarrasco]

Hello.
Thank you for your comments!
I removed the enlisting and it was working fine as you said!
I'm also testing the openAsync and it's seems ok too.

We are not using .NET 6 but .NET 3.1.
The library version that we use right now is 2.1.7 but I think I will update to 2.1.8
and the error started with 1.3.11 and I upgraded to 2.1.7 ten days after the error started to show up, just to check if that helped or not.

It's curious that the deadlock error is always in the same place to be honest, but I can't find why. I checked that the tables are not trying to call themselves and so on. I don't have any triggers in the DB so it's hard to think is at DB level.

We can't "retry" because the error is at dispose method, as you can see in this log:

Exception":"MySqlConnector.MySqlException (0x80004005): XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected\r\n   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 43\r\n   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 132\r\n   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 466\r\n   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56\r\n   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 296\r\n   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 107\r\n   at MySqlConnector.Core.XaEnlistedTransaction.ExecuteXaCommand(String statement) in /_/src/MySqlConnector/Core/XaEnlistedTransaction.cs:line 47\r\n   at MySqlConnector.Core.XaEnlistedTransaction.OnRollback(Enlistment enlistment) in /_/src/MySqlConnector/Core/XaEnlistedTransaction.cs:line 39\r\n   at MySqlConnector.Core.EnlistedTransactionBase.System.Transactions.IEnlistmentNotification.Rollback(Enlistment enlistment) in /_/src/MySqlConnector/Core/EnlistedTransactionBase.cs:line 37\r\n   at System.Transactions.VolatileEnlistmentAborting.EnterState(InternalEnlistment enlistment)\r\n   at System.Transactions.VolatileEnlistmentActive.InternalAborted(InternalEnlistment enlistment)\r\n   at System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx)\r\n   at System.Transactions.TransactionStateActive.Rollback(InternalTransaction tx, Exception e)\r\n   at System.Transactions.Transaction.Rollback()\r\n   at System.Transactions.TransactionScope.InternalDispose()\r\n   at System.Transactions.TransactionScope.Dispose()\r\n

Maybe this log can help to realize the problem.
My distributed transaction is with oracle DB and the oracle table is getting lock, our oracle team is reporting to us that my service has a open connection but not doing anything after the error for several hours/days.

I will start logging the status of the transaction using the
Transaction.Current.TransactionCompleted event and maybe I find something meanwhile.

Thank you again for your time and let me know if you can find something interesting too.

[wumingwuan]

Our system run into same problem yesterday as avidalcarrasco describe above.
"XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected"

.Net core 3.0
MysqlConnector:2.1.8
Database: MariaDB

20220525 16:00 Our
DBA also said a db session in sleep mode and transaction not complete(transaction already run 5 hours)

It lock the table and we have to manual kill the session to make our system work again.

key code:
public class TransactionScopeEx
{
public static TransactionScope CreateTransactionScope()
{
var transactionOptions = new TransactionOptions
{
IsolationLevel = IsolationLevel.RepeatableRead
};
return new TransactionScope(TransactionScopeOption.Required, transactionOptions, TransactionScopeAsyncFlowOption.Enabled);
}

using (var scope = TransactionScopeEx.CreateTransactionScope())
{
directoryDal.Open(DBConn.Conn);
//some db delete and insert
scope.Complete();
}

[avidalcarrasco]

Hello @wumingwuan, do you use a single DB?
In my case is a distributed transaction with 2 different DB.
So it's interesting that you are getting XA errors if you have only one DB, I think.

[wumingwuan]

Hi, @avidalcarrasco .
In our use case , we only have a single DB.

And we also catch a exception recently:
System.InvalidOperationException: Connection must be Open; current state is Closed
at MySqlConnector.MySqlConnection.get_Session() in //src/MySqlConnector/MySqlConnection.cs:line 690
at MySqlConnector.Core.ICancellableCommandExtensions.ResetCommandTimeout(ICancellableCommand command) in //src/MySqlConnector/Core/ICancellableCommand.cs:line 82
at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/MySqlCommand.cs:line 264
at MySqlConnector.MySqlCommand.ExecuteNonQuery() in //src/MySqlConnector/MySqlCommand.cs:line 101
at MySqlConnector.Core.XaEnlistedTransaction.ExecuteXaCommand(String statement) in //src/MySqlConnector/Core/XaEnlistedTransaction.cs:line 49
at MySqlConnector.Core.XaEnlistedTransaction.OnRollback(Enlistment enlistment) in //src/MySqlConnector/Core/XaEnlistedTransaction.cs:line 41
at MySqlConnector.Core.EnlistedTransactionBase.System.Transactions.IEnlistmentNotification.Rollback(Enlistment enlistment) in /_/src/MySqlConnector/Core/EnlistedTransactionBase.cs:line 39
at System.Transactions.VolatileEnlistmentAborting.EnterState(InternalEnlistment enlistment)
at System.Transactions.VolatileEnlistmentActive.InternalAborted(InternalEnlistment enlistment)
at System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStateActive.Rollback(InternalTransaction tx, Exception e)
at System.Transactions.Transaction.Rollback()
at System.Transactions.TransactionScope.InternalDispose()
at System.Transactions.TransactionScope.Dispose()

Maybe this is the root cause of not complete transaction? (long running and lock table?)

[avidalcarrasco]

Hello @wumingwuan. I just noticed in your code that you are not "using" the connection.

using (var scope = TransactionScopeEx.CreateTransactionScope())
using (var connection = directoryDal.Open(DBConn.Conn))
{
    
    //some db delete and insert
    scope.Complete();
}

Is your connection properly disposed?

Or are you reusing the connection for the whole service?
I think that the connection get automatically close after a while but inside mysql it can be kept as "sleep".

Maybe you can try to take a look to the disposing of the connection that you use.

[wumingwuan]

Hi, @avidalcarrasco
Oh, I just not writing all my code in the key code section. I use try {
//Transaction
}catch{} finally{ dal.Connection.Close()} to cover all of my Transaction.
So , I can confirm the connection will close properly (at least in my code).