views:

15

answers:

0

I have a Asp.net web service that I connect to and that insert, update or delete max 30 rows in a large MySql table using transaction to make the process faster. The whole process should just run in a few secs. Sometimes I can see in the logs that the user gets error:

Timeout expired. The timeout period elapsed prior to completion of the operation
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() 

Also I can sometimes see:

Deadlock found when trying to get lock; try restarting transaction
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() 

These errors doesn't happen every time, it's just random and it's really hard to find the problem. My code is the following:

MySqlCommand mysqlcmd = null;
MySqlConnection mysqlconn = null;
try
{
    mysqlconn = new MySqlConnection("Data Source=server;Database=db;User ID=user;Password=xxx;Pooling=false;Allow User Variables=True;charset=utf8;");
    mysqlconn.Open();
    mysqlcmd = new MySqlCommand();
    mysqlcmd.Connection = mysqlconn;
    using (MySqlTransaction mysqltransaction = mysqlconn.BeginTransaction())
    {
        using (mysqlcmd)
        {
            MySqlParameter id = new MySqlParameter("?id", MySqlDbType.Int32);
            mysqlcmd.Parameters.Add(id);

            foreach (Item item in list)
            {
                id.Value = item.id;
                if (item.type == 1) {
                    sql = "INSERT INTO t1 (id,title) VALUES (?id,'test');";
                } else if (item.type == 2) {
                    sql = "UPDATE t1 SET title='test' WHERE id=?id;";
                } else {
                    sql = "DELETE FROM t1 WHERE id=?id;";
                }
                mysqlcmd.CommandText = sql;
                mysqlcmd.ExecuteNonQuery();
            }
        }
        mysqltransaction.Commit();
    }
} 
catch (Exception exp) 
{
    LogError(exp);
}
finally
{
    if (null != mysqlcmd)
    {
        mysqlcmd.Dispose();
    }

    if (null != mysqlconn)
    {
        mysqlconn.Close();
        mysqlconn.Dispose();
    }
}