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

cMySQLnet

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

Best Answer

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.

Related Solutions

C# – The timeout period elapsed prior to obtaining a connection from the pool

The error means the connection pool is out of connections. The usual cause is forgetting to close a connection after you use it, like:

var con = new MySqlConnection("Server=YourDB;Database=YourDb;...");
con.Open();
var com = con.CreateCommand();
com.CommandText = "select * from YourTable";

This code forgets to close the connection, so the pool has one less connection. You could fix this by adding:

con.Close();

at the end. But if the query throws an exception, the Close will be skipped, and you're still down 1 connection.

A much better way is the using statement:

using (var con = new MySqlConnection("Server=YourDB;Database=YourDb;..."))
{
    con.Open();
    var com = con.CreateCommand();
    com.CommandText = "select * from YourTable";
}

You can troubleshoot this easily by adding max pool size at the end of your connection tring, like:

Server=YourDB;Database=YourDbUid=YourUser;Pwd=YourPwd;max pool size=1;

This gives you a pool size of 1, instantly triggering an error condition if you forget to free up your one connection.

C# – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

Looks like you have a query that is taking longer than it should. From your stack trace and your code you should be able to determine exactly what query that is.

This type of timeout can have three causes;

There's a deadlock somewhere
The database's statistics and/or query plan cache are incorrect
The query is too complex and needs to be tuned

A deadlock can be difficult to fix, but it's easy to determine whether that is the case. Connect to your database with Sql Server Management Studio. In the left pane right-click on the server node and select Activity Monitor. Take a look at the running processes. Normally most will be idle or running. When the problem occurs you can identify any blocked process by the process state. If you right-click on the process and select details it'll show you the last query executed by the process.

The second issue will cause the database to use a sub-optimal query plan. It can be resolved by clearing the statistics:

exec sp_updatestats

If that doesn't work you could also try

dbcc freeproccache

You should not do this when your server is under heavy load because it will temporarily incur a big performace hit as all stored procs and queries are recompiled when first executed. However, since you state the issue occurs sometimes, and the stack trace indicates your application is starting up, I think you're running a query that is only run on occasionally. You may be better off by forcing SQL Server not to reuse a previous query plan. See this answer for details on how to do that.

I've already touched on the third issue, but you can easily determine whether the query needs tuning by executing the query manually, for example using Sql Server Management Studio. If the query takes too long to complete, even after resetting the statistics you'll probably need to tune it. For help with that, you should post the exact query in a new question.

Best Answer

Related Solutions

C# – The timeout period elapsed prior to obtaining a connection from the pool

C# – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

Related Topic