Q: MySql Deadlocks / Commit not unlocking in c#? Can anyone tell me why the following code can deadlock? I'm simulating our webserver on multiple threads in a console app.
The console app has 5 threads and updates 250 records on each thread.
I am finding that transaction.Commit() is not enough, I will get deadlocks, so it clearly isn't releasing the locks at that point.
Unless I put the transaction.Dispose() in and the Sleep(50ms), I consistently get deadlocks on innodb. If I turn the code into a sproc, then the sleep needs to be bigger to avoid deadlocks. I'm not sure it does avoid them totally actually, need to run it with more threads.
Closing the connection after the transaction is more reliable but in the web app ideally we want to have a connection per request for performance.
Also putting transaction.Dispose() is far more reliable in terms of avoiding deadlocks, than using (var transaction = ...
We are using .NET currently, not .NET core.
I would bet if I write the same program using SqlClient for Sql/Server it will work - I'm going to try that tomorrow.
Can anyone explain this? What am I doing wrong?
static void Main(string[] args)
{
    Console.WriteLine("GenerateBarcodesTestConsoleApp");

    var connectionString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

    var threads = Enumerable.Range(1, 5);

    Parallel.ForEach(threads, t =>
    {
        GenerateBarcodes2(t, connectionString, 250);
    });

    Console.WriteLine("Press any key to exit...");
    Console.ReadKey();
}

static void GenerateBarcodes2(int thread, string connectionString, int numberToGenerate)
{
    using (var con = new MySqlConnection(connectionString))
    {
        con.Open();

        var sql1 = "SELECT p.barcode, p..barcode_id " +
                    "FROM p_barcode p " +
                    "WHERE p.company_id = 1 " +
                    "AND SUBSTRING(p.barcode,1,2) = 'OK' " +
                    "AND players.in_use = 0 " +
                    "LIMIT 1 " +
                    "FOR UPDATE;";

        var sql2 = "UPDATE p_barcode SET in_use = 1 WHERE company_id = 1 AND barcode_id = ?barcode_id AND in_use = 0";

        for (int b = 0; b < numberToGenerate; b++)
        {
            using (var transaction = con.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
            {
                string barcode = string.Empty;
                int barcodeId = 0;

                using (var cmd = new MySqlCommand(sql1, con, transaction))
                {
                    var rdr = cmd.ExecuteReader();
                    if (rdr.Read())
                    {
                        barcode = (string)rdr["barcode"];
                        barcodeId = (int)rdr["barcode_id"];
                    }
                    rdr.Close();

                    Console.WriteLine(barcode);
                }

                if (barcodeId != 0)
                {
                    using (var cmd = new MySqlCommand(sql2, con, transaction))
                    {
                        cmd.Parameters.AddWithValue("barcode_id", barcodeId);

                        cmd.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
                System.Threading.Thread.Sleep(50);
            }
            //transaction.Dispose();
        }

        con.Close();
    }
}


A: In MariaDb, SKIP LOCKED is the solution to prevent deadlocks.
There isn't a perfect solution to prevent deadlocks, without redesigning the system to avoid two threads trying to update the same record at the same time, however adding a small sleep after commit transaction appears to help massively. 20ms was about right on my dev machines.
Does this suggest that commit returns before the database has actually committed the transaction and released the locks? Either way, this behaviour is the same for INNODB and MARIADB.
