c# - 我的数据库事务是否正确？

Question

我的代码非常慢，我至少需要 10 秒才能运行此方法。我有一个 arrayList，其中包含我需要检查的成分。对于这些成分中的每一种，我都需要更改数据库中的数量。所有项目都在原 Material 表中。我的代码在交易方面是否正确？我应该在哪里提交？在我的代码中，我将在更新一个 cookie 的成分时锁定整个表

public void ProducePallet(string prodName, DateTime date)
{
    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = conn;
    cmd.CommandText =
        "INSERT INTO pallets(prodName,prodDate,blocked,orderID) VALUES(@name,@date, false, null)";
    cmd.Prepare();
    cmd.Parameters.AddWithValue("@name", prodName);
    cmd.Parameters.AddWithValue("@date", date);
    cmd.ExecuteNonQuery();

    cmd = new MySqlCommand();
    cmd.Connection = conn;
    cmd.CommandText =
        "Select mName, quantity from recipes natural join ingredients where prodName=@pName";
    cmd.Prepare();
    cmd.Parameters.AddWithValue("@pName", prodName);
    cmd.ExecuteNonQuery();

    ArrayList ingredients = new ArrayList();
    string[] ingredientsPair;
    MySqlDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        ingredientsPair = new string[2];
        ingredientsPair[0] = rdr.GetString(0); //Ingredient name
        ingredientsPair[1] = rdr.GetString(1); //Quantity
        ingredients.Add(ingredientsPair);

    }
    rdr.Close();
    MySqlTransaction tr = conn.BeginTransaction();
    foreach (string[] i in ingredients)
    {

        cmd = new MySqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "Select quantityLeft from rawmaterials where mName=@mName for update";
        cmd.Prepare();
        cmd.Parameters.AddWithValue("@mName", i[0]);
        rdr = cmd.ExecuteReader();
        int mQuantity = 0;
        while (rdr.Read())
        {
            mQuantity = rdr.GetInt32(0);
        }
        rdr.Close();
        mQuantity -= Int32.Parse(i[1]) * 36 * 10 * 15 / 100;
        //cmd = new MySqlCommand();
        //cmd.Connection = conn;
        cmd.CommandText = "Update rawmaterials Set quantityLeft = @newQL where mName=@mName";
        cmd.Prepare();
        cmd.Parameters.AddWithValue("@newQL", mQuantity);
        // cmd.Parameters.AddWithValue("@mName", i[0]);
        cmd.ExecuteNonQuery();


    }

    try
    {

        tr.Commit();
    }
    catch (Exception e)
    {
        try
        {
            tr.Rollback();
        }
        catch (Exception e2)
        {
            Debug.WriteLine(e2.ToString());
        }
        return;
    }
}

原料表

 mName           quantityLeft
Bread crumbs    8959500
Butter  7782300
Chocolate   8946000
Chopped almonds 8623350
Cinnamon    8986500
Egg whites  8886600
Eggs    8867700
Fine-ground nuts    8757000
Flour   7774200
Ground, roasted nuts    8797500
Icing sugar 8780760
Marzipan    8716500
Potato starch   8990550
Roasted, chopped nuts   8829900
Sodium bicarbonate  8998920
Sugar   8446500
Vanilla 8999460
Vanilla sugar   8996220
Wheat flour 8990550

Answer 1

添加“SET AUTOCOMMIT=0”；和“开始交易；”在开始交易之前。

希望这对您有所帮助。