mysql error: Lock wait timeout exceeded

Question

I'm using mysql together with asp.net on my webserver. I also use a sqlite database to be able to use the database on another device. I need to send data between the two databases. This is something that needs to be done many times a day. This is an example of how I do it:

    using (MySqlTransaction mysqltransaction = mysqlconn.BeginTransaction())
            {
                using (mysqlcmd)
                {
                    //Creates parameters
                    MySqlParameter parmitemid = new MySqlParameter("?itemid", MySqlDbType.Int32);
                    mysqlcmd.Parameters.Add(parmitemid);
     //Creates more parameters, can't show them all...

                    //For each row in the sqlite table, do the insert
                    while (reader.Read())
                    {
                        //Sets the parameters, can't show them all
                        parmitemid.Value = reader.GetInt(1);
   mysqlcmd.CommandText = "INSERT INTO tblitem (itemid,......,customerid) VALUES (?itemid,......,(SELECT customerid FROM tblcustomer WHERE fid=?fid ORDER BY customerid DESC LIMIT 1))";
                        mysqlcmd.ExecuteNonQuery();
                    }
                }
                mysqltransaction.Commit();
            }

This is how I do it, as I wrote in the comments, there are many more parameters but they are all the same as the first one. When sending data between the databases I have like 20 different sets of this code doing almost the same thing as the code here. But as you understand I can't show them all..

Sometimes there can be 3000-4000 rows that needs to insert, but often small like 1-50.

The problem is that I run into this error from time to time "Lock wait timeout exceeded; try restarting transaction" and wonder what I can do to avoid it?

Also the whole solution seems slow. Can it be that I need to do a subselect for each insert query?

Thanks!

Answer 1

You can insert multiple rows at once using this syntax:

INSERT INTO table (col1, col2) VALUES ('a', 1), ('b', 2), ('c', 3)

This will insert three rows at once and is much faster.

Answer 2

Thanks!

But do you think I should use transaction here, like I do now? What do you think can cause the "Lock wait timeout exceeded; try restarting transaction"?

If I use your way, can I still use the subquery as I do now or will this make it slow?