title: mysql error: Lock wait timeout exceeded
body: <p>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:</p>
  
  <pre><code>    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();
              }
  </code></pre>
  
  <p>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..</p>
  
  <p>Sometimes there can be 3000-4000 rows that needs to insert, but often small like 1-50. </p>
  
  <p>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?</p>
  
  <p>Also the whole solution seems slow. Can it be that I need to do a subselect for each insert query?</p>
  
  <p>Thanks!</p>