error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

Question

public void enqueue(object e)
{
      try
      {
            DataChangeEventArgs e113 = (DataChangeEventArgs)e;
            resetdatasource();
            for (i=0; i &lt; e113.sts.Length; i++)
            {
                  int hour113 = e113.sts[i].TimeStampNet.Hour;
                  int minute113 = e113.sts[i].TimeStampNet.Minute;
                  int second113 = e113.sts[i].TimeStampNet.Second;
                  int millisecond113 = e113.sts[i].TimeStampNet.Millisecond;
                  int year113 = e113.sts[i].TimeStampNet.Year;
                  int month113 = e113.sts[i].TimeStampNet.Month;
                  int day113 = e113.sts[i].TimeStampNet.Day;
                  DateTime sdate113 = new DateTime(year113, month113, day113, hour113, minute113, second113, millisecond113);

                  DataRow row1 = dt.NewRow();
                  row1["itemID"] = e113.sts[i].HandleClient;
                  row1["paramvalue"] = convert.ToString(e113.sts[i].DataValue);
                  row1["date_logged1"] = sdate113.ToString("dd-MM-yyyy HH:mm:ss.fff");
                  row1["Quality"] = e113.sts[i].Quality;
                  row1["date_logged"] = DateTime.FromFileTime(e113.sts[i].TimeStamp);
                  dt.Rows.Add(row1);
            }
            var threadupdate = new Thread(update);//start new thread & instance
            threadupdate.Start(dt);
      }
      catch { }
}


public void update(object dtnew)
            {
                  try
                  {
                           MySqlConnection con = new MySqlConnection(LocalConnection.GetLocalConnetionStringmysql());
                              DataSet oldvalueds=new DataSet();
                              DataTable newupdateddata = new DataTable();
                              MySqlDataAdapter da;


                              MySqlTransaction trans;
                              if (con.State == ConnectionState.Closed)
                                    con.Open();//throws exception
                              trans = con.BeginTransaction();
                              da = new MySqlDataAdapter();
                              da.InsertCommand = new MySqlCommand("INSERT INTO parameter (itemID,paramvalue, date_logged1,Quality,date_logged) " +
                                                               "VALUES (@itemID, @paramvalue,@date_logged1,@Quality,@date_logged)" +
                                                               "ON DUPLICATE KEY UPDATE " +
                                                               "itemID=VALUES(itemID),paramvalue=VALUES(paramvalue),date_logged1=VALUES(date_logged1),Quality=VALUES(Quality),date_logged=VALUES(date_logged)", con);

                              da.InsertCommand.Parameters.Add("@itemID", MySqlDbType.VarChar, 250, "itemID");
                              da.InsertCommand.Parameters.Add("@paramvalue", MySqlDbType.VarChar, 250, "paramvalue");
                              da.InsertCommand.Parameters.Add("@date_logged1", MySqlDbType.VarChar, 250, "date_logged1");
                              da.InsertCommand.Parameters.Add("@Quality", MySqlDbType.VarChar, 250, "Quality");
                              da.InsertCommand.Parameters.Add("@date_logged", MySqlDbType.VarChar, 250, "date_logged");

                           da.InsertCommand.Transaction = trans;
                              DataTable newupdateddt = (DataTable)dtnew;
                            int k = da.Update(newupdateddt);//exception thrown 2nd
                                 trans.Commit();
                                 con.Close();
                     }

                  catch(Exception ex) {

                     }
                  finally
                  {


                  }
            }

it will be thrown an exception like "error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

i will be set the timeout=200 in connection string this exception was thrown "Deadlock found when trying to get lock; try restarting transaction" thanks in advance

Marc Gravell Marc Gravell · Accepted Answer · 2011-05-30T12:08:42

You are setting up an open connection, etc - and not disposing it. It isn't actually clear to me why, since this is all for the adapter (da) that you don't actually seem to use. However: in general, you should use using around all such (IDisposable) objects:

using(var con = new MySqlConnection(...))
{
    ... all the code that uses the connection here ...
}

This ensures the object is disposed cleanly and quickly - putting the underlying connection back into the pool. The same applies to any other objects that implement IDisposable - for example, probably the transaction too.

error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

2 Answers