Deadlock is unrecoverable
Posted: 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
I have created a sample program that demonstrates the issue we are having, it does the following (in order):
This problem is reliably reproducible, and we've tried the following things to fix it:
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
If it matters, here is my my.ini file:
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