The Environment
- Windows 8.1 Pro 64bit
- MariaDB 5.5.34 Win64
- Devart DotConnect MySQL version 8.4.616.0
I have created a sample program that demonstrates the issue we are having, it does the following (in order):
- Sets up some tables for testing
- Causes a deadlock between transactions across some of those tables
- After the deadlock has occurred, try to insert data into a completely separate table
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()
- 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
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);
}
}
}
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