Re: Simple transactions cause deadlocks for each other by UPDATE queries locking the primary key
Thanks for your effort spent on this.
I've reproduced the deadlock in a test environment. From that I got more details in the logging of the deadlock. The problem seems to be a foreign key from the order table pointing at rows in the member table. When I remove the foreign key but keep the underlying index everything works fine without any deadlocks. (Haven't dared to try this in production.)
By now I've also written a minimal and isolated example of what's getting wrong. It's a C# console app, maybe you're not used to that but the code is pretty straight forward (I think).
I'd really appreciate if you could skim it through to identify any obvious mistakes. If you don't find any then I file a bug report.
Table set up:
CREATE TABLE `test`.`member` (
`MemberId` INT NOT NULL AUTO_INCREMENT COMMENT '',
`LastLoginDate` DATETIME NULL COMMENT '',
`LoginCount` INT NULL COMMENT '',
`LastLoginIP` VARCHAR(20) NULL COMMENT '',
`LastUsedOrderId` INT NULL COMMENT '',
`CreationDate` DATETIME NOT NULL COMMENT '',
`RemovedDate` DATETIME NULL COMMENT '',
PRIMARY KEY (`MemberId`) COMMENT '');
CREATE TABLE `test`.`order` (
`OrderId` INT NOT NULL AUTO_INCREMENT COMMENT '',
`MemberId` INT NOT NULL COMMENT '',
PRIMARY KEY (`OrderId`) COMMENT '',
CONSTRAINT `FK_O_MID_M_MID`
FOREIGN KEY (`MemberId`)
REFERENCES `test`.`member` (`MemberId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Console app:
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace DeadlockMemberOrder
{
public class Program
{
public static string testdb = "server=localhost;database=test;uid=mathem_user;password=mathem;logging=true;";
static void Main(string[] args)
{
Console.WriteLine("---------------------------------------");
Console.WriteLine("- Testing deadlocks -");
Console.WriteLine("---------------------------------------");
Console.WriteLine("");
const int memberCount = 200;
Console.WriteLine("Setup:");
Console.WriteLine("======");
Console.WriteLine("# test members: {0}", memberCount);
Console.WriteLine("");
var memberIds = SetUp(memberCount);
Console.WriteLine("Executing test...");
Console.WriteLine("");
RunTest(memberIds);
}
static List<int> SetUp(int memberCount)
{
SetUpOrders();
return SetUpMembers(memberCount);
}
static void SetUpOrders()
{
const string sql_truncate = @"TRUNCATE TABLE `order`;";
using (var connection = new MySqlConnection(testdb))
{
connection.Open();
var cmd = new MySqlCommand(sql_truncate, connection);
cmd.ExecuteNonQuery();
}
}
static List<int> SetUpMembers(int memberCount)
{
const string sql_truncate = @"DELETE FROM member;";
using (var connection = new MySqlConnection(testdb))
{
connection.Open();
var cmd = new MySqlCommand(sql_truncate, connection);
cmd.ExecuteNonQuery();
}
var members = new List<int>();
for (var i = 0; i < memberCount; ++i)
{
const string sql = @"INSERT INTO member (CreationDate) VALUES (NOW()); SELECT LAST_INSERT_ID();";
using (var connection = new MySqlConnection(testdb))
{
connection.Open();
var cmd = new MySqlCommand(sql, connection);
members.Add((int) (ulong) cmd.ExecuteScalar());
}
}
return members;
}
static void RunTest(List<int> memberIds)
{
memberIds.ForEach(mid =>
Parallel.Invoke(
() => SetLoggedIn(mid),
() => SetLoggedIn(mid),
() => SetLoggedIn(mid),
() => SetLoggedIn(mid),
() => SetLoggedIn(mid),
() => SetLoggedIn(mid),
() => SetLoggedIn(mid),
() => SetLoggedIn(mid),
() => SetLoggedIn(mid),
() => SaveCart(mid)
)
);
}
static void SetLoggedIn(int memberId)
{
const string sql = @"UPDATE member SET LastLoginDate=NOW(), LoginCount=IFNULL(LoginCount,0)+1, LastLoginIP='127.0.0.1' WHERE MemberID=?p_MemberId and RemovedDate IS NULL";
var para = new[]
{
new MySqlParameter("?p_MemberId", MySqlDbType.Int32) {Value = memberId}
};
using (var connection = new MySqlConnection(testdb))
{
connection.Open();
var cmd = new MySqlCommand(sql, connection);
foreach (var p in para)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
}
}
static void SaveCart(int memberId)
{
using (var connection = new MySqlConnection(testdb))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var orderId = InsertOrder(connection, transaction, memberId);
UpdateMemberLastUsedOrderId(connection, transaction, memberId, orderId);
transaction.Commit();
}
}
}
static int InsertOrder(IDbConnection connection, IDbTransaction transaction, int memberId)
{
const string sql = @"INSERT INTO `order` (MemberId) VALUES (?p_MemberId); SELECT LAST_INSERT_ID();";
var para = new[]
{
new MySqlParameter("?p_MemberId", MySqlDbType.Int32) {Value = memberId}
};
var cmd = new MySqlCommand(sql, (MySqlConnection)connection, (MySqlTransaction)transaction);
foreach (var p in para)
{
cmd.Parameters.Add(p);
}
return (int)(ulong) cmd.ExecuteScalar();
}
static void UpdateMemberLastUsedOrderId(IDbConnection connection, IDbTransaction transaction, int memberId, int orderId)
{
const string sql = @"UPDATE member SET LastUsedOrderId = ?p_OrderId WHERE MemberId = ?p_MemberId AND RemovedDate IS NULL;";
var para = new[]
{
new MySqlParameter("?p_MemberId", MySqlDbType.Int32) {Value = memberId},
new MySqlParameter("?p_OrderId", MySqlDbType.Int32) {Value = orderId}
};
var cmd = new MySqlCommand(sql, (MySqlConnection)connection, (MySqlTransaction)transaction);
foreach (var p in para)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
}
}
}