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

Question

5.00/5 (1 vote)

See more:

C#

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

Posted 30-May-11 2:02am

vrushali katkade

Updated 30-May-11 3:00am

Kim Togo

v5

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amul V V Wayangankar · Answer 1 · 2011-05-30T07:35:00

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

This error may also cause because u may not have saved ur database at proper location.

I think firstly set timeout to Default. Then save your database at proper location.Close ur database and then try to check ur application for Update query.This may solve ur timeout problem

Regards
Amul V V Wayangankar

karan joshua · Answer 2 · 2011-05-30T02:04:00

Solution 1

please check your database connection and set proper timeout... I think it is set to default...

Posted 30-May-11 2:04am

karan joshua

Comments

vrushali katkade 30-May-11 8:11am

please see my updates

Kim Togo · Answer 3 · 2011-05-30T03:03:00

Solution 2

Try to disable Connection pool and see how your code is working.

Pooling=false

Perhaps change Connection Lifetime or Max Pool Size

See link from MySql about connection string that is valid. http://dev.mysql.com/doc/refman/5.5/en/connector-net-connection-options.html[^]

Posted 30-May-11 3:03am

Kim Togo

Updated 30-May-11 20:23pm

v2

Comments

vrushali katkade 31-May-11 0:30am

when i will be set pooling in connection string it will be throws exception "Input string was not in a correct format."

Kim Togo 31-May-11 2:19am

Strange. Can you post the connection string you are using?

Kim Togo 31-May-11 2:23am

What version of MySql Connector/Net are you using ?

vrushali katkade 31-May-11 2:29am

in connection string i will be set the pool size & then set the pooling=false ,this exception solved but new exception is coming at
int k = da.Update(newupdateddt);this line "too many connections"

Kim Togo 31-May-11 2:43am

Okay, then I think that the problems is at the server end. In MySql you can limit the maximum connection.
But then, maybe you should rethink your design. Right now your flow is:

Connect -> Begin Transaction -> Update -> End Transaction -> Close

And if you do this many many time very fast, then you create a huge amount of connection to MySql.

On MySql server, try run a "SHOW PROCESSLIST" then you can see how many connections you have.

vrushali katkade 31-May-11 3:06am

i will be check the list 101 connections are in the list

vrushali katkade 31-May-11 3:14am

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"
solution on tis exception actually when the connection beyond the 101 connection it will be thrown exception too many connection . but when i will be create the new instance of the function of update using the thread then when the updates function finished the work it will be release the connection then why this problem is coming

vrushali katkade 31-May-11 3:19am

when i will be check for one connection it will be add the two process connection in process list why this will be happed.

vrushali katkade 31-May-11 3:39am

please help me

Kim Togo 31-May-11 3:41am

What version of MySql Connector/Net are you using ?

vrushali katkade 31-May-11 3:50am

version 5.2.2.0

Kim Togo 31-May-11 4:01am

That is an old driver. The latest version is version 6.3.6 - http://dev.mysql.com/downloads/connector/net/

And I know there has been bugfixs about connection pool not releasing connection to mysql etc..
If I where you, I would upgrade to version 6.3.6.

vrushali katkade 31-May-11 5:32am

i will be download the version 6.3.6 but it will be display the same exception
string ConnectionString = "Server=localhost;" +
"UID=root;" +
"DataBase=globasys;" +
"Allow Zero Datetime = false;" +
"Convert Zero Datetime=true;" +
"Pooling=false;";
my connection string given above

vrushali katkade 31-May-11 5:52am

any suggestion about this when i will be delete the pooling=false then it will be shown another exception as "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." it will be increased the size of the processlist

Kim Togo 31-May-11 5:54am

Try to set Connection Lifetime=2
It seams that connections to MySql is never closed and you hit the maximum connection allowed by the MySql.

vrushali katkade 31-May-11 6:48am

i will be set the Max_connections =200 & command timeout =90
it will be take the process list greater than 101 but after some time it will be show another exception as "lock wait timeout exceeded try restarting transaction"

vrushali katkade 31-May-11 6:57am

how to set the innodb_lock_wait_timeout in MYSQL

Kim Togo 31-May-11 6:59am

Is it necessary to start a transaction ? You only have 1 SQL statement.

Kim Togo 31-May-11 7:04am

How to change innodb_lock_wait_timeout
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

vrushali katkade 31-May-11 7:01am

the all process will be execute at every 1 seconds

Kim Togo 31-May-11 7:13am

Try remove transaction.

vrushali katkade 31-May-11 7:56am

i will be use this transaction for improve the performance of the int k = da.Update(newupdateddt); function because when the rows are increased into the datatable then it takes lots of time to update the table using the transaction it will be update the table within millisecond so i want speedup the performance also

Kim Togo 31-May-11 8:13am

I think the main problem is, that you have to many connection to MySql and resource is not free up again.

Perhaps a better design is to queue all SQL statements in a ConcurrentQueue<T>, and have 1 connection that takes from that queue and execute the SQL statements.

vrushali katkade 31-May-11 8:14am

can please tell how to improve the performance of the dataadapter.update() without transaction

vrushali katkade 31-May-11 8:16am

but it will be takes lots of time to dequeue i wants to update the at every 250 millisecond upto the 128000 rows

Kim Togo 31-May-11 9:08am

That is many update :-)
It still think you have to redesign how you what to update the database.

If it was me, I will make 1 thread that has 1 connection to MySql. And when there is data in the ConcurrentQueue≶T>, then dequeue it and update database.

The one that feeds the ConcurrentQueue≶T> only has to enqueue a SQL statement.

vrushali katkade 31-May-11 8:22am

it will not update the #innodb_lock_wait_timeout = 500

thatraja · Answer 4 · 2011-05-30T07:25:00

Solution 3

Look at this

How to Cope with Deadlocks[^]
found in below thread
How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'[^]

Posted 30-May-11 7:25am

thatraja