Transaction not properly closed and lock db table

[wumingwuan]

trafficstars

Software versions MySqlConnector version: 2.1.8 Server type (MySQL, MariaDB, Aurora, etc.) and version: MariaDB 10.1 .NET version: .net core 3.0 (Optional) ORM NuGet packages and versions: 2.1.8

Describe the bug Transaction not properly closed and lock rows.

Exception Full exception message and call stack (if applicable)

Connection must be Open; current state is Closed
   at MySqlConnector.MySqlConnection.get_Session() in /_/src/MySqlConnector/MySqlConnection.cs:line 741
   at MySqlConnector.Core.ICancellableCommandExtensions.ResetCommandTimeout(ICancellableCommand command) in /_/src/MySqlConnector/Core/ICancellableCommand.cs:line 56
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__78.MoveNext() in /_/src/MySqlConnector/MySqlCommand.cs:line 294
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 107
   at MySqlConnector.Core.XaEnlistedTransaction.ExecuteXaCommand(String statement) in /_/src/MySqlConnector/Core/XaEnlistedTransaction.cs:line 46
   at MySqlConnector.Core.XaEnlistedTransaction.OnRollback(Enlistment enlistment) in /_/src/MySqlConnector/Core/XaEnlistedTransaction.cs:line 39
   at MySqlConnector.Core.EnlistedTransactionBase.System.Transactions.IEnlistmentNotification.Rollback(Enlistment enlistment) in /_/src/MySqlConnector/Core/EnlistedTransactionBase.cs:line 37
   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()
   at PMIS.Service.DirectoryService.ApplyProjectRolePermissionToAllSubDirectory(Account user, String projectId, String directoryId) in C:\git2017\PMIS.Service\DirectoryService.cs:line 1945

Code sample

    public static TransactionScope CreateTransactionScope()
    {
        var transactionOptions = new TransactionOptions
        {
            IsolationLevel = IsolationLevel.RepeatableRead,
        };
        return new TransactionScope(TransactionScopeOption.Required, transactionOptions, TransactionScopeAsyncFlowOption.Enabled);
    }
    public void Test()
    {
        DirectoryRolePermissionDAL directoryRolePermissionDAL = new DirectoryRolePermissionDAL();
        try
        {
            using (var scope = CreateTransactionScope())
            {
                directoryRolePermissionDAL.Open(DBConn.Conn);
                directoryRolePermissionDAL.Delete(projectId, subIds); //  delete 200000 datas (db already have 1000000 datas)
                directoryRolePermissionDAL.Insert(projectId, needInserts); //insert 200000 datas
                scope.Complete();
            }
        }
        catch (Exception e)
        {
            log.Error(e, e);
            throw;
        }
        finally
        {
            directoryDal.Connection.Close();
        }
    }

Expected behavior Transaction timeout and rollback

Additional context first time , the delete or insert took long time and after about 45 to 50 second catch a exception: Connection must be Open; current state is Closed

after that , use hediqlsql to delete the row in the table will prompt "/* SQL error (1205)：Lock wait timeout exceeded; try restarting transaction */"

The lock will never end till I restart whole web to make db table unlocked. (or kill process in db).