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();
}
}