Deadlock is unrecoverable

Deadlock is unrecoverable

Postby jleblanc » Tue 19 Apr 2016 22:44

During periods of high activity in our system, sometimes things deadlock in the database. While we are trying to minimize the occurrence of deadlocks, they will still happen occasionally. What we are noticing though, is that when using the Devart connector for MySQL, our application does not recover from deadlocks. Once a deadlock has occurred, further database activity fails, and this is a serious problem.

The Environment
  • Windows 8.1 Pro 64bit
  • MariaDB 5.5.34 Win64
  • Devart DotConnect MySQL version 8.4.616.0

The Issue
I have created a sample program that demonstrates the issue we are having, it does the following (in order):

  1. Sets up some tables for testing
  2. Causes a deadlock between transactions across some of those tables
  3. After the deadlock has occurred, try to insert data into a completely separate table

We expect that despite the deadlock, all subsequent inserts should succeed; however this is not the case. The first insert seems to always work, but the next one fails with the error below.

Code: Select all
Unhandled Exception: Devart.Data.MySql.MySqlException: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ROLLBACK ONLY state
   at Devart.Data.MySql.bi.a()
   at Devart.Data.MySql.bi.i()
   at Devart.Data.MySql.ag.a(ab[]& A_0, Int32& A_1, Boolean A_2)
   at Devart.Data.MySql.ag.a(Byte[] A_0, Int32 A_1, Boolean A_2, String A_3)
   at Devart.Data.MySql.MySqlInternalConnection.BeginTransaction(Guid distributedIdentifier, IsolationLevel isolationLevel)
   at Devart.Common.DbConnectionInternal.EnlistToDistributedTransactionInternal(Transaction transaction)
   at Devart.Common.DbConnectionInternal.b()
   at Devart.Data.MySql.MySqlInternalConnection.b()
   at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
   at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
   at Devart.Common.DbConnectionBase.Open()
   at Devart.Data.MySql.MySqlConnection.m()
   at Devart.Data.MySql.MySqlConnection.Open()


This problem is reliably reproducible, and we've tried the following things to fix it:

  • Setting TransactionScopeLocal to true in the connection string - this made no difference
  • Setting innodb_support_xa to off on the server - this made no difference

What We've Noticed
No matter what, Devart always uses distributed transactions when a TransactionScope is involved, the TransactionScopeLocal setting appears to be completely ignored. MySQL's Connector/Net works as expected.

Below is the source code to reproduce the issue. The code is compatible with both Devart's dotConnect, and MySQL's Connector/Net. All of the InsertStuff() calls work when using MySQL's Connector/Net, but only one of them works when using Devart's dotConnect.

Is this a bug in dotConnect, or are we doing something wrong?

.Net Framework 4.5
Code: Select all
using System;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;
using Devart.Data.MySql;

namespace DevartDeadlockExperiment
{
   internal class Program
   {
      private const string DeadlockTable1 = "test_deadlock_table_1";
      private const string DeadlockTable2 = "test_deadlock_table_2";
      private const string TestTable = "test_deadlock_table_3";

      private static ManualResetEvent _sentinel1;
      private static ManualResetEvent _sentinel2;

      private static string _connectionString;

      private static string GetConnectionString()
      {
         return new MySqlConnectionStringBuilder
         {            
            Host = "localhost",
            Database = "testdatabase",
            UserId = "root",
            Password = "yourpassword",
            Port = 3306
         }.ConnectionString;
      }

      public static void RunDeadlocker2()
      {
         _sentinel2.WaitOne();

         using (var scope = new TransactionScope())
         using (var connection = new MySqlConnection(_connectionString))
         {
            connection.Open();
            using (var queryCommand = connection.CreateCommand())
            {
               queryCommand.CommandText =
                  "UPDATE test_deadlock_table_2 SET `testCol` = 2; UPDATE test_deadlock_table_1 SET `testCol` = 2;";

               Task.Delay(500).ContinueWith(t => _sentinel1.Set());
               queryCommand.ExecuteNonQuery();
            }
            scope.Complete();
         }
      }

      public static int RunDeadlocker1()
      {
         using (var scope = new TransactionScope())
         using (var connection = new MySqlConnection(_connectionString))
         {
            connection.Open();
            using (var queryCommand = connection.CreateCommand())
            {
               queryCommand.CommandText = "UPDATE test_deadlock_table_1 SET `testCol` = 1;";
               queryCommand.ExecuteNonQuery();
            }

            _sentinel2.Set();
            _sentinel1.WaitOne();

            using (var queryCommand = connection.CreateCommand())
            {
               queryCommand.CommandText = "UPDATE test_deadlock_table_2 SET `testCol` = 1;";
               queryCommand.ExecuteNonQuery();
            }
            scope.Complete();
         }

         return 0;
      }

      private static void CreateDeadlockTestTable(string tableName)
      {
         using (var connection = new MySqlConnection(_connectionString))
         {
            connection.Open();
            using (var queryCommand = connection.CreateCommand())
            {
               queryCommand.CommandText = "CREATE TABLE " + tableName + " (`testCol` INT(10) NULL); " + "INSERT INTO " +
                                          tableName + " SELECT 0;";
               queryCommand.ExecuteNonQuery();
            }
         }
      }

      private static void DropDeadlockTestTable(string tableName)
      {
         using (var connection = new MySqlConnection(_connectionString))
         {
            connection.Open();
            using (var queryCommand = connection.CreateCommand())
            {
               queryCommand.CommandText = "DROP TABLE " + tableName + ";";
               queryCommand.ExecuteNonQuery();
            }
         }
      }

      private static void InsertStuff()
      {
         Console.Write("Inserting...");
         using (var scope = new TransactionScope())
         using (var connection = new MySqlConnection(_connectionString))
         {
            connection.Open();
            {
               using (var command = connection.CreateCommand())
               {
                  command.CommandText =
                     "INSERT INTO test_deadlock_table_3(testCol) VALUES (555);";
                  command.ExecuteNonQuery();
               }
               scope.Complete();
            }
         }
         Console.WriteLine("done");
      }

      private static void Main(string[] args)
      {
         _sentinel1 = new ManualResetEvent(false);
         _sentinel2 = new ManualResetEvent(false);

         var dbMonitor = new MySqlMonitor {IsActive = true};

         _connectionString = GetConnectionString();

         CreateDeadlockTestTable(DeadlockTable1);
         CreateDeadlockTestTable(DeadlockTable2);
         CreateDeadlockTestTable(TestTable);

         var deadlocker2 = Task.Run(() => RunDeadlocker2());

         try
         {
            Task.Run(() => RunDeadlocker1()).Wait();
         }
         catch (Exception ex)
         {
            Console.WriteLine(ex);
         }
         finally
         {
            deadlocker2.Wait();
         }

         Console.WriteLine("got here");
         InsertStuff();
         InsertStuff();
         InsertStuff();
         InsertStuff();
         InsertStuff();

         DropDeadlockTestTable(DeadlockTable1);
         DropDeadlockTestTable(DeadlockTable2);
         DropDeadlockTestTable(TestTable);
      }
   }
}


If it matters, here is my my.ini file:

Code: Select all
[mysqld]
performance_schema
slow_query_log = 1
slow_query_log_file = D:/Databases/Logs/mysql-slow.log
log = D:/Databases/Logs/mysql.log
log-error = D:/Databases/Logs/mysql-error.log
log_warnings = 2
innodb_print_all_deadlocks = "ON"
datadir=D:/Databases
port=3306
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=511M
innodb_log_file_size=50M
character-set-server=utf8
event_scheduler="OFF"
max_connections=1000
wait_timeout=300
innodb_file_per_table
innodb_flush_log_at_trx_commit = 0
max_allowed_packet = 16M
[client]
port=3306
jleblanc
 
Posts: 14
Joined: Tue 19 Apr 2016 22:31

Re: Deadlock is unrecoverable

Postby Shalex » Tue 26 Apr 2016 06:26

We have reproduced the issue and are investigating it. We will notify you about the result.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Deadlock is unrecoverable

Postby Shalex » Thu 28 Apr 2016 11:23

The bug with processing errors within TransactionScope is fixed. We will notify you when the corresponding build of dotConnect for MySQL is available for download.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Deadlock is unrecoverable

Postby jleblanc » Thu 28 Apr 2016 16:40

Fantastic! Thanks!
jleblanc
 
Posts: 14
Joined: Tue 19 Apr 2016 22:31

Re: Deadlock is unrecoverable

Postby Shalex » Fri 06 May 2016 08:27

New build of dotConnect for MySQL 8.5.644 is available for download now: http://forums.devart.com/viewtopic.php?f=2&t=33634.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Deadlock is unrecoverable

Postby jleblanc » Mon 09 May 2016 23:03

Thanks, we're upgrading now.
jleblanc
 
Posts: 14
Joined: Tue 19 Apr 2016 22:31

Re: Deadlock is unrecoverable

Postby jleblanc » Wed 11 May 2016 18:12

This is resolved, thanks!
jleblanc
 
Posts: 14
Joined: Tue 19 Apr 2016 22:31


Return to dotConnect for MySQL